RE: Why wait?

Greg,

As one who has been using the (now (un)officially named) OWI since I read Anjo's paper in '98, I can speak for its' value. I believe we are approaching the same problem from two sides, and there is merit for each approach. The Wait interface provides invaluable information from a Top-level, *environmental* view. I can take a look at V$ views and say 'ah-hah', and then look at V$SQL and a variety of other things. Looking only at V$SQL will _not_ reveal problems such as Log/DB writer performance or Disk read performance, nor will it show up locking issues or delays caused by SQL*Net, DBLinks, PQ inefficiencies, etc. (to name a few). OWI cannot show which SQL is causing a problem, it can only show up problems caused by inefficient SQL, as well as the environmental issues mentioned above.

I have always taken an iterative approach of fixing the environment first and then taking up the Top SQLs next, followed again by environmental changes, etc. It doesn't matter (sometimes) where you start as long as both are addressed. I have once had a case where I noticed an inordinate amount of wait on Dblink events, and fixed the issue by creating a local copy of a remote table that was referenced 100,000 times in a PL/SQL loop. No SQL changes! but I wouldn't have noticed the problem from just looking at the Top SQLs.... This saved the company from committing to a large upgrade and made me very unpopular with the IBM Sales Rep :) I have repeated this pattern in many other cases - and have made a bunch of HP/Sun/Sequent Sales reps unhappy on the way. (Apologies in these forums to those from a H/W vendor!)

At the same time, I wouldn't go overboard with some figures from OWI or try to intrepret every single statistic - you will only end up confused. At times, it is still an art and not a science, but it is a much better and clearly defined art than using CHRs!

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Grace - Getting something we don't deserve Mercy - NOT getting something we deserve

Click on 'http://www.needhim.org' for Grace and Mercy that is freely available!

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Greg Moore [mailto:sqlgreg_at_pacbell.net]
> Sent: Wednesday, April 24, 2002 2:08 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Why wait?
>
>
> > 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: John Kanagaraj
  INET: john.kanagaraj_at_hds.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).