Mohamed Houri’s Oracle Notes

February 11, 2014

How to attach a hinted SPM baseline to a non hinted sql query?

Filed under: Sql Plan Managment — hourim @ 8:37 am

The last Jonathan Lewis post on RAC Plans which finished by the following phrases:

“If you’re going to hard-code hints into a query then take a look at the outline it generates when it does the right thing, and that will tell you about the 15 or so hints you’ve missed out. (Better still, consider generating an SQL Baseline from the hinted code and attaching it to the unhinted code.)”

Prompted me to write this blog post. This is also a good coincidence because I have recently answered a similar question in the French forum. Here below is an example on how to attach a SQL plan Baseline of a hinted sql query to the same query without hint.

First the usual model (thanks Dom Brooks )

 SQL> create table t1
          (col1  number
          ,col2  varchar2(50)
          ,flag  varchar2(2));

SQL>  insert into t1
          select rownum
             ,lpad('X',50,'X')
             ,case when rownum = 1
              then 'Y1'
                   when rownum = 2
              then 'Y2'
                   when mod(rownum,2) = 0
              then 'N1'
                    else 'N2'
              end
          from   dual
          connect by rownum <= 100000;

SQL> CREATE INDEX i1 ON t1 (flag);

SQL> exec dbms_stats.gather_table_stats(user, 't1');

Second my two queries, the unhinted one

SQL>  var n varchar2(2);
SQL>  exec :n := 'Y1';

SQL> select count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n
Plan hash value: 3625400295
-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |       |       |   124 (100)|          |
|   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1318K|   124   (2)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I1   | 25000 |       |    13   (8)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("FLAG"=:N)

And the hinted one

SQL> select /*+ full(t1) */ count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

SQL_ID  cnazndd6j7d1v, child number 0
-------------------------------------
select /*+ full(t1) */ count(*), max(col2) from t1 where flag = :n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   241 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 25000 |  1318K|   241   (3)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)

I have two queries an unhinted and a hinted one honored via two different execution plans. I want my unhinted query to be always honored by the plan of the hinted query. In order to achieve this goal I need to

  1. Create a SQL Baseline for the hinted query
  2. Attach this SQL Baseline to the unhinted query

So I will start by loading the above two plans into a SPM baseline using their corresponding sql_id

-- unhinted
SQL>declare
      l_op pls_integer;
    begin
      l_op := dbms_spm.load_plans_from_cursor_cache('731b98a8u0knf');
    end;
   /

-- hinted
SQL> declare
      l_op pls_integer;
     begin
      l_op := dbms_spm.load_plans_from_cursor_cache('cnazndd6j7d1v');
     end;
    /

I can now verify that I have two accepted and enabled SPM plans

SQL> select sql_handle, plan_name, sql_text, accepted, enabled
     from dba_sql_plan_baselines;

SQL_HANDLE           PLAN_NAME                      SQL_TEXT                                      ACC  ENA
-------------------- --------- -------------------- --------------------------------------------- ---- ---
SQL_11f0e4472549338d SQL_PLAN_13w748wknkcwd8576eb1f select count(*), max(col2) from t1 where flag YES  YES
 = :n
SQL_ba68302db880365b SQL_PLAN_bnu1h5qw80dkv616acf47 select /*+ full(t1) */ count(*), max(col2) fr YES YES
 om t1 where flag = :n

At this step we are very close to our goal which is to make the unhinted sql query(index range scan) use the baseline of the same but hinted sql query(full table scan). The first thing to do is to disable the index range scan SPM plan

declare
  ln_ps number;
begin
  ln_ps := dbms_spm.alter_sql_plan_baseline(sql_handle     => 'SQL_11f0e4472549338d'
                                           ,plan_name      => 'SQL_PLAN_13w748wknkcwd8576eb1f'
                                           ,attribute_name => 'enabled'
                                           ,attribute_value => 'NO');
end;
/

so that the original index range scan is not constrained anymore by its original SPM plan

SQL> select count(*), max(col2) from t1 where flag = :n;

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

 SQL_ID 731b98a8u0knf, child number 0
 -------------------------------------
 select count(*), max(col2) from t1 where flag = :n

 Plan hash value: 3625400295
 -------------------------------------------------------------------------------------
 | Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT             |      |       |       |   124 (100)|          |
 |   1 |  SORT AGGREGATE              |      |     1 |    54 |            |          |
 |   2 |   TABLE ACCESS BY INDEX ROWID| T1   | 25000 |  1318K|   124   (2)| 00:00:01 |
 |*  3 |    INDEX RANGE SCAN          | I1   | 25000 |       |    13   (8)| 00:00:01 |
 -------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
 3 - access("FLAG"=:N)

And finally I will attach the full table scan SPM plan to the sql_id of the index range scan

 SQL> declare
       l_op pls_integer;
    begin
      l_op := dbms_spm.load_plans_from_cursor_cache
                 (sql_id          => 'cnazndd6j7d1v'        -- sql_id of the full table scan
                 ,plan_hash_value => 3724264953             -- plan hash value of the full table scan
                 ,sql_handle      => 'SQL_11f0e4472549338d' -- sql handle of the index range scan query
                );
   end;
/

Spot now how the goal has been achieved

SQL> select count(*), max(col2) from t1 where flag = :n;

COUNT(*) MAX(COL2)
---------- --------------------------------------------------
1 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

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

SQL_ID  731b98a8u0knf, child number 0
-------------------------------------
select count(*), max(col2) from t1 where flag = :n

Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   241 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    54 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 25000 |  1318K|   241   (3)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("FLAG"=:N)

Note
-----
- SQL plan baseline SQL_PLAN_13w748wknkcwd616acf47 used for this statement
About these ads

1 Comment »

  1. Best SPM description i found online so far. Thanks for the effort!

    Comment by Jochen — May 28, 2014 @ 2:18 pm | Reply


RSS feed for comments on this post. TrackBack URI

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

Theme: Rubric. Get a free blog at WordPress.com

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Database 11g

Oracle Database

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Follow

Get every new post delivered to your Inbox.

Join 88 other followers

%d bloggers like this: