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

From: Hallas, John, Tech Dev <John.Hallas_at_gb.vodafone.co.uk>
Date: Tue, 9 Nov 2004 15:38:18 -0000
Message-ID: <1C6E45ADB2EC324F9553E468ABFE0F6301C5D101@UKWMXM04>

The total time utilised is made up of busy time and wait time = (=3D358,806 + 20,817 =3D 379,623 cs)
If we do the sum (10,827/379,623)*100 we see that direct path read = consists of 2.85% of the overall time used

John

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of BN Sent: Tuesday, November 09, 2004 3:30 PM 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=3DNOT&p_id=3D223117.1

<quote>

-----------------------------------------------------=20

MISINTERPRETING THE IMPACT OF WAIT EVENTS: AN EXAMPLE=20

-----------------------------------------------------=20

=20

Here is a real life example of why it is important to look at both 'Wait = Time'=20
and 'Service Time' when investigating database performance.=20
=20

The following is the "Top 5 Wait Events" section of a Statspack report=20 generated from two snapshots 46 minutes apart:=20
=20

Top 5 Wait Events                                                        =
     =20
~~~~~~~~~~~~~~~~~                                             Wait     % =
Total=20
Event                                               Waits  Time (cs)   =

Wt Time=20

-------------------------------------------- ------------ ------------ =
-------=20
direct path read                                    4,232       10,827   =
52.01=20
db file scattered read                              6,105        6,264   =
30.09=20
direct path write                                   1,992        3,268   =
15.70=20
control file parallel write                           893          198   =
  .95=20
db file parallel write                                 40          131   =
  .63=20
         -------------------------------------------------------------   =
     =20

=20

Based on this listing we may be tempted to immediately start looking at = the=20
causes between the 'direct path read' and 'db file scattered read' waits = and=20
to try to tune them. This approach would not take into account 'Service = Time'.=20
=20

Here is the statistic that measures 'Service Time' from the same report: =

=20

Statistic                                    Total   per Second    per =

Trans =20

--------------------------------- ---------------- ------------ =
------------  =20
CPU used by this session                   358,806        130.5     =

12,372.6 =20
=20

Let's do some simple math from these figures:=20

'Wait Time' =3D 10,827 x 100% / 52,01% =3D 20,817 cs=20
'Service Time' =3D 358,806 cs=20
'Response Time' =3D 358,806 + 20,817 =3D 379,623 cs=20

=20

If we now calculate percentages for all the 'Response Time' components:=20
=20

CPU time                    =3D 94.52%=20
direct path read            =3D  2.85%=20
db file scattered read      =3D  1.65%=20
direct path write           =3D  0.86%=20
control file parallel write =3D  0.05%=20
db file parallel write      =3D  0.03%=20

=20

It is now obvious that the I/O-related Wait Events are not really a = significant=20
component of the overall Response Time and that subsequent tuning should = be=20
directed to the Service Time component i.e. CPU consumption.=20
=20

Incidentally, the improved "Top 5 Timed Events" section in Statspack = starting=20
with Oracle9i Release 2 would show output similar to our calculated = listing.=20

Can sombody please expalin :

How did he come out with=20

CPU time                    =3D 94.52%
direct path read            =3D  2.85%  .........

--=20
Regards & Thanks
BN

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 09 2004 - 09:34:46 CST