Re: Poorly performing query and 10053 trace
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: kennaim@xxxxxxxxx
- Date: Fri, 13 Jan 2006 06:14:52 +0000
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
https://metalink.oracle.com/metalink/plsql/docs/CS_10053.htm 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 <kennaim@xxxxxxxxx> 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
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
Other related posts:
