Re: Optimizer issue in 11g
From: Randolf Geist <mahrah_at_web.de>
Date: Thu, 4 Feb 2010 13:49:13 -0800 (PST)
Message-ID: <cbf3a11f-dc26-4202-8ec8-b28401c35e64_at_3g2000yqn.googlegroups.com>
On Feb 3, 7:48 am, amy <amykl..._at_gmail.com> wrote:
> We have queries that have been completing in minutes for months in
> 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?
Date: Thu, 4 Feb 2010 13:49:13 -0800 (PST)
Message-ID: <cbf3a11f-dc26-4202-8ec8-b28401c35e64_at_3g2000yqn.googlegroups.com>
On Feb 3, 7:48 am, amy <amykl..._at_gmail.com> wrote:
> We have queries that have been completing in minutes for months in
> 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?
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 Received on Thu Feb 04 2010 - 15:49:13 CST