Hi all
below is results from my statspack and I would like get some opinion from yous guys to be sure that my analysis is correct:
Code:
Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 1,137.20 566,328.00
Logical reads: 5,048.43 2,514,118.00
Block changes: 2.22 1,105.00
Physical reads: 3,500.12 1,743,061.00
Physical writes: 9.47 4,718.00
User calls: 1.41 702.00
Parses: 1.20 600.00
Hard parses: 0.03 15.00
Sorts: 0.91 451.00
Logons: 0.00 0.00
Executes: 1.91 951.00
Transactions: 0.00
% Blocks changed per Read: 0.04 Recursive Call %: 89.55
Rollback per transaction %: 0.00 Rows per Sort: 640.81
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 30.84 In-memory Sort %: 99.11
Library Hit %: 88.40 Soft Parse %: 97.50
Execute to Parse %: 36.91 Latch Hit %: 99.99
Parse CPU to Parse Elapsd %: 82.61 % Non-Parse CPU: 99.80
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 93.86 93.88
% SQL with executions>1: 60.98 62.99
% Memory for SQL w/exec>1: 32.95 35.39
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 97 75.94
db file sequential read 1,532,454 28 22.23
db file scattered read 23,868 2 1.61
control file parallel write 162 0 .15
db file parallel read 626 0 .03
-------------------------------------------------------------
Wait Events for DB: TEST Instance: TEST Snaps: 36 -37
-> 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
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read 1,532,454 0 28 0 ########
db file scattered read 23,868 0 2 0 ########
control file parallel write 162 0 0 1 162.0
db file parallel read 626 0 0 0 626.0
latch free 7 2 0 3 7.0
log file sync 2 0 0 8 2.0
SQL*Net more data to client 101 0 0 0 101.0
control file sequential read 122 0 0 0 122.0
direct path read 420 0 0 0 420.0
direct path write 260 0 0 0 260.0
log file parallel write 31 31 0 0 31.0
db file parallel write 6 0 0 0 6.0
LGWR wait for redo copy 2 0 0 0 2.0
SQL*Net message from client 641 0 1,733 2704 641.0
SQL*Net message to client 641 0 0 0 641.0
-------------------------------------------------------------
I think I have problem in I/O but there is nothing much I can do b/c this server only have one RAId controller and it runs on RAID 5. If I have more than RAID array that would give me option to balance out the loads. Basically, this report was captured only during the READ period from the map. Other things I want to ask is the buffer hit ratio is faily low. Any other comment you can see or advises????
thanks and have a great weekend everyone.