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.

About these ads

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

  1. coskan says:

    Great info Dominic, thanks for sharing

    Looks like is also related with the same feature as side effect. Once there are different sqlids CBO is getting a bit confused and raises the one bwlo

    Bug 10222321: ORA-38141: SQL PLAN BASELINE SQL_PLAN_XXXXXXXX DOES NOT EXIST

    • coskan says:

      I think I was not be clear on my last comment. That error raised within one of our databases where multiple sqls with different sqlid but same signature with producable plans on 11.2.0.2.
      for one of the sqls which should not have the baseline at first point. Other sqls has got but that particular has not.

      different than bug description but error was same

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 72 other followers

%d bloggers like this: