Re: Optimizer issue in 11g
Date: Wed, 3 Feb 2010 20:02:42 +0000 (UTC)
Message-ID: <pan.2010.02.03.20.02.42_at_email.here.invalid>
On Tue, 02 Feb 2010 22:48:11 -0800, amy wrote:
> Hi,
> We have queries that have been completing in minutes for months in
> 11.1.0.7 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?
>
> Thanks.
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.
-- http://mgogala.byethost5.comReceived on Wed Feb 03 2010 - 14:02:42 CST