FW: Re: Tuning approach question

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
http://www.garry.to

--
http://www.freelists.org/webpage/oracle-l