Tuning I/O-related wait - Metalink Note: 223117.1

From: BN <bnsarma_at_gmail.com>
Date: Tue, 9 Nov 2004 10:29:59 -0500
Message-ID: <61292a9d041109072952c7b8a2@mail.gmail.com>

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.

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

Received on Tue Nov 09 2004 - 09:25:43 CST