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.