Poorly performing query and 10053 trace
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 12 Jan 2006 11:04:02 -0600
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
Other related posts:
