Oracle 等待事件

文章由LinuxBoy分享于2019-03-30 06:03:05热评（54）

Oracle 等待事件

等待事件分为空闲等待事件和非空闲（non-idle）等待事件。空闲事件指Oracle正等待某种工作，不用过多注意这部分事件。非空闲等待事件专门针对Oracle的活动，指数据任务或应用运行过程发生的等待。

v$event_name是一个很好的学习入口。

SYS@ orcl> select name, parameter1, parameter2, parameter3 from v$event_name where name = 'db file scattered read';
NAME PARAMETER1 PARAMETER2 PARAMETER3
-------------------- -------------------- -------------------- --------------------
db file scattered re file# block# blocks

SYS@ orcl> select * from v$system_wait_class order by time_waited;
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED TOTAL_WAITS_FG TIME_WAITED_FG
------------- ----------- -------------------- ----------- ----------- -------------- --------------
2000153315 7 Network 26 0 17 0
4166625743 3 Administrative 2 60 1 10
3386400367 5 Commit 16 60 14 53
4108307767 9 System I/O 5082 1162 2764 50
3875070507 4 Concurrency 67 1548 32 479
1893977003 0 Other 279 1596 145 977
2396326234 10 Scheduler 143 1649 143 1649
1740759767 8 User I/O 6418 11155 4661 7426
2723168908 6 Idle 2294 279039 527 36568

SYS@ orcl> select wait_class#, wait_class_id, wait_class, count(*) from v$event_name group by wait_class#, wait_class_id, wait_class order by wait_class#;
WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS COUNT(*)
----------- ------------- -------------------- ----------
0 1893977003 Other 719
1 4217450380 Application 17
2 3290255840 Configuration 24
3 4166625743 Administrative 54
4 3875070507 Concurrency 32
5 3386400367 Commit 2
6 2723168908 Idle 94
7 2000153315 Network 35
8 1740759767 User I/O 45
9 4108307767 System I/O 30
10 2396326234 Scheduler 7
11 3871361733 Cluster 50
12 644977587 Queueing 9

SYS@ orcl> select name, wait_class from v$event_name where wait_class = 'Idle';
NAME WAIT_CLASS
------------------------------------------------------------ --------------------
pmon timer Idle
VKTM Logical Idle Wait Idle
VKTM Init Wait for GSGA Idle
IORM Scheduler Slave Idle Wait Idle
rdbms ipc message Idle
i/o slave wait Idle
VKRM Idle Idle
wait for unread message on broadcast channel Idle
wait for unread message on multiple broadcast channels Idle
class slave wait Idle
KSV master wait Idle
PING Idle
watchdog main loop Idle
DIAG idle wait Idle
ges remote message Idle
gcs remote message Idle
heartbeat monitor sleep Idle
SGA: MMAN sleep for component shrink Idle
MRP redo arrival Idle
LNS ASYNC archive log Idle
LNS ASYNC dest activation Idle
LNS ASYNC end of log Idle
simulated log write delay Idle
LGWR real time apply sync Idle
parallel recovery slave idle wait Idle
LogMiner builder: idle Idle
LogMiner builder: branch Idle
LogMiner preparer: idle Idle
LogMiner reader: log (idle) Idle
LogMiner reader: redo (idle) Idle
LogMiner client: transaction Idle
LogMiner: other Idle
LogMiner: activate Idle
LogMiner: reset Idle
LogMiner: find session Idle
LogMiner: internal Idle
Logical Standby Apply Delay Idle
parallel recovery coordinator waits for slave cleanup Idle
parallel recovery control message reply Idle
parallel recovery slave next change Idle
PX Deq: Txn Recovery Start Idle
PX Deq: Txn Recovery Reply Idle
fbar timer Idle
smon timer Idle
PX Deq: Metadata Update Idle
Space Manager: slave idle wait Idle
PX Deq: Index Merge Reply Idle
PX Deq: Index Merge Execute Idle
PX Deq: Index Merge Close Idle
PX Deq: kdcph_mai Idle
PX Deq: kdcphc_ack Idle
shared server idle wait Idle
dispatcher timer Idle
cmon timer Idle
pool server timer Idle
JOX Jit Process Sleep Idle
jobq slave wait Idle
pipe get Idle
PX Deque wait Idle
PX Idle Wait Idle
PX Deq: Join ACK Idle
PX Deq Credit: need buffer Idle
PX Deq Credit: send blkd Idle
PX Deq: Msg Fragment Idle
PX Deq: Parse Reply Idle
PX Deq: Execute Reply Idle
PX Deq: Execution Msg Idle
PX Deq: Table Q Normal Idle
PX Deq: Table Q Sample Idle
Streams fetch slave: waiting for txns Idle
Streams: waiting for messages Idle
Streams capture: waiting for archive log Idle
single-task message Idle
SQL*Net message from client Idle
SQL*Net vector message from client Idle
SQL*Net vector message from dblink Idle
PL/SQL lock timer Idle
Streams AQ: emn coordinator idle wait Idle
EMON slave idle wait Idle
Streams AQ: waiting for messages in the queue Idle
Streams AQ: waiting for time management or cleanup tasks Idle
Streams AQ: delete acknowledged messages Idle
Streams AQ: deallocate messages from Streams Pool Idle
Streams AQ: qmn coordinator idle wait Idle
Streams AQ: qmn slave idle wait Idle
Streams AQ: RAC qmn coordinator idle wait Idle
HS message to agent Idle
ASM background timer Idle
auto-sqltune: wait graph update Idle
WCR: replay client notify Idle
WCR: replay clock Idle
WCR: replay paused Idle
JS external job Idle
cell worker idle Idle

v$session中还增加了BLOCKING_SESSION等字段，以前需要通过dba_waiters等视图才能获得的信息。

SYS@ orcl> select sid, username, sql_exec_start, sql_exec_id from v$session;
SID USERNAME SQL_EXEC_START SQL_EXEC_ID
---------- ------------------------------ ------------------- -----------
......
49 SYS 2013-03-01 23:30:28 16777216

一个活动事务可能经历很多等待，v$session_wait视图记录的是累积信息。
SYS@ orcl> select sid, event, time_waited, time_waited_micro from v$session_event where sid = 49 order by 3;
SID EVENT TIME_WAITED TIME_WAITED_MICRO
---------- ------------------------------ ----------- -----------------
49 Disk file operations I/O 0 557
49 SQL*Net message to client 0 245
49 SQL*Net message from client 35706 357060895

v$session_event的信息和会话生命周期相关，这些信息同时会被累积到v$system_event视图作为数据库整体等待数据保存。

推荐文章：

[Oracle] 常见的等待事件

Oracle 等待事件