No need to change source code, hint it using a baseline

Just a quick end-to-end walkthrough on how to transfer a plan from one sql statement to another via SQL Plan Baselines.

This has the potential to be very useful when it comes to tuning a production sql statement without actually touching the code and is much more straightforward than hacking outlines or profiles. Plus it’s supported – win/win.

Completely artificial test case – we’re going to force a “select * from t1” to change from a full table scan to a full scan of an index.

Setup:

SQL>create table t1 
  2  (col1  number 
  3  ,constraint pk_t1 primary key (col1)); 

Table created.

The plan we want to force:

SQL>select /*+ index(t1 pk_t1) */ 
  2         * 
  3  from   t1; 

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  an6t9h9g5s3vh, child number 0
-------------------------------------
select /*+ index(t1 pk_t1) */        * from   t1

Plan hash value: 646159151

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |     2 (100)|          |
|   1 |  INDEX FULL SCAN | PK_T1 |     1 |    13 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

17 rows selected.

The statement whose plan we want to change:

SQL>select * from t1; 

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  27uhu2q2xuu7r, child number 0
-------------------------------------
select * from t1

Plan hash value: 3617692013

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

17 rows selected.

Let’s change it using DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE:

SQL>declare 
  2    sqltext clob; 
  3    spm_op pls_integer; 
  4  begin 
  5    sqltext := 'select * from t1'; 
  6    spm_op  := 
  7    dbms_spm.load_plans_from_cursor_cache 
  8    (sql_id => 'an6t9h9g5s3vh', 
  9     plan_hash_value => 646159151, 
 10     sql_text => sqltext); 
 12  end; 
 13  / 

PL/SQL procedure successfully completed.

And let’s check it’s changed:

SQL>select * from t1; 

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  27uhu2q2xuu7r, child number 1

An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

NOTE: cannot fetch plan for SQL_ID: 27uhu2q2xuu7r, CHILD_NUMBER: 1
      Please verify value of SQL_ID and CHILD_NUMBER;
      It could also be that the plan is no longer in cursor cache (check v$sql_plan)

8 rows selected.

SQL>select * from t1; 

no rows selected

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  27uhu2q2xuu7r, child number 1
-------------------------------------
select * from t1

Plan hash value: 646159151

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |    26 (100)|          |
|   1 |  INDEX FULL SCAN | PK_T1 |    82 |  1066 |    26   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - SQL plan baseline SQL_PLAN_6x6k5dhdcczn6690169bf used for this statement


17 rows selected.

SQL>

Easy. Job done.

Note that you need the SQL_ID and PLAN_HASH_VALUE of the SOURCE statement (i.e. the plan you want to transfer).

This plan has to be in the cache – which shouldn’t be a problem because the main scenario we’re talking about here is doing some adhoc tuning on a statement, maybe by adding some hints to get a particular access pathe, with the aim of propagating the tuned plan to the production statement.

For the TARGET statement – i.e. the problem statement that you want to transfer the plan to – you just need the sql text.

If it’s a long sql statement it might be easier to this directly from v$sql.sql_fulltext and pass it in.

Why do you only need the sql text? Because it gets converted to a signature as per DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE. And signature is just a different sort of hash after the statement has been adjusted for white space and case insensitivity.

But you could use the overloaded version of LOAD_PLANS_FROM_CURSOR_CACHE and pass in SQL_HANDLE instead if this statement is already baselined. (Anyone know how SQL_HANDLE is generated? Yet another hash?).