--等待级别和等待事件相关动态性能视图
--通过查询v$event_name可以得知当前版本有哪些等待事件及其等待级别。
SYS@PROD1> select wait_class, count(*) from v$event_name group by rollup(wait_class) order by wait_class;
WAIT_CLASS COUNT(*)
---------------------------------------------------------------- ----------
Administrative 55
Application 17
Cluster 50
Commit 2
Concurrency 33
Configuration 24
Idle 95
Network 35
Other 745
Queueing 9
Scheduler 8
System I/O 31
User I/O 48
1152
14 rows selected.
SYS@PROD1> select name from v$event_name where wait_class='Application' and rownum<15;
NAME
----------------------------------------------------------------
enq: PW - flush prewarm buffers
enq: RO - contention
enq: RO - fast object reuse
enq: KO - fast object checkpoint
enq: TM - contention
enq: TX - row lock contention
Wait for Table Lock
enq: RC - Result Cache: Contention
Streams capture: filter callback waiting for ruleset
Streams: apply reader waiting for DDL to apply
SQL*Net break/reset to client
SQL*Net break/reset to dblink
External Procedure initial connection
External Procedure call
14 rows selected.
--对于会话级别的统计信息,可以通过v$session_wait_class。
SYS@PROD1> select sid from v$mystat where rownum=1;
SID
----------
1
SYS@PROD1> SELECT wait_class,
2 round(time_waited, 3) AS time_waited,
3 round(1E2 * ratio_to_report(time_waited) OVER (), 1) AS "%"
4 FROM (
5 SELECT sid, wait_class, time_waited / 1E2 AS time_waited
6 FROM v$session_wait_class
7 WHERE total_waits > 0
8 UNION ALL
9 SELECT sid, 'CPU', value / 1E6
10 FROM v$sess_time_model
11 WHERE stat_name = 'DB CPU'
12 )
13 WHERE sid = 1
14 ORDER BY 2 DESC;
WAIT_CLASS TIME_WAITED %
---------------------------------------------------------------- ----------- ----------
Idle 25202.32 99.9
User I/O 11.6 0
CPU 3.014 0
Other 2.14 0
System I/O 1.08 0
Commit .12 0
Configuration .03 0
Concurrency .02 0
Network 0 0
Application 0 0
10 rows selected.
--对于一些等待事件,可以查询得到平均延迟的信息。
SYS@PROD1> select time_waited_micro/total_waits/1E3 as avg_wait_ms from v$system_event where event = 'db file sequential read';
AVG_WAIT_MS
-----------
10.958592
--Oracle为每个等待事件提供直方图,v$event_histogram。
SYS@PROD1> SELECT wait_time_milli, wait_count, 100*ratio_to_report(wait_count) OVER() AS "%"
2 FROM v$event_histogram
3 WHERE event = 'db file sequential read';
WAIT_TIME_MILLI WAIT_COUNT %
--------------- ---------- ----------
1 4177 54.050207
2 145 1.876294
4 169 2.186853
8 559 7.23343685
16 1294 16.7443064
32 775 10.0284679
64 377 4.87836439
128 171 2.21273292
256 55 .711697723
512 5 .064699793
1024 1 .012939959
11 rows selected.
等待级别和等待事件相关动态性能视图
最新推荐文章于 2022-04-17 18:42:47 发布