等待级别和等待事件相关动态性能视图

--等待级别和等待事件相关动态性能视图--通过查询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 55Application 17Cluster  50Commit  2Concurrency 33Configuration 24Idle 95Network  35Other745Queueing  9Scheduler  8System I/O 31User I/O 48       115214 rows selected.SYS@PROD1> select name from v$event_name where wait_class='Application' and rownum<15;NAME----------------------------------------------------------------enq: PW - flush prewarm buffersenq: RO - contentionenq: RO - fast object reuseenq: KO - fast object checkpointenq: TM - contentionenq: TX - row lock contentionWait for Table Lockenq: RC - Result Cache: ContentionStreams capture: filter callback waiting for rulesetStreams: apply reader waiting for DDL to applySQL*Net break/reset to clientSQL*Net break/reset to dblinkExternal Procedure initial connectionExternal Procedure call14 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.9User I/O11.6      0CPU       3.014      0Other2.14      0System I/O1.08      0Commit .12      0Configuration .03      0Concurrency .02      0Network    0      0Application   0      010 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 .01293995911 rows selected.