发布时间 : 星期五 文章详细分析 - ORACLE - AWR报告 - 图文更新完毕开始阅读9611a63ed15abe23492f4d72
SQL*Net message from client SQL*Net message to client SQL*Net more data to client virtual circuit status client message SQL*Net message from client 下面是关于这里的常见的等待事件和解决方法的一个快速预览 等待事件 一般解决方法 Sequential Read 调整相关的索引和选择合适的驱动行源 Scattered Read 表明出现很多全表扫描。优化code，cache小表到内存中。 Free Buffer 增大DB_CACHE_SIZE，增大checkpoint的频率，优化代码 Buffer Busy Segment header 增加freelist或者freelistgroups Buffer Busy Data block 隔离热块；使用反转索引；使用更小的块；增大表的initrans Buffer Busy Undo header 增加回滚段的数量或者大小 Buffer Busy Undo block Commit more；增加回滚段的数量或者大小 Latch Free 检查具体的等待latch类型，解决方法参考后面介绍 Enqueue–ST 使用本地管理的表空间或者增加预分配的盘区大小 Enqueue–HW 在HWM之上预先分配盘区 Enqueue–TX4 在表或者索引上增大initrans的值或者使用更小的块 Log Buffer Space 增大LOG_BUFFER，改善I/O Log File Switch 增加或者增大日志文件 Log file sync 减小提交的频率；使用更快的I/O；或者使用裸设备 Write complete waits 增加DBWR；提高CKPT的频率； Time Model Statistics
?
? ?
Total time in database user-calls (DB Time): 663s
Statistics including the word \contribute to the DB time statistic
Ordered by % or DB time desc, Statistic name Statistic Name DB CPU Time (s) % of DB Time 514.50 77.61 sql execute elapsed time parse time elapsed PL/SQL execution elapsed time hard parse elapsed time connection management call elapsed time hard parse (sharing criteria) elapsed time repeated bind elapsed time PL/SQL compilation elapsed time failed parse elapsed time DB time background elapsed time background cpu time 482.27 3.76 0.50 0.34 0.08 0.00 0.00 0.00 0.00 662.97 185.19 67.48 72.74 0.57 0.08 0.05 0.01 0.00 0.00 0.00 0.00 此节显示了各种类型的数据库处理任务所占用的CPU时间。
DB time=报表头部显示的db time=cpu time + all of nonidle wait event time
Back to Wait Events Statistics Back to Top
Wait Class 等待事件的类型
? ? ? ? ?
s - second
cs - centisecond - 100th of a second ms - millisecond - 1000th of a second
us - microsecond - 1000000th of a second ordered by wait time desc, waits desc
查询Oracle 10gR1提供的12个等待事件类：
select wait_class#, wait_class_id, wait_class from v$event_name group by wait_class#, wait_class_id, wait_class order by wait_class#;
Wait Class User I/O System I/O Network Cluster Other Application Concurrency Commit Configuration Waits 66,837 28,295 1,571,450 210,548 81,783 333,155 5,182 919 25,427 %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn 0.00 0.00 0.00 0.00 71.82 0.00 0.04 0.00 99.46 120 93 66 29 28 16 5 4 1 2 3 0 0 0 0 1 4 0 11.94 5.05 280.72 37.61 14.61 59.51 0.93 0.16 4.54 Back to Wait Events Statistics Back to Top
Wait Events 现实非空闲等待事件 后面是空闲等待事件
? ? ? ? ?
s - second
cs - centisecond - 100th of a second ms - millisecond - 1000th of a second
us - microsecond - 1000000th of a second
ordered by wait time desc, waits desc (idle events last)
（1）查询所有等待事件及其属性：
select event#, name, parameter1, parameter2, parameter3 from v$event_name order by name;
（2）查询Oracle 10gR1提供的12个等待事件类：
select wait_class#, wait_class_id, wait_class from v$event_name group by wait_class#, wait_class_id, wait_class order by wait_class#;
wait_event.doc
下面显示的内容可能来自下面几个视图）
V$EVENT_NAME视图包含所有为数据库实例定义的等待事件。
V$SYSTEM_EVENT视图显示自从实例启动后，所有Oracle会话遇到的所有等待事件的总计统计。
V$SESSION_EVENT视图包含当前连接到实例的所有会话的总计等待事件统计。该视图包含了V$SYSTEM_EVENT视图中出现的所有列。它记录会话中每一个等待事件的总等待次数、已等待时间和最大等待时间。SID列标识出独立的会话。每个会话中每个事件的最大等待时间在MAX_WAIT列中追踪。通过用SID列将V$SESSION_EVENT视图和V$SESSION视图结合起来，可得到有关会话和用户的更多信息。
V$SESSION_WAIT视图提供关于每个会话正在等待的事件或资源的详细信息。该视图在任何给定时间，只包含每个会话的一行活动的或不活动的信息。
自从OWI在Oracle 7.0.12中引入后，就具有下来4个V$视图：
? V$EVENT_NAME ? V$SESSION_WAIT ? V$SESSION_EVENT ? V$SYSTEM_EVENT 除了这些等待事件视图之外，Oracle 10gR1中引入了下列新视图以从多个角度显示等待信息：
? V$SYSTEM_WAIT_CLASS ? V$SESSION_WAIT_CLASS ? V$SESSION_WAIT_HISTORY ? V$EVENT_HISTOGRAM
? V$ACTIVE_SESSION_HISTORY
然而，V$SESSION_WAIT、V$SESSION_WAIT和V$SESSION_WAIT仍然是3个重要的视图，它们提供了不同粒度级的等待事件统计和计时信息。三者的关系如下：
V$SESSION_WAIT ? V$SESSION_EVENT ? V$SYSTEM_EVENT
Event SQL*Net more data from client log file parallel write db file sequential read db file parallel write db file scattered read direct path write reliable message Waits 27,319 5,497 7,900 4,806 10,310 42,724 355 %Time -outs 0.00 0.00 0.00 0.00 0.00 0.00 2.82 Total Wait Time (s) 64 47 35 34 31 30 18 Avg wait (ms) 2 9 4 7 3 1 49 Waits /txn 4.88 0.98 1.41 0.86 1.84 7.63 0.06 SQL*Net break/reset to client db file parallel read gc current multi block request control file sequential read direct path read temp gc cr multi block request log file sync gc cr block busy enq: FB - contention DFS lock handle control file parallel write gc current block 2-way library cache lock name-service call wait row cache lock gcs log flush sync os thread startup gc cr block 2-way gc current block busy SQL*Net message to client gc buffer busy gc cr disk read direct path write temp gc current grant busy log file switch completion CGS wait for IPC msg gc current grant 2-way kjbdrmcvtq lmon drm quiesce: ping completion enq: US - contention direct path read enq: WF - contention ksxr poll remote instances library cache pin ges global resource directory to be frozen wait for scn ack log file sequential read undo segment extension rdbms ipc reply ktfbtgex enq: HW - contention gc cr grant 2-way enq: TX - index contention enq: CF - contention PX Deq: Signal ACK latch free buffer busy waits KJC: Wait for msg sends to complete log buffer space enq: PS - contention enq: TM - contention 333,084 3,732 175,710 15,974 1,873 20,877 919 526 10,384 3,517 1,946 4,165 432 22 3,894 1,259 18 3,671 113 1,544,115 15 3,272 159 898 29 48,739 1,142 9 567 138 14 13,291 211 9 583 36 25,342 279 6 44 158 1 64 37 3 625 154 11 46 70 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 42.02 5.56 0.00 0.00 0.00 6.67 0.00 0.00 0.00 0.00 99.87 0.00 0.00 0.00 0.00 0.00 58.45 0.00 100.00 0.00 0.00 99.79 0.00 100.00 0.00 0.00 0.00 0.00 21.62 0.00 0.16 0.00 0.00 0.00 0.00 16 13 10 10 9 8 4 3 3 3 3 2 2 2 2 2 2 2 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 4 0 1 5 0 4 6 0 1 1 0 4 76 0 1 89 0 12 0 70 0 5 1 17 0 0 19 0 1 9 0 1 10 0 2 0 0 10 1 0 34 1 1 10 0 0 2 1 0 59.50 0.67 31.39 2.85 0.33 3.73 0.16 0.09 1.85 0.63 0.35 0.74 0.08 0.00 0.70 0.22 0.00 0.66 0.02 275.83 0.00 0.58 0.03 0.16 0.01 8.71 0.20 0.00 0.10 0.02 0.00 2.37 0.04 0.00 0.10 0.01 4.53 0.05 0.00 0.01 0.03 0.00 0.01 0.01 0.00 0.11 0.03 0.00 0.01 0.01