In this example we will fix the plan for a query. Query is using indexed column in where clause hence doing table access by index. But we will force the query to do a full table scan instead of index access suing baseline.

table used in example has index based on empno column

SQL> select owner,index_name,table_name,index_type from dba_indexes where table_name='EMP';

OWNER                          INDEX_NAME                     TABLE_NAME                     INDEX_TYPE
------------------------------ ------------------------------ ------------------------------ ---------------------------
SCOTT                          PK_EMP                         EMP                            NORMAL


SQL> select INDEX_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where table_name='EMP';

INDEX_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ---------------
PK_EMP                         EMPNO                                        1

As you can see explain plan query is using index under normal behaviour

set pages 100
set lines 300
select /* INDEX_ACCESS */ ENAME,JOB,MGR from scott.emp where EMPNO=7782;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

SQL> SQL>
ENAME      JOB              MGR
---------- --------- ----------
CLARK      MANAGER         7839

SQL>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  83jaj3zcupjud, child number 0
-------------------------------------
select /* INDEX_ACCESS */ ENAME,JOB,MGR from scott.emp where EMPNO=7782

Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    22 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|          |
--------------------------------------------------------------------------------------

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

   2 - access("EMPNO"=7782)


19 rows selected.

Now lets force full table scan using hint

set pages 100
set lines 300
select /* FULL_ACCESS */ /*+ full(emp) */ ENAME,JOB,MGR from scott.emp where EMPNO=7782;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

SQL> SQL>
ENAME      JOB              MGR
---------- --------- ----------
CLARK      MANAGER         7839

SQL>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gdykz96dajq0u, child number 0
-------------------------------------
select /* FULL_ACCESS */ /*+ full(emp) */ ENAME,JOB,MGR from scott.emp
where EMPNO=7782

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    22 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"=7782)

lets identify both queries

set lines 180 pages 500
col sql_id format a14
col sql_plan_baseline format a30
col plan_hash_value format 999999999999999
col exact_matching_signature format 99999999999999999999
col sql_text format a50
select sql_id,
plan_hash_value,
sql_plan_baseline,
executions,
elapsed_time,
exact_matching_signature,
substr(sql_text,0,50) sql_text
from v$sql
where sql_text like '%_ACCESS%'
AND  sql_text LIKE '%7782%'
AND  sql_text NOT LIKE '%sql_id%';

SQL_ID          PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT
-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------
83jaj3zcupjud        2949544139                                         1       281076     18018059681855136467 select /* INDEX_ACCESS */ ENAME,JOB,MGR from scott
gdykz96dajq0u        3956160932                                         1        22011     17411293023211587822 select /* FULL_ACCESS */ /*+ full(emp) */ ENAME,JO

so we want force plan used by gdykz96dajq0u i.e. full access on 83jaj3zcupjud

First load the plan 83jaj3zcupjud into SPM baseline

set serveroutput on
declare
  plans_loaded pls_integer ;
begin
  plans_loaded := dbms_spm.load_plans_from_cursor_cache( sql_id => '83jaj3zcupjud' ) ;
  dbms_output.put_line( 'plans loaded: '||plans_loaded ) ;
end ;
/
plans loaded: 1

PL/SQL procedure successfully completed.

you can see that the plan is loaded

set lines 180 pages 500
col sql_id format a14
col sql_plan_baseline format a30
col plan_hash_value format 999999999999999
col exact_matching_signature format 99999999999999999999
SQL> SQL> SQL> SQL> SQL> col sql_text format a50
select sql_id,
plan_hash_value,
sql_plan_baseline,
executions,
elapsed_time,
exact_matching_signature,
substr(sql_text,0,50) sql_text
from v$sql
where sql_text like '%_ACCESS%'
AND  sql_text LIKE '%7782%'
AND  sql_text NOT LIKE '%sql_id%';

SQL_ID          PLAN_HASH_VALUE SQL_PLAN_BASELINE              EXECUTIONS ELAPSED_TIME EXACT_MATCHING_SIGNATURE SQL_TEXT
-------------- ---------------- ------------------------------ ---------- ------------ ------------------------ --------------------------------------------------
83jaj3zcupjud        2949544139                                         1       281076     18018059681855136467 select /* INDEX_ACCESS */ ENAME,JOB,MGR from scott
83jaj3zcupjud        2949544139 SQL_PLAN_gn381u4gc6sqm695cc014          3         3185     18018059681855136467 select /* INDEX_ACCESS */ ENAME,JOB,MGR from scott
gdykz96dajq0u        3956160932                                         3        22476     17411293023211587822 select /* FULL_ACCESS */ /*+ full(emp) */ ENAME,JO

nowlets find the SPM content for signature = ‘18018059681855136467’

set lines 200
set pages 500
col signature format 99999999999999999999
col sql_handle format a30
col plan_name format a30
col enabled format a5
col accepted format a5
col fixed format a5
select
signature,
sql_handle,
plan_name,
enabled,
accepted,
fixed
from dba_sql_plan_baselines
where signature = '18018059681855136467';  

            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED
--------------------- ------------------------------ ------------------------------ ----- ----- -----
 18018059681855136467 SQL_fa0d01d11ec362d3           SQL_PLAN_gn381u4gc6sqm695cc014 YES   YES   NO

now we will inject gdykz96dajq0u and its plan hash value gdykz96dajq0u in this sql handle SQL_fa0d01d11ec362d3

set serveroutput on
declare
  plans_loaded pls_integer ;
begin
  plans_loaded := dbms_spm.load_plans_from_cursor_cache(
    sql_id => 'gdykz96dajq0u',
    plan_hash_value => 3956160932,
    sql_handle => 'SQL_fa0d01d11ec362d3'
  ) ;
  dbms_output.put_line( 'plans loaded: '||plans_loaded ) ;
end ;
/

plans loaded: 1

PL/SQL procedure successfully completed.

Now, let’s seen what’s in the baseline of our query

note the fixed columns is set ot NO currently

set lines 200
set pages 500
col signature format 99999999999999999999
col sql_handle format a30
col plan_name format a30
col enabled format a5
col accepted format a5
col fixed format a5
select
signature,
sql_handle,
plan_name,
enabled,
accepted,
fixed
from dba_sql_plan_baselines
where signature = '18018059681855136467';

            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED
--------------------- ------------------------------ ------------------------------ ----- ----- -----
 18018059681855136467 SQL_fa0d01d11ec362d3           SQL_PLAN_gn381u4gc6sqm695cc014 YES   YES   NO
 18018059681855136467 SQL_fa0d01d11ec362d3           SQL_PLAN_gn381u4gc6sqmd8a279cc YES   YES   NO =======>

A new plan called “SQL_PLAN_gn381u4gc6sqmd8a279cc” has been generated. we will now fix this

set serveroutput on
declare
  plans_loaded pls_integer ;
begin
  plans_loaded := dbms_spm.alter_sql_plan_baseline(
    sql_handle => 'SQL_fa0d01d11ec362d3',
plan_name => 'SQL_PLAN_gn381u4gc6sqmd8a279cc',
    attribute_name => 'fixed',
    attribute_value => 'YES'
  ) ;
  dbms_output.put_line( 'plans modified: '||plans_loaded ) ;
end ;
/

plans modified: 1

PL/SQL procedure successfully completed.

So our plan is fixed. note the fixed columns is set to YES now

set lines 200
set pages 500
col signature format 99999999999999999999
col sql_handle format a30
col plan_name format a30
col enabled format a5
col accepted format a5
col fixed format a5
select
signature,
sql_handle,
plan_name,
enabled,
accepted,
fixed
from dba_sql_plan_baselines
where signature = '18018059681855136467';

            SIGNATURE SQL_HANDLE                     PLAN_NAME                      ENABL ACCEP FIXED
--------------------- ------------------------------ ------------------------------ ----- ----- -----
 18018059681855136467 SQL_fa0d01d11ec362d3           SQL_PLAN_gn381u4gc6sqm695cc014 YES   YES   NO
 18018059681855136467 SQL_fa0d01d11ec362d3           SQL_PLAN_gn381u4gc6sqmd8a279cc YES   YES   YES ======>

lets check the explain plan for the query again. in the note section you can see sql plan baseline is used. and its doing full table scan earlier it was index scan.

SQL> set pages 100
set lines 300
select /* INDEX_ACCESS */ ENAME,JOB,MGR from scott.emp where EMPNO=7782;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());
SQL> SQL>
ENAME      JOB              MGR
---------- --------- ----------
CLARK      MANAGER         7839

SQL>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  83jaj3zcupjud, child number 2
-------------------------------------
select /* INDEX_ACCESS */ ENAME,JOB,MGR from scott.emp where EMPNO=7782

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    22 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"=7782)

Note
-----
   - SQL plan baseline SQL_PLAN_gn381u4gc6sqmd8a279cc used for this statement ==================================>>>>>


22 rows selected.

other query has no chnage

SQL> set pages 100
set lines 300
select /* FULL_ACCESS */ /*+ full(emp) */ ENAME,JOB,MGR from scott.emp where EMPNO=7782;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());SQL> SQL>
ENAME      JOB              MGR
---------- --------- ----------
CLARK      MANAGER         7839

SQL>

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  gdykz96dajq0u, child number 0
-------------------------------------
select /* FULL_ACCESS */ /*+ full(emp) */ ENAME,JOB,MGR from scott.emp
where EMPNO=7782

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    22 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("EMPNO"=7782)


19 rows selected.