Re: Why wait?

John,

Agreed.

My comments were specifically about two events, "db file scattered read" and "db file sequential read."

I'm just a beginner at waits, so maybe I don't get it. My understanding is, all you need to do is three back flips, a few drill downs, interpret P1 and P2, analyze what STATE means, then do a few foreign key lookups and, Pow!!!, you've found an SQL statement that's causing a problem. Those pesky ratios are kinda vague, but these waits are specific, pointing you to exactly, precisely the source of a problem.

Except that in the case of *these two wait events*, which are so commonly described, they really don't point you to anything of interest.

(1) There's no guarantee that the SQL statement we locate will be in any need of tuning at all. Not even a little. The most we can say is it happened to be doing a "db file scattered read" or a "db file sequential read" at the instant we queried v$session_wait. How do we go from this modest finding:

"I've located an SQL statement that just did a read"

to the conclusion that:

"Here is the problem, an SQL statement so bad I can't believe it. We have used the wait interface to locate a specific source of trouble in our database."

Really, all we've found is an SQL statement that happens to be doing a read at the moment. Why focus on this? There's no guarantee this statement is in any need of tuning. None.

(2) The fallback, apparently, is that all SQL statements could probably use a little tuning, and those doing a lot of reads might be good targets. Fine ... then why not query v$sql? Now we have a nice list, ordered by physical reads.

With the wait views, all you can do is query v$session_wait. What am I supposed to do, keep hitting enter all day, watching for "db file sequential read" and "db file scattered read" waits to go by? This is a methodology? To me, it's the equivalent of randomly peeking at one SQL statement after another in the result list from v$sql, never even knowing where in the list you are.

Anyway, I don't claim to be an expert on this topic. I'm hoping someone will blow a hole through all this and show me the light.

Thanks.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, April 24, 2002 6:23 PM

> 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).

-- 
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).