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 June-August-15 09:35:04 107 110.9
End snap:53 June-August-15 09:49:47 112 106.5
elapsed:14.72 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer cache:600m Std Block size:8k
Shared Pool size:200m Log buffer:10,240k
Load profile
~~~~~~~~~~~~ per Second per Transaction
--------------- ---------------
Redo size:3,473.25 1,406.83
Logical reads:6,959.90 2,819.08
Block changes:15.52 6.28
Physical reads:554.17 224.46
Physical writes:391.05 158.39
User calls: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-1000000th of a second
-Ordered by wait time desc, waits desc (idle events)
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 0.3
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 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.0
LGWR wait for Redo copy 1 0 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)
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 0.7
Buffer busy Waits 1,441 0 146 102 0.7
Control file Parallel Write 244 0 100 408 0.1
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.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.0
Redo production per second only 3.4kb, and the system CPU, mem have no big overhead;
Log file parallel write average wait 168ms; it basically indicates that the system IO load is too high
Direct path read waits too high to characterize it because of the high IO load;
' Wait time ' = 49982.8604 CS
' Service time ' = 48414 CS
' Response time ' = 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%
Detailed analysis of the statspack found that the system's service time took up 49.2%, and the high CPU overhead,
It is also shown from one side that the application has a large overhead CPU operation, which can generally be a sort, or a lot of hard parsing,
However, the top event does not see a shared pool-related wait event.
Keep looking at SQL ordered by Gets
CPU ELAPSD
Buffer gets executions gets per Exec%total time (s) 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)--there is a sort operation
Suggestions:
1 "SQL statement adjustment, reduce the sorting; You can index the C_date field, or remove the order by
2. Replacing higher-performance storage
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Oracle9i statspack Report Analysis Direct path read wait event