Oracle 等待事件V$檢視

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
...

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;

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;

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

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
...

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

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

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
...

--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;