Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tuning I/O-related wait - Metalink Note: 223117.1

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

From: Cary Millsap <>
Date: Wed, 10 Nov 2004 09:57:54 -0600
Message-ID: <004101c4c73e$0a119240$6801a8c0@CVMLAP02>

Andy's exactly right: it's just not the right data for making = conclusions.
If you're interested in a few pages' worth of why this is the case, = please
see pp210-217 in Millsap & Holt's "Optimizing Oracle Performance."

There's another problem with the question, too. You can't use Oracle's = timed
event durations (called "wait times" in documentation prior to Oracle version 10) as values of W in the queueing theory formula R =3D S + W. = Nor can
you use Oracle-reported CPU times as values of S in the formula. I = explain
this on pages 239-242 of the same book if you're interested.

Cary Millsap
Hotsos Enterprises, Ltd.
* Nullius in verba *

Upcoming events:

- Performance Diagnosis 101: 1/4 Calgary, 2/2 Sydney
- SQL Optimization 101: 11/8 Dallas, 12/13 Atlanta, 2/7 Sydney
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit for schedule details...

-----Original Message-----
From: =
On Behalf Of Andy Rivenes
Sent: Tuesday, November 09, 2004 3:39 PM To:;; =
Subject: RE: Tuning I/O-related wait - Metalink Note: 223117.1

At this level (e.g. "system") response time is a misnomer. What you have = is=20
elapsed time and the "capacity" to consume service time up to the total, = or=20
elapsed time, of the interval measured. Elapsed time at this level = cannot=20
be accurately "calculated" by adding "service time" and "wait time".=20 Elapsed time is calculated as the difference between measurement = intervals.=20
Wait time measurements at the system level are flawed at best due to=20 interval boundary measurements and the capacity for "infinite" waiting = (I=20
believe Cary coined this term first). However, Lex is otherwise correct = in=20
my opinion, since the "wait time" will generally skew high and since it = is=20
only a fraction of the "service time" it is probably not an overriding=20 issue. Of course, now we're venturing from workload measurement to=20 performance tuning, and for that you need to look at the "process" = you're=20
concerned about and not focus on system level workload measurements.

At 08:28 AM 11/9/2004, Lex de Haan wrote:
>See in line ...
>Let's do some simple math from these figures:
>'Wait Time' =3D 10,827 x 100% / 52,01% =3D 20,817 cs 'Service Time' =3D =
>'Response Time' =3D 358,806 + 20,817 =3D 379,623 cs
>CPU time =3D 94.52% =3D 379,623 / 358,806
>direct path read =3D 2.85% =3D 10,827 / 358,806

Andy Rivenes


Received on Wed Nov 10 2004 - 09:55:40 CST

Original text of this message