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.