部分sql的user_io_wait_time值较大
2022-05-14
部分sql的user_io_wait_time值较大
背景:报“针对近一个月的sql语句执行进行分析,每天执行较频繁的sql中,有几个sql的user_io_wait_time值较大,建议进行优化
oracle sql 响应时间分析
1、按照IO等待取top5(https://blog.csdn.net/fengsd_sy/article/details/51322766#/)
select *
from
(select sql_text,
sql_id,
elapsed_time,
cpu_time,
user_io_wait_time
from sys.v_$sqlarea
order by 5 desc)
where rownum < 6;
2、根据步骤1获取具体SQL详情
select event,
time_waited,
owner,
object_name,
current_file#,
current_block#
from sys.v_$active_session_history a,
sys.dba_objects b
where sql_id = '6gvch1xu9ca3g' and
a.current_obj# = b.object_id and
time_waited <> 0;