Re: Optimizer issue in 11g
Date: Wed, 3 Feb 2010 20:02:42 +0000 (UTC)
On Tue, 02 Feb 2010 22:48:11 -0800, amy wrote:
> We have queries that have been completing in minutes for months in
> 184.108.40.206 that suddenly took hours to complete. Since the database table
> data are relatively static, we decided to disable the nightly auto stats
> gathering job, hoping for a more stable environment, but we are still
> having the same issue occasionally. Execution plan that used an index
> access path in the past suddenly used a Full table scan or a nested loop
> join in the past now becomes a hash join.
> We did verify that the statistics on the tables and indexes involved
> have not been reanalyzed and the stats remained the same since the last
> good run but yet the access path has changed. If everything remains the
> same, ie stats, init.ora parameters, could an access path changed? What
> else could influence the Optimizer?
In addition to what Jonathan has said about checking the execution plans, you should check the execution environment and see whether it is exactly the same as is in version 10g. Are the parameters you use for stats gathering exactly the same as on the version 10g? Did you gather system statistics on both versions? Are there any changes with regard to the system stats? Do you have histograms? For instance, in some cases having histograms could prevent the cursor from being shared. Also check the DBA_TAB_HISTOGRAMS and check whether all the underlying objects have exactly the same number of endpoints. Also, check DBA_TAB_COLUMNS and see that all the histograms are of the same type (frequency vs. height balanced). Also, check the parameters
optimizer_capture_sql_plan_baselines and optimizer_use_sql_plan_baselines. Those parameters are discussed at length in the excellent performance book written by Christian Antognini.