Wait for event

[Email protected]>select count (*) from V$event_name;

COUNT (*)

----------

1142

[Email Protected]>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 Scheduler 8

3871361733 Cluster 50

644977587 Queueing 9

[Email protected]>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

[Email protected]>select name,wait_class from v$event_name where wait_class= ' Idle ';

[Email protected]>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 record multiple waiting cumulative information for a session in the life cycle

[Email protected]>select sid,event,wait_class,time_waited from V$session_eventwhere sid=11 ORDER BY time_waited Desc ;

SID EVENT Wait_class time_waited

---------------- ------------------------------------------------------------------------- ---------------------- -------------------------------------- -------------------------

Sql*net message from client Idle 260608

Streams aq:waiting for messages in the queue Idle 100

Log file Sync Commit 47

One DB file sequential read User I/o 47

Disk file Operations I/O User I/o 16

Latch:shared Pool Concurrency 7

Sql*net message to client Network 0

Events in Waitclass and other 0

Latch:cache buffers Chains Concurrency 0

One db file scattered read User I/O 0

V$system_event Records accumulated information, records the average waiting calculation, and cannot know the duration of individual waits

[Email protected]>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 record the specific distribution of a wait event

[Email protected]>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

Wait for event