Oracle9i statspack report analysis direct path read wait event, oracle9istatspack

Oracle9i statspack report analysis direct path read wait event, oracle9istatspack
DB Name DB Id Instance Inst Num Release Cluster Host
-------------------------------------------------------------------------
LIXORA 1409317108 LIXORA 1 9.2.0.1.0 NO lixora-DATA


Snap Id Snap Time Sessions Curs/Sess Comment
-------------------------------------------------------------
Begin Snap: 52 06-8 months-15 09:35:04 107
End Snap: 53 112 09:49:47 106.5
Elapsed: 14.72 (mins)


Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 600 M Std Block Size: 8 K
Shared Pool Size: 200 M Log Buffer: 10,240 K

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
------------------------------
Redo size: 3,473.25 1,406.83
Logical read: 6,959.90 2,819.08
Block changes: 15.52 6.28
Physical read: 554.17 224.46
Physical writes: 391.05 158.39
User cballs: 80.09 32.44
Parses: 13.35 5.41
Hard parses: 1.69 0.68
Sorts: 6.34 2.57
Logons: 0.03 0.01
Executes: 167.66 67.91
Transactions: 2.47


% Blocks changed per Read: 0.22 Recursive Call %: 70.00
Rollback per transaction %: 79.27 Rows per Sort: 2161.38


Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~
Buffer Nowait %: 99.54 Redo NoWait %: 100.00
Buffer Hit %: 97.66 In-memory Sort %: 93.03
Library Hit %: 98.20 Soft Parse %: 87.35
Execute to Parse %: 92.04 Latch Hit %: 98.16
Parse CPU to Parse Elapsd %: 19.16% Non-Parse CPU: 95.76


Shared Pool Statistics Begin End
------------
Memory Usage %: 86.21 86.82
% SQL with executions> 1: 52.42 54.94
% Memory for SQL w/exec> 1: 49.55 51.72


Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
---------------------------------------------------------------------------
Direct path read 40,202 8,654 51.85
Buffer busy waits 28,721 2,001 11.99
Db file sequential read 26,887 1,997 11.97
Enqueue 553 1,702 10.20
Db file scattered read 22,423 1,661 9.95
-------------------------------------------------------------
Wait Events for DB: LIXORA Instance: LIXORA Snaps: 52-53
-> S-second
-> Cs-centisecond-100th of a second
-> MS-millisecond-1000th of a second
-> Us-microsecond-000000th 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
--------------------------------------------------------------------------
Direct path read 40,202 0 8,654 215 18.4
Buffer busy waits 28,721 16 2,001 70 13.2
Db file sequential read 26,887 0 1,997 74 12.3
Enqueue 553 553 1,702 3078
Db file scattered read 22,423 0 1,661 74 10.3
Log file sync 493 4 111 224 0.2
Direct path write 1,588 0 100 63 0.7
Control file parallel write 243 0 99 408 0.1
Log file parallel write 508 439 86 169 0.2
Db file parallel write 342 171 80 233 0.2
Control file sequential read 318 0 16 51 0.1
Library cache pin 118 0 8 67 0.1
Library cache load lock 6 2 6 1060 0.0
Latch free 679 625 0 1 0.3
Row cache lock 5 0 0 17 0.0.
SQL * Net more data to client 871 0 0 0 0.4
SQL * Net break/reset to clien 26 0 0 0.0
LGWR wait for redo copy 1 0 0 0.0
SQL * Net message from client 69,253 0 20,459 295 31.8
Virtual circuit status 28 28 840 29999 0.0
SQL * Net message to client 69,269 0 0 0 31.8
SQL * Net more data from clien 21 0 0 0 0.0
-------------------------------------------------------------
Background Wait Events for DB: LIXORA Instance: LIXORA Snaps: 52-53
-> Ordered by wait time desc, waits desc (idle events last)


Avg
Total Wait wait Waits
Event Waits Timeouts Time (s) (MS)/txn
--------------------------------------------------------------------------
Direct path read 1,467 0 278 190 0.7
Db file scattered read 1,483 0 152 103
Buffer busy waits 1,441 0 146 102 0.7
Control file parallel write 244 0 100 408
Log file parallel write 508 439 85 168 0.2
Db file parallel write 344 172 80 233 0.2
Db file sequential read 319 0 23 71 0.1
Control file sequential read 100 0 6 59 0.0
Direct path write 8 0 0 25 0.0
Latch free 23 22 0 1 0.0
LGWR wait for redo copy 1 0 0 0.0
Rdbms ipc message 2,435 1,959 5,846 2401 1.1
Smon timer 3 3 922 ##### 0.0
SQL * Net message from client 32 0 0 1 0.0
SQL * Net message to client 32 0 0 0.0


Redo production is only KB per second, and the system cpu and mem have no large overhead;
Log file parallel write waits for an average of 168 ms. It basically indicates that the system io load is too high.
The direct path read wait is characterized by high io load;


'Wait time' = 49982.8604 cs
'Service time' = 48414 cs
& Apos; Response Time & apos; = 98396.8604 cs

-- 'Response time' components :---
CPU time = 49.2%
Direct path read = 26.7%
Db file sequential read = 7.3%
Enqueue = 6.9%.
Db file scattered read = 3.9%
Buffer busy waits = 2.1%


After detailed analysis of statspack, it is found that the service time of the system is 49.2%, while the cpu is high-end,
It is also indicated from one side that the application has a large number of cpu operations, such operations may be sorting, or a large number of hard parsing,
However, no wait events related to the shared pool are displayed in the top event.


Continue to check SQL ordered by Gets
CPU Elapsd
Buffer Gets Executions Gets per Exec % Total Time (s) Hash Value
--------------------------------------------------------------------------
333,146 7 47,592.3 2.6 43.73 ######## 3804332362
Select count (0) from (select LIXORA_QX. *, (select yqlb from LIXORA_QX
Yq where LIXORA_HLJ.SEQ_NUMBER = LIXORA_QX.DQYQXH) as yqlb from T_S
B _QX where (1 = 1) order by C_DATE) -- A sorting operation exists.





Suggestion:
1. SQL statement adjustment to reduce sorting; you can add an index to the c_date field or remove order

2. replace higher-performance storage

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.