oracle v$sql last_load_time,Oracle 等待事件V$视图

等待事件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 msEVENT                            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报表)。