Re: Why wait?

Greg,

You wrote:

If your goal is to find SQL that does a lot of physical reads, why not forget about these two events? Just go to v$sql and sort by physical reads. Now you have a comprehensive picture of what's going on.

One of the problems with that is that you assume that 100 I/Os are always faster than 1000 I/Os. And that is not always true ...... Why not ? If the 100 I/Os go to a file that has a service time of 100 msec per I/O and the 1000 I/Os go to a file with a service time of 5 msec per I/O, it would be better to tune the statement that does 100 I/Os (well in this example). The mistake made is that we assume that all I/O is the same cost acros all disks (and that is not true ........). So sorting by physical reads looks like a good thing, but it doesn't guarantee it !

You wrote:

If your goal is something else, then what is it? Is it ever worthwhile to use the wait views to investigate these two wait events - "db file scattered reads" and "db file sequential reads"?

Yes, to find files that have high service times. The problem in Oracle is that wait events are not broken down per SQL statement (only on the instance and session level). There are products that out there that do this for you, but that would be completely different post ;-)

Anjo.

Greg Moore wrote:

> > Thats fine for expensive sql...but waits can be a
> > whole lot more than that...
>
> Agreed. I'm just asking about "db file scattered read" and "db file
> sequential read." What's the purpose of using the wait interface to
> investigate these two events?
>
> The accounts I've read of drilling down through the wait interface views
> make it seem like you're hot on the trail of Dr. Evil. But in fact these
> two wait events *don't* lead you to poorly tuned SQL, just SQL that does a
> lot of physical reads and happen to be popular at the time you looked at
> v$session_wait.
>
> What's more, using the wait views to find such SQL seems to be a very
> catch-as-catch-can kind of affair. Maybe an SQL statement that does a lot
> of physical reads is running when you look, maybe it isn't.
>
> If your goal is to find SQL that does a lot of physical reads, why not
> forget about these two events? Just go to v$sql and sort by physical reads.
> Now you have a comprehensive picture of what's going on.
>
> If your goal is something else, then what is it? Is it ever worthwhile to
> use the wait views to investigate these two wait events - "db file scattered
> reads" and "db file sequential reads"?
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Greg Moore
> INET: sqlgreg_at_pacbell.net
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Anjo Kolk
  INET: anjo_at_oraperf.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).