Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Poorly performing query and 10053 trace

Re: Poorly performing query and 10053 trace

From: Niall Litchfield <>
Date: 2006-01-13 07:14:52

It is certainly an opportunity to order Jonathan Lewis' latest book Cost Based Oracle Fundamentals, chapter 14 of which walks through an example 10053 trace file (albeit from 10g), and which is the most complete documentation of this trace of which I am aware. There is also a fairly recent case study on metalink for this trace will take you there.

To be honest I would start by taking a look at the explain plan generated for this query on the bad box, in particular I would look for bad estimates of the numbers of rows returned by different parts of the plan - you know that you will get them on the non-analyzed tables (unless you are locky and the tables are around the 100 row mark) - especially in comparison to your good plan. You know the sort of thing, why does Oracle think that this index lookup will return 10 rows when in fact the true figure is 1000.

Another fairly sensible thing that you can do, given that you have a plan that performs well enough for you, is to hint the sql to run the good plan and then see how Oracle costs that access (plus again look at the row estimates and see if they are just wrong). Essentially what you are looking for are glaring errors in the CBO calculations, or else surprising differences between the two plans.

On 1/12/06, Ken Naim wrote:
> I have two 817 databases dev/test that are cloned from production every 2
> weeks or so and the other day one query started performing poorly on test
> yet it is/was fine on both dev and prod. The optimizer mode is set to
> choose, however we still have one application that uses rule so its schema
> is not analyzed and this query accesses 2 analyzed tables and 3 unanalyzed
> tables. I have verified nothing has changed on the table/index structures
> and the statistics are very similar on the analyzed tables/indexes. The
> tables are accessed in the same way from both db's however 2 joins are
> different one uses a sort and a merge join, the other uses a hash and a
> nested loop. I ran a 10053 trace which I do not have a lot of experience
> with, and am at a loss why a different plan is chosen, the optimizer
> parameters are identical except for the hash_multiblock_io_count which in
> dev is showing as 0 and in test is showing as 1 as test is using mts as a
> test, both traces were run under a dedicated connection and when I set the
> hash_multiblock_io_count to 1 in the good plan still is generated.
> Any suggestions or pointers to what I should be looking for in the trace
> files would be appreciated. Also is there any software that analyzes 10053
> trace files similar to what the hotsos profiler does for the 10046?
> Thank you,
> Ken Naim
> --

Niall Litchfield
Oracle DBA
Received on Fri Jan 13 2006 - 07:14:52 CST

Original text of this message