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

Following on from my post yesterday, I just want to emphasise a point about the baseline mechanism.

When it comes to sql sharing, we’re used to the statement having to be EXACTLY the same, even down to space and case sensitivity.

For example:

SQL> select /*+dom_baseline*/ * from t1;

no rows selected

SQL> SELECT /*+dom_baseline*/ * FROM T1;

no rows selected

SQL> select /*+dom_baseline*/
  2         *
  3  from   t1;

no rows selected

SQL> select sql_id
  2  ,      to_char(exact_matching_signature) exact_signature
  3  ,      sql_text 
  4  from   v$sql 
  5  where  sql_text like '%dom_baseline%'
  6  and    sql_text not like '%sql_id%';

SQL_ID        EXACT_SIGNATURE                          SQL_TEXT
------------- ---------------------------------------- ---------------------------------------------
chbm0pdpb4q9a 3617515095634892221                      select /*+dom_baseline*/ * from t1
bmg3pd81wapdz 3617515095634892221                      SELECT /*+dom_baseline*/ * FROM T1
0g29rnfuf3a75 3617515095634892221                      select /*+dom_baseline*/        * from   t1

SQL> 

So slight differences lead to different sql cursors (sql_id).

But baselines work by signature (exact_matching_signature although profile can work via force_matching_signature) – see that the exact_matching_signature for the three statements is the same – 3617515095634892221

Now if we repeat yesterday’s transfer of the plan from one statement to the another:

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

no rows selected

SQL> 
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 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=4)


17 rows selected.

SQL> declare
  2     sqltext clob;
  3     spm_op pls_integer;
  4  begin
  5     sqltext := 'select /*+dom_baseline*/ * 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);
 11  end;
 12  / 

PL/SQL procedure successfully completed.

SQL> 

See what we’ve got in dba_sql_plan_baselines:

SQL> select to_char(signature)
  2  ,      sql_handle
  3  ,      plan_name 
  4  from   dba_sql_plan_baselines;

TO_CHAR(SIGNATURE)                       SQL_HANDLE                     PLAN_NAME
---------------------------------------- ------------------------------ ----------------------------
3617515095634892221                      SQL_3233fed024ee55bd           SQL_PLAN_34czyu0kfwpdx690169bf

SQL> 

And yet if we re-run those first three statements above:

SQL> select /*+dom_baseline*/ * from t1;

no rows selected

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  chbm0pdpb4q9a, child number 1
-------------------------------------
select /*+dom_baseline*/ * 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_34czyu0kfwpdx690169bf used for this statement


17 rows selected.

SQL> SELECT /*+dom_baseline*/ * FROM T1;

no rows selected

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  bmg3pd81wapdz, child number 1
-------------------------------------
SELECT /*+dom_baseline*/ * 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_34czyu0kfwpdx690169bf used for this statement


17 rows selected.

SQL> select /*+dom_baseline*/
  2         *
  3  from   t1;

no rows selected

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
SQL_ID  0g29rnfuf3a75, child number 1
-------------------------------------
select /*+dom_baseline*/        * 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_34czyu0kfwpdx690169bf used for this statement


17 rows selected.

SQL> 

Three statements – essentially the same – all changed by our baseline.

Cool.