首页 > 代码库 > 等待事件
等待事件
SYS@ora11g>select count(*) from v$event_name;
COUNT(*)
----------
1142
SYS@ora11g>select wait_class_id,wait_class#,wait_class,count(*) from v$event_name
group by wait_class_id,wait_class#,wait_class order by wait_class#;
WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS COUNT(*)
--------------------- ------------------ -------------------- ----------
1893977003 0 Other 736
4217450380 1 Application 17
3290255840 2 Configuration 24
4166625743 3 Administrative 55
3875070507 4 Concurrency 33
3386400367 5 Commit 2
2723168908 6 Idle 95
2000153315 7 Network 35
1740759767 8 User I/O 48
4108307767 9 System I/O 30
2396326234 10 Scheduler 8
3871361733 11 Cluster 50
644977587 12 Queueing 9
SYS@ora11g>select wait_class,total_waits,time_waited from v$system_wait_class order by time_waited desc;
WAIT_CLASS TOTAL_WAITS TIME_WAITED
-------------------- ------------------------------- -----------
Idle 1398061 1.2199E+10
System I/O 221278 1005215797
Other 106012 849684041
Commit 14136 272539568
Concurrency 7539 222714566
User I/O 43155 4061687
Application 27216 2592962
Scheduler 192 5584
Configuration 32 569
Network 330911 203
SYS@ora11g>select name,wait_class from v$event_name where wait_class=‘Idle‘;
SYS@ora11g>show parameter statis
NAME_COL_PLUS_SHOW_P TYPE VALUE_COL_PLUS_SHOW_
---------------------------------------- ----------- --------------------
optimizer_use_pendin boolean FALSE
g_statistics
statistics_level string TYPICAL
timed_os_statistics integer 0
timed_statistics boolean TRUE
v$session_event记录一个会话在生命周期的多个等待的累积信息
SYS@ora11g>select sid,event,wait_class,time_waited from v$session_eventwhere sid=11 order by time_waited desc;
SID EVENT WAIT_CLASS TIME_WAITED
---------------- ------------------------------------------------------------------------- ------------------------------------------------------------ -------------------------
11 SQL*Net message from client Idle 260608
11 Streams AQ: waiting for messages in the queue Idle 100
11 log file sync Commit 47
11 db file sequential read User I/O 47
11 Disk file operations I/O User I/O 16
11 latch: shared pool Concurrency 7
11 SQL*Net message to client Network 0
11 events in waitclass Other Other 0
11 latch: cache buffers chains Concurrency 0
11 db file scattered read User I/O 0
v$system_event记录累积信息，记录等待的平均计算，无法知道个别等待消耗时间长短
SYS@ora11g>select event,total_waits,wait_class,time_waited,average_wait from v$system_event where event=‘latch: shared pool‘;
EVENT TOTAL_WAITS WAIT_CLASS TIME_WAITED AVERAGE_WAIT
-------------------------------- --------------------- -------------------- ---------------------- ------------
latch: shared pool 2970 Concurrency 236069 79.48
v$event_histogram记录一个等待事件的具体分布
SYS@ora11g>select event,wait_time_milli,wait_count from v$event_histogram where event=‘latch: shared pool‘;
EVENT WAIT_TIME_MILLI WAIT_COUNT
---------------------------- -------------------------- -------------------
latch: shared pool 1 1428
latch: shared pool 2 221
latch: shared pool 4 155
latch: shared pool 8 163
latch: shared pool 16 207
latch: shared pool 32 200
latch: shared pool 64 176
latch: shared pool 128 136
latch: shared pool 256 95
latch: shared pool 512 72
latch: shared pool 1024 24
latch: shared pool 2048 18
latch: shared pool 4096 12
latch: shared pool 8192 15
latch: shared pool 16384 17
latch: shared pool 32768 2
latch: shared pool 65536 15
latch: shared pool 131072 14
等待事件