内容发布更新时间 : 2024/12/24 11:42:18星期一 下面是文章的全部内容请认真阅读。

Global Cache Load Profile

Global Cache blocks received: Global Cache blocks served: GCS/GES messages received: GCS/GES messages sent: DBWR Fusion writes: Estd Interconnect traffic (KB) Per Second 4.16 5.97 408.47 258.03 0.05 211.16 Per Transaction 3.51 5.04 344.95 217.90 0.05 Global Cache Efficiency Percentages (Target local+remote 100%)

Buffer access - local cache %: Buffer access - remote cache %: Buffer access - disk %: 98.60 0.12 1.28 Global Cache and Enqueue Services - Workload Characteristics

Avg global enqueue get time (ms): Avg global cache cr block receive time (ms): Avg global cache current block receive time (ms): Avg global cache cr block build time (ms): Avg global cache cr block send time (ms): Global cache log flushes for cr blocks served %: Avg global cache cr block flush time (ms): Avg global cache current block pin time (ms): Avg global cache current block send time (ms): Global cache log flushes for current blocks served %: Avg global cache current block flush time (ms): 0.1 1.1 0.8 0.0 0.0 3.5 3.9 0.0 0.0 0.4 3.0 Global Cache and Enqueue Services - Messaging Statistics

Avg message sent queue time (ms): Avg message sent queue time on ksxp (ms): Avg message received queue time (ms): Avg GCS message process time (ms): Avg GES message process time (ms): % of direct sent messages: % of indirect sent messages: % of flow controlled messages: 0.0 0.3 0.5 0.0 0.0 14.40 77.04 8.56 Main Report ? ? Wait Events Statistics SQL Statistics ? ? ? ? ? ? ? ? ? ? ? ? ? ? Instance Activity Statistics IO Stats Buffer Pool Statistics Advisory Statistics Wait Statistics Undo Statistics Latch Statistics Segment Statistics Dictionary Cache Statistics Library Cache Statistics Memory Statistics Streams Statistics Resource Limit Statistics init.ora Parameters Wait Events Statistics

? ? ? ? ? ? ? Time Model Statistics Wait Class Wait Events Background Wait Events Operating System Statistics Service Statistics Service Wait Class Stats Back to Top

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 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 Time (s) % of DB Time 514.50 482.27 3.76 0.50 0.34 0.08 0.00 0.00 0.00 0.00 662.97 185.19 67.48 77.61 72.74 0.57 0.08 0.05 0.01 0.00 0.00 0.00 0.00 此节显示了各种类型的数据库处理任务所占用的CPU时间。

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

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 Wait Class User I/O System I/O Network Cluster Other Application Concurrency Commit Configuration 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)

%Time -outs 0.00 0.00 0.00 0.00 0.00 0.00 2.82 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 Total Wait Time (s) 64 47 35 34 31 30 18 16 13 10 10 9 8 4 3 3 3 3 2 2 Avg wait (ms) 2 9 4 7 3 1 49 0 4 0 1 5 0 4 6 0 1 1 0 4 Waits /txn 4.88 0.98 1.41 0.86 1.84 7.63 0.06 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 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 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 Waits 27,319 5,497 7,900 4,806 10,310 42,724 355 333,084 3,732 175,710 15,974 1,873 20,877 919 526 10,384 3,517 1,946 4,165 432 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 IPC send completion sync PX Deq: reap credit log file single write enq: TT - contention enq: TD - KTF dump entries read by other session LGWR wait for redo copy PX Deq Credit: send blkd enq: TA - contention latch: ges resource hash list enq: PI - contention write complete waits enq: DR - contention 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 40 1,544 36 46 12 1 540 17 14 44 8 1 3 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 100.00 99.81 0.00 0.00 0.00 0.00 0.00 5.88 0.00 0.00 0.00 0.00 0.00 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 0 0 0 0 0 0 0 0 0 0 0 0 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 0 0 0 0 1 12 0 0 0 0 0 2 0 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 0.01 0.28 0.01 0.01 0.00 0.00 0.10 0.00 0.00 0.01 0.00 0.00 0.00 enq: MW - contention enq: TS - contention PX qref latch enq: MD - contention latch: KCL gc element parent latch enq: JS - job run lock - synchronize SQL*Net more data to client latch: cache buffers lru chain enq: UL - contention gc current split enq: AF - task serialization latch: object queue header operation latch: cache buffers chains latch: enqueue hash chains SQL*Net message from client gcs remote message DIAG idle wait ges remote message Streams AQ: qmn slave idle wait Streams AQ: qmn coordinator idle wait Streams AQ: waiting for messages in the queue virtual circuit status PX Idle Wait jobq slave wait Streams AQ: waiting for time management or cleanup tasks PX Deq: Parse Reply PX Deq: Execution Msg PX Deq: Join ACK PX Deq: Execute Reply PX Deq: Msg Fragment Streams AQ: RAC qmn coordinator idle wait class slave wait 3 3 150 2 11 1 16 1 1 1 1 3 1 2 1,544,113 634,884 23,628 149,591 167 351 488 157 1,072 145 1 40 121 38 34 16 351 2 0.00 0.00 100.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 98.64 0.00 93.45 0.00 47.86 100.00 100.00 97.11 97.93 100.00 40.00 26.45 42.11 32.35 0.00 100.00 0.00 0 0 0 0 0 0 0 0 0 0 0 0 0 0 12,626 9,203 4,616 4,612 4,611 4,611 4,605 4,596 2,581 420 270 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 8 14 195 31 27611 13137 9436 29272 2407 2896 269747 3 0 1 0 0 0 0 0.00 0.00 0.03 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 275.83 113.41 4.22 26.72 0.03 0.06 0.09 0.03 0.19 0.03 0.00 0.01 0.02 0.01 0.01 0.00 0.06 0.00 db file scattered read等待事件是当SESSION等待multi-block I/O时发生的，通过是由于full table scans或 index fast full scans。发生过多读操作的Segments可以在“Segments by Physical Reads”和 “SQL ordered by Reads”节中识别（在其它版本的报告中，可能是别的名称）。如果在OLTP应用中，不应该有过多的全扫描操作，而应使用选择性好的索引操作。

DB file sequential read等待意味着发生顺序I/O读等待（通常是单块读取到连续的内存区域中），如果这个等待非常严重，应该使用上一段的方法确定执行读操作的热点SEGMENT，然后通过对大表进行分区以减少I/O量，或者优化执行计划（通过使用存储大纲或执行数据分析）以避免单块读操作引起的sequential read等待。通过在批量应用中，DB file sequential read是很影响性能的事件，总是应当设法避免。

Log File Parallel Write事件是在等待LGWR进程将REDO记录从LOG 缓冲区写到联机日志文件时发生的。虽然写操作可能是并发的，但LGWR需要等待最后的I/O写到磁盘上才能认为并行写的完成，因此等待时间依赖于OS完成所有