What You See Is What You Get Element

OTN Virtual Technology Summit Hands On Lab


How To Create and Evolve a SQL Plan Baseline


This lab is presented in the OTN Virtual Technology Summit session entitled "Change the Way You Think about SQL Tuning with SQL Plan Management" presented by Bjoern Rost, Oracle ACE Director. It provides an example of how to create a SQL Plan Baseline for a query with auto capture, and it demonstrates how even after adding an index, only the accepted baseline (with the full scan) is actually being used until we review and evolve the new baseline.




Required Elements: Oracle Developer Virtual Machine


Access the Oracle Developer Virtual Box download and instructions here.

This one virtual box image contains all the software and guides you need for the Hands-on Exercise. The image is around 4.7GB compressed (nee image being posted soon will update size after it is posted), so you should download VirtualBox and import the image before the event starts.



Lab Instructions


We will use a simple example table with a very simple query. We will run a query against an unindexed column first which will return in a full table scan. Then, we will add an index on the column and see that the full scan will still be executed but a new baseline is added with a status of unaccepted. We will generate an evolve report and eventually evolve the new baseline and remove the old one.



In the developer VM, run this as pmuser/oracle and gather statistics. You can use sqlplus on the commandline or the SQLDeveloper GUI tool.



[oracle@localhost ~]$ sqlplus pmuser/oracle

SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 12 09:48:13 2014

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

PDB1@ORCL> create table t as select * from dba_objects;

Table created.

PDB1@ORCL> exec DBMS_STATS.GATHER_SCHEMA_STATS ('PMUSER');

PL/SQL procedure successfully completed.

Step 1: Verify that OPTIMIZER_USE_SQL_BLAN_BASELINES is set to true (the default)



PDB1@ORCL> show parameter baselines

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE


step 2: enable auto capture for this session, run a statement twice and disable auto capture again.




PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE; Session altered. PDB1@ORCL> variable var42 varchar2(42); PDB1@ORCL> exec :var42 := 'PMUSER'; PL/SQL procedure successfully completed. PDB1@ORCL> select count(*) from t where owner= :var42; COUNT(*) ---------- 5 PDB1@ORCL> select count(*) from t where owner= :var42; COUNT(*) ---------- 5 PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE; Session altered.


Now we should have a baseline for this sql:


PDB1@ORCL> set linesize 300
PDB1@ORCL> column sql_handle format a20
PDB1@ORCL> column plan_name format a42
PDB1@ORCL> column sql_text format a42
PDB1@ORCL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                       SQL_TEXT                                   ENA ACC FIX
-------------------- ------------------------------  ------------------------------------------ --- --- ---
SQL_abdfaaa7e926cf0a SQL_PLAN_arrxanznkdmsa3fdbb376  select count(*) from t where owner= :var42 YES YES NO 


Notice how there is one baseline for this statement and it is automatically set to ACCEPTED. Now we create an index, re-run the query with auto capture enabled to collect a new baseline with the index scan.



PDB1@ORCL> create index t_idx on t (owner);

Index created.

PDB1@ORCL> exec dbms_stats.gather_schema_stats ('PMUSER');

PL/SQL procedure successfully completed.

PDB1@ORCL> alter system flush shared_pool;

System altered.

PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE;

Session altered.

PDB1@ORCL> select count(*) from t where owner= :var42;

COUNT(*)
----------
      5

PDB1@ORCL> select count(*) from t where owner= :var42;

COUNT(*)
----------
      5

PDB1@ORCL> ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = FALSE;

Session altered.

Check the plan that is used to execute the query and notice how the explain plan mentions the use of a baseline:

PDB1@ORCL> set pagesize 1000
PDB1@ORCL> select count(*) from t where owner = :var42;

COUNT(*)
----------
      5

PDB1@ORCL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 364z0straymuv, child number 0
-------------------------------------
select count(*) from t where owner = :var42

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time         |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT  |      |      |       | 426 (100)  |              |
| 1 | SORT AGGREGATE    |      | 1    | 6     |            |              |
|* 2 | TABLE ACCESS FULL| T    | 5    | 30    | 426 (1)    | 00:00:01     |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"=:VAR42)

Note
-----
- SQL plan baseline SQL_PLAN_arrxanznkdmsa3fdbb376 used for this statement


23 rows selected.


Check the baselines table for the newly created but unaccepted baseline. Notice how the header mentions if the plan is accepted or not.



PDB1@ORCL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                       SQL_TEXT                                   ENA ACC FIX
-------------------- ------------------------------  ------------------------------------------ --- --- ---
SQL_abdfaaa7e926cf0a SQL_PLAN_arrxanznkdmsa3fdbb376  select count(*) from t where owner= :var42 YES YES NO
SQL_abdfaaa7e926cf0a SQL_PLAN_arrxanznkdmsaded8ae2f  select count(*) from t where owner= :var42 YES NO  NO


Have a look at both execution plans for this sql handle:



PDB1@ORCL> select * from table(dbms_xplan.display_sql_plan_baseline('SQL_abdfaaa7e926cf0a'));

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

--------------------------------------------------------------------------------
SQL handle: SQL_abdfaaa7e926cf0a
SQL text: select count(*) from t where owner= :var42
--------------------------------------------------------------------------------

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

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation         | Name     | Rows | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT   |          | 1    | 6     | 426 (1)    | 00:00:01 |
| 1 | SORT AGGREGATE     |          | 1    | 6     |            |          |
|* 2 | TABLE ACCESS FULL | T        | 3143 | 18858 | 426 (1)    | 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("OWNER"=:VAR42)

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

Plan hash value: 293504097

---------------------------------------------------------------------------
| Id | Operation       | Name     | Rows | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |          | 1    | 6     | 1 (0)      | 00:00:01  |
| 1 | SORT AGGREGATE   |          | 1    | 6     |            |           |
|* 2| INDEX RANGE SCAN | T_IDX    | 5    | 30    | 1 (0)      | 00:00:01  |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"=:VAR42)

47 rows selected.

Create the evolve report but do not actually change the ACCEPTED flag yet by setting commit=>no like this:


  set serveroutput on
declare evolve_out CLOB;
begin
evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_abdfaaa7e926cf0a', COMMIT => 'NO' );
dbms_output.put_line(evolve_out);
end;
/


This is what the report should look like:


GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------

Task Information:
---------------------------------------------
Task Name : TASK_21
Task Owner : PMUSER

Execution Name : EXEC_131
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 07/03/2014 10:03:15
Finished : 07/03/2014 10:03:16
Last Updated :
07/03/2014 10:03:16
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------------------------

SUMMARY
SECTION
---------------------------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 1
Number of recommendations : 1
Number of errors : 0

---------------------------------------------------------------------------------------------

DETAILS SECTION
---------------------------------------------------------------------------------------------
Object ID : 2
Test Plan
Name : SQL_PLAN_arrxanznkdmsaded8ae2f
Base Plan Name : SQL_PLAN_arrxanznkdmsa3fdbb376
SQL Handle : SQL_abdfaaa7e926cf0a
Parsing Schema : PMUSER

Test Plan Creator : PMUSER
SQL Text : select count(*) from t where owner= :var42

Bind Variables:
-----------------------------
1 - (VARCHAR2(128)): PMUSER


Execution Statistics:
-----------------------------
                     Base Plan                    Test Plan
                     ---------------------------- ----------------------------
Elapsed Time (s):    .00099                       .000002
CPU Time (s):        .000489                       0
Buffer Gets:         153                           0
Optimizer Cost:      426                           1
Disk Reads:          0                             0

Direct Writes:       0                             0
Rows Processed:      0                             0
Executions:          10                            10


FINDINGS
SECTION
---------------------------------------------------------------------------------------------

Findings (1):
-----------------------------
1. The plan was verified in 0.29000 seconds. It passed the benefit criterion
because its verified performance was 767.74440 times better than that of
the baseline plan.

Recommendation:
-----------------------------
Consider accepting the plan.


EXPLAIN PLANS
SECTION
---------------------------------------------------------------------------------------------

Baseline Plan
-----------------------------
Plan Id :         1
Plan Hash Value : 1071362934

----------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost | Time         |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT   |      | 1    | 6     | 426  | 00:00:01     |
| 1 | SORT AGGREGATE     |      | 1    | 6     |      |              |
| * 2| TABLE ACCESS FULL | T    | 5    | 30    | 426  | 00:00:01     |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("OWNER"=:VAR42)


Test Plan
-----------------------------
Plan Id : 2
Plan
Hash Value : 3738742319

----------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes  | Cost | Time       |
----------------------------------------------------------------------
| 0  | SELECT STATEMENT   |       | 1   |  6     | 1    | 00:00:01   |
| 1 | SORT AGGREGATE      |       | 1   |  6     |      |            |
| * 2 | INDEX RANGE SCAN  | T_IDX | 5   | 30     | 1    | 00:00:01   |
----------------------------------------------------------------------

Predicate Information (identified by operation
id):
------------------------------------------
* 2 - access("OWNER"=:VAR42)

---------------------------------------------------------------------------------------------

PL/SQL procedure successfully completed.


After the review, run the evolve again but set commit to yes this time to actually evolve the new baseline.


set serveroutput on
declare evolve_out CLOB;
begin
evolve_out := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE ( SQL_HANDLE => 'SQL_abdfaaa7e926cf0a', COMMIT => 'YES' );
dbms_output.put_line(evolve_out);
end;
/

Check the baseline table again and notice how both plans are now accepted.

PDB1@ORCL> select sql_handle, plan_name, sql_text, enabled, accepted, fixed from dba_sql_plan_baselines;

SQL_HANDLE            PLAN_NAME                       SQL_TEXT                                    ENA ACC FIX
--------------------  ------------------------------  ------------------------------------------  --- --- ---
SQL_abdfaaa7e926cf0a  SQL_PLAN_arrxanznkdmsa3fdbb376  select count(*) from t where owner= :var42  YES YES NO
SQL_abdfaaa7e926cf0a  SQL_PLAN_arrxanznkdmsaded8ae2f  select count(*) from t where owner= :var42  YES YES NO


Let's verify that this new plan is used for queries from now on:

PDB1@ORCL> set autotrace on
PDB1@ORCL> select count(*) from t where owner = :var42;

COUNT(*)
----------
      5


Execution Plan
----------------------------------------------------------
Plan hash value: 293504097

---------------------------------------------------------------------------
| Id | Operation          | Name  | Rows | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
| 0  | SELECT STATEMENT   |       | 1    | 6     | 8 (0)      | 00:00:01  |
| 1  | SORT AGGREGATE     |       | 1    | 6     |            |           |
|* 2 | INDEX RANGE SCAN   |T_IDX  | 3143 | 18858 | 8 (0)      | 00:00:01  |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OWNER"=:VAR42)

Note
-----
- SQL plan baseline "SQL_PLAN_arrxanznkdmsaded8ae2f" used for this statement


The last step would be to think about what to do with the original baseline. Since both plans are accepted now, both are eligible for execution which could be helpful in situations where a adaptive cursor sharing kicks in and the full scan would actually be the better plan. In this case though I decided to delete the old baseline:


 declare 
drop_result pls_integer;
begin
drop_result := DBMS_SPM.DROP_SQL_PLAN_BASELINE(
sql_handle => 'SQL_abdfaaa7e926cf0a',
plan_name => 'SQL_PLAN_arrxanznkdmsa3fdbb376');
dbms_output.put_line(drop_result);
end;
/


This lab showed how to create a SQL Plan Baseline for a query with auto capture. It demonstrated how even after adding an index, only the accepted baseline (with the full scan) was actually being used until we reviewed and evolved the new baseline.


About the Author

 

Bjoern Rost is the co-founder of portrix Systems, a service provider and consulting company focused on Oracle Technology including servers, storage, Oracle Linux and Solaris to Real Application Cluster databases. He enjoys working with software developers to tightly integrate with existing Oracle features, oversees the operations for a number of cloud or SaaS platforms and consults on high availability, performance tuning and OS and DB management. His passionate about sharing knowledge and is a regular speaker at user group conferences around the world with more than 42 presentations on five continents. He also serves as the European Chair of IOUG's RAC special interest group.