Oracle Database 19c and SQL Plan Management Diagnostics

A popular enhancement request we 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. 

How can I check a SQL statement that's in the buffer cache?

In the real world it can be time consuming and difficult to test execute or test parse application SQL statements in SQL Plus in the way I described above. There is a simple solution to this if you are licensed to use SQL Performance Analyzer (SPA). 

SPA allows you to test-execute or test-parse SQL statements in the buffer cache very easily. Here's the idea...

alter session set "_sql_plan_management_control"=4;

Test parse a statement in the cursor cache (identified by SQL ID) using dbms_sqlpa.execute_analysis_task

alter session set "_sql_plan_management_control"=0;

Generate a SPA report to see the plan, using dbms_sqlpa.report_analysis_task

Take a look at the SPA example in GitHub. There is also an example that uses EXPLAIN PLAN, but this will not work for very long SQL statements.

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.