X-list: oracle-l Return-Path: Subject: Poorly performing query and 10053 trace From: Ken Naim Message-id: 43c68bfa.3242818a.6800.4e4b@mx.gmail.com Date: 2006-01-12 18:04:02 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 -- http://www.freelists.org/webpage/oracle-l