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

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Tue, 9 Nov 2004 17:28:51 +0100
Message-Id: <20041109162856.9594F8188C0@ha-smtp1.tiscali.nl>

 

See in line ...  

Lex.  

---

visit http://www.naturaljoin.nl

---

skype me  

-----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 16:30
To: oracle-l_at_freelists.org
Subject: Tuning I/O-related wait - Metalink Note: 223117.1

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  

CPU time                    = 94.52% = 379,623 / 358,806
direct path read            =  2.85% = 10,827  / 358,806

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

Received on Tue Nov 09 2004 - 10:24:49 CST