Oracle Scratchpad

July 6, 2014

SQL Plan Baselines

Filed under: CBO,Hints,Oracle — Jonathan Lewis @ 6:34 pm BST Jul 6,2014

Here’s a thread from Oracle-L that reminded me of an important reason why you still have to hint SQL sometimes (rather than following the mantra “if you can hint it, baseline it”).

I have a query that takes 77 seconds to optimize (it’s not a production query, fortunately, but one I engineered to make a point). I can enable sql plan baseline capture and create a baseline for it, and given the nature of the query I can be confident that the resulting plan will always be exactly the plan I want. If I have to re-optimize the query at any time  (because it runs once per hour, say, and is constantly being flushed from the library cache) how much time will the SQL plan baseline save for me ?

The answer is NONE.

The first thing that the optimizer does for a query with a stored sql plan baseline is to optimize it as if the baseline did not exist.

If I want to get rid of that 77 seconds I’ll have to extract (most of) the hints from the SQL Plan Baseline and write them into the query.  (Or, maybe, create a Stored Outline – except that they’re deprecated in the latest version of Oracle, and I’d have to check whether the optimizer used the same strategy with stored outlines or whether it applied the outline before doing any optimisation). Maybe we could do with a hint which forces the optimizer to attempt to use an existing, accepted SQL Baseline without attempting the initial optimisation pass.



I claim ownership of the mantra: “if you can hint it, baseline it”, but as a generous soul I’ve allowed both Maria Colgan and Tom Kyte to make use of it without payment of royalties.



  1. A good point – baselines are not a solution to parsing problems.

    So, for parse problems (either slow or errors from bugs), there’s a good case for either a SQL Profile (for a specific plan) or a SQL Patch.(to avoid the bug)

    SQL Profiles should be better, created either manually or better via the COE_XFR_SQL_PROFILE.SQL script of SQLT (1614107.1)
    These should not have the parsing overhead because there’s no feedback mechanism for plans that the optimizer would have generated otherwise.

    Comment by Dom Brooks — July 7, 2014 @ 10:19 am BST Jul 7,2014 | Reply

    • Dom,

      Good idea. I take it you mean using the SQL Profile or SQL Patch mechanism to apply the hints that would have been in the SQL Baseline so that you don’t have to change the code. Of the two I think I’d prefer to go with the SQL Patch, since I still feel uncomfortable putting “the wrong kind of hints” into an SQL profile.

      Comment by Jonathan Lewis — July 7, 2014 @ 1:26 pm BST Jul 7,2014 | Reply

      • > since I still feel uncomfortable putting “the wrong kind of hints” into an SQL profile
        From a poll I did before, and from past and recent experience, a lot of DBAs are more comfortable with this style of SQL Profile (i.e. with plan outline hints) than with baselines.

        I’m completely comfortable with it – it’s a supported methodology, published on Oracle Support, part of SQLT, etc, etc.

        Comment by Dom Brooks — July 7, 2014 @ 2:56 pm BST Jul 7,2014 | Reply

        • On the plus side for profiles, of course, is that you can “force match” when you create them – which means apply the same profile to many statememts that differ only in their literal value usage. On the possible minus side is that you have to call dbms_sqltune to load a profile – and is there an official statement that you can do this without the diagnostic and performance pack licences ?

          The minus side for patches is that you’re limited to 500 bytes at the total length of the text string you can use. (The procedure accepts a varchar2() parameter, but that’s cast into the first element of a variable of type sys.sqlprof_attr, which is an array(2000) of varchar2(500))

          Comment by Jonathan Lewis — July 8, 2014 @ 5:07 am BST Jul 8,2014

        • Another good point. Yes, some licensing differences between baselines and sql profiles but are they that significant?

          While baseline usage via DBMS_SPM is part of enterprise edition, that’s only good if your plan is in memory (various methods).
          But if you want to transfer from AWR into SPM, you still need to go via a SQL Tuning Set and that requires DBMS_SQLTUNE which requires Tuning Pack.
          And SQL Profile creation is also done via DBMS_SQLTUNE.

          For the SQL patch, I was not aware of that limitation but does that then support the idea that a SQL Patch is best suited for injecting a single or small number of hints to avoid specific problems/bugs? e.g. inject a bind_aware hint (as per optimizer blog example) or inject a hint to prevent a particular transformation?

          Comment by Dom Brooks — July 8, 2014 @ 10:19 am BST Jul 8,2014

        • Dom,

          The licensing bit is difficult – even with a note about baselines. Part of the confusion is that a call to create a patch calls the dbms_sqltune package and the licensing documents makes various noises about requiring the licence to query views and execute packages.

          I would like to think that your comment about the 500 byte limit being an indication of expected use – but I suspect it’s more a question of someone creating a quick and dirty layer on top of dbms_sqltune to get a job done without having to grant execute rights on the whole tuning package. Ultimately the patch procedure and the profile procedure are the same procedure with a different input value for a couple of the parameters.

          Comment by Jonathan Lewis — July 9, 2014 @ 1:59 am BST Jul 9,2014

  2. Jonathan,

    Thanks to remember us that SPM can’t help solving a long time parsing (plan optimization) problem
    I am thinking of the possibility to hint a SQL PLAN NAME taken from an existing SPM baseline. The CBO, before starting its plan optimization, knows already the presence (or not) of a SPM plan baseline as far as we find the following information in the beginning of the 10053 trace file:

    SPM: statement found in SMB

    This is why I don’t think it is too complicated for the Oracle Optimizer group to add a hint that allows using a given PLAN name from an existing SPM baseline. Something like this:

    select /*+ SQL_PLAN_8xbb2605t56kcac124365 */ empno, ename from emp where sal =:sal;

    In that case the CBO has only to check the presence of this hinted SQL_PLAN_NAME and reproduce it. Otherwise the hint is ignored and the CBO will optimize its normal plan.

    Of course several questions can be asked if this feature is implemented. I am thinking of what the CBO will do if the hinted SQL_PLAN_NAME is not valid (or not reproducible) but there is another SQL_PLAN_NAME for the same force matching signature which is valid and reproducible. If by hinting the plan we wanted to avoid the CBO optimization then we are not done in this case. And so on for each new implementation in Oracle.

    Best regards

    Comment by hourim — July 14, 2014 @ 9:52 am BST Jul 14,2014 | Reply

  3. Hello Jonathan,

    Could you please help me to understand the following scenario?

    Plan Hash Value of a query in Cursor Cache differs with the Plan Hash Value (and also the plan) of the specific SPM which is being used by the Query.

    SQL ID – 0yc7a706upwna

    1) Showing output from dbms_xplan.display_cursor
    Plan hash value: 3927359371
    SQL plan baseline SQL_PLAN_f6kbqs97r7vdg89b07845 used for this statement

    for SQL_PLAN_f6kbqs97r7vdg89b07845
    Plan hash value: 1387980137 (with a different plan)

    Is it a normal behavior?

    And the Specific plan is Enabled, Accepted and Fixed.

    Now, I am confused on which plan is being used by the SQL

    Note:- I will provide the full details if needed.


    Comment by Neel Kumar — December 16, 2015 @ 7:21 pm GMT Dec 16,2015 | Reply

    • Neel,

      This isn’t a forum, so I don’t get involved with answering specific questions.
      Your best bet is to pose your question to the OTN database forum – you have to create an account to log on but it’s free. Make sure you quote your Oracle version number when you ask the question.

      I think Randolf Geist or Chris Antognini may already have written something about getting the message that a Baseline has been used when the plan it “should” give was different – possibly it was Kerry Osborne. So the specific answer to your question may already be available online.

      Comment by Jonathan Lewis — December 16, 2015 @ 8:17 pm GMT Dec 16,2015 | Reply

    • Neel,

      I have shown an example in this oracle list thread (,8) where Oracle is saying that it has used the SPM plan while in reality it hasn’t because of transitive closure which is present in the SPM plan predicate part is not possible when I’ve set OFE to ‘9.0.1’.

      Bear in mind as well that when it comes to SPM plan reproducibility it is PHV2 of the CBO plan that should be compared to the PlanId of the SPM plan and not the plan_hash_value because this one does not include the predicate part. For the sake of completness here’s a CBO-SPM plan interaction:

      Best regards
      Mohamed Houri

      Comment by hourim — December 17, 2015 @ 7:41 am GMT Dec 17,2015 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by