Prev: Problem with 10G Physical Standby Database (archive gap). HELP :-)
Next: ytd calculation
From: amy on 3 Feb 2010 01:48
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.

From: Gerard H. Pille on 3 Feb 2010 14:20
Jonathan Lewis wrote:
> The obvious guess is that there is some variation in bind
> variables used to run the query, and someone uses a value
> that produces a plan that's good for them but bad for everyone
> else.
>
> Since you have execution plans that take hours to complete
> you should have time to query v$sql_plan to find the bind
> variables used to generate the plan. If you can do the same
> when a good plan is running, this may give you a clue about
> the problem.
>
> You'll need to find the SQL_ID of the statement then make a
> call the appropriate call to dbms_xplan
>
....
>

Indeed, and if you're sure to have a plan that should fit all queries, you could freeze it with
an outline.
From: Mladen Gogala on 3 Feb 2010 15:02
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.com
From: Shakespeare on 3 Feb 2010 16:02
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
From: vsevolod afanassiev on 3 Feb 2010 17:44
Hmm, so the plan changed even for queries with literal values (no bind
variables)
where none of the tables in the query was analyzed? What's about so-
called system statistics?
What is value of parameter TIMED_OS_STATISTICS?

 |  Next  |  Last
Pages: 1 2
Prev: Problem with 10G Physical Standby Database (archive gap). HELP :-)
Next: ytd calculation