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
- Create a SQL Baseline for the hinted query
- 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
Mohamed Houri’s Oracle Notes 
Best SPM description i found online so far. Thanks for the effort!
Comment by Jochen — May 28, 2014 @ 2:18 pm |
you might taking one more step than necessary, there isn’t a need to create plan baseline for the hinted SQL, like described here;
https://asktom.oracle.com/Misc/oramag/on-table-updates-and-sql-plan-baselines.html
Unless of course you really intend to use the hinted statement and have a baseline attached to it as well, I’m guessing you don’t as there’s no explanation why the hinted baseline is there
Comment by Oj — April 15, 2023 @ 10:20 am |
Oj
Yes, you are right. I distinctly remember reading this article by Tom Kyte at the time. But since then I use the following script to do this kind of manipulation
declare v_sql_text clob; ln_plans pls_integer; begin select replace(sql_fulltext, chr(00), ' ') into v_sql_text from gv$sqlarea where sql_id = trim('&original_sql_id') and rownum = 1; -- create sql_plan_baseline for original sql using plan from modified sql ln_plans := dbms_spm.load_plans_from_cursor_cache ( sql_id => trim('&modified_sql_id'), plan_hash_value => to_number(trim('&plan_hash_value')), sql_text => v_sql_text ); dbms_output.put_line('Plans Loaded: '||ln_plans); end; /Best regards
Mohamed Houri
Comment by hourim — April 15, 2023 @ 12:35 pm |