Re: Why wait?

I wrote a paper/presentation (entitled "SQL Tuning for DBAs") in 1996, presenting it at several user groups and at Open World Australasia in late 1997. I still have it on my website (www.evdbt.com/library.htm). It advocated examining the SQL Area on one side (to find "bad" SQL statements) and examining the wait-interface on the other side (to find contention). Prior to that, I thought that SQL Trace and TKPROF were the only tuning tools available...

The methodology worked well, but it was weakest in getting started! It was OK once you got going I guess, but I really had no definite starting point, no clear way to differentiate between the two tracks of investigation, of quantifying the difference between the "resource consumption" issues and the "contention" issues. You just had one big area to investigate and then another big area to investigate. I was left with kind of a zen thing, very seat-of-the-pants, trying to "feel your way" to deciding whether resource-consumption or wait-contention was the biggest problem. In teaching it, I just simply stated that most problems involve SQL tuning, but sometimes there's contention, and...uh...you'll figure it out. Making things more confusing was the duality of the "db file .... read" events, which could cut both ways (i.e. resource-consumption or wait-contention).

I wasn't even aware of Anjo's YAPP paper until this year's IOUG-A "Live 2002" a few weeks ago, but I found www.oraperf.com in 1998 (I think? maybe 1999?) and the YAPP report available there was a blinding revelation. At last, a clear and definite starting point! No more zen, no more mysticism, no more guessing! Having that initial high-level view was a marvelous way to prove that nothing was being overlooked, that the agonies of trying to tune specific SQL statements or obscure wait-events was not wasted effort, that some worse bogey-man wasn't hiding somewhere else. Using YAPP, I can use the proven techniques of the SQL Area and Wait Events with confidence. They go together -- it's not an "either-or" thing...

Thanks Anjo! I'd offer you my first born, but he's almost 13, is starting to get uppity (i.e. makes a fool out of me often), is only a few inches shorter than me, and eats as much as the Denver Broncos (the whole team, not just one player), and has not yet figured out all the reasons for personal hygiene. Somehow, I don't think it would be gift of gratitude that it must have been once. :-) Besides, I've grown curious to see how he turns out -- lately, there's been hope that he would learn to appreciate Monty Python and Tom Lehrer, advancing beyond Mel Brooks, Jim Carrey, Drew Carey, and Austin Powers...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, April 24, 2002 7: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: Tim Gorman
  INET: Tim_at_SageLogix.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).