Re: 9i Slow SQL Performance - EXPERTS apply within

On Wed, 05 Apr 2006 06:42:58 -0700, Johne_uk wrote:

> I've take a number of steps to identify the problem.

The first step should be to study the problem carefully.

>
> 1. Rebuilt all indexes.

Why did you do that? Did it have any impact?

> 2. Gathered up-to-date stats.

Why did you do that? Did it have any impact?

> 3. Changed sort_area_size parameter (increased). No difference.

Why did you do that? Did it have any impact?

> 4. Ran Explain Plans on both 9i instances (execution plans were
> identical as were stats).

OK. Now we're comparing plans on 9i instances (2 0f them) vs. plans on 8i instance. CBO on 9i is fairly different from the one on 8i instance. In addition to that, your SQL trace shows only 1.05 second execution time, not 34 seconds. This might have been an application latency. Oracle 9i can do certain things that Oracle 8i can't do: index skip scan, parallel fast full index scan, hash semi-join and some other things. You don't have any of those operations in your plan, but comparing plans on 8i and 9i based on the identical statistics is still comparing apples to oranges. Unless 9i instances do something that 8i is not capable of, you can force the plan by using hints. If, on the other hand, your 9i instances do something that your 8i instance is not capable of, then you should upgrade your 8i instance to 10GR2, optimize the statement there, analyze 9i instances and expect them to follow the suit.

> 5. Ran trace and examined output with TKPROF. Output is below but there
> is nothing alarming other than that the Fetch time is 1 second whilst
> the entire query takes 34 secs to run.

I don't see those 34 seconds. At any rate, you should have run the event 10046, level 8 and not SQL trace. On your $ORACLE_HOME/rdbms/admin directory there is a script called dbmssupp.sql:  $ ls -l $ORACLE_HOME/rdbms/admin/dbmssupp.sql -rw-r--r-- 1 501 1546 Mar 31 08:08 /oracle/product/10g/rdbms/admin/dbmssupp.sql

It is described in the note:62294.1. Install the package and use it instead of SQL trace. You can use tkprof from 9i to analyze it with WAITS=YES option.

You need this not only to find an execution plan but also to find out what are you waiting for and what events are the main cause of the difference in the execution times. In other words, you must find out where is the time lost. That part of your homework is a non-trivial one. Fortunately, there is a company called Hotsos (www.hotsos.com) which is very good at analyzing those traces and helping people with the performance advice. They do that for a living. Analysis of a single trace file or two is not exorbitantly expensive and you can learn quite a bit from them. Also, in UK there is a gentleman named Jonathan Lewis who is very good at performance tuning and CBO analysis, he even wrote a book about it. He can be reached at: jonathan_at_jlcomp.demon.co.uk and his web page is http://www.jlcomp.demon.co.uk. Jonathan can always come up with a cunning optimization plan which, unlike Baldrick's, usually works. Things like this usually need a professional, if you want them done right and when it comes to CBO, I cannot think of anybody better then Jonathan. Disclaimer: I don't work for him nor am I paid a commission or fee of any kind. I'm just a fan and a long time DBA who learned a lot from Jonathan's books.

-- 
http://www.mgogala.com