Tuning I/O-related wait - Metalink Note: 223117.1
- From: BN <bnsarma@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 9 Nov 2004 10:29:59 -0500
Greetings,
This is with reference to the metalink note:
http://metalink.oracle.com/metalink/plsql/ml2_documents.showFrameDocument?p_database_id=NOT&p_id=223117.1
<quote>
-----------------------------------------------------
MISINTERPRETING THE IMPACT OF WAIT EVENTS: AN EXAMPLE
-----------------------------------------------------
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
