FW: Re: Tuning approach question

  • From: "Joel Garry" <joelgarry@xxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 26 Sep 2005 11:34:06 -0700

Mladen wrote:

> Oracle instances seldomly complain, users do that instead.

The first thing one should do is look at the alert log, since that is
where Oracle complains, and the problem has already stated that many
users are affected.  We all ought to know that most performance problems
lay in the sql, but when you have a production instance you need to
check the obvious production issues first, even before any complaints
are made - and shouldn't the assumption be that the sql was tuned in
development?  Then, when a problem is given, you must narrow it down -
what has changed?  So I think the second step is what the OP said, use
tools to see if anything is out of the ordinary (including waits).  At
that point you may see something obvious - for example, excessive
scattered reads, maybe an index got lost?  You can use the GUI's (or try
to be impressive by giving CLI scripts off the top of your head :-) to
look at the SQL in question, and perhaps get a clue where to look next -
statistics messed up, plan stability lost... Then you can get into such
things as, are they running statspack, various tracing options, etc.  as
in the OP.

It's difficult to know in an interview situation what the correct answer
they are seeking is, so you must also query the interviewer to get an
idea .  You never know when they think hit ratio tuning is a good idea
(after all, don't the advisors use ratios?), and must be able to divine
from them where to go.  Hopefully, the best interviewers will be more
interested in your methods for getting to an answer than whether you
know how to twiddle a particular knob or parrot a party line, but I
don't know that many interviewers are "best."  Most just seem to have a
checklist of keywords.  Some are impressed if you happen to mention a
tuning controversy making the rounds online.  Some are unimpressive if
they have no clue about such things.

YMMV, I may have a minority viewpoint about these things.

Joel Garry


Other related posts: