| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tuning I/O-related wait - Metalink Note: 223117.1
Why there is no "Top 5 Wait Events" in our statspack report?
But we have:
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~ % Total
Event Waits Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time 20,014 41.90
db file sequential read 12,629,049 17,830 37.33
log file sync 223,490 4,221 8.84
enqueue 4,977 2,711 5.68
db file parallel write 27,227 1,082 2.27
-------------------------------------------------------------
I guess "db file sequential read" is our problem. Any comments?
Thanks,
Roger
-----Original Message-----
From: BN [ mailto:bnsarma_at_gmail.com]
Sent: Tuesday, November 09, 2004 9:30 AM
To: oracle-l_at_freelists.org
Subject: Tuning I/O-related wait - Metalink Note: 223117.1
Greetings,
This is with reference to the metalink note:
http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_database_id=NOT <http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_database_id=NOT&p_id=223117.1> &p_id=223117.1
<quote>
Here is a real life example of why it is important to look at both 'Wait Time' and 'Service Time' when investigating database performance.
The following is the "Top 5 Wait Events" section of a Statspack report generated from two snapshots 46 minutes apart:
Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
direct path read 4,232 10,827 52.01
db file scattered read 6,105 6,264 30.09
direct path write 1,992 3,268 15.70
control file parallel write 893 198 .95
db file parallel write 40 131 .63
-------------------------------------------------------------
Based on this listing we may be tempted to immediately start looking at the causes between the 'direct path read' and 'db file scattered read' waits and to try to tune them. This approach would not take into account 'Service Time'.
Here is the statistic that measures 'Service Time' from the same report:
Statistic Total per Second per Trans --------------------------------- ---------------- ------------ ------------ CPU used by this session 358,806 130.5 12,372.6
Let's do some simple math from these figures:
'Wait Time' = 10,827 x 100% / 52,01% = 20,817 cs 'Service Time' = 358,806 cs 'Response Time' = 358,806 + 20,817 = 379,623 cs
If we now calculate percentages for all the 'Response Time' components:
CPU time = 94.52% direct path read = 2.85% db file scattered read = 1.65% direct path write = 0.86% control file parallel write = 0.05% db file parallel write = 0.03%
It is now obvious that the I/O-related Wait Events are not really a significant component of the overall Response Time and that subsequent tuning should be directed to the Service Time component i.e. CPU consumption.
Incidentally, the improved "Top 5 Timed Events" section in Statspack starting with Oracle9i Release 2 would show output similar to our calculated listing.
<End quote>
Can sombody please expalin :
How did he come out with
CPU time = 94.52% direct path read = 2.85% .........
-- Regards & Thanks BN -- http://www.freelists.org/webpage/oracle-l For technical support please email tech_support_at_dp7uptx.com or you can call (972)721-8257. This email has been scanned for all viruses by the MessageLabs Email Security System. This e-mail is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Any review, dissemination, copying, printing or other use of this e-mail by persons or entities other than the addressee is prohibited. If you have received this e-mail in error, please contact the sender immediately and delete the material. ____________________________________________________________________ This email has been scanned for all viruses by the MessageLabs Email Security System. Any questions please call 972-721-8257 or email your request to tech_support_at_dp7uptx.com. -- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 09 2004 - 15:18:25 CST
 |
 |