SQL Profiles vs. SQL Plan Baselines
Recently one of my customers asked, what would happen if we had SQL Profile set up for a particular query to make sure it uses the correct execution plan, as well as an existing SQL Plan Baseline for the same query? What would happen if these two features would like the SQL statement to use different execution plans? Which one would win? I hesitated to answer immediately, although the answer seams obvious now. Look how I came to the answer!
I decided to build a quick test case in my 12.1.0.2 lab. The plan is to observe how the preferred execution plan changes after each of these steps:
- Create query that uses full table access to query some table, create SQL Plan Baseline for it
- Create an index on the table so that it allowed more efficient access to the data
- Observe the new baseline for indexed access being created, evolve it to get it accepted
- Create SQL Profile to make the full table access favored
- Remove the SQL Plan Baseline that allows the full table access
- Remove the SQL Plan Baseline for the indexed access
The Test
This is the table I’m building the test case on:
SQL> desc all_ash
Name Null? Type
----------------------------------------- -------- ----------------------------
DAT DATE
MODULE VARCHAR2(64)
PROGRAM VARCHAR2(64)
ACTION VARCHAR2(64)
EVENT VARCHAR2(64)
MACHINE VARCHAR2(64)
CNT NUMBER
And here’s a very simple query that I’ll be using!
SQL> select count(*) from all_ash where module='TEST';
COUNT(*)
----------
0
SQL> explain plan for select count(*) from all_ash where module='TEST'
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3460552988
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 189 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| ALL_ASH | 1 | 17 | 189 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter('MODULE'='TEST')
Creating the SQL Plan Baseline for the query using the Full Table Scan. I’ll load the Plan in to the SQL Management Base from the cursor cache and then will verify if the baseline is there:
SQL> select sql_id, sql_text, plan_hash_value from v$sql where plan_hash_value=3460552988 and sql_text like 'select%';
SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
PLAN_HASH_VALUE
---------------
9kt35fkxmk1qw
select count(*) from all_ash where module='TEST'
3460552988
SQL> var n number
SQL> exec :n:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE('9kt35fkxmk1qw',3460552988);
PL/SQL procedure successfully completed.
SQL> print n
N
----------
1
SQL> explain plan for select count(*) from all_ash where module='TEST';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3460552988
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 189 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| ALL_ASH | 1 | 17 | 189 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - filter('MODULE'='TEST')
Note
-----
- SQL plan baseline 'SQL_PLAN_6bnp3kg5khwxa275ca563' used for this statement
The SQL Plan Baseline SQL_PLAN_6bnp3kg5khwxa275ca563 is now in place for the plan using TABLE ACCESS FULL. Let’s create the index, after which we’ll verify it’s not used immediately (because we have the SQL Plan Baseline), but then we’ll evolve the Baseline to start using the index
SQL> create index ix_all_ash_mod on all_ash(module);
Index created.
SQL> explain plan for select count(*) from all_ash where module='TEST';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3460552988
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 189 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| ALL_ASH | 1 | 17 | 189 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - filter('MODULE'='TEST')
Note
-----
- SQL plan baseline 'SQL_PLAN_6bnp3kg5khwxa275ca563' used for this statement
18 rows selected.
SQL> select count(*) from all_ash where module='TEST';
COUNT(*)
----------
0
SQL> select sql_handle from dba_sql_plan_baselines where plan_name='SQL_PLAN_6bnp3kg5khwxa275ca563';
SQL_HANDLE
--------------------------------------------------------------------------------------------------------------------------------
SQL_65d2a393cb2873aa
1 rows selected.
SQL> col r for a200
SQL> set lines 200
SQL> set pages 50000
SQL> set long 999999
SQL> var r clob
SQL> exec :r:=DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_65d2a393cb2873aa');
PL/SQL procedure successfully completed.
SQL> print r
R
---------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
---------------------------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name : TASK_83
Task Owner : C##MARIS
Execution Name : EXEC_115
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 03/23/2016 07:50:34
Finished : 03/23/2016 07:50:35
Last Updated : 03/23/2016 07:50:35
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------------------------
SUMMARY SECTION
---------------------------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 2
Number of recommendations : 1
Number of errors : 0
---------------------------------------------------------------------------------------------
DETAILS SECTION
---------------------------------------------------------------------------------------------
Object ID : 2
Test Plan Name : SQL_PLAN_6bnp3kg5khwxa81486eb4
Base Plan Name : SQL_PLAN_6bnp3kg5khwxa275ca563
SQL Handle : SQL_65d2a393cb2873aa
Parsing Schema : C##MARIS
Test Plan Creator : C##MARIS
SQL Text : select count(*) from all_ash where module='TEST'
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ----------------------------
Elapsed Time (s): .000302 .000003
CPU Time (s): .000289 0
Buffer Gets: 67 0
Optimizer Cost: 189 1
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 0 0
Executions: 10 10
FINDINGS SECTION
---------------------------------------------------------------------------------------------
Findings (2):
-----------------------------
1. The plan was verified in 0.06000 seconds. It passed the benefit criterion
because its verified performance was 338.14440 times better than that of
the baseline plan.
2. The plan was automatically accepted.
Recommendation:
-----------------------------
Consider accepting the plan.
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 203
Plan Hash Value : 660383075
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
prompt 'Enter value for force_matching (FALSE): ' -
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 189 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
| * 2 | TABLE ACCESS FULL | ALL_ASH | 1 | 17 | 189 | 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter('MODULE'='TEST')
Test Plan
-----------------------------
Plan Id : 204
Plan Hash Value : 2169007796
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 1 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
| * 2 | INDEX RANGE SCAN | IX_ALL_ASH_MOD | 1 | 17 | 1 | 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access('MODULE'='TEST')
---------------------------------------------------------------------------------------------
SQL> explain plan for select count(*) from all_ash where module='TEST';
Explained.
SQL> select * from table(dbms_xplan.display)
2 ;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2623139211
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | INDEX RANGE SCAN| IX_ALL_ASH_MOD | 1 | 17 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access('MODULE'='TEST')
Note
-----
- SQL plan baseline 'SQL_PLAN_6bnp3kg5khwxa81486eb4' used for this statement
So the new plan was accepted and baseline SQL_PLAN_6bnp3kg5khwxa81486eb4 became the preferred one.
In the next step I’ll use create_sql_profile.sql script that I found on this post on Kerry Osborne’s Oracle Blog. The script extracts the outline hints from the execution plan and packages them into a SQL Profile.
SQL> select child_number, plan_hash_value from v$sql where sql_id='9kt35fkxmk1qw';
CHILD_NUMBER PLAN_HASH_VALUE
------------ ---------------
1 3460552988
SQL> @create_sql_profile.sql
Enter value for sql_id: 9kt35fkxmk1qw
Enter value for child_no (0): 1
Enter value for profile_name (PROF_sqlid_planhash):
Enter value for category (DEFAULT):
Enter value for force_matching (FALSE):
old 19: sql_id = '&&sql_id'
new 19: sql_id = '9kt35fkxmk1qw'
old 20: and child_number = &&child_no
new 20: and child_number = 1
old 27: decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name')
new 27: decode('X0X0X0X0','X0X0X0X0','PROF_9kt35fkxmk1qw'||'_'||plan_hash_value,'X0X0X0X0')
old 33: sql_id = '&&sql_id'
new 33: sql_id = '9kt35fkxmk1qw'
old 34: and child_number = &&child_no;
new 34: and child_number = 1;
old 39: category => '&&category',
new 39: category => 'DEFAULT',
old 41: force_match => &&force_matching
new 41: force_match => false
old 52: dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
new 52: dbms_output.put_line('ERROR: sql_id: '||'9kt35fkxmk1qw'||' Child: '||'1'||' not found in v$sql.');
SQL>
SQL>
SQL> explain plan for select count(*) from all_ash where module='TEST';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3460552988
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 189 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| ALL_ASH | 1 | 17 | 189 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter('MODULE'='TEST')
Note
-----
- SQL profile 'PROF_9kt35fkxmk1qw_3460552988' used for this statement
- SQL plan baseline 'SQL_PLAN_6bnp3kg5khwxa275ca563' used for this statement
19 rows selected.
I think the result above is one of the most interesting ones in this test, because we see both the SQL Profile and the SQL Plan baseline being used by the SQL statement.
It’s also interesting to observe that after the profile was created, the SQL Plan Baselines that suggests using the full table scan was preferred, although before that the indexed access was preferred. Does this mean that SQL Profiles are stronger than SQL Plan Baselines? No, because at this moment both of the SQL Plan Baselines are there and the optimizer is allowed to pick any of them. Next, let’s drop SQL Plan Baseline SQL_PLAN_6bnp3kg5khwxa275ca563, and the only remaining baseline would allow the indexed access.
SQL> exec :n:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_65d2a393cb2873aa',plan_name=>'SQL_PLAN_6bnp3kg5khwxa275ca563');
PL/SQL procedure successfully completed.
SQL> print n
N
----------
1
SQL> explain plan for select count(*) from all_ash where module='TEST';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2623139211
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | INDEX RANGE SCAN| IX_ALL_ASH_MOD | 1 | 17 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access('MODULE'='TEST')
Note
-----
- SQL profile 'PROF_9kt35fkxmk1qw_3460552988' used for this statement
- SQL plan baseline 'SQL_PLAN_6bnp3kg5khwxa81486eb4' used for this statement
19 rows selected.
Again, both SQL Profile and the SQL Plan Baseline is reported in the Notes section of the dbms_xplan output, but this time, the index range scan was used although the SQL Profile packages hints that ask the optimizer to use the full table scan. What changes if the remaining SQL Plan Baseline is removed, but the index and the Profile still remains in place?
SQL> exec :n:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_65d2a393cb2873aa',plan_name=>'SQL_PLAN_6bnp3kg5khwxa81486eb4');
PL/SQL procedure successfully completed.
SQL> print n
N
----------
1
SQL> explain plan for select count(*) from all_ash where module='TEST';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3460552988
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 189 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 17 | | |
|* 2 | TABLE ACCESS FULL| ALL_ASH | 1 | 17 | 189 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter('MODULE'='TEST')
Note
-----
- SQL profile 'PROF_9kt35fkxmk1qw_3460552988' used for this statement
The SQL Profile is tolerated.
Conclusions
I actually needed this test so that the result stuck better in my head :) The interaction of SQL Profiles and SQL Plan Baselines was already very well explained by Maria Colgan in her blog post “What is the difference between SQL Profiles and SQL Plan Baselines”? And I confirmed what was stated in the blog post - the SQL Profiles and SQL Plan Baselines don’t work against each other, but they complement each other. While the Baselines define the set of execution plans that can be used by each query, the SQL Profiles only provide additional information to “push” the optimizer to favor one or another plan, but they don’t guarantee the plan would be used.
That was well visible during the test - while we had both SQL Plan Baselines accepted, the profile was tolerated and the optimizer chose the full table scan plan as “suggested” by the profile. As soon as the FTS plan was removed from Sql Plan Baselines, the “suggestion” from the SQL Profile didn’t have any effect, because there was only one allowed execution plan for the SQL Statement - the one using INDEX RANGE SCAN, and no SQL Profile can override that. However, as soon as the remaining SQL Plan Baseline was removed, the SQL Profile was again tolerated as at that moment there were no restrictions on which plans were allowed to be used, so again the FULL TABLE SCAN was used.