Oracle Scratchpad

December 16, 2009

Adaptive Optimisation ?

Filed under: CBO,Execution plans,trace files — Jonathan Lewis @ 11:53 pm GMT Dec 16,2009

Here’s an interesting post and test case from Gregory Guillou (WeDoStreams blog). It features an SQL statement that is re-optimised the second time you run it.

Since it’s running on 11.2.0.1 your first thought is likely to be “SQL Plan Management”, or “Adaptive Cursor Sharing” – except the first feature wasn’t enabled, and the statement doesn’t include any bind variables.

Gregory emailed me about this one, and  it was the thing that finally persuaded me to tear down a laptop and install 64-bit OEL with 11.2 – and I ran his test case and got the same results. (If you build his sample schema, you’ll need about 1GB of free space).

Highlights are:

  • Just run the query from SQL*Plus, then hit “/” to re-run it.
  • Check v$sql and you’ll find two child cursors, with different execution plans
  • Use dbms_xplan.display_cursor() to check v$sql_plan and you’ll see the second plan report that it’s using “cardinality feedback” (shades of Wolfgang Breitling)
  • Flush the shared_pool and repeat the exercise with event 10053 (optimizer trace) enabled and you’ll see that the second execution is optimised with a few opt_estimate() hints embedded.
  • Flush the shared_pool and repeat the exercise with event 10046 enabled and there’s no clue about how Oracle is getting the estimates it uses in the hints – it’s clearly not running any dynamic sampling code, and the “actuals” of v$sql_plan_statistics_all are not populated.

A word of warning – the test is not guaranteeably reproducible. Gregory discovered it on 32-bit Oracle, I’ve reproduced it on 64-bit Oracle. We were both using small SGAs and PGAs to make the database work; but Gregory found that he could not reproduce the effect on some other systems.

16 Comments »

  1. Jonathan. Thanks for the sharing.

    This is one of the most fantastic(but embarassing) new features I’ve ever seen.

    I’d try to reproduice this case on my laptop, but before that, I’ve got somethings on my mind.

    1) 11gR2 has introducied new parameters called “_optimizer_use_feedback” and “_optimizer_feedback_control” whose names seem to be quite related to this feature. It’s just a guess and I’d check it myself.

    2) Patent description(http://www.faqs.org/patents/app/20090037404) is saying that Oracle checks the system workloads and environments while doing a soft parse to determine whether the query needs to be re-optimized. For example, how many index blocks are cached in the buffer cache. This could be a reason for this phenomenon.

    Comment by Dion Cho — December 17, 2009 @ 2:03 am GMT Dec 17,2009 | Reply

  2. At last they did it. Though I don’t want it to be on by default – in my view it should be incorporated into some package, not into runtime engine.

    Documentation contains reference to the bug 8867819 (not visible in the MOS) which looks like the cause of this issue.

    Comment by Timur Akhmadeev — December 17, 2009 @ 8:10 am GMT Dec 17,2009 | Reply

  3. I may suggest seeing the VLDB 2008 paper and presentation “Closing the Query Processing Loop in Oracle 11g”

    Comment by Greg Rahn — December 17, 2009 @ 6:03 pm GMT Dec 17,2009 | Reply

  4. From the paper mentioned above by Greg:

    2.1 Cardinality Feedback

    Cardinality feedback aims to address cardinality misestimates by the Oracle cost-based optimizer due to complex predicates, and inaccurate or missing statistics. During the first execution of a SQL statement, an execution plan is generated as usual. During optimization, certain types of estimates that are known to be of low quality (for instance, estimates for tables which lack statistics or tables with complex predicates) are noted, and monitoring is enabled for the cursor that is produced. If cardinality feedback monitoring is enabled for a cursor, than at the end of execution, the estimates for single table cardinalities are compared to the actual cardinalities. If an estimate is found to be significantly different from the actual value, then the correct value is stored for later use. On subsequent executions, the query is re-optimized, and the correct cardinality is substituted for the usual estimate. If the estimate is found to be relatively close to the original estimate after the first execution, then the monitoring is disabled for future executions.

    Comment by Iggy Fernandez — December 17, 2009 @ 11:59 pm GMT Dec 17,2009 | Reply

    • Iggy,

      So all we need to know now is what types of predicate(s) cause this behaviour to appear. It would also be nice to know how close is “relatively” close.

      In this case, the query has a predicate:

          and t.time_id=trunc(t.time_id,'W')
      

      This type of expresssion typically makes the optimizer use a selectivity guess of 1% – so it may be this that prompted it to gather run-time stats on the first pass.

      Comment by Jonathan Lewis — December 18, 2009 @ 10:00 am GMT Dec 18,2009 | Reply

  5. ORACLE6>
    ORACLE7> ALTER SYSTEM SET OPTIMIZER_MODE=CHOOSE — CBO has bugs, don’t collect stats, but might use it when bugs are fixed
    ORACLE73> ALTER SYSTEM SET OPTIMIZER_MODE=RULE — CBO still has bugs
    ORACLE8> ALTER SYSTEM SET OPTIMIZER_MODE=RULE — CBO still has bugs – Tim who?
    ORACLE81> ALTER SYSTEM SET OPTIMIZER_MODE=RULE — CBO still has bugs, app not certified
    (9,10,11… spend lots of time following Jonathan et al)
    ORACLE14R3> ALTER SYSTEM SET OPTIMIZER_MODE=USER_RULE;

    Comment by joel garry — December 18, 2009 @ 1:24 am GMT Dec 18,2009 | Reply

  6. I’ve already came across this “cardinality feedback” keyword that made me wonder when examining some 10046 traces of the DBMS_XPLAN.DISPLAY function – so at least that function was already prepared for that in some earlier releases – off the top of my head I’m not sure if it was 10.2 or 11.1 where I saw this in the query executed by DBMS_XPLAN.DISPLAY – I guess it was 11.1.

    Randolf

    Comment by Randolf Geist — December 18, 2009 @ 9:23 pm GMT Dec 18,2009 | Reply

  7. By the way, thinking about it a bit further, this might be the first time that having suboptimal statistics in Oracle eventually leads to superior execution plans – depending on the thresholds defined when the difference between actual and estimated cardinalities is significant.

    I think there was some time ago a thread on CDOS http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/6aac0570974c9d62/ where this might have been the case – depending on how the SQL was using multi-column joins the “cardinality feedback” correction kicked in or not.

    Randolf

    Comment by Randolf Geist — December 21, 2009 @ 10:22 am GMT Dec 21,2009 | Reply

    • Randolf,
      That seems perfectly plausible. Anything to do with multi-column selectivity (without a helpful index) could turn into a threat.

      In fact, thinking about the possibilities, you have to start wondering more about why the feature WON’T switch on automatically.

      Comment by Jonathan Lewis — December 22, 2009 @ 9:35 am GMT Dec 22,2009 | Reply

  8. It’s obvious, with hind-sight, where the information is coming from. The VLDB paper that Greg linked to supplies the clue – and Iggy even quoted the relevant phrase in his extract:
    monitoring is enabled for the cursor.

    This is 11g – with SQL monitoring. You don’t look in v$sql_plan_statistics(_all), you look in v$sql_monitor and v$sql_plan_monitor.

    Comment by Jonathan Lewis — December 22, 2009 @ 9:38 am GMT Dec 22,2009 | Reply

  9. […] 19-How does auto cardinality feedback works in 11GR2? –read the comments Jonathan Lewis-Adaptive Optimisation ? […]

    Pingback by Blogroll Report 11/12/2009-18/12/2009 « Coskan’s Approach to Oracle — January 2, 2010 @ 5:50 pm GMT Jan 2,2010 | Reply

  10. […] cardinality feedback (apparently not documented) is a new feature in Oracle Database 11.2.0.1 (see here for a related blog article).  The first execution required 2 minutes and 33 seconds, but a […]

    Pingback by Impact of the TRUNC Function on an Indexed Date Column « Charles Hooper's Oracle Notes — March 8, 2010 @ 6:05 am GMT Mar 8,2010 | Reply

  11. An article was written recently on this topic on the Optimizer dev team’s blog:

    http://blogs.oracle.com/optimizer/entry/cardinality_feedback

    Comment by Ahmed AANGOUR — May 27, 2011 @ 11:09 am GMT May 27,2011 | Reply

  12. […] hints are the type of hints that are injected by SQL Profiles or (in 11g) cardinality feedback. They simply correct the optimizer estimates as the optimizer is doing its thing.  In this case I […]

    Pingback by Wrong Index 2 | Oracle Scratchpad — July 12, 2013 @ 5:18 pm GMT Jul 12,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,429 other followers