2MUCH

部分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;