9.2.0.6 Optimizer issue

I am having an issue where on the production database a report query is = taking about 15 minutes longer than on the test database (run time is = 15 minutes on test and 30 minutes on prod). The Test is an exact replica = of production including stats. (This was done by copying the backup of = the production database to the test server)=20 The problem is the with the stats being the same the execution plans are = different. The only difference between the two is db_cache_size and = pga_aggregate_target, with the test instance being sized smaller. You = would think that since the test instance is smaller would run slower. = Also in the production database their are about 500-600 active = connections to the database (OTLP). The big difference between the two = execution plans is in prod it is using Nested loop joins and in test, = where it is faster, it is using Hash joins.

Here is the oddity/question, Last weekend we had an application upgrade = and I had to re-analyzed the database. The difference in the way I = gathered the stats changed as well for the method_opt I changed 'FOR ALL = INDEXED COLUMNS SIZE SKEWONLY' to 'FOR ALL INDEXED COLUMNS SIZE AUTO'. = Last week the report ran in less than 15 minutes. Would this change be = the make that much of a difference?

I normally do not analyze the database often because the database is = running optimally and there are no Huge changes to the database.=20

9.2.0.6 32-bit
Sun Solaris 2.8

Rich

--
http://www.freelists.org/webpage/oracle-l