客户做压力测试,客户反应数据库慢,请帮忙看看这AWR报告是否有问题。。自己看了下AWR报告,并未发现有什么问题,但客户反应慢 真不知道是什么问题。 (怎么不能上传附件,。)
附件在这里
http://www.itpub.net/forum.php?mod=viewthread&tid=1798928&extra=Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 5204 25-Jun-13 09:00:06 58 2.1
End Snap: 5211 25-Jun-13 16:00:08 52 1.8
Elapsed: 420.03 (mins)
DB Time: 12.20 (mins)
Load ProfilePer Second Per Transaction Per Exec Per Call
DB Time(s): 0.0 0.0 0.00 0.00
DB CPU(s): 0.0 0.0 0.00 0.00
Redo size: 12,276.2 7,512.2
Logical reads: 1,302.8 797.3
Block changes: 73.6 45.0
Physical reads: 0.1 0.1
Physical writes: 2.3 1.4
User calls: 47.2 28.9
Parses: 8.7 5.3
Hard parses: 3.4 2.1
W/A MB processed: 0.6 0.4
Logons: 0.1 0.0
Executes: 27.7 17.0
Rollbacks: 0.0 0.0
Transactions: 1.6
Instance Efficiency Percentages (Target 100%)Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.99 In-memory Sort %: 100.00
Library Hit %: 79.44 Soft Parse %: 61.39
Execute to Parse %: 68.70 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 98.54 % Non-Parse CPU: 74.34
Shared Pool Statistics Begin End
Memory Usage %: 76.68 78.19
% SQL with executions>1: 96.69 98.07
% Memory for SQL w/exec>1: 93.64 95.40
Top 5 Timed Foreground EventsEvent Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU 571 78.05
log file sync 44,216 114 3 15.58 Commit
gc cr block busy 5,986 16 3 2.18 Cluster
gc cr block 2-way 53,006 14 0 1.89 Cluster
gc current block 2-way 44,488 12 0 1.59 Cluster数据库压力测试慢AWR
附件在这里
http://www.itpub.net/forum.php?mod=viewthread&tid=1798928&extra=Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 5204 25-Jun-13 09:00:06 58 2.1
End Snap: 5211 25-Jun-13 16:00:08 52 1.8
Elapsed: 420.03 (mins)
DB Time: 12.20 (mins)
Load ProfilePer Second Per Transaction Per Exec Per Call
DB Time(s): 0.0 0.0 0.00 0.00
DB CPU(s): 0.0 0.0 0.00 0.00
Redo size: 12,276.2 7,512.2
Logical reads: 1,302.8 797.3
Block changes: 73.6 45.0
Physical reads: 0.1 0.1
Physical writes: 2.3 1.4
User calls: 47.2 28.9
Parses: 8.7 5.3
Hard parses: 3.4 2.1
W/A MB processed: 0.6 0.4
Logons: 0.1 0.0
Executes: 27.7 17.0
Rollbacks: 0.0 0.0
Transactions: 1.6
Instance Efficiency Percentages (Target 100%)Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.99 In-memory Sort %: 100.00
Library Hit %: 79.44 Soft Parse %: 61.39
Execute to Parse %: 68.70 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 98.54 % Non-Parse CPU: 74.34
Shared Pool Statistics Begin End
Memory Usage %: 76.68 78.19
% SQL with executions>1: 96.69 98.07
% Memory for SQL w/exec>1: 93.64 95.40
Top 5 Timed Foreground EventsEvent Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU 571 78.05
log file sync 44,216 114 3 15.58 Commit
gc cr block busy 5,986 16 3 2.18 Cluster
gc cr block 2-way 53,006 14 0 1.89 Cluster
gc current block 2-way 44,488 12 0 1.59 Cluster数据库压力测试慢AWR
真晕。
1、系统似乎起了audit,导致热块明显,建议排查下;
2、系统的硬解析比较多,消耗CPU较多,看看有些语句是否可以用绑定变量;
3、应用的应用逻辑可能有改进的空间,比如:可能很多地方用到了循环操作数据的情况(消耗了较多的CPU),还有,锁冲突比较严重(行锁等待);
4、系统的IO并不是很好;