会话级别
--当然也可以查看具体哪个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;
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;
总体来讲,响应时间给了用户更加直观的观察数据库的度量!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/317034/viewspace-2096871/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/317034/viewspace-2096871/