Displaying SQL Baseline Plans

Since I’m on vacation and not “really” working, I thought I might have time to write up a quick blog post. The idea for this one was triggered by one of Maria Colgan’s presentations at Hotsos last week. Maria was talking about SQL Plan Management and Baselines and somehow got me thinking about the DBMS_XPLAN option to display plans for Baselines. This is a pretty neat feature that allows you to the see the plan associated with a Baseline (well sort of).

The 11.2 documentation (Oracle® Database PL/SQL Packages and Types Reference) says this about the DISPLAY_SQL_PLAN_BASELINE function:

This procedure uses plan information stored in the plan baseline to explain and display the plans.It is possible that the plan_id stored in the SQL management base may not match with the plan_id of the generated plan. A mismatch between stored plan_id and generated plan_id means that it is a non-reproducible plan. Such a plan is deemed invalid and is bypassed by the optimizer during SQL compilation.

But what does that mean? Well in short it means that Baselines don’t store plans, they store hints that when fed to the optimizer will hopefully cause it to come up with the desired plan. Baselines also store a plan_hash_value so it’s possible to tell whether the hints worked or not. Baselines do not actually store all the steps of a plan. So if that’s the case, then it’s obviously not possible for the display_sql_plan_baseline function to show the plan if the optimizer can’t reproduce it for some reason. When the doc’s say “it is possible that the plan_id stored in the SQL management base may not match with the plan_id of the generated plan”, that’s what they are talking about. I decided to create a test case to see what happens when the generated plan can’t match the original. Here’s the basic idea:

  1. run a statement that uses an index and check the plan
  2. create a Baseline on the statement using the index (using my create_baseline.sql script)
  3. check the hints stored with the baseline (using my baselines_hints.sql script)
  4. run the statement again and check the real plan to see that the Baseline was used
  5. use the display_sql_plan_baseline function to show the Baseline plan
  6. make the index invisible (thus rendering the Baseline plan non-reproducible)
  7. execute the statement again and check the real plan
  8. use the display_sql_plan_baseline function to show the Baseline plan

So here’s the test:

 
SYS@dbm1> @avgskewi
 
AVG(PK_COL)
-----------
 
 
Elapsed: 00:00:00.02
SYS@dbm1> select * from table(dbms_xplan.display_cursor(null,null,'LAST'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  84q0zxfzn5u6s, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 136133
 
Plan hash value: 3723858078
 
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |    33 (100)|          |
|   1 |  SORT AGGREGATE              |           |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    32 |   352 |    33   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    33 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=136133)
 
 
20 rows selected.
 
Elapsed: 00:00:00.03
SYS@dbm1>
SYS@dbm1> -- Create the Baseline
SYS@dbm1>
SYS@dbm1> @create_baseline
Enter value for sql_id: 7s0b9ygcrj77u
Enter value for plan_hash_value: 3723858078
Enter value for fixed (NO): 
Enter value for enabled (YES): 
Enter value for plan_name (ID_sqlid_planhashvalue): 
 
Baseline SQLID_7S0B9YGCRJ77U_3723858078 created.
 
Elapsed: 00:00:00.08
SYS@dbm1>
SYS@dbm1> -- Check Baseline Hints
SYS@dbm1>
SYS@dbm1> @baseline_hints
Enter value for baseline_plan_name: SQLID_7S0B9YGCRJ77U_3723858078
 
OUTLINE_HINTS
------------------------------------------------------------------------------------------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "SKEW"@"SEL$1" ("SKEW"."COL1"))
 
6 rows selected.
 
Elapsed: 00:00:00.04
SYS@dbm1> @avgskewi
 
AVG(PK_COL)
-----------
 
 
1 row selected.
 
Elapsed: 00:00:00.01
SYS@dbm1> /
 
AVG(PK_COL)
-----------
 
 
1 row selected.
 
Elapsed: 00:00:00.01
SYS@dbm1>
SYS@dbm1> -- Check to make sure statement is using the Baseline
SYS@dbm1>
SYS@dbm1> select * from table(dbms_xplan.display_cursor(null,null,'LAST'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7s0b9ygcrj77u, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 23489
 
Plan hash value: 3723858078
 
-----------------------------------------------------------
| Id  | Operation                    | Name      | E-Rows |
-----------------------------------------------------------
|   0 | SELECT STATEMENT             |           |        |
|   1 |  SORT AGGREGATE              |           |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |     35 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |     37 |
-----------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=23489)
 
Note
-----
   - SQL plan baseline SQLID_7S0B9YGCRJ77U_3723858078 used for this statement
 
27 rows selected.
 
Elapsed: 00:00:00.05
SYS@dbm1>
SYS@dbm1> -- Use display_sql_plan_baseline to see Baseline Plan
SYS@dbm1>
SYS@dbm1> select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle','&plan_name','typical'));
Enter value for sql_handle: SQL_1e2d7159fc8f7496
Enter value for plan_name: SQLID_7S0B9YGCRJ77U_3723858078
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
SQL handle: SQL_1e2d7159fc8f7496
SQL text: select avg(pk_col) from kso.skew where col1 = 23489
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQLID_7S0B9YGCRJ77U_3723858078         Plan id: 1416105523
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
 
Plan hash value: 3723858078
 
------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    11 |    36   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |           |     1 |    11 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| SKEW      |    35 |   385 |    36   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SKEW_COL1 |    37 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("COL1"=23489)
 
26 rows selected.
 
Elapsed: 00:00:00.05
SYS@dbm1>
SYS@dbm1> -- Hide the index and rerun the statement
SYS@dbm1>
SYS@dbm1> alter index kso.skew_col1 invisible;
 
Index altered.
 
Elapsed: 00:00:00.01
SYS@dbm1> @avgskewi
 
AVG(PK_COL)
-----------
 
 
1 row selected.
 
Elapsed: 00:00:01.14
SYS@dbm1> /
 
AVG(PK_COL)
-----------
 
 
1 row selected.
 
Elapsed: 00:00:01.12
SYS@dbm1>
SYS@dbm1> -- Check the actual plan and see that the Baseline is no longer used
SYS@dbm1>
SYS@dbm1> select * from table(dbms_xplan.display_cursor(null,null,'LAST'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7s0b9ygcrj77u, child number 0
-------------------------------------
select avg(pk_col) from kso.skew where col1 = 23489
 
Plan hash value: 568322376
 
----------------------------------------------------
| Id  | Operation                  | Name | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT           |      |        |
|   1 |  SORT AGGREGATE            |      |      1 |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW |     35 |
----------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1"=23489)
       filter("COL1"=23489)
 
 
20 rows selected.
 
Elapsed: 00:00:00.02
SYS@dbm1>
SYS@dbm1> -- Use display_sql_plan_baseline to see Baseline Plan now
SYS@dbm1>
SYS@dbm1> select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle','&plan_name','typical'));
Enter value for sql_handle: SQL_1e2d7159fc8f7496
Enter value for plan_name: SQLID_7S0B9YGCRJ77U_3723858078
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
SQL handle: SQL_1e2d7159fc8f7496
SQL text: select avg(pk_col) from kso.skew where col1 = 23489
--------------------------------------------------------------------------------
 
--------------------------------------------------------------------------------
Plan name: SQLID_7S0B9YGCRJ77U_3723858078         Plan id: 1416105523
Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
 
Plan hash value: 568322376
 
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |    11 | 44513   (2)| 00:00:02 |
|   1 |  SORT AGGREGATE            |      |     1 |    11 |            |          |
|*  2 |   TABLE ACCESS STORAGE FULL| SKEW |    35 |   385 | 44513   (2)| 00:00:02 |
-----------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - storage("COL1"=23489)
       filter("COL1"=23489)
 
26 rows selected.
 
Elapsed: 00:00:00.04

|
So in the last step you can see that the display_sql_plan_baseline function does not actually show the plan that was associated with the baseline. It can’t because the plan is not stored. So the optimizer must attempt to reproduce the plan with the hints and if the plan is not reproducible, it spits out some other plan. Note that the plan_id in the output still matches the desired plan even though the optimizer was unable to reproduce this plan.

Note: After I wrote this up I realized that Coskan Gundogar had already blogged about it here (pretty thoroughly I might add). So please see his post as well. By the way, I do agree with his point that when a plan is not reproducible the display_sql_plan_baseline function should probably just throw an error.

Leave a Reply