2 Useful Things To Know About SQL Plan Baselines

Last two weeks, we covered the building blocks of SQL Plan Management, and then we got started with SQL Plan Baselines!

What’s next?, you might be asking! There is more SQL Plan Baselines awesomeness, and why?

Because the performance problems never come scheduled! They usually show up unannounced at the worst time! But when they show up, you must be ready!

I’ll show you today the different attributes of plans in the SQL Baseline that you need to be aware of and also how to display the execution plans from the baseline!

1. Attributes of plans in SQL Plan Baselines
2. Display Execution Plan From The SQL Plan Baseline

1. Attributes of plans in SQL Plan Baselines

If you check the data dictionary view DBA_SQL_PLAN_BASELINES, you will notice some attributes, specific to baselines. One of these attributes only applies in 12c:

enabled: YES | NO
accepted: YES | NO
fixed: YES | NO
reproduced: YES | NO
adaptive: YES | NO (this applies for 12c)

SQL> select sql_handle, enabled, accepted, fixed, reproduced, adaptive from dba_sql_plan_baselines order by 1;

SQL_HANDLE		       ENABLED	  ACCEPTED   FIX REPRODUCED ADAPTIVE
------------------------------ ---------- ---------- --- ---------- ----------
SQL_0df11bb17a294a3e	       YES	  YES	     NO  YES	    NO
SQL_1a341328949c4e67	       YES	  YES	     NO  YES	    NO
SQL_2330e37740264284	       YES	  YES	     NO  YES	    NO
SQL_34e80b11cd10d020	       YES	  YES	     NO  YES	    NO
SQL_3e62952f24b7a9e1	       YES	  YES	     NO  YES	    NO
SQL_673640cc2d641024	       YES	  YES	     NO  YES	    NO
SQL_71de3871335abb5e	       YES	  YES	     NO  YES	    NO
SQL_823e74d07256313c	       YES	  YES	     NO  YES	    NO

Let’s get a better understanding of these attributes.

A plan in the the SQL Plan baseline, when enabled, can be used by the optimizer. If disabled, the optimizer will no longer use it.
When you load plans into the SQL Plan baselines, these are enabled by default, unless you explicitly specify otherwise.

When OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES is set to TRUE, aka, capture is on, the first plan that is captured in the SQL Plan Baseline, is market as accepted.
Subsequent plans are added to the SQL Plan History (not the baseline) as unaccepted plans, and are waiting for verification. Remember SQL Plan Baseline only contains accepted plans!

A fixed plan in the SQL Plan Baseline, is a plan that is preferred by the optimizer. When we “fix” a plan, (we mark it as fixed), we tell the optimizer we want this plan to be used, no other plans.
You can have more than one fixed plan in the SQL Plan Baseline.

A plan is reproducible, when the optimizer is able to reproduce it. When a plan is first added to the SQL Plan Baseline, the value of this column is YES. Later, if the optimizer cannot reproduce the plan, (because an index was dropped), the value of this column becomes NO, not reproducible.

A plan is adaptive, if the optimizer started with one plan, and during execution it changed its mind, and ended up running a different execution plan.

These 5 attributes are important to check. Let’s say you change the attribute of on plan to “fixed = YES” and wonder why the plan is not used. One answer could be: maybe the plan is also market as disabled (enabled = NO).

Always check all these attributes to get a better understanding of the plans in the baseline.

2. Display Execution Plan From The SQL Plan Baseline

Did you know you can actually display the execution plan from SQL Plan Baselines? Instead of just looking at a plan name and sql handle, you can actually see the execution plan.
Isn’t this awesome?

I was not aware of this functionality for a long time, and when I discovered it, I felt really good!

In order to display the plan, use DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE

This functionality was available in 11g as well, however it was working differently.

In 11g, the SQL Baseline was not actually storing the execution plan, line by line. The baseline was storing hints, and statistics to come up with the desired plan.
When you wanted to display the execution plan of a specific plan in the SQL Plan Baseline, the optimizer had to compile the SQL Statement, and generate the execution plan. If the plan couldn’t be reproduced, you would end up with a different execution plan.
Caution: do not rely to much on DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE in 11g for accuracy.

Things are different in 12c! SQL Plan Baseline stores the actual execution plan! When you want to display the execution plan from the SQL Plan Baseline, the optimizer doesn’t need to compile the statement and generate the plan. What you need to pass as parameters is the SQL_HANDLE and the PLAN_NAME, both available in DBA_SQL_PLAN_BASELINES view.

select * from table(dbms_xplan.display_sql_plan_baseline('SQL_HANDLE','PLAN_NAME','typical')); 

example:

SQL>select * from table(dbms_xplan.display_sql_plan_baseline('SQL_34e80b11cd10d020','SQL_PLAN_39u0b276j1n10d8a279cc','typical')); 

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
SQL handle: SQL_34e80b11cd10d020
SQL text: select empno, ename, sal from scott.emp where deptno=10
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
Plan name: SQL_PLAN_39u0b276j1n10d8a279cc	  Plan id: 3634526668
Enabled: YES	 Fixed: NO	Accepted: YES	  Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     5 |    85 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     5 |    85 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------

   1 - filter("DEPTNO"=10)

25 rows selected.

Find below a mind map, to use it as a reference! As always, feel free to print it, use it, refer to it!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana