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)
Elapsed表示整个AWR报表统计的时间长度
DB Time是记录在服务器花在数据库运算(非后台进程)和等待(非空闲等待)上的时间
DB Time=cpu time+wait time(不包含空闲等待)(非后台进程)
DB Time不包括Oracle后台进程消耗的时间。如果DB Time远远小于Elapsed时间，说明数据库比较空闲。
上述报表中
Snapshot时间间隔约为79分钟，cpu就公有8*79=632分钟。DB Time为11.05分钟，则：cpu花费了11.05分钟在处理oracle非空闲等待和运算上(比如逻辑读)，也就是说cpu有11.05/632=0.017%花费在处理oracle的操作上。
从awr report的Elapsed time和DB Time就能大概了解db的负载，计算公式可参考为：cpu负载=DB Time/(cpu数*Elapsed)*100%
在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
DB Time(s)
2.4
0.0
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表明可能有争用问题。
DB Time(s)：每秒内用于DB处理的时间，其他时间为等待时间
Redo size：每秒/每事务产生的redo大小(单位字节)，可标志数据库任务的繁重程序。其中Per Second表示每秒中产生的redo的字节数，Per Transaction表示每个事务产生的redo的字节数，可以通过后者可以看到事务的大小，协助判断是否commit次数太多。例如per second很大，而per transaction很小，说明commit次数太多。通常在很繁忙的系统中日志生成量可能达到上百k，甚至几百k。
Logical reads：每秒/每事务逻辑读的块数(我们可以这样认为，block在内存中，我们每一次读一块内存，就相当于一次逻辑读)，单位为块，在良好的OLTP环境中，Logical reads/Executes不会超过50，一般只有10左右，如果该指标较大，表示语句可能不够优化，需要具体分析，在该示例中，3,521.77/354.34,那么在OLAP中呢？
Block changes：每秒/每事务修改的块数，即每秒中多少个块发生变化
Physical reads：每秒/每事务物理读的块数，即每秒数据库从磁盘读取的块个数
Physical writes：每秒/每事务物理写的块数，即每秒有多少个块接受了数据库写入数据。
User calls：每秒/每事务用户call次数User calls/Executes基本代表每个语句的请求次数，Executes越接近User calls越好。
Parses：每秒的SQL语句解析的次数，超过300即需要关注，可以考虑调整参数session_cursor_cache来改善解析次数过高的现象。
Hard parses：其中硬解析的次数，如果硬解析次数太高，说明SQL重用率不高。例如超过100，基本都是由于不使用bind var所导致的，导致cpu使用率的问题，极有使得性能急剧下降。
Sorts：每秒/每事务的排序次数
Logons：每秒/每事务登录的次数
Executes：每秒/每事务SQL执行次数，包括用户执行的sql语句与系统执行的sql语句，表示一个系统sql的繁忙程度。
Transactions：每秒产生的事物个数，反映数据库负载程度，不同的系统，略有差异，在典型的交易系统中，事务较多，而网站系统，可能select查询较多。
Blocks changed per Read：表示逻辑读用于修改数据块的比例
Recursive Call：递归调用占所有操作的比率
Rollback per transaction：每事务的回滚率
Rollbacks：表示数据库中事务的回退率，如果不是因为业务本身的原因，通常应该小于10%为好，回退是一个很消耗资源的操作。
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时，未进行等待的比率，越高越好。
buffer hit%表示进程从内存中找到数据块的比率，监视这个值是否发生重大变化比这个值本身更重要。对于一般的OLTP系统，命中率通常在95@以上，如果此值低于80%，应该给数据库分配更多的内存，考虑加大db_cache_size。在数据仓库OLAP环境中，数据缓冲命中率不是一个重要的指标，因为OLAP数据库主要是物理读，甚至是直接读，该命中率不可能高。
Redo NoWai%t表示在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消耗，降低性能。Sql语句在库缓冲中能否找到相应的解析计划，如果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解析时间)，太低表示解析消耗时间过多。说明解析时间所占比率过高，需要考虑提高sql语句重用性。
Execute to Parse：是语句执行与分析的比例,表示sql语句解析后被重复执行的命中率，计算公式=100*(1-Parses/Executions),如果该值偏小，说明分析(硬分析和软分析)的比例较大，快速分析较少，根据实际情况，可以考虑调整session_cached_cursors参数，有些报告中这个值是负的，看上去很奇怪，事实上这表示一个问题，sql如果被age out的话就可能出现这种情况 ，也就是sql老化，执行alter system flush shared_pool
如果要SQL重用率高，则这个比例会很高。该值越高表示一次解析后被重复执行的次数越多。
Soft Parse%：SQL语句软解析占整个分析的命中率，如果低于95，需检查是否有硬编码现象，如果低于80，说明sql语句基本没有重用性=soft/(soft+hard)
In-memory Sort：在内存中排序的比率，即有多少排序在内存中进行的，如果过低说明有大量的排序在临时表空间中进行，性能肯定不好，考虑调大PGA参数，sort_area_size。
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比率，如果此值太小的话要结合Parse，看看是不是硬编码现象，说明需要在应用中更多使用绑定变量，避免过多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
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时间。
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
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(空间管理锁)。
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
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)
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
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
SQL Statistics
本节按各种资源分别列出对资源消耗最严重的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;
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
cuidmain@HPGICCI1 (TNS V1-V3)
insert into CUID select CUID_...
76
75
172,329
0.00
11.52
load_fnsact@HPGICCI1 (TNS V1-V3)
insert into ICCICCS values (:...
58
42
1
58.04
8.75
cumimain@HPGICCI1 (TNS V1-V3)
insert into CUMI select CUSV_...
51
42
1
50.93
7.68
cusmmain@HPGICCI1 (TNS V1-V3)
insert into CUSM select CUSM_...
38
36
166,069
0.00
5.67
select c.name, u.name from co...
35
3
1
35.00
5.28
SQL*Plus
SELECT F.TABLESPACE_NAME, TO_...
23
23
172,329
0.00
3.46
load_fnsact@HPGICCI1 (TNS V1-V3)
insert into iccifnsact values...
15
11
5
2.98
2.25
DECLARE job BINARY_INTEGER := ...
14
14
172,983
0.00
2.16
load_fnsact@HPGICCI1 (TNS V1-V3)
update ICCIFNSACT set BORM_AD...
13
13
172,337
0.00
2.00
load_oldnewact@HPGICCI1 (TNS V1-V3)
insert into OLDNEWACT values ...
13
13
166,051
0.00
1.89
icci_migact@HPGICCI1 (TNS V1-V3)
insert into ICCICCS values (:...
10
4
1
9.70
1.46
cuidmain@HPGICCI1 (TNS V1-V3)
select CUID_CUST_NO , CUID_ID_...
10
8
5
1.91
1.44
INSERT INTO STATS$SGA_TARGET_A...
8
8
172,329
0.00
1.25
load_fnsact@HPGICCI1 (TNS V1-V3)
update ICCICCS set CCSMAXOVER...
8
8
172,983
0.00
1.16
load_fnsact@HPGICCI1 (TNS V1-V3)
select * from ICCIPRODCODE wh...
记录了执行总和时间的TOP
SQL(请注意是监控范围内该SQL的执行时间总和，而不是单次SQL执行时间 Elapsed Time = CPU Time + Wait
Time)。
Elapsed Time(S): SQL语句执行用总时长，此排序就是按照这个字段进行的。注意该时间不是单个SQL跑的时间，而是监控范围内SQL执行次数的总和时间。单位时间为秒。Elapsed Time = CPU
Time + Wait Time
CPU Time(s): 为SQL语句执行时CPU占用时间总时长，此时间会小于等于Elapsed Time时间。单位时间为秒。
Executions: SQL语句在监控范围内的执行次数总计。
Elap per Exec(s): 执行一次SQL的平均时间。单位时间为秒。
% Total DB Time: 为SQL的Elapsed Time时间占数据库总时间的百分比。
SQL ID: SQL语句的ID编号，点击之后就能导航到下边的SQL详细列表中，点击IE的返回可以回到当前SQL ID的地方。
SQL Module: 显示该SQL是用什么方式连接到数据库执行的，如果是用SQL*Plus或者PL/SQL链接上来的那基本上都是有人在调试程序。一般用前台应用链接过来执行的sql该位置为空。
SQL Text: 简单的sql提示，详细的需要点击SQL
ID。
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
load_fnsact@HPGICCI1 (TNS V1-V3)
insert into ICCICCS values (:...
57
93
1
57.31
14.10
cuidmain@HPGICCI1 (TNS V1-V3)
insert into CUID select CUID_...
42
51
1
42.43
7.68
cusmmain@HPGICCI1 (TNS V1-V3)
insert into CUSM select CUSM_...
42
58
1
42.01
8.75
cumimain@HPGICCI1 (TNS V1-V3)
insert into CUMI select CUSV_...
36
38
166,069
0.00
5.67
select c.name, u.name from co...
23
23
172,329
0.00
3.46
load_fnsact@HPGICCI1 (TNS V1-V3)
insert into iccifnsact values...
14
14
172,983
0.00
2.16
load_fnsact@HPGICCI1 (TNS V1-V3)
update ICCIFNSACT set BORM_AD...
13
13
172,337
0.00
2.00
load_oldnewact@HPGICCI1 (TNS V1-V3)
insert into OLDNEWACT values ...
13
13
166,051
0.00
1.89
icci_migact@HPGICCI1 (TNS V1-V3)
insert into ICCICCS values (:...
11
15
5
2.23
2.25
DECLARE job BINARY_INTEGER := ...
8
8
172,329
0.00
1.25
load_fnsact@HPGICCI1 (TNS V1-V3)
update ICCICCS set CCSMAXOVER...
8
10
5
1.60
1.44
INSERT INTO STATS$SGA_TARGET_A...
8
8
172,983
0.00
1.16
load_fnsact@HPGICCI1 (TNS V1-V3)
select * from ICCIPRODCODE wh...
4
10
1
3.54
1.46
cuidmain@HPGICCI1 (TNS V1-V3)
select CUID_CUST_NO , CUID_ID_...
3
35
1
3.13
5.28
SQL*Plus
SELECT F.TABLESPACE_NAME, TO_...
记录了执行占CPU时间总和时间最长的TOP
SQL(请注意是监控范围内该SQL的执行占CPU时间总和，而不是单次SQL执行时间)。
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
load_fnsact@HPGICCI1 (TNS V1-V3)
insert into ICCICCS values (:...
2,064,414
1
2,064,414.00
12.40
57.31
93.50
cuidmain@HPGICCI1 (TNS V1-V3)
insert into CUID select CUID_...
1,826,869
166,069
11.00
10.97
35.84
37.60
select c.name, u.name from co...
1,427,648
172,337
8.28
8.58
12.97
13.29
load_oldnewact@HPGICCI1 (TNS V1-V3)
insert into OLDNEWACT values ...
1,278,667
172,329
7.42
7.68
22.85
22.94
load_fnsact@HPGICCI1 (TNS V1-V3)
insert into iccifnsact values...
1,216,367
1
1,216,367.00
7.31
42.43
50.93
cusmmain@HPGICCI1 (TNS V1-V3)
insert into CUSM select CUSM_...
记录了执行占总buffer gets(逻辑IO)的TOP SQL(请注意是监控范围内该SQL的执行占Gets总和，而不是单次SQL执行所占的Gets)。
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
cuidmain@HPGICCI1 (TNS V1-V3)
insert into CUID select CUID_...
50,646
1
50,646.00
15.70
3.54
9.70
cuidmain@HPGICCI1 (TNS V1-V3)
select CUID_CUST_NO , CUID_ID_...
24,507
1
24,507.00
7.59
42.01
58.04
cumimain@HPGICCI1 (TNS V1-V3)
insert into CUMI select CUSV_...
21,893
1
21,893.00
6.78
42.43
50.93
cusmmain@HPGICCI1 (TNS V1-V3)
insert into CUSM select CUSM_...
19,761
1
19,761.00
6.12
2.14
6.04
cumimain@HPGICCI1 (TNS V1-V3)
select CUSV_CUST_NO from CUMI...
记录了执行占总磁盘物理读(物理IO)的TOP SQL(请注意是监控范围内该SQL的执行占磁盘物理读总和，而不是单次SQL执行所占的磁盘物理读)。
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
load_fnsact@HPGICCI1 (TNS V1-V3)
select * from ICCIPRODCODE wh...
172,983
172,329
1.00
0.00
0.00
load_fnsact@HPGICCI1 (TNS V1-V3)
update ICCIFNSACT set BORM_AD...
172,337
172,337
1.00
0.00
0.00
load_oldnewact@HPGICCI1 (TNS V1-V3)
insert into OLDNEWACT values ...
172,329
172,329
1.00
0.00
0.00
load_fnsact@HPGICCI1 (TNS V1-V3)
insert into iccifnsact values...
172,329
172,329
1.00
0.00
0.00
load_fnsact@HPGICCI1 (TNS V1-V3)
update ICCICCS set CCSMAXOVER...
172,329
6,286
0.04
0.00
0.00
load_fnsact@HPGICCI1 (TNS V1-V3)
insert into ICCICCS values (:...
166,069
166,069
1.00
0.00
0.00
select c.name, u.name from co...
记录了按照SQL的执行次数排序的TOP SQL。该排序可以看出监控范围内的SQL执行次数。
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
select c.name, u.name from co...
6,304
6,304
3.45
select type#, blocks, extents,...
2,437
2,438
1.33
select file# from file$ where ...
1,568
1,568
0.86
update seg$ set type#=:4, bloc...
1,554
1,554
0.85
update tsq$ set blocks=:3, max...
444
444
0.24
select blocks, maxblocks, gran...
421
421
0.23
lock table sys.mon_mods$ in ex...
421
421
0.23
update sys.mon_mods$ set inser...
86
86
0.05
INSERT INTO sys.wri$_adv_messa...
81
81
0.04
SELECT sys.wri$_adv_seq_msggro...
记录了SQL的软解析次数的TOP SQL。说到软解析(soft prase)和硬解析(hard prase)，就不能不说一下Oracle对sql的处理过程。
SQL ordered by Sharable Memory
Only Statements with Sharable Memory
greater than 1048576 are displayed
Sharable
Mem (b)
Executions
%
Total
SQL
Id
SQL
Module
SQL
Text
2,215,022
120
0.46
select size_for_estimate, ...
记录了SQL占用library
cache的大小的TOP SQL。Sharable Mem (b)：占用library cache的大小，单位是byte。
SQL ordered by Version Count
Only
Statements with Version Count greater than 20 are displayed
Version
Count
Executions
SQL
Id
SQL
Module
SQL
Text
47
34
select grantee#, privilege#, n...
46
37
select col#, grantee#, privi...
42
33
select pos#, intcol#, col#, sp...
38
801
select name, intcol#, segcol#,...
34
68
select con#, obj#, rcon#, enab...
记录了SQL的打开子游标的TOP SQL。
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
cuidmain@HPGICCI1 (TNS V1-V3)
insert into CUID select CUID_...
4.21
7.25
58.04
42.01
1
cumimain@HPGICCI1 (TNS V1-V3)
insert into CUMI select CUSV_...
3.62
7.12
50.93
42.43
1
cusmmain@HPGICCI1 (TNS V1-V3)
insert into CUSM select CUSM_...
2.39
6.35
37.60
35.84
166,069
select c.name, u.name from co...
2.38
3.12
76.41
74.57
172,329
load_fnsact@HPGICCI1 (TNS V1-V3)
insert into ICCICCS values (:...
1.64
16.91
9.70
3.54
1
cuidmain@HPGICCI1 (TNS V1-V3)
select CUID_CUST_NO , CUID_ID_...
1.06
3.02
35.00
3.13
1
SQL*Plus
SELECT F.TABLESPACE_NAME, TO_...
0.83
13.76
6.04
2.14
1
cumimain@HPGICCI1 (TNS V1-V3)
select CUSV_CUST_NO from CUMI...
记录了集群的等待时间的TOP
SQL
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
Instance Activity Statistics
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
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
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
IO Stats
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争用。
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
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
Advisory Statistics
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
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
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
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
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
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
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
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
Streams Pool Advisory
No data exists for this section of the report.
Java Pool Advisory
No data exists for this section of the report.
Wait Statistics
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
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
Undo Statistics
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
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
Latch Statistics
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
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
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
Parent Latch Statistics
No data exists for this section of the report.
Child Latch Statistics
No data exists for this section of the report.
Segment Statistics
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
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
Segments by Row Lock Waits
当一个进程予在正被其它进程锁住的数据行上获得排它锁时发生这种等待。这种等待经常是由于在一个有主键索引的表上做大量INSERT操作。
No data exists for this section of the report.
Segments by ITL Waits
No data exists for this section of the report.
Segments by Buffer Busy Waits
No data exists for this section of the report.
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
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
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
Dictionary Cache Statistics
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
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
Library Cache Statistics
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
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
Memory Statistics
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
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
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
EndMB
%
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
Streams Statistics
Streams CPU/IO Usage
No data exists for this section of the report.
Streams Capture
No data exists for this section of the report.
Streams Apply
No data exists for this section of the report.
Buffered Queues
No data exists for this section of the report.
Buffered Subscribers
No data exists for this section of the report.
Rule Set
No data exists for this section of the report.
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
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
More RAC
Statistics
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
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
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
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
End of Report
扫一扫
1952
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
