oracle 10g dataguard 配置,物理standby,高的log file sync等待, 没有看见 log buffer space wait event,,所以buffer尺寸不打算调整 现在初步估计是i/o问题,和高的应用提交频率引起过多的log file sync, 但是看metalink 发现bug 7936993, 大家有遇到类似的问题?
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
dbtest 5066879 dbtest 1 10.2.0.4.0 NO dghost1
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 9745 17-Jun-11 17:00:04 348 7.7
End Snap: 9776 20-Jun-11 07:00:31 349 7.5
Elapsed: 3,720.44 (mins)
DB Time: 2,334.21 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 1,136M 1,040M Std Block Size: 8K
Shared Pool Size: 1,728M 1,824M Log Buffer: 4,224K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 224,050.10 873.58
Logical reads: 8,240.41 32.13
Block changes: 1,594.25 6.22
Physical reads: 59.71 0.23
Physical writes: 71.30 0.28
User calls: 1,484.90 5.79
Parses: 336.95 1.31
Hard parses: 1.17 0.00
Sorts: 53.93 0.21
Logons: 0.11 0.00
Executes: 348.80 1.36
Transactions: 256.47
% Blocks changed per Read: 19.35 Recursive Call %: 19.55
Rollback per transaction %: 0.00 Rows per Sort: 78.68
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %: 99.99
Buffer Hit %: 99.45 In-memory Sort %: 100.00
Library Hit %: 99.09 Soft Parse %: 99.65
Execute to Parse %: 3.40 Latch Hit %: 99.80
Parse CPU to Parse Elapsd %: 90.34 % Non-Parse CPU: 99.44
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 79.50 84.19
% SQL with executions>1: 64.23 30.37
% Memory for SQL w/exec>1: 66.99 37.35
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 107,984 77.1
log file sync 57,440,980 105,684 2 75.5 Commit
LGWR wait on LNS 54,379,410 45,048 1 32.2 Network
LGWR-LNS wait on channel 59,315,991 44,620 1 31.9 Other
LNS wait on SENDREQ 54,376,406 43,127 1 30.8 Network
-------------------------------------------------------------
Time Model Statistics DB/Inst: dbtest/dbtest Snaps: 9745-9776
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
dbtest 5066879 dbtest 1 10.2.0.4.0 NO dghost1
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 9745 17-Jun-11 17:00:04 348 7.7
End Snap: 9776 20-Jun-11 07:00:31 349 7.5
Elapsed: 3,720.44 (mins)
DB Time: 2,334.21 (mins)
Cache Sizes
~~~~~~~~~~~ Begin End
---------- ----------
Buffer Cache: 1,136M 1,040M Std Block Size: 8K
Shared Pool Size: 1,728M 1,824M Log Buffer: 4,224K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 224,050.10 873.58
Logical reads: 8,240.41 32.13
Block changes: 1,594.25 6.22
Physical reads: 59.71 0.23
Physical writes: 71.30 0.28
User calls: 1,484.90 5.79
Parses: 336.95 1.31
Hard parses: 1.17 0.00
Sorts: 53.93 0.21
Logons: 0.11 0.00
Executes: 348.80 1.36
Transactions: 256.47
% Blocks changed per Read: 19.35 Recursive Call %: 19.55
Rollback per transaction %: 0.00 Rows per Sort: 78.68
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.98 Redo NoWait %: 99.99
Buffer Hit %: 99.45 In-memory Sort %: 100.00
Library Hit %: 99.09 Soft Parse %: 99.65
Execute to Parse %: 3.40 Latch Hit %: 99.80
Parse CPU to Parse Elapsd %: 90.34 % Non-Parse CPU: 99.44
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 79.50 84.19
% SQL with executions>1: 64.23 30.37
% Memory for SQL w/exec>1: 66.99 37.35
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 107,984 77.1
log file sync 57,440,980 105,684 2 75.5 Commit
LGWR wait on LNS 54,379,410 45,048 1 32.2 Network
LGWR-LNS wait on channel 59,315,991 44,620 1 31.9 Other
LNS wait on SENDREQ 54,376,406 43,127 1 30.8 Network
-------------------------------------------------------------
Time Model Statistics DB/Inst: dbtest/dbtest Snaps: 9745-9776
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/36779/viewspace-700388/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/36779/viewspace-700388/