等待事件V$视图

本节包含一些显示等待事件的V$ 脚本。从个人角度来说，我更喜欢使用STATSPACK 报表、AWR 报表
或企业管理器来查找等待事件。也就是说，有些很好的视图可以查看等待事件。 Oracle 10gR2 中添加了
一些新的视图，但最幸运的是在V$SESSION_WAIT 中找到的东西现在在V$SESSION 中可以找到。
马上该谁等待——查询V$SESSION_WAIT / V$SESSION
select event, sum(decode(wait_time,0,1,0)) "Waiting Now",
sum(decode(wait_time,0,0,1)) "Previous Waits",
count(*) "Total"
from v$session_wait
group by event
order by count(*);
WAIT_TIME = 0 means that it’s waiting
WAIT_TIME > 0 means that it previously waited this many ms
317
EVENT Waiting Now Previous Waits Total
--------------------------- ------------ -------------- -------
db file sequential read 0 1 1
db file scattered read 2 0 2
latch free 0 1 1
enqueue 2 0 2
SQL*Net message from client 0 254 480
...
select event, sum(decode(wait_time,0,1,0)) "Waiting Now",
sum(decode(wait_time,0,0,1)) "Previous Waits",
count(*) "Total"
from v$session
group by event
order by count(*);
EVENT Waiting Now Previous Waits Total
--------------------------- ------------ -------------- --------
db file sequential read 0 1 1
db file scattered read 2 0 2
latch free 0 1 1
enqueue 2 0 2
SQL*Net message from client 0 254 480
...
马上该谁等待；SPECIFIC Waits——查询V$SESSION_WAIT
SELECT /*+ ordered */ sid, event, owner, segment_name, segment_type,p1,p2,p3
FROM v$session_wait sw, dba_extents de
WHERE de.file_id = sw.p1
AND sw.p2 between de.block_id and de.block_id+de.blocks – 1
AND (event = 'buffer busy waits' OR event = 'write complete waits')
AND p1 IS NOT null
ORDER BY event,sid;
谁在等待 – 最后10 个等待数——查询V$SESSION_WAIT_HISTORY
SELECT /*+ ordered */ sid, event, owner, segment_name, segment_type,p1,p2,p3
FROM v$session_wait_history sw, dba_extents de
WHERE de.file_id = sw.p1
AND sw.p2 between de.block_id and de.block_id+de.blocks – 1
AND (event = 'buffer busy waits' OR event = 'write complete waits')
AND p1 IS NOT null
ORDER BY event,sid;
318
查找P1, P2, P3 代表什么——查询 V$EVENT_NAME
col name for a20
col p1 for a10
col p2 for a10
col p3 for a10
select event#,name,parameter1 p1,parameter2 p2,parameter3 p3
from v$event_name
where name in ('buffer busy waits', 'write complete waits');
EVENT# NAME P1 P2 P3
------------- -------------------- ---------- ---------- ----------
143 write complete waits file# block#
145 buffer busy waits file# block# id
会话开始后的所有等待数——查询 V$SESSION_EVENT
select sid, event, total_waits, time_waited, event_id
from v$session_event
where time_waited > 0
order by time_waited;
SID EVENT TOTAL_WAITS TIME_WAITED
---------- ------------------------------ ----------- -----------
159 process startup 2 1
167 latch: redo allocation 4 1
168 log buffer space 2 3
166 control file single write 5 4
...
类的所有会话等待数——查询V$SESSION_WAIT_CLASS
select sid, wait_class, total_waits
from v$session_wait_class;
SID WAIT_CLASS TOTAL_WAITS
---------- -------------------- -----------
168 Other 2
168 Concurrency 1
168 Idle 12825
168 User I/O 12
168 System I/O 4448
169 Other 1
169 Idle 12812
170 Idle 13527
系统启动后的所有等待数——查询V$SYSTEM_EVENT
319
select event, total_waits, time_waited, event_id
from v$system_event
where time_waited > 0
order by time_waited;
EVENT TOTAL_WAITS TIME_WAITED EVENT_ID
----------------------------------- ----------- ----------- ----------
enq: TX - row lock contention 1196 366837 310662678
enq: TM - contention 170 52074 668627480
db file sequential read 17387 3163 2652584166
control file parallel write 12961 23117 4078387448
db file scattered read 4706 15762 506183215
class slave wait 20 10246 1055154682
类的系统等待数——查询V$SYSTEM_WAIT_CLASS
select wait_class, total_waits
from v$system_wait_class
order by total_waits desc;
WAIT_CLASS TOTAL_WAITS
-------------------- -----------
Idle 161896
Other 65308
System I/O 24339
User I/O 22227
Application 1404
Commit 524
Network 522
Concurrency 221
Configuration 55
...
类的系统等待数——查询V$ACTIVE_SESSION_HISTORY
--In the query below, the highest count session is leader in non-idle wait events.
select session_id,count(1)
from v$active_session_history
group by session_id
order by 2;
In the query below, find the SQL for the leader in non-idle wait events.
select c.sql_id, a.sql_text
from v$sql a, (select sql_id,count(1)
from v$active_session_history b where sql_id is not null
group by sql_id
320
order by 2 desc) c
where rownum <= 5
order by rownum;
技巧：
在Oracle 10g 中V$SESSION_WAIT 中的所有等待事件列现在都在V$SESSION 中。因此，
确保查询等待信息的 V$SESSION，因为它是一个更快的视图。V$ACTIVE_SESSION_HISTORY
(ASH)将许多重要统计数据合并为一个视图或一个报表(ASH 报表)。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25198367/viewspace-707608/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25198367/viewspace-707608/