oracle 响应时间分析（二）

会话级别

--当然也可以查看具体哪个SESSION 

select a.sid,
       b.username,
       a.wait_class,
       a.total_waits,
       round((a.time_waited / 100), 2) time_waited_secs
  from sys.v_$session_wait_class a, sys.v_$session b
 where b.sid = a.sid
   and b.username is not null
   and a.wait_class != 'Idle'
 order by 5 desc;

 

--下钻到某个时间的所有回话

<span style="font-weight: normal;">select sess_id,
       username,
       program,
       wait_event,
       sess_time,
       round(100 * (sess_time / total_time), 2) pct_time_waited
  from (select a.session_id sess_id,
               decode(session_type, 'background', session_type, c.username) username,
               a.program program,
               b.name wait_event,
               sum(a.time_waited) sess_time
          from sys.v_$active_session_history a,
               sys.v_$event_name             b,
               sys.dba_users                 c
         where a.event# = b.event#
           and a.user_id = c.user_id
           and sample_time > '21-NOV-04 12:00:00 AM'
           and sample_time < '21-NOV-04 05:00:00 AM'
           and b.wait_class = 'User I/O'
         group by a.session_id,
                  decode(session_type,
                         'background',
                         session_type,
                         c.username),
                  a.program,
                  b.name),
       (select sum(a.time_waited) total_time
          from sys.v_$active_session_history a, sys.v_$event_name b
         where a.event# = b.event#
           and sample_time > '21-NOV-04 12:00:00 AM'
           and sample_time < '21-NOV-04 05:00:00 AM'
           and b.wait_class = 'User I/O')
 order by 6 desc;</span>

 

SQL级别

从ORACLE10g开始以下等待相关的事件列被加入到了session视图中。

• APPLICATION_WAIT_TIME
• CONCURRENCY_WAIT_TIME
• CLUSTER_WAIT_TIME
• USER_IO_WAIT_TIME
• PLSQL_EXEC_TIME
• JAVA_EXEC_TIME

--按照IO等待取top5

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;

--再次进行下钻获得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;

 

总体来讲，响应时间给了用户更加直观的观察数据库的度量！