STATSPACK report for DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ---------------- OMSfinance 1226615582 omsfinance 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: OMSfinance/omsfinance 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: OMSfinance/omsfinance 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)