等待事件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

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;

查找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

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
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博客

Oracle 等待事件V$视图

最新文章