会话级别
--当然也可以查看具体哪个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;
总体来讲,响应时间给了用户更加直观的观察数据库的度量!