Re: 9i Slow SQL Performance - EXPERTS apply within

General Tuning

Besides holding slightly more data it is also likely that your production system has a significantly heavier user load. This means there are more processes contending for resources including buffer pool blocks, shared pool latches, and IO's.

When tunning a multi-table join make sure that all tables have data in all environments. You want to be able to run the same query and get the exact same result set. I have seen where a query ran very fast in test but was slow in production and the reason was that the CBO was able to not do work where no matching row were found in test for part of the query but in production there was data. This resulted in the production version running much slower than the developer expected.

Also if the SQL is written with bind variables it is important that the explain plan being looked at was ran using bind variable place holders rather than constrants in the SQL. A query with constants instead of bind variables is a totally different query to the CBO and the plans for the two versions of the same query are often very different.

More specific to posted problem after above is verified

You should pull the production plan from v$sql_plan and compare that to the regular explain. You should grab some statistics for physical io, logical io, and latching for runs of the query in both test and production. Finding where the differences lie may well identify if a resource issue exists.

Bind variable peeking is a possible issue.

HTH -- Mark D Powell -- Received on Wed Apr 05 2006 - 09:14:26 CDT