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

  • From: "Roger Xu" <roger_xu@xxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 9 Nov 2004 15:19:41 -0600

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@xxxxxxxxx]
Sent: Tuesday, November 09, 2004 9:30 AM
To: oracle-l@xxxxxxxxxxxxx
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>

-----------------------------------------------------
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

For technical support please email tech_support@xxxxxxxxxxx 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@xxxxxxxxxxxx
--
//www.freelists.org/webpage/oracle-l

Other related posts: