Re: Optimizer issue in 11g
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 03 Feb 2010 22:02:36 +0100
Message-ID: <4b69e465$0$22936$e4fe514c_at_news.xs4all.nl>
Op 3-2-2010 21:02, Mladen Gogala schreef:
> 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.
>
>
>
Who mentioned 10g here?
Date: Wed, 03 Feb 2010 22:02:36 +0100
Message-ID: <4b69e465$0$22936$e4fe514c_at_news.xs4all.nl>
Op 3-2-2010 21:02, Mladen Gogala schreef:
> 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.
>
>
>
Who mentioned 10g here?
Shakespeare Received on Wed Feb 03 2010 - 15:02:36 CST