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

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.
http://www.hotsos.com
* 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 www.hotsos.com for schedule details...

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Andy Rivenes
Sent: Tuesday, November 09, 2004 3:39 PM To: lex.de.haan_at_naturaljoin.nl; bnsarma_at_gmail.com; = oracle-l_at_freelists.org
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 ...
>
>Lex.
>
>
>
>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 =
358,806
cs
>'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
Email: arivenes_at_llnl.gov

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

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