Insights into Statistics, Query Optimization and the Oracle Optimizer

Oracle Database 19c and SQL Plan Management Diagnostics

Nigel Bayliss
Product Manager

A popular enhancement request I see is to provide an easier way to diagnose issues with SQL plan baselines; in particular the situation where a SQL plan baseline is ACCEPTED but is not being used for whatever reason. This is rare, but can happen if changes are made to the database such as dropping indexes or changing partitioning schemes. If a SQL plan baseline can't be used, you will see something like this in Oracle Database 19c:

So why did it fail?

In this example I captured a plan that uses an index and then I made the index invisible. There's no way the index plan can be used anymore. However, let's pretend that we don't know what happened.

There is now a really nice way to help diagnose issues with SQL plan baselines. It relies on Oracle Database 19c (hint usage reporting) and a hidden parameter to force the SQL statement to use the outline in the SQL plan baseline even if the resulting plan doesn't match the SQL plan baseline (a pretend match).

This is how you do it:

alter session set "_sql_plan_management_control"=4;
explain plan for select /* MYTESTSQL */ sum(num) from mytest1 where id = 10;
select * from table(DBMS_XPLAN.DISPLAY(FORMAT=>'typical'));
alter session set "_sql_plan_management_control"=0;
And then you will see something like this:

How beautiful is that? The hint report tells us that INDEX_RS_ASC is not used - a really strong clue.

There's a worked example on GitHub if you want to try it yourself. There's also a spool file if you want to look at the example but don't have access to a version 19c database at the moment. I'm afraid that LiveSQL doesn't allow you to use SQL plan management yet.

Hey, what? Forcing the plan baseline plan?

Some of you might jump on the idea that a parameter can be used to "force a SQL plan baseline to be used" (another popular request). This is not the case! As I said, it forces the outline in the SQL plan baseline to be used even if the plan is not the one we want. The parameter is not a magic bullet that will somehow force the optimizer to use the plan in the SQL plan baseline. You can see from this example that it is just not possible: the index is not available for use.

In other words - if the outline in the SQL plan baseline can be used successfully, then it will be used. Telling the optimizer to pretend-match the outline in the SQL plan baseline won't somehow fix the issue. This is demonstrated above - the outline is applied to the SQL statement but the hints it uses cannot be honored. The FULL plan is still used. 


Join the discussion

Comments ( 4 )
  • Houri Mohamed Thursday, February 28, 2019
    I am wondering if you have implemented a way to load a SPM baseline form AWR USING SQL_ID and not a portion of text to identify the SQL_ID

  • Nigel Bayliss Thursday, February 28, 2019
    Hi Houri,

    Here's an example if that's what you mean...

    n := dbms_spm.load_plans_from_awr(373,374,'sql_id=''brpsx1vq1hjg5''');

  • Joe Dunn Sunday, November 1, 2020
    How do SQL diagnostic patches affect SQL Plan baselines?
  • Nigel Bayliss Monday, November 2, 2020
    Hi Joe - on hard-parse, any SQL patch hints will be applied to the SQL statement. This can prevent a SQL plan baseline from being used, depending on what hints are used. For example - if a SQL statement has a single SQL plan baseline that specifies that a certain index should be used, but a SQL patch explicitly hints against this, then the SQL plan baseline cannot be used and the SQL statement will choose a plan that does not include the index.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.