Re: Poorly performing query and 10053 trace
- From: Jurijs Velikanovs <j.velikanovs@xxxxxxxxx>
- To: kennaim@xxxxxxxxx
- Date: Fri, 13 Jan 2006 00:07:46 +0000
>> this query accesses 2 analyzed tables
>> and 3 unanalyzed tables
If I recall correctly in 817 if some of the tables have no statistics
Oracle uses RULE based optimizer.
For RULE based optimizer the order in which indexes have been created
some times play trumendios role.
Can you confirm the that query parsed in the RULE mode?
Jurijs
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
>
>
>
--
Jurijs
+44 7738 013090 (GMT)
============================================
http://otn.oracle.com/ocm/jvelikanovs.html
--
//www.freelists.org/webpage/oracle-l
Other related posts:
