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)。说明系统压力非常小。
可是对于批量系统,数据库的工作负载总是集中在一段时间内。如果快照周期不在这一段时间内,或者快照周期跨度太长而包含了大量的数据库空闲时间,所得出的分析结果是没有意义的。这也说明选择分析时间段很关键,要选择能够代表性能问题的时间段。
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 prase)和硬解析(hard prase),就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle,在执行和获取结果前,Oracle对此sql将进行几个步骤的处理过程:
1、语法检查(syntax check)
检查此sql的拼写是否语法。
2、语义检查(semantic check)
诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。
3、对sql语句进行解析(prase)
利用内部算法对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 EventsEvent |
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一节。
|
Begin |
End |
Number of Instances: |
2 |
2 |
|
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 |
|
Buffer access - local cache %: |
98.60 |
Buffer access - remote cache %: |
0.12 |
Buffer access - disk %: |
1.28 |
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 |
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 |
- 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
- Time Model Statistics
- Wait Class
- Wait Events
- Background Wait Events
- Operating System Statistics
- Service Statistics
- Service Wait Class Stats
- 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
- 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
- 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 |