Optimizer issue in 11g
amy
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.
Jonathan Lewis
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
http://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/
http://rwijk.blogspot.com/2008/03/dbmsxplandisplaycursor.html
select * from table(dbms_xplan.display_cursor({your
SQL_ID},null,'advanced'));
or simply
select * from table(dbms_xplan.display_cursor({your
SQL_ID},null,'peeked_binds'));
--
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
"amy" <amyk...@gmail.com> wrote in message
news:d687e1b6-7758-47c4...@m31g2000yqd.googlegroups.com...
amy
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
>
>
> select * from table(dbms_xplan.display_cursor({your
> SQL_ID},null,'advanced'));
> or simply
> select * from table(dbms_xplan.display_cursor({your
> SQL_ID},null,'peeked_binds'));
>
> --
> Regards
>
> Jonathan Lewishttp://jonathanlewis.wordpress.com
>
>
> news:d687e1b6-7758-47c4...@m31g2000yqd.googlegroups.com...
>
> > 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.
Jonathan, thanks for your response. There was only one query that used
bind variables.The rest of the queries used hardcoded values. It's
puzzling how the execution plan for a static query could change given
that the table/index stats were frozen and the table data remains
pretty much unchanged. I'll check out the bind values as suggested by
you if the query runs slow again.
Thanks.
Gerard H. Pille
> 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.
Mladen Gogala
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.
Shakespeare
Shakespeare
vsevolod afanassiev
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?
Mladen Gogala
> Who mentioned 10g here?
>
> Shakespeare
Oh, I apologize. My understanding was that queries were running fine
under 10g and that the problem emerged with an upgrade to 11g. I should
be reading more carefully.
Randolf Geist
> We have queries that have been completing in minutes for months in
> 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?
That is indeed an interesting question. There are a couple of ideas
how this could happen, some of them have already been mentioned here:
- Bind variable peeking (as mentioned by Jonathan): But you say that
queries are affected that do not use bind variables
- Dynamic Sampling: This could be verified by checking the "Notes"
section of the DBMS_XPLAN.DISPLAY_CURSOR/AWR output for the queries in
question. Note that depending on the level of Dynamic Sampling used
you can get Dynamic Sampling even with statistics in place (starting
with Dynamic Sampling at cursor/session/system level 3)
- Re-gathered System Statistics: If you re-gather System Statistics,
the plans might change without changes to the object statistics
- Usage of SQL Profiles / SQL Baselines: If you have a suitable
license (Enterprise Edition + Tuning Pack) you could have configured
the automatic execution of the SQL Tuning Advisor to accept SQL
Profiles automatically, which in case of existing SQL Baselines also
includes the automatic evolution of unaccepted SQL Baselines. Again
both (Usage of SQL Profiles / SQL Baselines) can be checked in the
"Notes" section, where it will be indicated if the plan uses a SQL
Profile or Baseline. See e.g. Christian Antognini's blog post about
this topic: http://antognini.ch/2008/12/automatic-evolution-of-sql-plan-baselines/
- Adaptive Optimisation: I don't think that this feature is already
there in 11.1 (enabled in 11.2) - this would be another obvious
explanation - of course this feature is supposed to improve estimates
of the optimizer, but who knows. Again this could be checked via the
"Notes" section ("Cardinality Feedback used"). For more information:
http://jonathanlewis.wordpress.com/2009/12/16/adaptive-optimisation/
- Optimizer Bug: You might hit a bug in the optimizer code
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
hpuxrac
snip
Did someone put on patches or perhaps the 11.1.0.7.1 and/or 11.1.0.7.2
patchset update?
That won't show from sqlplus etc output ( will still look like
11.1.0.7.0 ) ...
amy
I posted this question. I was hoping to check out some of the
suggestions posted here. We do not use SQL Baseline and our System
Stats have not been re-gathered. I did not realize that Dynamic
Sampling can happen even with Stats in place. Thanks Randolf for
pointing that out. That might have contributed to the plan changes.
Thanks.
Randolf Geist
Just to point to another possibility that Richard Foote has written
about. According to your description so far I thought that it doesn't
apply to your particular case but I find his post useful, so I'm
linking it here: