Home » RDBMS Server » Performance Tuning » Statspack: How to reduce the LNS wait for LGWR redo EVENT and CPU TIME
| Statspack: How to reduce the LNS wait for LGWR redo EVENT and CPU TIME [message #199575] |
Wed, 25 October 2006 02:41 |
beastjj
Messages: 3
Registered: October 2006
Location: CHINA
|
Junior Member |
|
|
I apologize in advance for not being able to make my question shorter.Here is the portion of the statspack output:
STATSPACK report for
DB Name DB Id Instance Inst Num Release RAC Host
------------ ----------- ------------ -------- ----------- --- ----------------
finance 1226615582 finance 1 10.1.0.2.0 NO finance-DB-PROD
Snap Id Snap Time Sessions Curs/Sess Comment
--------- ------------------ -------- --------- -------------------
Begin Snap: 531 25-Oct-06 10:57:02 183 3.8
End Snap: 549 25-Oct-06 13:37:06 185 3.6
Elapsed: 160.07 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 4,608M Std Block Size: 8K
Shared Pool Size: 480M Log Buffer: 512K
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 376,120.02 116,629.75
Logical reads: 72,601.27 22,512.67
Block changes: 1,680.73 521.17
Physical reads: 1,668.97 517.52
Physical writes: 38.96 12.08
User calls: 1,367.99 424.20
Parses: 505.38 156.71
Hard parses: 7.78 2.41
Sorts: 10.46 3.24
Logons: 0.04 0.01
Executes: 539.73 167.36
Transactions: 3.22
% Blocks changed per Read: 2.32 Recursive Call %: 11.85
Rollback per transaction %: 3.55 Rows per Sort: 1397.25
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 97.70 In-memory Sort %: 100.00
Library Hit %: 98.66 Soft Parse %: 98.46
Execute to Parse %: 6.36 Latch Hit %: 99.88
Parse CPU to Parse Elapsd %: 86.65 % Non-Parse CPU: 93.29
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 84.66 95.13
% SQL with executions>1: 44.49 40.98
% Memory for SQL w/exec>1: 37.94 34.08
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Call Time
-------------------------------------------- ------------ ----------- ---------
LNS wait for LGWR redo 1,113,487 8,745 44.76
CPU time 5,839 29.89
db file scattered read 1,122,716 1,798 9.20
db file sequential read 361,181 1,012 5.18
LNS wait on SENDREQ 4,175 837 4.28
-------------------------------------------------------------
Wait Events DB/Inst: OMSAMMIC/omsammic Snaps: 531-549
-> 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)
Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (ms) /txn
---------------------------- ------------ ---------- ---------- ------ --------
LNS wait for LGWR redo 1,113,487 532,715 8,745 8 36.0
db file scattered read 1,122,716 0 1,798 2 36.2
db file sequential read 361,181 0 1,012 3 11.7
LNS wait on SENDREQ 4,175 0 837 200 0.1
log file sync 46,783 1 231 5 1.5
log file parallel write 70,930 0 224 3 2.3
latch: library cache 43,956 0 81 2 1.4
enq: TX - row lock contentio 65 17 80 1234 0.0
db file parallel write 37,138 0 76 2 1.2
ARCH wait on SENDREQ 156 0 75 479 0.0
latch: cache buffers chains 6,601 6,533 71 11 0.2
log file switch completion 154 35 62 405 0.0
SQL*Net more data to client 764,572 0 61 0 24.7
log file sequential read 3,700 0 50 13 0.1
ARCH wait on c/f tx acquire 42 38 39 931 0.0
class slave wait 6 6 31 5121 0.0
read by other session 30,020 0 29 1 1.0
log buffer space 1,570 0 26 17 0.1
row cache lock 12 5 25 2078 0.0
control file sequential read 20,904 0 24 1 0.7
control file parallel write 5,078 0 24 5 0.2
LGWR wait on LNS 148 0 21 143 0.0
LGWR-LNS wait on channel 1,567 1,296 21 13 0.1
latch: shared pool 5,417 0 17 3 0.2
latch free 6,713 60 16 2 0.2
SQL*Net break/reset to clien 12,353 0 5 0 0.4
LNS wait on LGWR 1,042,421 0 4 0 33.7
buffer busy waits 86 3 4 48 0.0
LGWR wait on full LNS buffer 1,580 146 2 1 0.1
rdbms ipc reply 477 0 2 4 0.0
control file single write 537 0 1 2 0.0
direct path read 433 0 1 2 0.0
enq: US - contention 7 0 0 69 0.0
direct path write temp 108 0 0 3 0.0
process startup 3 0 0 61 0.0
direct path read temp 108 0 0 2 0.0
local write wait 10 0 0 12 0.0
enq: RO - fast object reuse 8 0 0 13 0.0
LGWR wait for redo copy 2,502 0 0 0 0.1
latch: In memory undo latch 2 0 0 28 0.0
log file single write 74 0 0 1 0.0
latch: library cache pin 38 0 0 1 0.0
direct path write 527 0 0 0 0.0
ksfd: async disk IO 1,745 0 0 0 0.1
latch: row cache objects 2 0 0 9 0.0
library cache pin 2 0 0 8 0.0
latch: undo global data 47 0 0 0 0.0
refresh controlfile command 4 0 0 2 0.0
latch: checkpoint queue latc 1 0 0 7 0.0
Wait Events DB/Inst: OMSAMMIC/omsammic Snaps: 531-549
-> 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)
___________________________________________________________
I look at the CPU time--it is has 4 HT CPUs
,and there are more resource show up in Windows task manager). Now, the question is how to reduce the LNS wait for LGWR redo EVENT and CPU TIME EVENT.
attachment is the whole statspack report.
Thanks for your help!
Beast Ji
-
Attachment: spreport.TXT
(Size: 7.97KB, Downloaded 1352 times)
|
|
|
|
Goto Forum:
Current Time: Mon Apr 29 14:08:03 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
