Force the SQL to use a plan using SQL baseline

This is an example where we see how to force a particular execution plan.It is useful in the cases when there are multiple execution plans for the SQL and its not using optimal execution plan or we want to force a hint to be used to improve the execution plan but can't use it with the packaged application.

So here we go..Following are the steps that can be followed to test this out..



Step 1) Create the test table and load the data to create the test case.

SQL> create table shas ( id number  primary key,name varchar2(128) );

Table created.

SQL>
SQL> insert into shas select OBJECT_ID,OBJECT_NAME from dba_objects;
 72884 rows created.

SQL> commit;

Commit complete.

Step 2) For testing purpose I am going to use the following SQL which is doing an Index  unique scan and I want to force it to do a full table scan.

SQL>  select * from shas where id='75322';

        ID
----------
NAME
--------------------------------------------------------------------------------
     75322
EMP



Execution Plan
----------------------------------------------------------
Plan hash value: 1793678797

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

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
| Time     |

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

|   0 | SELECT STATEMENT            |              |     1 |    79 |     1   (0)
| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| SHAS         |     1 |    79 |     1   (0)
| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | SYS_C0012285 |     1 |       |     1   (0)
| 00:00:01 |

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


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

   2 - access("ID"=75322)


Step 3) Suppose I want to force the full table scan hint(although not a very desired option in this case). I run the following hinted SQL

SQL> select /*+ FULL(shas) */ * from shas where id ='75322';

        ID
----------
NAME
--------------------------------------------------------------------------------
     75322
EMP



Execution Plan
----------------------------------------------------------
Plan hash value: 622179821

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    79 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| SHAS |     1 |    79 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("ID"=75322)

Step 4) Get the SQL id from the v$sql_text.
 
SQL> select sql_text,sql_id from v$sql where sql_text like '%75322%';

SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID
-------------
 select * from shas where id='75322'
5zfadn00x105u

select /*+ FULL(shas) */ * from shas where id ='75322'
d8kdxq5baunx4


Step 6) Load the baseline doing Index unique scan from the cursor cache.

var res number ;
exec :res := dbms_spm.load_plans_from_cursor_cache(sql_id => '5zfadn00x105u', plan_hash_value => '1793678797' );

SQL> select sql_text, sql_handle, plan_name, enabled, accepted from dba_sql_plan_baselines;

SQL_TEXT
--------------------------------------------------------------------------------
SQL_HANDLE                     PLAN_NAME                      ENA ACC
------------------------------ ------------------------------ --- ---
 select * from shas where id='75322'
SYS_SQL_9d09bd8936f5f4b0       SQL_PLAN_9u2dxj4vgbx5h230cac57 YES YES



SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  d8kdxq5baunx4, child number 0
-------------------------------------
select /*+ FULL(shas) */ * from shas where id ='75322'

Plan hash value: 622179821

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    95 (100)|          |
|*  1 |  TABLE ACCESS FULL| SHAS |   282 | 22278 |    95   (0)| 00:00:02 |

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

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

   1 - filter("ID"=75322)


18 rows selected.

Step 7) Force the SQL id to use the PHV 622179821(Full table scan)

var res number
exec :res := dbms_spm.load_plans_from_cursor_cache( -
sql_id => 'd8kdxq5baunx4', -
plan_hash_value => '622179821', -
sql_handle => 'SYS_SQL_9d09bd8936f5f4b0');

Step 8) Drop the original baseline with the "Index unique scan plan"

SQL> exec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('&original_sql_handle','&original_plan_name');
Enter value for original_sql_handle: SYS_SQL_9d09bd8936f5f4b0
Enter value for original_plan_name: SQL_PLAN_9u2dxj4vgbx5h230cac57

PL/SQL procedure successfully completed.
Step 7) Test that the baseline is being used and the SQL is doing a full table scan without a hint

SQL> set autotrace on explain
SQL> select * from shas where id ='75322';

        ID
----------
NAME
--------------------------------------------------------------------------------
     75322
EMP



Execution Plan
----------------------------------------------------------
Plan hash value: 622179821

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   282 | 22278 |    95   (0)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| SHAS |   282 | 22278 |    95   (0)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("ID"=75322)

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