oracle中AWR详细分析AWR报告详细分析 AWR 是 Oracle 10g 版本推出的新特性，全…

首页 > ITPUX技术网 > oracle中AWR详细分析AWR报告详细分析 AWR 是 Oracle 10g 版本推出的新特性，全…

oracle中AWR详细分析AWR报告详细分析 AWR 是 Oracle 10g 版本推出的新特性，全…

教程发布：风哥教程分类：ITPUX技术网更新日期：2022-02-12 浏览学习：1720

WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst num Release RAC Host
ICCI 1314098396 ICCI1 1 10.2.0.3.0 YES HPGICCI1

Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 2678 25-Dec-08 14:04:50 24 1.5
End Snap: 2680 25-Dec-08 15:23:37 26 1.5
Elapsed: 78.79 (mins)
DB Time: 11.05 (mins)
DB Time不包括Oracle后台进程消耗的时间。如果DB Time远远小于Elapsed时间，说明数据库比较空闲。
在79分钟里（其间收集了3次快照数据），数据库耗时11分钟，RDA数据中显示系统有8个逻辑CPU（4个物理CPU），平均每个CPU耗时1.4分钟，CPU利用率只有大约2%（1.4/79）。说明系统压力非常小。
可是对于批量系统，数据库的工作负载总是集中在一段时间内。如果快照周期不在这一段时间内，或者快照周期跨度太长而包含了大量的数据库空闲时间，所得出的分析结果是没有意义的。这也说明选择分析时间段很关键，要选择能够代表性能问题的时间段。

Report Summary
Cache Sizes
Begin End
Buffer Cache: 3,344M 3,344M Std Block Size: 8K
Shared Pool Size: 704M 704M Log Buffer: 14,352K
显示SGA中每个区域的大小（在AMM改变它们之后），可用来与初始参数值比较。
shared pool主要包括library cache和dictionary cache。library cache用来存储最近解析（或编译）后SQL、PL/SQL和Java classes等。library cache用来存储最近引用的数据字典。发生在library cache或dictionary cache的cache miss代价要比发生在buffer cache的代价高得多。因此shared pool的设置要确保最近使用的数据都能被cache。

Load Profile
Per Second Per Transaction
Redo size: 918,805.72 775,912.72
Logical reads: 3,521.77 2,974.06
Block changes: 1,817.95 1,535.22
Physical reads: 68.26 57.64
Physical writes: 362.59 306.20
User calls: 326.69 275.88
Parses: 38.66 32.65
Hard parses: 0.03 0.03
Sorts: 0.61 0.51
Logons: 0.01 0.01
Executes: 354.34 299.23
Transactions: 1.18
% Blocks changed per Read: 51.62 Recursive Call %: 51.72
Rollback per transaction %: 85.49 Rows per Sort: ########
显示数据库负载概况，将之与基线数据比较才具有更多的意义，如果每秒或每事务的负载变化不大，说明应用运行比较稳定。单个的报告数据只说明应用的负载情况，绝大多数据并没有一个所谓“正确”的值，然而Logons大于每秒1~2个、Hard parses大于每秒100、全部parses超过每秒300表明可能有争用问题。
Redo size：每秒/每事务产生的redo大小（单位字节），可标志数据库任务的繁重程序。
Logical reads：每秒/每事务逻辑读的块数
Block changes：每秒/每事务修改的块数
Physical reads：每秒/每事务物理读的块数
Physical writes：每秒/每事务物理写的块数
User calls：每秒/每事务用户call次数
Parses：SQL解析的次数
Hard parses：其中硬解析的次数，硬解析太多，说明SQL重用率不高。
Sorts：每秒/每事务的排序次数
Logons：每秒/每事务登录的次数
Executes：每秒/每事务SQL执行次数
Transactions：每秒事务数
Blocks changed per Read：表示逻辑读用于修改数据块的比例
Recursive Call：递归调用占所有操作的比率
Rollback per transaction：每事务的回滚率
Rows per Sort：每次排序的行数
注:
Oracle的硬解析和软解析
　　提到软解析(soft parse)和硬解析(hard parse)，就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程：
　　1、语法检查(syntax check)
　　检查此sql的拼写是否语法。
　　2、语义检查(semantic check)
　　诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。
　　3、对sql语句进行解析(parse)
　　利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan)。
　　4、执行sql，返回结果(execute and return)
　　其中，软、硬解析就发生在第三个过程里。
　　Oracle利用内部的hash算法来取得该sql的hash值，然后在library cache里查找是否存在该hash值；
　　假设存在，则将此sql与cache中的进行比较；
　　假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是软解析的过程。
　　诚然，如果上面的2个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。
　　创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 98.72 In-memory Sort %: 99.86
Library Hit %: 99.97 Soft Parse %: 99.92
Execute to Parse %: 89.09 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 7.99 % Non-Parse CPU: 99.95
本节包含了Oracle关键指标的内存命中率及其它数据库实例操作的效率。其中Buffer Hit Ratio 也称Cache Hit Ratio，Library Hit ratio也称Library Cache Hit ratio。同Load Profile一节相同，这一节也没有所谓“正确”的值，而只能根据应用的特点判断是否合适。在一个使用直接读执行大型并行查询的DSS环境，20%的Buffer Hit Ratio是可以接受的，而这个值对于一个OLTP系统是完全不能接受的。根据Oracle的经验，对于OLTPT系统，Buffer Hit Ratio理想应该在90%以上。
Buffer Nowait表示在内存获得数据的未等待比例。
buffer hit表示进程从内存中找到数据块的比率，监视这个值是否发生重大变化比这个值本身更重要。对于一般的OLTP系统，如果此值低于80%，应该给数据库分配更多的内存。
Redo NoWait表示在LOG缓冲区获得BUFFER的未等待比例。如果太低（可参考90%阀值），考虑增加LOG BUFFER。
library hit表示Oracle从Library Cache中检索到一个解析过的SQL或PL/SQL语句的比率，当应用程序调用SQL或存储过程时，Oracle检查Library Cache确定是否存在解析过的版本，如果存在，Oracle立即执行语句；如果不存在，Oracle解析此语句，并在Library Cache中为它分配共享SQL区。低的library hit ratio会导致过多的解析，增加CPU消耗，降低性能。如果library hit ratio低于90%，可能需要调大shared pool区。
Latch Hit：Latch是一种保护内存结构的锁，可以认为是SERVER进程获取访问内存数据结构的许可。要确保Latch Hit>99%，否则意味着Shared Pool latch争用，可能由于未共享的SQL，或者Library Cache太小，可使用绑定变更或调大Shared Pool解决。
Parse CPU to Parse Elapsd：解析实际运行时间/(解析实际运行时间+解析中等待资源时间)，越高越好。
Non-Parse CPU ：SQL实际运行时间/(SQL实际运行时间+SQL解析时间)，太低表示解析消耗时间过多。
Execute to Parse：是语句执行与分析的比例，如果要SQL重用率高，则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。
In-memory Sort：在内存中排序的比率，如果过低说明有大量的排序在临时表空间中进行。考虑调大PGA。
Soft Parse：软解析的百分比（softs/softs+hards），近似当作sql在共享区的命中率，太低则需要调整应用使用绑定变量。
Shared Pool Statistics
Begin End
Memory Usage %: 47.19 47.50
% SQL with executions>1: 88.48 79.81
% Memory for SQL w/exec>1: 79.99 73.52
Memory Usage %：对于一个已经运行一段时间的数据库来说，共享池内存使用率，应该稳定在75%-90%间，如果太小，说明Shared Pool有浪费，而如果高于90，说明共享池中有争用，内存不足。
SQL with executions>1：执行次数大于1的sql比率，如果此值太小，说明需要在应用中更多使用绑定变量，避免过多SQL解析。
Memory for SQL w/exec>1：执行次数大于1的SQL消耗内存的占比。
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 515 77.6
SQL*Net more data from client 27,319 64 2 9.7 Network
log file parallel write 5,497 47 9 7.1 System I/O
db file sequential read 7,900 35 4 5.3 User I/O
db file parallel write 4,806 34 7 5.1 System I/O
这是报告概要的最后一节，显示了系统中最严重的5个等待，按所占等待时间的比例倒序列示。当我们调优时，总希望观察到最显著的效果，因此应当从这里入手确定我们下一步做什么。例如如果‘buffer busy wait’是较严重的等待事件，我们应当继续研究报告中Buffer Wait和File/Tablespace IO区的内容，识别哪些文件导致了问题。如果最严重的等待事件是I/O事件，我们应当研究按物理读排序的SQL语句区以识别哪些语句在执行大量I/O，并研究Tablespace和I/O区观察较慢响应时间的文件。如果有较高的LATCH等待，就需要察看详细的LATCH统计识别哪些LATCH产生的问题。
在这里，log file parallel write是相对比较多的等待，占用了7%的CPU时间。
通常，在没有问题的数据库中，CPU time总是列在第一个。
更多的等待事件，参见本报告的Wait Events一节。

RAC Statistics
Begin End
Number of Instances: 2 2
Global Cache Load Profile
Per Second Per Transaction
Global Cache blocks received: 4.16 3.51
Global Cache blocks served: 5.97 5.04
GCS/GES messages received: 408.47 344.95
GCS/GES messages sent: 258.03 217.90
DBWR Fusion writes: 0.05 0.05
Estd Interconnect traffic (KB) 211.16
Global Cache Efficiency Percentages (Target local+remote 100%)
Buffer access - local cache %: 98.60
Buffer access - remote cache %: 0.12
Buffer access - disk %: 1.28
Global Cache and Enqueue Services - Workload Characteristics
Avg global enqueue get time (ms): 0.1
Avg global cache cr block receive time (ms): 1.1
Avg global cache current block receive time (ms): 0.8
Avg global cache cr block build time (ms): 0.0
Avg global cache cr block send time (ms): 0.0
Global cache log flushes for cr blocks served %: 3.5
Avg global cache cr block flush time (ms): 3.9
Avg global cache current block pin time (ms): 0.0
Avg global cache current block send time (ms): 0.0
Global cache log flushes for current blocks served %: 0.4
Avg global cache current block flush time (ms): 3.0
Global Cache and Enqueue Services - Messaging Statistics
Avg message sent queue time (ms): 0.0
Avg message sent queue time on ksxp (ms): 0.3
Avg message received queue time (ms): 0.5
Avg GCS message process time (ms): 0.0
Avg GES message process time (ms): 0.0
% of direct sent messages: 14.40
% of indirect sent messages: 77.04
% of flow controlled messages: 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 "background" measure background process time, and so do not contribute to the DB time statistic
• Ordered by % or DB time desc, Statistic name
Statistic Name Time (s) % of DB Time
DB CPU 514.50 77.61
sql execute elapsed time 482.27 72.74
parse time elapsed 3.76 0.57
PL/SQL execution elapsed time 0.50 0.08
hard parse elapsed time 0.34 0.05
connection management call elapsed time 0.08 0.01
hard parse (sharing criteria) elapsed time 0.00 0.00
repeated bind elapsed time 0.00 0.00
PL/SQL compilation elapsed time 0.00 0.00
failed parse elapsed time 0.00 0.00
DB time 662.97
background elapsed time 185.19
background cpu time 67.48
此节显示了各种类型的数据库处理任务所占用的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
Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
User I/O 66,837 0.00 120 2 11.94
System I/O 28,295 0.00 93 3 5.05
Network 1,571,450 0.00 66 0 280.72
Cluster 210,548 0.00 29 0 37.61
Other 81,783 71.82 28 0 14.61
Application 333,155 0.00 16 0 59.51
Concurrency 5,182 0.04 5 1 0.93
Commit 919 0.00 4 4 0.16
Configuration 25,427 99.46 1 0 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)
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
SQL*Net more data from client 27,319 0.00 64 2 4.88
log file parallel write 5,497 0.00 47 9 0.98
db file sequential read 7,900 0.00 35 4 1.41
db file parallel write 4,806 0.00 34 7 0.86
db file scattered read 10,310 0.00 31 3 1.84
direct path write 42,724 0.00 30 1 7.63
reliable message 355 2.82 18 49 0.06
SQL*Net break/reset to client 333,084 0.00 16 0 59.50
db file parallel read 3,732 0.00 13 4 0.67
gc current multi block request 175,710 0.00 10 0 31.39
control file sequential read 15,974 0.00 10 1 2.85
direct path read temp 1,873 0.00 9 5 0.33
gc cr multi block request 20,877 0.00 8 0 3.73
log file sync 919 0.00 4 4 0.16
gc cr block busy 526 0.00 3 6 0.09
enq: FB - contention 10,384 0.00 3 0 1.85
DFS lock handle 3,517 0.00 3 1 0.63
control file parallel write 1,946 0.00 3 1 0.35
gc current block 2-way 4,165 0.00 2 0 0.74
library cache lock 432 0.00 2 4 0.08
name-service call wait 22 0.00 2 76 0.00
row cache lock 3,894 0.00 2 0 0.70
gcs log flush sync 1,259 42.02 2 1 0.22
os thread startup 18 5.56 2 89 0.00
gc cr block 2-way 3,671 0.00 2 0 0.66
gc current block busy 113 0.00 1 12 0.02
SQL*Net message to client 1,544,115 0.00 1 0 275.83
gc buffer busy 15 6.67 1 70 0.00
gc cr disk read 3,272 0.00 1 0 0.58
direct path write temp 159 0.00 1 5 0.03
gc current grant busy 898 0.00 1 1 0.16
log file switch completion 29 0.00 1 17 0.01
CGS wait for IPC msg 48,739 99.87 0 0 8.71
gc current grant 2-way 1,142 0.00 0 0 0.20
kjbdrmcvtq lmon drm quiesce: ping completion 9 0.00 0 19 0.00
enq: US - contention 567 0.00 0 0 0.10
direct path read 138 0.00 0 1 0.02
enq: WF - contention 14 0.00 0 9 0.00
ksxr poll remote instances 13,291 58.45 0 0 2.37
library cache pin 211 0.00 0 1 0.04
ges global resource directory to be frozen 9 100.00 0 10 0.00
wait for scn ack 583 0.00 0 0 0.10
log file sequential read 36 0.00 0 2 0.01
undo segment extension 25,342 99.79 0 0 4.53
rdbms ipc reply 279 0.00 0 0 0.05
ktfbtgex 6 100.00 0 10 0.00
enq: HW - contention 44 0.00 0 1 0.01
gc cr grant 2-way 158 0.00 0 0 0.03
enq: TX - index contention 1 0.00 0 34 0.00
enq: CF - contention 64 0.00 0 1 0.01
PX Deq: Signal ACK 37 21.62 0 1 0.01
latch free 3 0.00 0 10 0.00
buffer busy waits 625 0.16 0 0 0.11
KJC: Wait for msg sends to complete 154 0.00 0 0 0.03
log buffer space 11 0.00 0 2 0.00
enq: PS - contention 46 0.00 0 1 0.01
enq: TM - contention 70 0.00 0 0 0.01
IPC send completion sync 40 100.00 0 0 0.01
PX Deq: reap credit 1,544 99.81 0 0 0.28
log file single write 36 0.00 0 0 0.01
enq: TT - contention 46 0.00 0 0 0.01
enq: TD - KTF dump entries 12 0.00 0 1 0.00
read by other session 1 0.00 0 12 0.00
LGWR wait for redo copy 540 0.00 0 0 0.10
PX Deq Credit: send blkd 17 5.88 0 0 0.00
enq: TA - contention 14 0.00 0 0 0.00
latch: ges resource hash list 44 0.00 0 0 0.01
enq: PI - contention 8 0.00 0 0 0.00
write complete waits 1 0.00 0 2 0.00
enq: DR - contention 3 0.00 0 0 0.00
enq: MW - contention 3 0.00 0 0 0.00
enq: TS - contention 3 0.00 0 0 0.00
PX qref latch 150 100.00 0 0 0.03
enq: MD - contention 2 0.00 0 0 0.00
latch: KCL gc element parent latch 11 0.00 0 0 0.00
enq: JS - job run lock - synchronize 1 0.00 0 1 0.00
SQL*Net more data to client 16 0.00 0 0 0.00
latch: cache buffers lru chain 1 0.00 0 0 0.00
enq: UL - contention 1 0.00 0 0 0.00
gc current split 1 0.00 0 0 0.00
enq: AF - task serialization 1 0.00 0 0 0.00
latch: object queue header operation 3 0.00 0 0 0.00
latch: cache buffers chains 1 0.00 0 0 0.00
latch: enqueue hash chains 2 0.00 0 0 0.00
SQL*Net message from client 1,544,113 0.00 12,626 8 275.83
gcs remote message 634,884 98.64 9,203 14 113.41
DIAG idle wait 23,628 0.00 4,616 195 4.22
ges remote message 149,591 93.45 4,612 31 26.72
Streams AQ: qmn slave idle wait 167 0.00 4,611 27611 0.03
Streams AQ: qmn coordinator idle wait 351 47.86 4,611 13137 0.06
Streams AQ: waiting for messages in the queue 488 100.00 4,605 9436 0.09
virtual circuit status 157 100.00 4,596 29272 0.03
PX Idle Wait 1,072 97.11 2,581 2407 0.19
jobq slave wait 145 97.93 420 2896 0.03
Streams AQ: waiting for time management or cleanup tasks 1 100.00 270 269747 0.00
PX Deq: Parse Reply 40 40.00 0 3 0.01
PX Deq: Execution Msg 121 26.45 0 0 0.02
PX Deq: Join ACK 38 42.11 0 1 0.01
PX Deq: Execute Reply 34 32.35 0 0 0.01
PX Deq: Msg Fragment 16 0.00 0 0 0.00
Streams AQ: RAC qmn coordinator idle wait 351 100.00 0 0 0.06
class slave wait 2 0.00 0 0 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完成所有请求的时间。如果这个等待比较严重，可以通过将LOG文件移到更快的磁盘上或者条带化磁盘（减少争用）而降低这个等待。
Buffer Busy Waits事件是在一个SESSION需要访问BUFFER CACHE中的一个数据库块而又不能访问时发生的。缓冲区“busy”的两个原因是：1）另一个SESSION正在将数据块读进BUFFER。2）另一个SESSION正在以排它模式占用着这块被请求的BUFFER。可以在“Segments by Buffer Busy Waits”一节中找出发生这种等待的SEGMENT，然后通过使用reverse-key indexes并对热表进行分区而减少这种等待事件。
Log File Sync事件，当用户SESSION执行事务操作（COMMIT或ROLLBACK等）后，会通知 LGWR进程将所需要的所有REDO信息从LOG BUFFER写到LOG文件，在用户SESSION等待LGWR返回安全写入磁盘的通知时发生此等待。减少此等待的方法写Log File Parallel Write事件的处理。
Enqueue Waits是串行访问本地资源的本锁，表明正在等待一个被其它SESSION（一个或多个）以排它模式锁住的资源。减少这种等待的方法依赖于生产等待的锁类型。导致Enqueue等待的主要锁类型有三种：TX（事务锁）, TM D（ML锁）和ST（空间管理锁）。
Back to Wait Events Statistics
Back to Top
Background Wait Events
• ordered by wait time desc, waits desc (idle events last)
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
log file parallel write 5,497 0.00 47 9 0.98
db file parallel write 4,806 0.00 34 7 0.86
events in waitclass Other 69,002 83.25 22 0 12.33
control file sequential read 9,323 0.00 7 1 1.67
control file parallel write 1,946 0.00 3 1 0.35
os thread startup 18 5.56 2 89 0.00
direct path read 138 0.00 0 1 0.02
db file sequential read 21 0.00 0 5 0.00
direct path write 138 0.00 0 0 0.02
log file sequential read 36 0.00 0 2 0.01
gc cr block 2-way 96 0.00 0 0 0.02
gc current block 2-way 78 0.00 0 0 0.01
log buffer space 11 0.00 0 2 0.00
row cache lock 59 0.00 0 0 0.01
log file single write 36 0.00 0 0 0.01
buffer busy waits 151 0.66 0 0 0.03
gc current grant busy 29 0.00 0 0 0.01
library cache lock 4 0.00 0 1 0.00
enq: TM - contention 10 0.00 0 0 0.00
gc current grant 2-way 8 0.00 0 0 0.00
gc cr multi block request 7 0.00 0 0 0.00
gc cr grant 2-way 5 0.00 0 0 0.00
rdbms ipc message 97,288 73.77 50,194 516 17.38
gcs remote message 634,886 98.64 9,203 14 113.41
DIAG idle wait 23,628 0.00 4,616 195 4.22
pmon timer 1,621 100.00 4,615 2847 0.29
ges remote message 149,591 93.45 4,612 31 26.72
Streams AQ: qmn slave idle wait 167 0.00 4,611 27611 0.03
Streams AQ: qmn coordinator idle wait 351 47.86 4,611 13137 0.06
smon timer 277 6.50 4,531 16356 0.05
Streams AQ: waiting for time management or cleanup tasks 1 100.00 270 269747 0.00
PX Deq: Parse Reply 40 40.00 0 3 0.01
PX Deq: Join ACK 38 42.11 0 1 0.01
PX Deq: Execute Reply 34 32.35 0 0 0.01
Streams AQ: RAC qmn coordinator idle wait 351 100.00 0 0 0.06
Back to Wait Events Statistics
Back to Top
Operating System Statistics
Statistic Total
NUM_LCPUS 0
NUM_VCPUS 0
AVG_BUSY_TIME 101,442
AVG_IDLE_TIME 371,241
AVG_IOWAIT_TIME 5,460
AVG_SYS_TIME 25,795
AVG_USER_TIME 75,510
BUSY_TIME 812,644
IDLE_TIME 2,971,077
IOWAIT_TIME 44,794
SYS_TIME 207,429
USER_TIME 605,215
LOAD 0
OS_CPU_WAIT_TIME 854,100
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 8,589,934,592
NUM_CPUS 8
NUM_CPU_CORES 4
NUM_LCPUS：如果显示0，是因为没有设置LPARS
NUM_VCPUS：同上。
AVG_BUSY_TIME： BUSY_TIME / NUM_CPUS
AVG_IDLE_TIME： IDLE_TIME / NUM_CPUS
AVG_IOWAIT_TIME： IOWAIT_TIME / NUM_CPUS
AVG_SYS_TIME： SYS_TIME / NUM_CPUS
AVG_USER_TIME： USER_TIME / NUM_CPUSar o
BUSY_TIME： time equiv of %usr+%sys in sar output
IDLE_TIME： time equiv of %idle in sar
IOWAIT_TIME： time equiv of %wio in sar
SYS_TIME： time equiv of %sys in sar
USER_TIME： time equiv of %usr in sar
LOAD：未知
OS_CPU_WAIT_TIME： supposedly time waiting on run queues
RSRC_MGR_CPU_WAIT_TIME： time waited coz of resource manager
PHYSICAL_MEMORY_BYTES： total memory in use supposedly
NUM_CPUS： number of CPUs reported by OS
NUM_CPU_CORES： number of CPU sockets on motherboard
总的elapsed time也可以用以公式计算：
BUSY_TIME + IDLE_TIME + IOWAIT TIME
或：SYS_TIME + USER_TIME + IDLE_TIME + IOWAIT_TIME
（因为BUSY_TIME = SYS_TIME+USER_TIME）
Back to Wait Events Statistics
Back to Top
Service Statistics
• ordered by DB Time
Service Name DB Time (s) DB CPU (s) Physical Reads Logical Reads
ICCI 608.10 496.60 315,849 16,550,972
SYS$USERS 54.70 17.80 6,539 58,929
ICCIXDB 0.00 0.00 0 0
SYS$BACKGROUND 0.00 0.00 282 38,990
Back to Wait Events Statistics
Back to Top
Service Wait Class Stats
• Wait Class info for services in the Service Statistics section.
• Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
• Time Waited (Wt Time) in centisecond (100th of a second)
Service Name User I/O Total Wts User I/O Wt Time Concurcy Total Wts Concurcy Wt Time Admin Total Wts Admin Wt Time Network Total Wts Network Wt Time
ICCI 59826 8640 4621 338 0 0 1564059 6552
SYS$USERS 6567 3238 231 11 0 0 7323 3
SYS$BACKGROUND 443 115 330 168 0 0 0 0
Back to Wait Events Statistics
Back to Top
SQL Statistics
• SQL ordered by Elapsed Time
• SQL ordered by CPU Time
• SQL ordered by Gets
• SQL ordered by Reads
• SQL ordered by Executions
• SQL ordered by Parse Calls
• SQL ordered by Sharable Memory
• SQL ordered by Version Count
• SQL ordered by Cluster Wait Time
• Complete List of SQL Text
本节按各种资源分别列出对资源消耗最严重的SQL语句，并显示它们所占统计期内全部资源的比例，这给出我们调优指南。例如在一个系统中，CPU资源是系统性能瓶颈所在，那么优化buffer gets最多的SQL语句将获得最大效果。在一个I/O等待是最严重事件的系统中，调优的目标应该是physical IOs最多的SQL语句。
在STATSPACK报告中，没有完整的SQL语句，可使用报告中的Hash Value通过下面语句从数据库中查到：
select sql_text
from stats$sqltext
where hash_value = &hash_value
order by piece;
Back to Top
SQL ordered by Elapsed Time
• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
Elapsed Time (s) CPU Time (s) Executions Elap per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
93 57 1 93.50 14.10 d8z0u8hgj8xdy
cuidmain@HPGICCI1 (TNS V1-V3) insert into CUID select CUID_...
76 75 172,329 0.00 11.52 4vja2k2gdtyup
load_fnsact@HPGICCI1 (TNS V1-V3) insert into ICCICCS values (:...
58 42 1 58.04 8.75 569r5k05drsj7
cumimain@HPGICCI1 (TNS V1-V3) insert into CUMI select CUSV_...
51 42 1 50.93 7.68 ackxqhnktxnbc
cusmmain@HPGICCI1 (TNS V1-V3) insert into CUSM select CUSM_...
38 36 166,069 0.00 5.67 7gtztzv329wg0
select c.name, u.name from co...
35 3 1 35.00 5.28 6z06gcfw39pkd
SQL*Plus SELECT F.TABLESPACE_NAME, TO_...
23 23 172,329 0.00 3.46 1dm3bq36vu3g8
load_fnsact@HPGICCI1 (TNS V1-V3) insert into iccifnsact values...
15 11 5 2.98 2.25 djs2w2f17nw2z
DECLARE job BINARY_INTEGER := ...
14 14 172,983 0.00 2.16 7wwv1ybs9zguz
load_fnsact@HPGICCI1 (TNS V1-V3) update ICCIFNSACT set BORM_AD...
13 13 172,337 0.00 2.00 gmn2w09rdxn14
load_oldnewact@HPGICCI1 (TNS V1-V3) insert into OLDNEWACT values ...
13 13 166,051 0.00 1.89 chjmy0dxf9mbj
icci_migact@HPGICCI1 (TNS V1-V3) insert into ICCICCS values (:...
10 4 1 9.70 1.46 0yv9t4qb1zb2b
cuidmain@HPGICCI1 (TNS V1-V3) select CUID_CUST_NO , CUID_ID_...
10 8 5 1.91 1.44 1crajpb7j5tyz
INSERT INTO STATS$SGA_TARGET_A...
8 8 172,329 0.00 1.25 38apjgr0p55ns
load_fnsact@HPGICCI1 (TNS V1-V3) update ICCICCS set CCSMAXOVER...
8 8 172,983 0.00 1.16 5c4qu2zmj3gux
load_fnsact@HPGICCI1 (TNS V1-V3) select * from ICCIPRODCODE wh...
Back to SQL Statistics
Back to Top
SQL ordered by CPU Time
• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
CPU Time (s) Elapsed Time (s) Executions CPU per Exec (s) % Total DB Time SQL Id SQL Module SQL Text
75 76 172,329 0.00 11.52 4vja2k2gdtyup
load_fnsact@HPGICCI1 (TNS V1-V3) insert into ICCICCS values (:...
57 93 1 57.31 14.10 d8z0u8hgj8xdy
cuidmain@HPGICCI1 (TNS V1-V3) insert into CUID select CUID_...
42 51 1 42.43 7.68 ackxqhnktxnbc
cusmmain@HPGICCI1 (TNS V1-V3) insert into CUSM select CUSM_...
42 58 1 42.01 8.75 569r5k05drsj7
cumimain@HPGICCI1 (TNS V1-V3) insert into CUMI select CUSV_...
36 38 166,069 0.00 5.67 7gtztzv329wg0
select c.name, u.name from co...
23 23 172,329 0.00 3.46 1dm3bq36vu3g8
load_fnsact@HPGICCI1 (TNS V1-V3) insert into iccifnsact values...
14 14 172,983 0.00 2.16 7wwv1ybs9zguz
load_fnsact@HPGICCI1 (TNS V1-V3) update ICCIFNSACT set BORM_AD...
13 13 172,337 0.00 2.00 gmn2w09rdxn14
load_oldnewact@HPGICCI1 (TNS V1-V3) insert into OLDNEWACT values ...
13 13 166,051 0.00 1.89 chjmy0dxf9mbj
icci_migact@HPGICCI1 (TNS V1-V3) insert into ICCICCS values (:...
11 15 5 2.23 2.25 djs2w2f17nw2z
DECLARE job BINARY_INTEGER := ...
8 8 172,329 0.00 1.25 38apjgr0p55ns
load_fnsact@HPGICCI1 (TNS V1-V3) update ICCICCS set CCSMAXOVER...
8 10 5 1.60 1.44 1crajpb7j5tyz
INSERT INTO STATS$SGA_TARGET_A...
8 8 172,983 0.00 1.16 5c4qu2zmj3gux
load_fnsact@HPGICCI1 (TNS V1-V3) select * from ICCIPRODCODE wh...
4 10 1 3.54 1.46 0yv9t4qb1zb2b
cuidmain@HPGICCI1 (TNS V1-V3) select CUID_CUST_NO , CUID_ID_...
3 35 1 3.13 5.28 6z06gcfw39pkd
SQL*Plus SELECT F.TABLESPACE_NAME, TO_...
Back to SQL Statistics
Back to Top
SQL ordered by Gets
• Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
• Total Buffer Gets: 16,648,792
• Captured SQL account for 97.9% of Total
Buffer Gets Executions Gets per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
3,305,363 172,329 19.18 19.85 74.57 76.41 4vja2k2gdtyup
load_fnsact@HPGICCI1 (TNS V1-V3) insert into ICCICCS values (:...
2,064,414 1 2,064,414.00 12.40 57.31 93.50 d8z0u8hgj8xdy
cuidmain@HPGICCI1 (TNS V1-V3) insert into CUID select CUID_...
1,826,869 166,069 11.00 10.97 35.84 37.60 7gtztzv329wg0
select c.name, u.name from co...
1,427,648 172,337 8.28 8.58 12.97 13.29 gmn2w09rdxn14
load_oldnewact@HPGICCI1 (TNS V1-V3) insert into OLDNEWACT values ...
1,278,667 172,329 7.42 7.68 22.85 22.94 1dm3bq36vu3g8
load_fnsact@HPGICCI1 (TNS V1-V3) insert into iccifnsact values...
1,216,367 1 1,216,367.00 7.31 42.43 50.93 ackxqhnktxnbc
cusmmain@HPGICCI1 (TNS V1-V3) insert into CUSM select CUSM_...
1,107,305 1 1,107,305.00 6.65 42.01 58.04 569r5k05drsj7
cumimain@HPGICCI1 (TNS V1-V3) insert into CUMI select CUSV_...
898,868 172,983 5.20 5.40 14.28 14.34 7wwv1ybs9zguz
load_fnsact@HPGICCI1 (TNS V1-V3) update ICCIFNSACT set BORM_AD...
711,450 166,051 4.28 4.27 12.52 12.55 chjmy0dxf9mbj
icci_migact@HPGICCI1 (TNS V1-V3) insert into ICCICCS values (:...
692,996 172,329 4.02 4.16 8.31 8.31 38apjgr0p55ns
load_fnsact@HPGICCI1 (TNS V1-V3) update ICCICCS set CCSMAXOVER...
666,748 166,052 4.02 4.00 6.36 6.36 7v9dyf5r424yh
icci_migact@HPGICCI1 (TNS V1-V3) select NEWACTNO into :b0 from...
345,357 172,983 2.00 2.07 7.70 7.71 5c4qu2zmj3gux
load_fnsact@HPGICCI1 (TNS V1-V3) select * from ICCIPRODCODE wh...
231,756 51,633 4.49 1.39 5.75 5.83 49ms69srnaxzj
load_fnsact@HPGICCI1 (TNS V1-V3) insert into ICCIRPYV values (...
Back to SQL Statistics
Back to Top
SQL ordered by Reads
• Total Disk Reads: 322,678
• Captured SQL account for 66.1% of Total
Physical Reads Executions Reads per Exec %Total CPU Time (s) Elapsed Time (s) SQL Id SQL Module SQL Text
66,286 1 66,286.00 20.54 57.31 93.50 d8z0u8hgj8xdy
cuidmain@HPGICCI1 (TNS V1-V3) insert into CUID select CUID_...
50,646 1 50,646.00 15.70 3.54 9.70 0yv9t4qb1zb2b
cuidmain@HPGICCI1 (TNS V1-V3) select CUID_CUST_NO , CUID_ID_...
24,507 1 24,507.00 7.59 42.01 58.04 569r5k05drsj7
cumimain@HPGICCI1 (TNS V1-V3) insert into CUMI select CUSV_...
21,893 1 21,893.00 6.78 42.43 50.93 ackxqhnktxnbc
cusmmain@HPGICCI1 (TNS V1-V3) insert into CUSM select CUSM_...
19,761 1 19,761.00 6.12 2.14 6.04 a7nh7j8zmfrzw
cumimain@HPGICCI1 (TNS V1-V3) select CUSV_CUST_NO from CUMI...
19,554 1 19,554.00 6.06 1.27 3.83 38gak8u2qm11w
SQL*Plus select count(*) from CUSVAA_T...
6,342 1 6,342.00 1.97 3.13 35.00 6z06gcfw39pkd
SQL*Plus SELECT F.TABLESPACE_NAME, TO_...
4,385 1 4,385.00 1.36 1.59 2.43 cp5duhcsj72q0
cusmmain@HPGICCI1 (TNS V1-V3) select CUSM_CUST_ACCT_NO from...
63 5 12.60 0.02 11.17 14.91 djs2w2f17nw2z
DECLARE job BINARY_INTEGER := ...
35 1 35.00 0.01 0.08 0.67 1uk5m5qbzj1vt
SQL*Plus BEGIN dbms_workload_repository...
Back to SQL Statistics
Back to Top
SQL ordered by Executions
• Total Executions: 1,675,112
• Captured SQL account for 99.8% of Total
Executions Rows Processed Rows per Exec CPU per Exec (s) Elap per Exec (s) SQL Id SQL Module SQL Text
172,983 172,329 1.00 0.00 0.00 5c4qu2zmj3gux
load_fnsact@HPGICCI1 (TNS V1-V3) select * from ICCIPRODCODE wh...
172,983 172,329 1.00 0.00 0.00 7wwv1ybs9zguz
load_fnsact@HPGICCI1 (TNS V1-V3) update ICCIFNSACT set BORM_AD...
172,337 172,337 1.00 0.00 0.00 gmn2w09rdxn14
load_oldnewact@HPGICCI1 (TNS V1-V3) insert into OLDNEWACT values ...
172,329 172,329 1.00 0.00 0.00 1dm3bq36vu3g8
load_fnsact@HPGICCI1 (TNS V1-V3) insert into iccifnsact values...
172,329 172,329 1.00 0.00 0.00 38apjgr0p55ns
load_fnsact@HPGICCI1 (TNS V1-V3) update ICCICCS set CCSMAXOVER...
172,329 6,286 0.04 0.00 0.00 4vja2k2gdtyup
load_fnsact@HPGICCI1 (TNS V1-V3) insert into ICCICCS values (:...
166,069 166,069 1.00 0.00 0.00 7gtztzv329wg0
select c.name, u.name from co...
166,052 166,052 1.00 0.00 0.00 7v9dyf5r424yh
icci_migact@HPGICCI1 (TNS V1-V3) select NEWACTNO into :b0 from...
166,051 166,051 1.00 0.00 0.00 chjmy0dxf9mbj
icci_migact@HPGICCI1 (TNS V1-V3) insert into ICCICCS values (:...
51,740 51,740 1.00 0.00 0.00 bu8tnqr3xv25q
load_fnsact@HPGICCI1 (TNS V1-V3) select count(*) into :b0 fro...
51,633 51,633 1.00 0.00 0.00 49ms69srnaxzj
load_fnsact@HPGICCI1 (TNS V1-V3) insert into ICCIRPYV values (...
Back to SQL Statistics
Back to Top
SQL ordered by Parse Calls
• Total Parse Calls: 182,780
• Captured SQL account for 99.0% of Total
Parse Calls Executions % Total Parses SQL Id SQL Module SQL Text
166,069 166,069 90.86 7gtztzv329wg0
select c.name, u.name from co...
6,304 6,304 3.45 2ym6hhaq30r73
select type#, blocks, extents,...
2,437 2,438 1.33 bsa0wjtftg3uw
select file# from file$ where ...
1,568 1,568 0.86 9qgtwh66xg6nz
update seg$ set type#=:4, bloc...
1,554 1,554 0.85 aq4js2gkfjru8
update tsq$ set blocks=:3, max...
444 444 0.24 104pd9mm3fh9p
select blocks, maxblocks, gran...
421 421 0.23 350f5yrnnmshs
lock table sys.mon_mods$ in ex...
421 421 0.23 g00cj285jmgsw
update sys.mon_mods$ set inser...
86 86 0.05 3m8smr0v7v1m6
INSERT INTO sys.wri$_adv_messa...
81 81 0.04 f80h0xb1qvbsk
SELECT sys.wri$_adv_seq_msggro...
Back to SQL Statistics
Back to Top
SQL ordered by Sharable Memory
No data exists for this section of the report.
Back to SQL Statistics
Back to Top
SQL ordered by Version Count
No data exists for this section of the report.
Back to SQL Statistics
Back to Top
SQL ordered by Cluster Wait Time
Cluster Wait Time (s) CWT % of Elapsd Time Elapsed Time(s) CPU Time(s) Executions SQL Id SQL Module SQL Text
10.96 11.72 93.50 57.31 1 d8z0u8hgj8xdy
cuidmain@HPGICCI1 (TNS V1-V3) insert into CUID select CUID_...
4.21 7.25 58.04 42.01 1 569r5k05drsj7
cumimain@HPGICCI1 (TNS V1-V3) insert into CUMI select CUSV_...
3.62 7.12 50.93 42.43 1 ackxqhnktxnbc
cusmmain@HPGICCI1 (TNS V1-V3) insert into CUSM select CUSM_...
2.39 6.35 37.60 35.84 166,069 7gtztzv329wg0
select c.name, u.name from co...
2.38 3.12 76.41 74.57 172,329 4vja2k2gdtyup
load_fnsact@HPGICCI1 (TNS V1-V3) insert into ICCICCS values (:...
1.64 16.91 9.70 3.54 1 0yv9t4qb1zb2b
cuidmain@HPGICCI1 (TNS V1-V3) select CUID_CUST_NO , CUID_ID_...
1.06 3.02 35.00 3.13 1 6z06gcfw39pkd
SQL*Plus SELECT F.TABLESPACE_NAME, TO_...
0.83 13.76 6.04 2.14 1 a7nh7j8zmfrzw
cumimain@HPGICCI1 (TNS V1-V3) select CUSV_CUST_NO from CUMI...
0.66 87.90 0.75 0.42 444 104pd9mm3fh9p
select blocks, maxblocks, gran...
0.50 13.01 3.83 1.27 1 38gak8u2qm11w
SQL*Plus select count(*) from CUSVAA_T...
0.50 51.75 0.96 0.79 1,554 aq4js2gkfjru8
update tsq$ set blocks=:3, max...
0.33 91.11 0.36 0.33 187 04xtrk7uyhknh
select obj#, type#, ctime, mti...
0.33 2.47 13.29 12.97 172,337 gmn2w09rdxn14
load_oldnewact@HPGICCI1 (TNS V1-V3) insert into OLDNEWACT values ...
0.29 1.26 22.94 22.85 172,329 1dm3bq36vu3g8
load_fnsact@HPGICCI1 (TNS V1-V3) insert into iccifnsact values...
0.25 10.14 2.43 1.59 1 cp5duhcsj72q0
cusmmain@HPGICCI1 (TNS V1-V3) select CUSM_CUST_ACCT_NO from...
0.21 27.92 0.74 0.74 1,568 9qgtwh66xg6nz
update seg$ set type#=:4, bloc...
0.20 3.49 5.83 5.75 51,633 49ms69srnaxzj
load_fnsact@HPGICCI1 (TNS V1-V3) insert into ICCIRPYV values (...
0.17 1.39 12.55 12.52 166,051 chjmy0dxf9mbj
icci_migact@HPGICCI1 (TNS V1-V3) insert into ICCICCS values (:...
0.16 57.64 0.28 0.24 39 cn1gtsav2d5jh
cusvaamain@HPGICCI1 (TNS V1-V3) BEGIN BEGIN IF (xdb.DBMS...
0.14 74.58 0.19 0.14 121 5ngzsfstg8tmy
select o.owner#, o.name, o.nam...
0.11 64.72 0.18 0.15 80 78m9ryygp65v5
cusvaamain@HPGICCI1 (TNS V1-V3) SELECT /*+ ALL_ROWS */ COUNT(*...
0.11 94.54 0.12 0.01 17 bwt0pmxhv7qk7
delete from con$ where owner#=...
0.11 80.26 0.14 0.14 327 53saa2zkr6wc3
select intcol#, nvl(pos#, 0), ...
0.08 19.20 0.42 0.24 1 d92h3rjp0y217
begin prvt_hdm.auto_execute( :...
0.07 54.97 0.13 0.13 83 7ng34ruy5awxq
select i.obj#, i.ts#, i.file#,...
0.06 5.22 1.13 0.72 77 0hhmdwwgxbw0r
select obj#, type#, flags, ...
0.06 86.50 0.06 0.06 45 a2any035u1qz1
select owner#, name from con$...
0.06 8.19 0.67 0.08 1 1uk5m5qbzj1vt
SQL*Plus BEGIN dbms_workload_repository...
0.04 75.69 0.06 0.06 87 6769wyy3yf66f
select pos#, intcol#, col#, sp...
0.04 48.05 0.09 0.07 7 0pvtkmrrq8usg
select file#, block# from seg...
0.04 8.84 0.40 0.40 6,304 2ym6hhaq30r73
select type#, blocks, extents,...
0.03 28.15 0.12 0.12 49 b52m6vduutr8j
delete from RecycleBin$ ...
0.03 66.23 0.05 0.05 85 1gu8t96d0bdmu
select t.ts#, t.file#, t.block...
0.03 67.03 0.05 0.05 38 btzq46kta67dz
DBMS_SCHEDULER update obj$ set obj#=:6, type#...
0.02 66.73 0.04 0.04 86 3m8smr0v7v1m6
INSERT INTO sys.wri$_adv_messa...
0.02 26.94 0.09 0.09 38 0k8h617b8guhf
delete from RecycleBin$ ...
0.02 76.76 0.03 0.03 51 9vtm7gy4fr2ny
select con# from con$ where ow...
0.02 51.91 0.05 0.05 84 83taa7kaw59c1
select name, intcol#, segcol#,...
0.02 0.15 14.91 11.17 5 djs2w2f17nw2z
DECLARE job BINARY_INTEGER := ...
0.02 2.12 1.00 0.99 8,784 501v412s13r4m
load_fnsact@HPGICCI1 (TNS V1-V3) update ICCIFNSACT set BORM_FA...
0.02 53.82 0.03 0.03 39 bdv0rkkssq2jm
cusvaamain@HPGICCI1 (TNS V1-V3) SELECT count(*) FROM user_poli...
0.01 0.10 14.34 14.28 172,983 7wwv1ybs9zguz
load_fnsact@HPGICCI1 (TNS V1-V3) update ICCIFNSACT set BORM_AD...
0.01 8.29 0.16 0.13 421 g00cj285jmgsw
update sys.mon_mods$ set inser...
0.01 1.65 0.56 0.54 2 84qubbrsr0kfn
insert into wrh$_latch (snap...
0.01 22.33 0.04 0.02 26 44au3v5mzpc1c
load_curmmast@HPGICCI1 (TNS V1-V3) insert into ICCICURMMAST valu...
0.01 0.08 7.71 7.70 172,983 5c4qu2zmj3gux
load_fnsact@HPGICCI1 (TNS V1-V3) select * from ICCIPRODCODE wh...
Back to SQL Statistics
Back to Top
Complete List of SQL Text
SQL Id SQL Text
04xtrk7uyhknh select obj#, type#, ctime, mtime, stime, status, dataobj#, flags, oid$, spare1, spare2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
0hhmdwwgxbw0r select obj#, type#, flags, related, bo, purgeobj, con# from RecycleBin$ where ts#=:1 and to_number(bitand(flags, 16)) = 16 order by dropscn
0k8h617b8guhf delete from RecycleBin$ where purgeobj=:1
0pvtkmrrq8usg select file#, block# from seg$ where type# = 3 and ts# = :1
0yv9t4qb1zb2b select CUID_CUST_NO , CUID_ID_TYPE , CUID_ID_RECNO from CUID_TMP where CHGFLAG='D'
104pd9mm3fh9p select blocks, maxblocks, grantor#, priv1, priv2, priv3 from tsq$ where ts#=:1 and user#=:2
1crajpb7j5tyz INSERT INTO STATS$SGA_TARGET_ADVICE ( SNAP_ID , DBID , INSTANCE_NUMBER , SGA_SIZE , SGA_SIZE_FACTOR , ESTD_DB_TIME , ESTD_DB_TIME_FACTOR , ESTD_PHYSICAL_READS ) SELECT :B3 , :B2 , :B1 , SGA_SIZE , SGA_SIZE_FACTOR , ESTD_DB_TIME , ESTD_DB_TIME_FACTOR , ESTD_PHYSICAL_READS FROM V$SGA_TARGET_ADVICE
1dm3bq36vu3g8 insert into iccifnsact values (:b0, :b1, :b2, null , null , :b3, :b4, GREATEST(:b5, :b6), null , :b7, :b8, null , :b9, :b10, :b6, null , null , null , null , null , :b12, null , null , null , :b13, :b14, null , null , :b15, :b16, :b17)
1gu8t96d0bdmu select t.ts#, t.file#, t.block#, nvl(t.bobj#, 0), nvl(t.tab#, 0), t.intcols, nvl(t.clucols, 0), t.audit$, t.flags, t.pctfree$, t.pctused$, t.initrans, t.maxtrans, t.rowcnt, t.blkcnt, t.empcnt, t.avgspc, t.chncnt, t.avgrln, t.analyzetime, t.samplesize, t.cols, t.property, nvl(t.degree, 1), nvl(t.instances, 1), t.avgspc_flb, t.flbcnt, t.kernelcols, nvl(t.trigflag, 0), nvl(t.spare1, 0), nvl(t.spare2, 0), t.spare4, t.spare6, ts.cachedblk, ts.cachehit, ts.logicalread from tab$ t, tab_stats$ ts where t.obj#= :1 and t.obj# = ts.obj# (+)
1uk5m5qbzj1vt BEGIN dbms_workload_repository.create_snapshot; END;
2ym6hhaq30r73 select type#, blocks, extents, minexts, maxexts, extsize, extpct, user#, iniexts, NVL(lists, 65535), NVL(groups, 65535), cachehint, hwmincr, NVL(spare1, 0), NVL(scanhint, 0) from seg$ where ts#=:1 and file#=:2 and block#=:3
350f5yrnnmshs lock table sys.mon_mods$ in exclusive mode nowait
38apjgr0p55ns update ICCICCS set CCSMAXOVERDUE=GREATEST(:b0, CCSMAXOVERDUE) where FNSACTNO=:b1
38gak8u2qm11w select count(*) from CUSVAA_TMP
3m8smr0v7v1m6 INSERT INTO sys.wri$_adv_message_groups (task_id, id, seq, message#, fac, hdr, lm, nl, p1, p2, p3, p4, p5) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)
44au3v5mzpc1c insert into ICCICURMMAST values (:b0, :b1, :b2)
49ms69srnaxzj insert into ICCIRPYV values (:b0, :b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9, :b10, :b11, :b12, :b13, :b14, :b15, :b16, :b17, :b18, :b19, :b20, :b21, :b22, :b23, :b24, :b25, :b26, :b27, :b28, :b29, :b30, :b31, :b32, :b33, :b34, :b35, :b36, :b37, :b38, :b39, :b40, :b41, :b42, :b43, :b44, :b45, :b46, :b47, :b48, :b49, :b50, :b51)
4vja2k2gdtyup insert into ICCICCS values (:b0, '////////////////////////', 0, 0, 0, 0, 0, ' ', 0, 0, 0, ' ', '0', null )
501v412s13r4m update ICCIFNSACT set BORM_FACILITY_NO=:b0 where BORM_MEMB_CUST_AC=:b1
53saa2zkr6wc3 select intcol#, nvl(pos#, 0), col#, nvl(spare1, 0) from ccol$ where con#=:1
569r5k05drsj7 insert into CUMI select CUSV_CUST_NO , CUSV_EDUCATION_CODE , CHGDATE from CUMI_TMP where CHGFLAG<>'D'
5c4qu2zmj3gux select * from ICCIPRODCODE where PRODCODE=to_char(:b0)
5ngzsfstg8tmy select o.owner#, o.name, o.namespace, o.remoteowner, o.linkname, o.subname, o.dataobj#, o.flags from obj$ o where o.obj#=:1
6769wyy3yf66f select pos#, intcol#, col#, spare1, bo#, spare2 from icol$ where obj#=:1
6z06gcfw39pkd SELECT F.TABLESPACE_NAME, TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE), '999, 999') "USED (MB)", TO_CHAR (F.FREE_SPACE, '999, 999') "FREE (MB)", TO_CHAR (T.TOTAL_SPACE, '999, 999') "TOTAL (MB)", TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)), '999')||' %' PER_FREE FROM ( SELECT TABLESPACE_NAME, ROUND (SUM (BLOCKS*(SELECT VALUE/1024 FROM V$PARAMETER WHERE NAME = 'db_block_size')/1024) ) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ) F, ( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES/1048576)) TOTAL_SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ) T WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
78m9ryygp65v5 SELECT /*+ ALL_ROWS */ COUNT(*) FROM ALL_POLICIES V WHERE V.OBJECT_OWNER = :B3 AND V.OBJECT_NAME = :B2 AND (POLICY_NAME LIKE '%xdbrls%' OR POLICY_NAME LIKE '%$xd_%') AND V.FUNCTION = :B1
7gtztzv329wg0 select c.name, u.name from con$ c, cdef$ cd, user$ u where c.con# = cd.con# and cd.enabled = :1 and c.owner# = u.user#
7ng34ruy5awxq select i.obj#, i.ts#, i.file#, i.block#, i.intcols, i.type#, i.flags, i.property, i.pctfree$, i.initrans, i.maxtrans, i.blevel, i.leafcnt, i.distkey, i.lblkkey, i.dblkkey, i.clufac, i.cols, i.analyzetime, i.samplesize, i.dataobj#, nvl(i.degree, 1), nvl(i.instances, 1), i.rowcnt, mod(i.pctthres$, 256), i.indmethod#, i.trunccnt, nvl(c.unicols, 0), nvl(c.deferrable#+c.valid#, 0), nvl(i.spare1, i.intcols), i.spare4, i.spare2, i.spare6, decode(i.pctthres$, null, null, mod(trunc(i.pctthres$/256), 256)), ist.cachedblk, ist.cachehit, ist.logicalread from ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols, min(to_number(bitand(defer, 1))) deferrable#, min(to_number(bitand(defer, 4))) valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#
7v9dyf5r424yh select NEWACTNO into :b0 from OLDNEWACT where OLDACTNO=:b1
7wwv1ybs9zguz update ICCIFNSACT set BORM_ADV_DATE=:b0, BOIS_MATURITY_DATE=:b1, BOIS_UNPD_BAL=:b2, BOIS_UNPD_INT=:b3, BOIS_BAL_FINE=:b4, BOIS_INT_FINE=:b5, BOIS_FINE_FINE=:b6, BORM_LOAN_TRM=:b7, BORM_FIVE_STAT=:b8, BOIS_ARREARS_CTR=:b9, BOIS_ARREARS_SUM=:b10 where BORM_MEMB_CUST_AC=:b11
83taa7kaw59c1 select name, intcol#, segcol#, type#, length, nvl(precision#, 0), decode(type#, 2, nvl(scale, -127/*MAXSB1MINAL*/), 178, scale, 179, scale, 180, scale, 181, scale, 182, scale, 183, scale, 231, scale, 0), null$, fixedstorage, nvl(deflength, 0), default$, rowid, col#, property, nvl(charsetid, 0), nvl(charsetform, 0), spare1, spare2, nvl(spare3, 0) from col$ where obj#=:1 order by intcol#
84qubbrsr0kfn insert into wrh$_latch (snap_id, dbid, instance_number, latch_hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, sleep2, sleep3, sleep4, wait_time) select :snap_id, :dbid, :instance_number, hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, sleep2, sleep3, sleep4, wait_time from v$latch order by hash
9qgtwh66xg6nz update seg$ set type#=:4, blocks=:5, extents=:6, minexts=:7, maxexts=:8, extsize=:9, extpct=:10, user#=:11, iniexts=:12, lists=decode(:13, 65535, NULL, :13), groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17, 0, NULL, :17), scanhint=:18 where ts#=:1 and file#=:2 and block#=:3
9vtm7gy4fr2ny select con# from con$ where owner#=:1 and name=:2
a2any035u1qz1 select owner#, name from con$ where con#=:1
a7nh7j8zmfrzw select CUSV_CUST_NO from CUMI_TMP where CHGFLAG='D'
ackxqhnktxnbc insert into CUSM select CUSM_CUST_ACCT_NO , CUSM_STAT_POST_ADD_NO , CHGDATE from CUSM_TMP where CHGFLAG<>'D'
aq4js2gkfjru8 update tsq$ set blocks=:3, maxblocks=:4, grantor#=:5, priv1=:6, priv2=:7, priv3=:8 where ts#=:1 and user#=:2
b52m6vduutr8j delete from RecycleBin$ where bo=:1
bdv0rkkssq2jm SELECT count(*) FROM user_policies o WHERE o.object_name = :tablename AND (policy_name LIKE '%xdbrls%' OR policy_name LIKE '%$xd_%') AND o.function='CHECKPRIVRLS_SELECTPF'
bsa0wjtftg3uw select file# from file$ where ts#=:1
btzq46kta67dz update obj$ set obj#=:6, type#=:7, ctime=:8, mtime=:9, stime=:10, status=:11, dataobj#=:13, flags=:14, oid$=:15, spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)
bu8tnqr3xv25q select count(*) into :b0 from ICCIFNSACT where BORM_MEMB_CUST_AC=:b1
bwt0pmxhv7qk7 delete from con$ where owner#=:1 and name=:2
chjmy0dxf9mbj insert into ICCICCS values (:b0, :b1, :b2, :b3, :b4, :b5, :b6, :b7, :b8, :b9, :b10, :b11, :b12, :b13)
cn1gtsav2d5jh BEGIN BEGIN IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner)) THEN xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name); END IF; EXCEPTION WHEN OTHERS THEN null; END; BEGIN IF (xdb.DBMS_XDBZ0.is_hierarchy_enabled_internal(sys.dictionary_obj_owner, sys.dictionary_obj_name, sys.dictionary_obj_owner, xdb.DBMS_XDBZ.IS_ENABLED_RESMETADATA)) THEN xdb.XDB_PITRIG_PKG.pitrig_dropmetadata(sys.dictionary_obj_owner, sys.dictionary_obj_name); END IF; EXCEPTION WHEN OTHERS THEN null; END; END;
cp5duhcsj72q0 select CUSM_CUST_ACCT_NO from CUSM_TMP where CHGFLAG='D'
d8z0u8hgj8xdy insert into CUID select CUID_CUST_NO , CUID_ID_MAIN , CUID_ID_TYPE , CUID_ID_RECNO , CUID_ID_NUMBER , CHGDATE from CUID_TMP where CHGFLAG<>'D'
d92h3rjp0y217 begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end;
djs2w2f17nw2z DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN statspack.snap; :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
f80h0xb1qvbsk SELECT sys.wri$_adv_seq_msggroup.nextval FROM dual
g00cj285jmgsw update sys.mon_mods$ set inserts = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time where obj# = :objn
gmn2w09rdxn14 insert into OLDNEWACT values (:b0, :b1)
Back to SQL Statistics
Back to Top
Instance Activity Statistics
• Instance Activity Stats
• Instance Activity Stats - Absolute Values
• Instance Activity Stats - Thread Activity
Back to Top
Instance Activity Stats
Statistic Total per Second per Trans
CPU used by this session 23,388 4.95 4.18
CPU used when call started 21,816 4.61 3.90
CR blocks created 2,794 0.59 0.50
Cached Commit SCN referenced 237,936 50.33 42.50
Commit SCN cached 3 0.00 0.00
DB time 583,424 123.41 104.22
DBWR checkpoint buffers written 402,781 85.20 71.95
DBWR checkpoints 9 0.00 0.00
DBWR fusion writes 255 0.05 0.05
DBWR object drop buffers written 0 0.00 0.00
DBWR thread checkpoint buffers written 221,341 46.82 39.54
DBWR transaction table writes 130 0.03 0.02
DBWR undo block writes 219,272 46.38 39.17
DFO trees parallelized 16 0.00 0.00
PX local messages recv'd 40 0.01 0.01
PX local messages sent 40 0.01 0.01
PX remote messages recv'd 80 0.02 0.01
PX remote messages sent 80 0.02 0.01
Parallel operations not downgraded 16 0.00 0.00
RowCR - row contention 9 0.00 0.00
RowCR attempts 14 0.00 0.00
RowCR hits 5 0.00 0.00
SMON posted for undo segment recovery 0 0.00 0.00
SMON posted for undo segment shrink 9 0.00 0.00
SQL*Net roundtrips to/from client 1,544,063 326.62 275.82
active txn count during cleanout 276,652 58.52 49.42
application wait time 1,620 0.34 0.29
auto extends on undo tablespace 0 0.00 0.00
background checkpoints completed 7 0.00 0.00
background checkpoints started 9 0.00 0.00
background timeouts 21,703 4.59 3.88
branch node splits 337 0.07 0.06
buffer is not pinned count 1,377,184 291.32 246.01
buffer is pinned count 20,996,139 4,441.37 3,750.65
bytes received via SQL*Net from client 7,381,397,183 1,561,408.36 1,318,577.56
bytes sent via SQL*Net to client 149,122,035 31,544.22 26,638.45
calls to get snapshot scn: kcmgss 1,696,712 358.91 303.09
calls to kcmgas 433,435 91.69 77.43
calls to kcmgcs 142,482 30.14 25.45
change write time 4,707 1.00 0.84
cleanout - number of ktugct calls 282,045 59.66 50.38
cleanouts and rollbacks - consistent read gets 55 0.01 0.01
cleanouts only - consistent read gets 2,406 0.51 0.43
cluster key scan block gets 21,886 4.63 3.91
cluster key scans 10,540 2.23 1.88
cluster wait time 2,855 0.60 0.51
commit batch/immediate performed 294 0.06 0.05
commit batch/immediate requested 294 0.06 0.05
commit cleanout failures: block lost 2,227 0.47 0.40
commit cleanout failures: callback failure 750 0.16 0.13
commit cleanout failures: cannot pin 4 0.00 0.00
commit cleanouts 427,610 90.45 76.39
commit cleanouts successfully completed 424,629 89.82 75.85
commit immediate performed 294 0.06 0.05
commit immediate requested 294 0.06 0.05
commit txn count during cleanout 111,557 23.60 19.93
concurrency wait time 515 0.11 0.09
consistent changes 1,716 0.36 0.31
consistent gets 5,037,471 1,065.59 899.87
consistent gets - examination 2,902,016 613.87 518.40
consistent gets direct 0 0.00 0.00
consistent gets from cache 5,037,471 1,065.59 899.87
current blocks converted for CR 0 0.00 0.00
cursor authentications 434 0.09 0.08
data blocks consistent reads - undo records applied 1,519 0.32 0.27
db block changes 8,594,158 1,817.95 1,535.22
db block gets 11,611,321 2,456.18 2,074.19
db block gets direct 1,167,830 247.03 208.62
db block gets from cache 10,443,491 2,209.14 1,865.58
deferred (CURRENT) block cleanout applications 20,786 4.40 3.71
dirty buffers inspected 25,007 5.29 4.47
drop segment calls in space pressure 0 0.00 0.00
enqueue conversions 6,734 1.42 1.20
enqueue releases 595,149 125.89 106.31
enqueue requests 595,158 125.90 106.32
enqueue timeouts 9 0.00 0.00
enqueue waits 7,901 1.67 1.41
exchange deadlocks 1 0.00 0.00
execute count 1,675,112 354.34 299.23
free buffer inspected 536,832 113.56 95.90
free buffer requested 746,999 158.01 133.44
gc CPU used by this session 9,099 1.92 1.63
gc cr block build time 13 0.00 0.00
gc cr block flush time 143 0.03 0.03
gc cr block receive time 474 0.10 0.08
gc cr block send time 36 0.01 0.01
gc cr blocks received 4,142 0.88 0.74
gc cr blocks served 10,675 2.26 1.91
gc current block flush time 23 0.00 0.00
gc current block pin time 34 0.01 0.01
gc current block receive time 1,212 0.26 0.22
gc current block send time 52 0.01 0.01
gc current blocks received 15,502 3.28 2.77
gc current blocks served 17,534 3.71 3.13
gc local grants 405,329 85.74 72.41
gc remote grants 318,630 67.40 56.92
gcs messages sent 1,129,094 238.84 201.70
ges messages sent 90,695 19.18 16.20
global enqueue get time 1,707 0.36 0.30
global enqueue gets async 12,731 2.69 2.27
global enqueue gets sync 190,492 40.30 34.03
global enqueue releases 190,328 40.26 34.00
global undo segment hints helped 0 0.00 0.00
global undo segment hints were stale 0 0.00 0.00
heap block compress 108,758 23.01 19.43
hot buffers moved to head of LRU 18,652 3.95 3.33
immediate (CR) block cleanout applications 2,462 0.52 0.44
immediate (CURRENT) block cleanout applications 325,184 68.79 58.09
index crx upgrade (positioned) 4,663 0.99 0.83
index fast full scans (full) 13 0.00 0.00
index fetch by key 852,181 180.26 152.23
index scans kdiixs1 339,583 71.83 60.66
leaf node 90-10 splits 34 0.01 0.01
leaf node splits 106,552 22.54 19.03
lob reads 11 0.00 0.00
lob writes 83 0.02 0.01
lob writes unaligned 83 0.02 0.01
local undo segment hints helped 0 0.00 0.00
local undo segment hints were stale 0 0.00 0.00
logons cumulative 61 0.01 0.01
messages received 20,040 4.24 3.58
messages sent 19,880 4.21 3.55
no buffer to keep pinned count 0 0.00 0.00
no work - consistent read gets 1,513,070 320.06 270.29
opened cursors cumulative 183,375 38.79 32.76
parse count (failures) 1 0.00 0.00
parse count (hard) 143 0.03 0.03
parse count (total) 182,780 38.66 32.65
parse time cpu 27 0.01 0.00
parse time elapsed 338 0.07 0.06
physical read IO requests 82,815 17.52 14.79
physical read bytes 2,643,378,176 559,161.45 472,200.46
physical read total IO requests 98,871 20.91 17.66
physical read total bytes 2,905,491,456 614,607.04 519,023.13
physical read total multi block requests 24,089 5.10 4.30
physical reads 322,678 68.26 57.64
physical reads cache 213,728 45.21 38.18
physical reads cache prefetch 191,830 40.58 34.27
physical reads direct 108,950 23.05 19.46
physical reads direct temporary tablespace 108,812 23.02 19.44
physical reads prefetch warmup 0 0.00 0.00
physical write IO requests 223,456 47.27 39.92
physical write bytes 14,042,071,040 2,970,360.02 2,508,408.55
physical write total IO requests 133,835 28.31 23.91
physical write total bytes 23,114,268,672 4,889,428.30 4,129,022.63
physical write total multi block requests 116,135 24.57 20.75
physical writes 1,714,120 362.59 306.20
physical writes direct 1,276,780 270.08 228.08
physical writes direct (lob) 0 0.00 0.00
physical writes direct temporary tablespace 108,812 23.02 19.44
physical writes from cache 437,340 92.51 78.12
physical writes non checkpoint 1,673,703 354.04 298.98
pinned buffers inspected 10 0.00 0.00
prefetch clients - default 0 0.00 0.00
prefetch warmup blocks aged out before use 0 0.00 0.00
prefetch warmup blocks flushed out before use 0 0.00 0.00
prefetched blocks aged out before use 0 0.00 0.00
process last non-idle time 4,730 1.00 0.84
queries parallelized 16 0.00 0.00
recursive calls 1,654,650 350.01 295.58
recursive cpu usage 2,641 0.56 0.47
redo blocks written 8,766,094 1,854.32 1,565.93
redo buffer allocation retries 24 0.01 0.00
redo entries 4,707,068 995.70 840.85
redo log space requests 34 0.01 0.01
redo log space wait time 50 0.01 0.01
redo ordering marks 277,042 58.60 49.49
redo size 4,343,559,400 918,805.72 775,912.72
redo subscn max counts 2,693 0.57 0.48
redo synch time 408 0.09 0.07
redo synch writes 6,984 1.48 1.25
redo wastage 1,969,620 416.64 351.84
redo write time 5,090 1.08 0.91
redo writer latching time 1 0.00 0.00
redo writes 5,494 1.16 0.98
rollback changes - undo records applied 166,609 35.24 29.76
rollbacks only - consistent read gets 1,463 0.31 0.26
rows fetched via callback 342,159 72.38 61.12
session connect time 1,461 0.31 0.26
session cursor cache hits 180,472 38.18 32.24
session logical reads 16,648,792 3,521.77 2,974.06
session pga memory 37,393,448 7,909.94 6,679.79
session pga memory max 45,192,232 9,559.64 8,072.92
session uga memory 30,067,312,240 6,360,225.77 5,371,081.14
session uga memory max 61,930,448 13,100.33 11,062.96
shared hash latch upgrades - no wait 6,364 1.35 1.14
shared hash latch upgrades - wait 0 0.00 0.00
sorts (disk) 4 0.00 0.00
sorts (memory) 2,857 0.60 0.51
sorts (rows) 42,379,505 8,964.66 7,570.47
space was found by tune down 0 0.00 0.00
space was not found by tune down 0 0.00 0.00
sql area evicted 7 0.00 0.00
sql area purged 44 0.01 0.01
steps of tune down ret. in space pressure 0 0.00 0.00
summed dirty queue length 35,067 7.42 6.26
switch current to new buffer 17 0.00 0.00
table fetch by rowid 680,469 143.94 121.56
table fetch continued row 0 0.00 0.00
table scan blocks gotten 790,986 167.32 141.30
table scan rows gotten 52,989,363 11,208.99 9,465.77
table scans (long tables) 4 0.00 0.00
table scans (short tables) 169,201 35.79 30.23
total number of times SMON posted 259 0.05 0.05
transaction lock background get time 0 0.00 0.00
transaction lock background gets 0 0.00 0.00
transaction lock foreground requests 0 0.00 0.00
transaction lock foreground wait time 0 0.00 0.00
transaction rollbacks 294 0.06 0.05
tune down retentions in space pressure 0 0.00 0.00
undo change vector size 1,451,085,596 306,952.35 259,215.00
user I/O wait time 11,992 2.54 2.14
user calls 1,544,383 326.69 275.88
user commits 812 0.17 0.15
user rollbacks 4,786 1.01 0.85
workarea executions - onepass 1 0.00 0.00
workarea executions - optimal 1,616 0.34 0.29
write clones created in background 0 0.00 0.00
write clones created in foreground 11 0.00 0.00
Back to Instance Activity Statistics
Back to Top
Instance Activity Stats - Absolute Values
• Statistics with absolute values (should not be diffed)
Statistic Begin Value End Value
session cursor cache count 3,024 3,592
opened cursors current 37 39
logons current 24 26
Back to Instance Activity Statistics
Back to Top
Instance Activity Stats - Thread Activity
• Statistics identified by '(derived)' come from sources other than SYSSTAT
Statistic Total per Hour
log switches (derived) 9 6.85
Back to Instance Activity Statistics
Back to Top
IO Stats
• Tablespace IO Stats
• File IO Stats
Back to Top
Tablespace IO Stats
• ordered by IOs (Reads + Writes) desc
Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
ICCIDAT01 67,408 14 3.76 3.17 160,261 34 6 0.00
UNDOTBS1 10 0 12.00 1.00 57,771 12 625 0.02
TEMP 15,022 3 8.74 7.24 3,831 1 0 0.00
USERS 68 0 5.44 1.00 971 0 0 0.00
SYSAUX 263 0 5.48 1.00 458 0 0 0.00
SYSTEM 32 0 5.94 1.00 158 0 3 23.33
UNDOTBS2 6 0 16.67 1.00 6 0 0 0.00
显示每个表空间的I/O统计。根据Oracle经验，Av Rd(ms) [Average Reads in milliseconds]不应该超过30，否则认为有I/O争用。
Back to IO Stats
Back to Top
File IO Stats
• ordered by Tablespace, File
Tablespace Filename Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)
ICCIDAT01 /dev/rora_icci01 5,919 1 4.30 3.73 15,161 3 1 0.00
ICCIDAT01 /dev/rora_icci02 7,692 2 4.12 3.18 16,555 4 0 0.00
ICCIDAT01 /dev/rora_icci03 6,563 1 2.59 3.80 15,746 3 0 0.00
ICCIDAT01 /dev/rora_icci04 8,076 2 2.93 3.11 16,164 3 0 0.00
ICCIDAT01 /dev/rora_icci05 6,555 1 2.61 3.31 21,958 5 0 0.00
ICCIDAT01 /dev/rora_icci06 6,943 1 4.03 3.41 20,574 4 0 0.00
ICCIDAT01 /dev/rora_icci07 7,929 2 4.12 2.87 18,263 4 0 0.00
ICCIDAT01 /dev/rora_icci08 7,719 2 3.83 2.99 17,361 4 0 0.00
ICCIDAT01 /dev/rora_icci09 6,794 1 4.79 3.29 18,425 4 0 0.00
ICCIDAT01 /dev/rora_icci10 211 0 5.31 1.00 6 0 0 0.00
ICCIDAT01 /dev/rora_icci11 1,168 0 4.45 1.00 6 0 0 0.00
ICCIDAT01 /dev/rora_icci12 478 0 4.23 1.00 6 0 0 0.00
ICCIDAT01 /dev/rora_icci13 355 0 5.13 1.00 6 0 0 0.00
ICCIDAT01 /dev/rora_icci14 411 0 4.91 1.00 6 0 1 0.00
ICCIDAT01 /dev/rora_icci15 172 0 5.29 1.00 6 0 1 0.00
ICCIDAT01 /dev/rora_icci16 119 0 7.23 1.00 6 0 1 0.00
ICCIDAT01 /dev/rora_icci17 227 0 6.26 1.00 6 0 1 0.00
ICCIDAT01 /dev/rora_icci18 77 0 8.44 1.00 6 0 1 0.00
SYSAUX /dev/rora_SYSAUX 263 0 5.48 1.00 458 0 0 0.00
SYSTEM /dev/rora_SYSTEM 32 0 5.94 1.00 158 0 3 23.33
TEMP /dev/rora_TEMP 3,653 1 5.67 6.61 827 0 0
TEMP /dev/rora_TEMP2 2,569 1 4.42 6.70 556 0 0
TEMP /dev/rora_TEMP3 1,022 0 2.50 16.86 557 0 0
TEMP /dev/rora_TEMP5 7,778 2 12.43 6.46 1,891 0 0
UNDOTBS1 /dev/rora_UNDO0101 10 0 12.00 1.00 57,771 12 625 0.02
UNDOTBS2 /dev/rora_UNDO0201 6 0 16.67 1.00 6 0 0 0.00
USERS /dev/rora_USERS 68 0 5.44 1.00 971 0 0 0.00
Back to IO Stats
Back to Top
Buffer Pool Statistics
• Standard block size Pools D: default, K: keep, R: recycle
• Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
P Number of Buffers Pool Hit% Buffer Gets Physical Reads Physical Writes Free Buff Wait Writ Comp Wait Buffer Busy Waits
D 401,071 99 15,480,754 213,729 437,340 0 0 634

Back to Top
Advisory Statistics
• Instance Recovery Stats
• Buffer Pool Advisory
• PGA Aggr Summary
• PGA Aggr Target Stats
• PGA Aggr Target Histogram
• PGA Memory Advisory
• Shared Pool Advisory
• SGA Target Advisory
• Streams Pool Advisory
• Java Pool Advisory
Back to Top
Instance Recovery Stats
• B: Begin snapshot, E: End snapshot
Targt MTTR (s) Estd MTTR (s) Recovery Estd IOs Actual Redo Blks Target Redo Blks Log File Size Redo Blks Log Ckpt Timeout Redo Blks Log Ckpt Interval Redo Blks
B 0 11 369 2316 5807 1883700 5807
E 0 98 116200 1828613 1883700 1883700 5033355
Back to Advisory Statistics
Back to Top
Buffer Pool Advisory
• Only rows with estimated physical reads >0 are displayed
• ordered by Block Size, Buffers For Estimate
P Size for Est (M) Size Factor Buffers for Estimate Est Phys Read Factor Estimated Physical Reads
D 320 0.10 38,380 1.34 10,351,726
D 640 0.19 76,760 1.25 9,657,000
D 960 0.29 115,140 1.08 8,365,242
D 1,280 0.38 153,520 1.04 8,059,415
D 1,600 0.48 191,900 1.02 7,878,202
D 1,920 0.57 230,280 1.01 7,841,140
D 2,240 0.67 268,660 1.01 7,829,141
D 2,560 0.77 307,040 1.01 7,817,370
D 2,880 0.86 345,420 1.01 7,804,884
D 3,200 0.96 383,800 1.00 7,784,014
D 3,344 1.00 401,071 1.00 7,748,403
D 3,520 1.05 422,180 0.99 7,702,243
D 3,840 1.15 460,560 0.99 7,680,429
D 4,160 1.24 498,940 0.99 7,663,046
D 4,480 1.34 537,320 0.99 7,653,232
D 4,800 1.44 575,700 0.99 7,645,544
D 5,120 1.53 614,080 0.98 7,630,008
D 5,440 1.63 652,460 0.98 7,616,886
D 5,760 1.72 690,840 0.98 7,614,591
D 6,080 1.82 729,220 0.98 7,613,191
D 6,400 1.91 767,600 0.98 7,599,930
Back to Advisory Statistics
Back to Top
PGA Aggr Summary
• PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written
87.91 1,100 151
Back to Advisory Statistics
Back to Top
PGA Aggr Target Stats
• B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
• Auto PGA Target - actual workarea memory target
• W/A PGA Used - amount of memory used for all Workareas (manual + auto)
• %PGA W/A Mem - percentage of PGA memory allocated to workareas
• %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
• %Man W/A Mem - percentage of workarea memory under manual control
PGA Aggr Target(M) Auto PGA Target(M) PGA Mem Alloc(M) W/A PGA Used(M) %PGA W/A Mem %Auto W/A Mem %Man W/A Mem Global Mem Bound(K)
B 1,024 862 150.36 0.00 0.00 0.00 0.00 104,850
E 1,024 860 154.14 0.00 0.00 0.00 0.00 104,850
Back to Advisory Statistics
Back to Top
PGA Aggr Target Histogram
• Optimal Executions are purely in-memory operations
Low Optimal High Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs
2K 4K 1,385 1,385 0 0
64K 128K 28 28 0 0
128K 256K 5 5 0 0
256K 512K 79 79 0 0
512K 1024K 108 108 0 0
1M 2M 7 7 0 0
8M 16M 1 1 0 0
128M 256M 3 2 1 0
256M 512M 1 1 0 0
Back to Advisory Statistics
Back to Top
PGA Memory Advisory
• When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0
PGA Target Est (MB) Size Factr W/A MB Processed Estd Extra W/A MB Read/ Written to Disk Estd PGA Cache Hit % Estd PGA Overalloc Count
128 0.13 4,652.12 2,895.99 62.00 0
256 0.25 4,652.12 2,857.13 62.00 0
512 0.50 4,652.12 2,857.13 62.00 0
768 0.75 4,652.12 2,857.13 62.00 0
1,024 1.00 4,652.12 717.82 87.00 0
1,229 1.20 4,652.12 717.82 87.00 0
1,434 1.40 4,652.12 717.82 87.00 0
1,638 1.60 4,652.12 717.82 87.00 0
1,843 1.80 4,652.12 717.82 87.00 0
2,048 2.00 4,652.12 717.82 87.00 0
3,072 3.00 4,652.12 717.82 87.00 0
4,096 4.00 4,652.12 717.82 87.00 0
6,144 6.00 4,652.12 717.82 87.00 0
8,192 8.00 4,652.12 717.82 87.00 0
Back to Advisory Statistics
Back to Top
Shared Pool Advisory
• SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
• Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.
Shared Pool Size(M) SP Size Factr Est LC Size (M) Est LC Mem Obj Est LC Time Saved (s) Est LC Time Saved Factr Est LC Load Time (s) Est LC Load Time Factr Est LC Mem Obj Hits
304 0.43 78 7,626 64,842 1.00 31 1.00 3,206,955
384 0.55 78 7,626 64,842 1.00 31 1.00 3,206,955
464 0.66 78 7,626 64,842 1.00 31 1.00 3,206,955
544 0.77 78 7,626 64,842 1.00 31 1.00 3,206,955
624 0.89 78 7,626 64,842 1.00 31 1.00 3,206,955
704 1.00 78 7,626 64,842 1.00 31 1.00 3,206,955
784 1.11 78 7,626 64,842 1.00 31 1.00 3,206,955
864 1.23 78 7,626 64,842 1.00 31 1.00 3,206,955
944 1.34 78 7,626 64,842 1.00 31 1.00 3,206,955
1,024 1.45 78 7,626 64,842 1.00 31 1.00 3,206,955
1,104 1.57 78 7,626 64,842 1.00 31 1.00 3,206,955
1,184 1.68 78 7,626 64,842 1.00 31 1.00 3,206,955
1,264 1.80 78 7,626 64,842 1.00 31 1.00 3,206,955
1,344 1.91 78 7,626 64,842 1.00 31 1.00 3,206,955
1,424 2.02 78 7,626 64,842 1.00 31 1.00 3,206,955
Back to Advisory Statistics
Back to Top
SGA Target Advisory
SGA Target Size (M) SGA Size Factor Est DB Time (s) Est Physical Reads
1,024 0.25 9,060 9,742,760
2,048 0.50 7,612 7,948,245
3,072 0.75 7,563 7,886,258
4,096 1.00 7,451 7,748,338
5,120 1.25 7,423 7,713,470
6,144 1.50 7,397 7,680,927
7,168 1.75 7,385 7,666,980
8,192 2.00 7,385 7,666,980
Back to Advisory Statistics
Back to Top
Streams Pool Advisory
No data exists for this section of the report.
Back to Advisory Statistics
Back to Top
Java Pool Advisory
No data exists for this section of the report.
Back to Advisory Statistics
Back to Top
Wait Statistics
• Buffer Wait Statistics
• Enqueue Activity
Back to Top
Buffer Wait Statistics
• ordered by wait time desc, waits desc
Class Waits Total Wait Time (s) Avg Time (ms)
data block 3 0 23
undo header 616 0 0
file header block 8 0 0
undo block 7 0 0
Back to Wait Statistics
Back to Top
Enqueue Activity
• only enqueues with waits are shown
• Enqueue stats gathered prior to 10g should not be compared with 10g data
• ordered by Wait Time desc, Waits desc
Enqueue Type (Request Reason) Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms)
FB-Format Block 14,075 14,075 0 7,033 3 0.43
US-Undo Segment 964 964 0 556 0 0.32
WF-AWR Flush 24 24 0 14 0 9.00
HW-Segment High Water Mark 4,223 4,223 0 37 0 1.22
CF-Controlfile Transaction 10,548 10,548 0 58 0 0.67
TX-Transaction (index contention) 1 1 0 1 0 35.00
TM-DML 121,768 121,761 6 70 0 0.43
PS-PX Process Reservation 103 103 0 46 0 0.65
TT-Tablespace 9,933 9,933 0 39 0 0.54
TD-KTF map table enqueue (KTF dump entries) 12 12 0 12 0 1.42
TA-Instance Undo 18 18 0 13 0 0.38
PI-Remote PX Process Spawn Status 16 16 0 8 0 0.50
MW-MWIN Schedule 3 3 0 3 0 0.67
DR-Distributed Recovery 3 3 0 3 0 0.33
TS-Temporary Segment 14 11 3 3 0 0.33
AF-Advisor Framework (task serialization) 14 14 0 1 0 1.00
JS-Job Scheduler (job run lock - synchronize) 2 2 0 1 0 1.00
UL-User-defined 2 2 0 1 0 1.00
MD-Materialized View Log DDL 6 6 0 2 0 0.00
Back to Wait Statistics
Back to Top
Undo Statistics
• Undo Segment Summary
• Undo Segment Stats
Back to Top
Undo Segment Summary
• Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
• STO - Snapshot Too Old count, OOS - Out of Space count
• Undo segment block stats:
• uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
• eS - expired Stolen, eR - expired Released, eU - expired reUsed
Undo TS# Num Undo Blocks (K) Number of Transactions Max Qry Len (s) Max Tx Concurcy Min/Max TR (mins) STO/ OOS uS/uR/uU/ eS/eR/eU
1 219.12 113,405 0 6 130.95/239.25 0/0 0/0/0/13/24256/0
Back to Undo Statistics
Back to Top
Undo Segment Stats
• Most recent 35 Undostat rows, ordered by Time desc
End Time Num Undo Blocks Number of Transactions Max Qry Len (s) Max Tx Concy Tun Ret (mins) STO/ OOS uS/uR/uU/ eS/eR/eU
25-Dec 15:18 182,021 74,309 0 5 131 0/0 0/0/0/13/24256/0
25-Dec 15:08 57 170 0 3 239 0/0 0/0/0/0/0/0
25-Dec 14:58 68 31 0 2 229 0/0 0/0/0/0/0/0
25-Dec 14:48 194 4,256 0 4 219 0/0 0/0/0/0/0/0
25-Dec 14:38 570 12,299 0 5 209 0/0 0/0/0/0/0/0
25-Dec 14:28 36,047 21,328 0 6 200 0/0 0/0/0/0/0/0
25-Dec 14:18 70 907 0 3 162 0/0 0/0/0/0/0/0
25-Dec 14:08 91 105 0 3 154 0/0 0/0/0/0/0/0
Back to Undo Statistics
Back to Top
Latch Statistics
• Latch Activity
• Latch Sleep Breakdown
• Latch Miss Sources
• Parent Latch Statistics
• Child Latch Statistics
Back to Top
Latch Activity
• "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests
• "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
• "Pct Misses" for both should be very close to 0.0
Latch Name Get Requests Pct Get Miss Avg Slps /Miss Wait Time (s) NoWait Requests Pct NoWait Miss
ASM db client latch 11,883 0.00 0 0
AWR Alerted Metric Element list 18,252 0.00 0 0
Consistent RBA 5,508 0.02 0.00 0 0
FOB s.o list latch 731 0.00 0 0
JS broadcast add buf latch 6,193 0.00 0 0
JS broadcast drop buf latch 6,194 0.00 0 0
JS broadcast load blnc latch 6,057 0.00 0 0
JS mem alloc latch 8 0.00 0 0
JS queue access latch 8 0.00 0 0
JS queue state obj latch 218,086 0.00 0 0
JS slv state obj latch 31 0.00 0 0
KCL gc element parent latch 2,803,392 0.04 0.01 0 108 0.00
KJC message pool free list 43,168 0.06 0.00 0 14,532 0.01
KJCT flow control latch 563,875 0.00 0.00 0 0
KMG MMAN ready and startup request latch 1,576 0.00 0 0
KSXR large replies 320 0.00 0 0
KTF sga latch 23 0.00 0 1,534 0.00
KWQMN job cache list latch 352 0.00 0 0
KWQP Prop Status 5 0.00 0 0
MQL Tracking Latch 0 0 94 0.00
Memory Management Latch 0 0 1,576 0.00
OS process 207 0.00 0 0
OS process allocation 1,717 0.00 0 0
OS process: request allocation 73 0.00 0 0
PL/SQL warning settings 226 0.00 0 0
SGA IO buffer pool latch 20,679 0.06 0.00 0 20,869 0.00
SQL memory manager latch 7 0.00 0 1,575 0.00
SQL memory manager workarea list latch 439,442 0.00 0 0
Shared B-Tree 182 0.00 0 0
Undo Hint Latch 0 0 12 0.00
active checkpoint queue latch 7,835 0.00 0 0
active service list 50,936 0.00 0 1,621 0.00
archive control 5 0.00 0 0
begin backup scn array 72,901 0.00 0.00 0 0
business card 32 0.00 0 0
cache buffer handles 331,153 0.02 0.00 0 0
cache buffers chains 48,189,073 0.00 0.00 0 1,201,379 0.00
cache buffers lru chain 891,796 0.34 0.00 0 991,605 0.23
cache table scan latch 0 0 10,309 0.01
channel handle pool latch 99 0.00 0 0
channel operations parent latch 490,324 0.01 0.00 0 0
checkpoint queue latch 671,856 0.01 0.00 0 555,469 0.02
client/application info 335 0.00 0 0
commit callback allocation 12 0.00 0 0
compile environment latch 173,428 0.00 0 0
dml lock allocation 243,087 0.00 0.00 0 0
dummy allocation 134 0.00 0 0
enqueue hash chains 1,539,499 0.01 0.03 0 263 0.00
enqueues 855,207 0.02 0.00 0 0
error message lists 64 0.00 0 0
event group latch 38 0.00 0 0
file cache latch 4,694 0.00 0 0
gcs drop object freelist 8,451 0.19 0.00 0 0
gcs opaque info freelist 38,584 0.00 0.00 0 0
gcs partitioned table hash 9,801,867 0.00 0 0
gcs remaster request queue 31 0.00 0 0
gcs remastering latch 1,014,198 0.00 0.33 0 0
gcs resource freelist 1,154,551 0.03 0.00 0 771,650 0.00
gcs resource hash 3,815,373 0.02 0.00 0 2 0.00
gcs resource scan list 4 0.00 0 0
gcs shadows freelist 795,482 0.00 0.00 0 779,648 0.00
ges caches resource lists 209,655 0.02 0.00 0 121,613 0.01
ges deadlock list 840 0.00 0 0
ges domain table 366,702 0.00 0 0
ges enqueue table freelist 487,875 0.00 0 0
ges group table 543,887 0.00 0 0
ges process hash list 59,503 0.00 0 0
ges process parent latch 908,232 0.00 0 1 0.00
ges process table freelist 73 0.00 0 0
ges resource hash list 862,590 0.02 0.28 0 72,266 0.01
ges resource scan list 534 0.00 0 0
ges resource table freelist 135,406 0.00 0.00 0 0
ges synchronous data 160 0.63 0.00 0 2,954 0.07
ges timeout list 3,256 0.00 0 4,478 0.00
global KZLD latch for mem in SGA 21 0.00 0 0
hash table column usage latch 59 0.00 0 1,279 0.00
hash table modification latch 116 0.00 0 0
job workq parent latch 0 0 14 0.00
job_queue_processes parameter latch 86 0.00 0 0
kks stats 384 0.00 0 0
ksuosstats global area 329 0.00 0 0
ktm global data 296 0.00 0 0
kwqbsn:qsga 182 0.00 0 0
lgwr LWN SCN 6,547 0.18 0.00 0 0
library cache 235,060 0.00 0.00 0 22 0.00
library cache load lock 486 0.00 0 0
library cache lock 49,284 0.00 0 0
library cache lock allocation 566 0.00 0 0
library cache pin 27,863 0.00 0.00 0 0
library cache pin allocation 204 0.00 0 0
list of block allocation 10,101 0.00 0 0
loader state object freelist 108 0.00 0 0
longop free list parent 6 0.00 0 6 0.00
message pool operations parent latch 1,424 0.00 0 0
messages 222,581 0.00 0.00 0 0
mostly latch-free SCN 6,649 1.43 0.00 0 0
multiblock read objects 29,230 0.03 0.00 0 0
name-service memory objects 18,842 0.00 0 0
name-service namespace bucket 56,712 0.00 0 0
name-service namespace objects 15 0.00 0 0
name-service pending queue 6,436 0.00 0 0
name-service request 44 0.00 0 0
name-service request queue 57,312 0.00 0 0
ncodef allocation latch 77 0.00 0 0
object queue header heap 37,721 0.00 0 7,457 0.00
object queue header operation 2,706,992 0.06 0.00 0 0
object stats modification 22 0.00 0 0
parallel query alloc buffer 939 0.00 0 0
parallel query stats 72 0.00 0 0
parallel txn reco latch 630 0.00 0 0
parameter list 193 0.00 0 0
parameter table allocation management 68 0.00 0 0
post/wait queue 4,205 0.00 0 2,712 0.00
process allocation 46,895 0.00 0 38 0.00
process group creation 73 0.00 0 0
process queue 175 0.00 0 0
process queue reference 2,621 0.00 0 240 62.50
qmn task queue latch 668 0.15 1.00 0 0
query server freelists 159 0.00 0 0
query server process 8 0.00 0 7 0.00
queued dump request 23,628 0.00 0 0
redo allocation 21,206 0.57 0.00 0 4,706,826 0.02
redo copy 0 0 4,707,106 0.01
redo writing 29,944 0.01 0.00 0 0
resmgr group change latch 69 0.00 0 0
resmgr:actses active list 137 0.00 0 0
resmgr:actses change group 52 0.00 0 0
resmgr:free threads list 130 0.00 0 0
resmgr:schema config 7 0.00 0 0
row cache objects 1,644,149 0.00 0.00 0 321 0.00
rules engine rule set statistics 500 0.00 0 0
sequence cache 360 0.00 0 0
session allocation 535,514 0.00 0.00 0 0
session idle bit 3,262,141 0.00 0.00 0 0
session state list latch 166 0.00 0 0
session switching 77 0.00 0 0
session timer 1,620 0.00 0 0
shared pool 60,359 0.00 0.00 0 0
shared pool sim alloc 13 0.00 0 0
shared pool simulator 4,246 0.00 0 0
simulator hash latch 1,862,803 0.00 0 0
simulator lru latch 1,719,480 0.01 0.00 0 46,053 0.00
slave class 2 0.00 0 0
slave class create 8 12.50 1.00 0 0
sort extent pool 1,284 0.00 0 0
state object free list 4 0.00 0 0
statistics aggregation 280 0.00 0 0
temp lob duration state obj allocation 2 0.00 0 0
threshold alerts latch 202 0.00 0 0
transaction allocation 211 0.00 0 0
transaction branch allocation 77 0.00 0 0
undo global data 779,759 0.07 0.00 0 0
user lock 102 0.00 0 0
Back to Latch Statistics
Back to Top
Latch Sleep Breakdown
• ordered by misses desc
Latch Name Get Requests Misses Sleeps Spin Gets Sleep1 Sleep2 Sleep3
cache buffers lru chain 891,796 3,061 1 3,060 0 0 0
object queue header operation 2,706,992 1,755 3 1,752 0 0 0
KCL gc element parent latch 2,803,392 1,186 11 1,176 0 0 0
cache buffers chains 48,189,073 496 1 495 0 0 0
ges resource hash list 862,590 160 44 116 0 0 0
enqueue hash chains 1,539,499 79 2 78 0 0 0
gcs remastering latch 1,014,198 3 1 2 0 0 0
qmn task queue latch 668 1 1 0 0 0 0
slave class create 8 1 1 0 0 0 0
Back to Latch Statistics
Back to Top
Latch Miss Sources
• only latches with sleeps are shown
• ordered by name, sleeps desc
Latch Name Where NoWait Misses Sleeps Waiter Sleeps
KCL gc element parent latch kclrwrite 0 8 0
KCL gc element parent latch kclnfndnewm 0 4 6
KCL gc element parent latch KCLUNLNK 0 1 1
KCL gc element parent latch kclbla 0 1 0
KCL gc element parent latch kclulb 0 1 1
KCL gc element parent latch kclzcl 0 1 0
cache buffers chains kcbnew: new latch again 0 2 0
cache buffers chains kclwrt 0 1 0
cache buffers lru chain kcbzgws 0 1 0
enqueue hash chains ksqcmi: if lk mode not requested 0 2 0
event range base latch No latch 0 1 1
gcs remastering latch 69 0 1 0
ges resource hash list kjlmfnd: search for lockp by rename and inst id 0 23 0
ges resource hash list kjakcai: search for resp by resname 0 13 0
ges resource hash list kjrmas1: lookup master node 0 5 0
ges resource hash list kjlrlr: remove lock from resource queue 0 2 33
ges resource hash list kjcvscn: remove from scan queue 0 1 0
object queue header operation kcbo_switch_q_bg 0 3 0
object queue header operation kcbo_switch_mq_bg 0 2 4
object queue header operation kcbw_unlink_q 0 2 0
object queue header operation kcbw_link_q 0 1 0
slave class create ksvcreate 0 1 0
Back to Latch Statistics
Back to Top
Parent Latch Statistics
No data exists for this section of the report.
Back to Latch Statistics
Back to Top
Child Latch Statistics
No data exists for this section of the report.
Back to Latch Statistics
Back to Top
Segment Statistics
• Segments by Logical Reads
• Segments by Physical Reads
• Segments by Row Lock Waits
• Segments by ITL Waits
• Segments by Buffer Busy Waits
• Segments by Global Cache Buffer Busy
• Segments by CR Blocks Received
• Segments by Current Blocks Received
Back to Top
Segments by Logical Reads
• Total Logical Reads: 16,648,792
• Captured Segments account for 85.2% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Logical Reads %Total
ICCI01 ICCIDAT01 ICCICCS_PK INDEX 1,544,848 9.28
ICCI01 ICCIDAT01 CUSCAD_TMP TABLE 1,349,536 8.11
ICCI01 ICCIDAT01 ICCIFNSACT_PK INDEX 1,268,400 7.62
ICCI01 ICCIDAT01 IND_OLDNEWACT INDEX 1,071,072 6.43
ICCI01 ICCIDAT01 CUID_PK INDEX 935,584 5.62
Back to Segment Statistics
Back to Top
Segments by Physical Reads
• Total Physical Reads: 322,678
• Captured Segments account for 64.2% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Physical Reads %Total
ICCI01 ICCIDAT01 CUID_TMP TABLE 116,417 36.08
ICCI01 ICCIDAT01 CUMI_TMP TABLE 44,086 13.66
ICCI01 ICCIDAT01 CUSM_TMP TABLE 26,078 8.08
ICCI01 ICCIDAT01 CUSVAA_TMP_PK INDEX 19,554 6.06
ICCI01 ICCIDAT01 CUID TABLE 259 0.08
Back to Segment Statistics
Back to Top
Segments by Row Lock Waits
当一个进程予在正被其它进程锁住的数据行上获得排它锁时发生这种等待。这种等待经常是由于在一个有主键索引的表上做大量INSERT操作。
No data exists for this section of the report.
Back to Segment Statistics
Back to Top
Segments by ITL Waits
No data exists for this section of the report.
Back to Segment Statistics
Back to Top
Segments by Buffer Busy Waits
No data exists for this section of the report.
Back to Segment Statistics
Back to Top
Segments by Global Cache Buffer Busy
• % of Capture shows % of GC Buffer Busy for each top segment compared
• with GC Buffer Busy for all segments captured by the Snapshot
Owner Tablespace Name Object Name Subobject Name Obj. Type GC Buffer Busy % of Capture
SYS SYSTEM TSQ$ TABLE 2 100.00
Back to Segment Statistics
Back to Top
Segments by CR Blocks Received
• Total CR Blocks Received: 4,142
• Captured Segments account for 95.6% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type CR Blocks Received %Total
SYS SYSTEM USER$ TABLE 1,001 24.17
SYS SYSTEM TSQ$ TABLE 722 17.43
SYS SYSTEM SEG$ TABLE 446 10.77
SYS SYSTEM OBJ$ TABLE 264 6.37
SYS SYSTEM I_OBJ2 INDEX 174 4.20
Back to Segment Statistics
Back to Top
Segments by Current Blocks Received
• Total Current Blocks Received: 15,502
• Captured Segments account for 84.8% of Total
Owner Tablespace Name Object Name Subobject Name Obj. Type Current Blocks Received %Total
ICCI01 ICCIDAT01 CUSM_TMP TABLE 5,764 37.18
ICCI01 ICCIDAT01 CUMI_TMP TABLE 2,794 18.02
ICCI01 ICCIDAT01 CUID_TMP TABLE 2,585 16.68
SYS SYSTEM SEG$ TABLE 361 2.33
SYS SYSTEM TSQ$ TABLE 361 2.33
Back to Segment Statistics
Back to Top
Dictionary Cache Statistics
• Dictionary Cache Stats
• Dictionary Cache Stats (RAC)
Back to Top
Dictionary Cache Stats
• "Pct Misses" should be very low (< 2% in most cases) • "Final Usage" is the number of cache entries being used Cache Get Requests Pct Miss Scan Reqs Pct Miss Mod Reqs Final Usage dc_awr_control 86 0.00 0 4 1 dc_constraints 59 91.53 0 20 1,350 dc_files 23 0.00 0 0 23 dc_global_oids 406 0.00 0 0 35 dc_histogram_data 673 0.15 0 0 1,555 dc_histogram_defs 472 24.36 0 0 4,296 dc_object_grants 58 0.00 0 0 154 dc_object_ids 1,974 6.13 0 0 1,199 dc_objects 955 19.58 0 56 2,064 dc_profiles 30 0.00 0 0 1 dc_rollback_segments 3,358 0.00 0 0 37 dc_segments 2,770 2.56 0 1,579 1,312 dc_sequences 9 33.33 0 9 5 dc_table_scns 6 100.00 0 0 0 dc_tablespace_quotas 1,558 28.50 0 1,554 3 dc_tablespaces 346,651 0.00 0 0 7 dc_usernames 434 0.00 0 0 14 dc_users 175,585 0.00 0 0 43 outstanding_alerts 57 71.93 0 0 1 Back to Dictionary Cache Statistics Back to Top Dictionary Cache Stats (RAC) Cache GES Requests GES Conflicts GES Releases dc_awr_control 8 0 0 dc_constraints 88 22 0 dc_histogram_defs 115 0 0 dc_object_ids 143 101 0 dc_objects 253 111 0 dc_segments 3,228 49 0 dc_sequences 17 3 0 dc_table_scns 6 0 0 dc_tablespace_quotas 3,093 441 0 dc_users 8 1 0 outstanding_alerts 113 41 0 Back to Dictionary Cache Statistics Back to Top Library Cache Statistics • Library Cache Activity • Library Cache Activity (RAC) Back to Top Library Cache Activity • "Pct Misses" should be very low Namespace Get Requests Pct Miss Pin Requests Pct Miss Reloads Invali- dations BODY 105 0.00 247 0.00 0 0 CLUSTER 3 0.00 4 0.00 0 0 INDEX 13 46.15 26 42.31 5 0 SQL AREA 56 100.00 1,857,002 0.02 32 12 TABLE/PROCEDURE 179 35.75 3,477 8.02 63 0 TRIGGER 323 0.00 386 0.00 0 0 Back to Library Cache Statistics Back to Top Library Cache Activity (RAC) Namespace GES Lock Requests GES Pin Requests GES Pin Releases GES Inval Requests GES Invali- dations BODY 5 0 0 0 0 CLUSTER 4 0 0 0 0 INDEX 26 22 6 17 0 TABLE/PROCEDURE 1,949 285 63 244 0 Back to Library Cache Statistics Back to Top Memory Statistics • Process Memory Summary • SGA Memory Summary • SGA breakdown difference Back to Top Process Memory Summary • B: Begin snap E: End snap • All rows below contain absolute values (i.e. not diffed over the interval) • Max Alloc is Maximum PGA Allocation size at snapshot time • Hist Max Alloc is the Historical Max Allocation for still-connected processes • ordered by Begin/End snapshot, Alloc (MB) desc Category Alloc (MB) Used (MB) Avg Alloc (MB) Std Dev Alloc (MB) Max Alloc (MB) Hist Max Alloc (MB) Num Proc Num Alloc B Other 136.42 5.25 8.55 24 27 26 26 Freeable 13.50 0.00 1.50 1.11 3 9 9 SQL 0.33 0.16 0.03 0.03 0 2 12 10 PL/SQL 0.12 0.06 0.01 0.01 0 0 24 24 E Other 138.65 4.78 8.20 24 27 29 29 Freeable 14.94 0.00 1.36 1.04 3 11 11 SQL 0.39 0.19 0.03 0.03 0 2 15 12 PL/SQL 0.18 0.11 0.01 0.01 0 0 27 26 Back to Memory Statistics Back to Top SGA Memory Summary SGA regions Begin Size (Bytes) End Size (Bytes) (if different) Database Buffers 3,506,438,144 Fixed Size 2,078,368 Redo Buffers 14,696,448 Variable Size 771,754,336 Back to Memory Statistics Back to Top SGA breakdown difference • ordered by Pool, Name • N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot Pool Name Begin MB End MB % Diff java free memory 16.00 16.00 0.00 large PX msg pool 1.03 1.03 0.00 large free memory 14.97 14.97 0.00 shared ASH buffers 15.50 15.50 0.00 shared CCursor 8.58 8.85 3.09 shared KQR L PO 8.75 8.80 0.55 shared db_block_hash_buckets 22.50 22.50 0.00 shared free memory 371.80 369.61 -0.59 shared gcs resources 66.11 66.11 0.00 shared gcs shadows 41.65 41.65 0.00 shared ges big msg buffers 13.75 13.75 0.00 shared ges enqueues 7.44 7.56 1.63 shared ges reserved msg buffers 7.86 7.86 0.00 shared library cache 10.78 10.93 1.41 shared row cache 7.16 7.16 0.00 shared sql area 27.49 28.50 3.67 buffer_cache 3,344.00 3,344.00 0.00 fixed_sga 1.98 1.98 0.00 log_buffer 14.02 14.02 0.00 Back to Memory Statistics Back to Top Streams Statistics • Streams CPU/IO Usage • Streams Capture • Streams Apply • Buffered Queues • Buffered Subscribers • Rule Set Back to Top Streams CPU/IO Usage No data exists for this section of the report. Back to Streams Statistics Back to Top Streams Capture No data exists for this section of the report. Back to Streams Statistics Back to Top Streams Apply No data exists for this section of the report. Back to Streams Statistics Back to Top Buffered Queues No data exists for this section of the report. Back to Streams Statistics Back to Top Buffered Subscribers No data exists for this section of the report. Back to Streams Statistics Back to Top Rule Set No data exists for this section of the report. Back to Streams Statistics Back to Top Resource Limit Stats • only rows with Current or Maximum Utilization > 80% of Limit are shown
• ordered by resource name
Resource Name Current Utilization Maximum Utilization Initial Allocation Limit
gcs_resources 349,392 446,903 450063 450063
gcs_shadows 400,300 447,369 450063 450063

Back to Top
init.ora Parameters
Parameter Name Begin value End value (if different)
audit_file_dest /oracle/app/oracle/admin/ICCI/adump
background_dump_dest /oracle/app/oracle/admin/ICCI/bdump
cluster_database TRUE
cluster_database_instances 2
compatible 10.2.0.3.0
control_files /dev/rora_CTL01, /dev/rora_CTL02, /dev/rora_CTL03
core_dump_dest /oracle/app/oracle/admin/ICCI/cdump
db_block_size 8192
db_domain
db_file_multiblock_read_count 16
db_name ICCI
dispatchers (PROTOCOL=TCP) (SERVICE=ICCIXDB)
instance_number 1
job_queue_processes 10
open_cursors 800
pga_aggregate_target 1073741824
processes 500
remote_listener LISTENERS_ICCI
remote_login_passwordfile EXCLUSIVE
sga_max_size 4294967296
sga_target 4294967296
sort_area_size 196608
spfile /dev/rora_SPFILE
thread 1
undo_management AUTO
undo_retention 900
undo_tablespace UNDOTBS1
user_dump_dest /oracle/app/oracle/admin/ICCI/udump

Back to Top
More RAC Statistics
• Global Enqueue Statistics
• Global CR Served Stats
• Global CURRENT Served Stats
• Global Cache Transfer Stats

Global Enqueue Statistics
Statistic Total per Second per Trans
acks for commit broadcast(actual) 18,537 3.92 3.31
acks for commit broadcast(logical) 21,016 4.45 3.75
broadcast msgs on commit(actual) 5,193 1.10 0.93
broadcast msgs on commit(logical) 5,491 1.16 0.98
broadcast msgs on commit(wasted) 450 0.10 0.08
dynamically allocated gcs resources 0 0.00 0.00
dynamically allocated gcs shadows 0 0.00 0.00
false posts waiting for scn acks 0 0.00 0.00
flow control messages received 0 0.00 0.00
flow control messages sent 2 0.00 0.00
gcs assume cvt 0 0.00 0.00
gcs assume no cvt 9,675 2.05 1.73
gcs ast xid 1 0.00 0.00
gcs blocked converts 7,099 1.50 1.27
gcs blocked cr converts 8,442 1.79 1.51
gcs compatible basts 45 0.01 0.01
gcs compatible cr basts (global) 273 0.06 0.05
gcs compatible cr basts (local) 12,593 2.66 2.25
gcs cr basts to PIs 0 0.00 0.00
gcs cr serve without current lock 0 0.00 0.00
gcs dbwr flush pi msgs 223 0.05 0.04
gcs dbwr write request msgs 223 0.05 0.04
gcs error msgs 0 0.00 0.00
gcs forward cr to pinged instance 0 0.00 0.00
gcs immediate (compatible) converts 2,998 0.63 0.54
gcs immediate (null) converts 170,925 36.16 30.53
gcs immediate cr (compatible) converts 0 0.00 0.00
gcs immediate cr (null) converts 722,748 152.88 129.11
gcs indirect ast 306,817 64.90 54.81
gcs lms flush pi msgs 0 0.00 0.00
gcs lms write request msgs 189 0.04 0.03
gcs msgs process time(ms) 16,164 3.42 2.89
gcs msgs received 1,792,132 379.09 320.14
gcs out-of-order msgs 0 0.00 0.00
gcs pings refused 0 0.00 0.00
gcs pkey conflicts retry 0 0.00 0.00
gcs queued converts 2 0.00 0.00
gcs recovery claim msgs 0 0.00 0.00
gcs refuse xid 0 0.00 0.00
gcs regular cr 0 0.00 0.00
gcs retry convert request 0 0.00 0.00
gcs side channel msgs actual 437 0.09 0.08
gcs side channel msgs logical 21,086 4.46 3.77
gcs stale cr 3,300 0.70 0.59
gcs undo cr 5 0.00 0.00
gcs write notification msgs 23 0.00 0.00
gcs writes refused 3 0.00 0.00
ges msgs process time(ms) 1,289 0.27 0.23
ges msgs received 138,891 29.38 24.81
global posts dropped 0 0.00 0.00
global posts queue time 0 0.00 0.00
global posts queued 0 0.00 0.00
global posts requested 0 0.00 0.00
global posts sent 0 0.00 0.00
implicit batch messages received 81,181 17.17 14.50
implicit batch messages sent 19,561 4.14 3.49
lmd msg send time(ms) 0 0.00 0.00
lms(s) msg send time(ms) 0 0.00 0.00
messages flow controlled 15,306 3.24 2.73
messages queue sent actual 108,411 22.93 19.37
messages queue sent logical 222,518 47.07 39.75
messages received actual 474,202 100.31 84.71
messages received logical 1,931,144 408.50 344.97
messages sent directly 25,742 5.45 4.60
messages sent indirectly 137,725 29.13 24.60
messages sent not implicit batched 88,859 18.80 15.87
messages sent pbatched 1,050,224 222.16 187.61
msgs causing lmd to send msgs 61,682 13.05 11.02
msgs causing lms(s) to send msgs 85,978 18.19 15.36
msgs received queue time (ms) 911,013 192.71 162.74
msgs received queued 1,931,121 408.50 344.97
msgs sent queue time (ms) 5,651 1.20 1.01
msgs sent queue time on ksxp (ms) 66,767 14.12 11.93
msgs sent queued 215,124 45.51 38.43
msgs sent queued on ksxp 243,729 51.56 43.54
process batch messages received 120,003 25.38 21.44
process batch messages sent 181,019 38.29 32.34

Back to Top
Global CR Served Stats
Statistic Total
CR Block Requests 10,422
CURRENT Block Requests 251
Data Block Requests 10,422
Undo Block Requests 2
TX Block Requests 20
Current Results 10,664
Private results 4
Zero Results 5
Disk Read Results 0
Fail Results 0
Fairness Down Converts 1,474
Fairness Clears 0
Free GC Elements 0
Flushes 370
Flushes Queued 0
Flush Queue Full 0
Flush Max Time (us) 0
Light Works 2
Errors 0

Back to Top
Global CURRENT Served Stats
• Pins = CURRENT Block Pin Operations
• Flushes = Redo Flush before CURRENT Block Served Operations
• Writes = CURRENT Block Fusion Write Operations
Statistic Total % <1ms % <10ms % <100ms % <1s % <10s Pins 17,534 99.96 0.01 0.03 0.00 0.00 Flushes 77 48.05 46.75 5.19 0.00 0.00 Writes 255 5.49 53.73 40.00 0.78 0.00 Back to Top Global Cache Transfer Stats • Immediate (Immed) - Block Transfer NOT impacted by Remote Processing Delays • Busy (Busy) - Block Transfer impacted by Remote Contention • Congested (Congst) - Block Transfer impacted by Remote System Load • ordered by CR + Current Blocks Received desc CR Current Inst No Block Class Blocks Received % Immed % Busy % Congst Blocks Received % Immed % Busy % Congst 2 data block 3,945 87.20 12.80 0.00 13,324 99.71 0.26 0.04 2 Others 191 100.00 0.00 0.00 2,190 96.48 3.52 0.00 2 undo header 11 100.00 0.00 0.00 2 100.00 0.00 0.00 Back to Top End of Report

本文标签：

本文标题：oracle中AWR详细分析AWR报告详细分析 AWR 是 Oracle 10g 版本推出的新特性，全…

固定链接：http://www.fgedu.net.cn/5752.html

网站声明：本文由风哥整理发布,转载请保留此段声明，本站所有内容将不对其使用后果做任何承诺，请读者谨慎使用！

技术交流：Oracle/MySQL/Linux学习群1-4：189070296、244609803、522261684、522651731

技术交流：Oracle/MySQL/Linux学习群5-8：150201289、199479729、158277749、336282998

最新Oracle/MySQL教程文章

Oracle/MySQL教程文章推荐

最热Oracle/MySQL教程文章

热门课程推荐

oracle中AWR详细分析AWR报告详细分析 AWR 是 Oracle 10g 版本 推出的新特性， 全…

oracle中AWR详细分析AWR报告详细分析 AWR 是 Oracle 10g 版本推出的新特性，全…