This note describes changing a hint in a SQL Outline by updating the OUTLN.OL$ and OUTLN.OL$HINTS tables directly. It shows that in the limited test case that it does affect the resulting execution plan.
CAVEAT: This is a hack, so this is more of an oddity that something that I would recommend for use in production. I am only interested in it for the limited purpose of adding a NO_EXPAND hint to an outline, that would not otherwise be included if the outline was generated in the conventional manner.
Why Outlines?
I am using outlines because I am working with Oracle 10g, so I don't have SQL Plan Management until Oracle 11g. I am not using SQL Profiles because I am working with PeopleSoft Global Payroll. I want complete stability of execution plans rather than have the optimizer produce 'better plans' with adjusted costing. Otherwise a single employee payroll calculation can put a different plan into the library cache which can then be used for a company-wide payroll calculation. I want to guarantee just one execution plan.
Preparation
I'll start by creating a table to use.
DROP TABLE t PURGE / CREATE TABLE t (a NUMBER NOT NULL ,b NUMBER NOT NULL ,c VARCHAR2(100) ) / TRUNCATE TABLE t / INSERT INTO t (a,b,c) SELECT row_number() over (order by level) , row_number() over (order by level desc) , TO_CHAR(TO_DATE(rownum,'J'),'Jsp') FROM DUAL CONNECT BY LEVEL <= 10000 / BEGIN dbms_stats.gather_table_stats (ownname=>user ,tabname=>'T' ,cascade=>TRUE ); end; /
Table T has 10000 rows. Column A is numbered 1 to 10000, B is numbered 10000 to 1, and there is a third column to provide some padding.
A B C
---------- ---------- ----------------------------------------
1 10000 One
2 9999 Two
3 9998 Three
…
9998 3 Nine Thousand Nine Hundred Ninety-Eight
9999 2 Nine Thousand Nine Hundred Ninety-Nine
10000 1 Ten Thousand
Collecting Outlines
So, the table doesn't have any indexes. I will disable autotrace in SQL*Plus, and dynamic sampling to prevent collection of additional SQL outlines.
DROP INDEX t1; DROP INDEX t2; SET AUTOTRACE OFF CLEAR SCREEN ALTER SESSION SET optimizer_dynamic_sampling=0; ALTER SYSTEM FLUSH SHARED_POOL; ALTER SESSION SET create_stored_outlines=C1; SELECT * FROM t WHERE a=42 OR b=42 / ALTER SESSION SET create_stored_outlines=FALSE; ALTER SESSION SET use_stored_outlines=FALSE;
Without any indexes, Oracle can only do a full scan on the table.
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 86 | 22 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 2 | 86 | 22 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=42 OR "B"=42)
I will now add the indexes and create another stored outline for the same statement, but in a different category.
CREATE INDEX t1 ON t (a,b); CREATE INDEX t2 ON t (b,a); SET AUTOTRACE OFF ALTER SYSTEM FLUSH SHARED_POOL; ALTER SESSION SET create_stored_outlines=C2; SELECT * FROM t WHERE a=42 OR b=42 / ALTER SESSION SET create_stored_outlines=FALSE; ALTER SESSION SET use_stored_outlines=FALSE;
With the indexes the execution plan includes contenation of queries on each index.
Plan hash value: 277049827
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 86 | 6 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 43 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T2 | 1 | | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 43 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"=42)
5 - access("A"=42)
filter(LNNVL("B"=42))
I will create a third stored outline in a third category so that I have two copies of the outline for comparison.
ALTER SYSTEM FLUSH SHARED_POOL; ALTER SESSION SET create_stored_outlines=C3; SELECT * FROM t WHERE a=42 OR b=42 / ALTER SESSION SET create_stored_outlines=FALSE;
I am going to rename the outlines to match the category to make it easier to work in the rest of this example.
BEGIN FOR i IN ( SELECT DISTINCT c1.category, c1.name name FROM user_outlines c1 , user_outlines c2 WHERE c1.category != c2.category AND c1.signature = c2.signature AND c1.category != c1.name ) LOOP EXECUTE IMMEDIATE 'ALTER OUTLINE '||i.name||' RENAME TO '||i.category; END LOOP; END; /
Looking at Outlines
We now have three outlines
SET AUTOTRACE OFF PAGES 100 LINES 120 BREAK ON name SKIP 1 SELECT * FROM user_outlines ORDER BY 1 / NAME CATEGORY USED TIMESTAMP VERSION ---------- ---------- ------ ------------------- ---------- SQL_TEXT -------------------------------------------------------------------------------- SIGNATURE COMPATIBLE ENABLED FORMAT MIGRATED -------------------------------- ---------- -------- ---------- ------------ C1 C1 UNUSED 14:49:10 01/03/2012 11.2.0.2.0 SELECT * FROM t WHERE a=42 OR b=42 260D04C39979D97078EDCE9B6727C4A3 COMPATIBLE ENABLED NORMAL NOT-MIGRATED C2 C2 UNUSED 14:49:10 01/03/2012 11.2.0.2.0 SELECT * FROM t WHERE a=42 OR b=42 260D04C39979D97078EDCE9B6727C4A3 COMPATIBLE ENABLED NORMAL NOT-MIGRATED C3 C3 UNUSED 14:48:33 01/03/2012 11.2.0.2.0 SELECT * FROM t WHERE a=42 OR b=42 260D04C39979D97078EDCE9B6727C4A3 COMPATIBLE ENABLED NORMAL NOT-MIGRATED
And these are the hints in the outlines
SELECT *
FROM user_outline_hints
ORDER BY 1,2,3
/
NAME NODE STAGE JOIN_POS HINT
---------- ----- ------ --------- ----------------------------------------
C1 1 1 1 FULL(@"SEL$1" "T"@"SEL$1")
1 1 0 OUTLINE_LEAF(@"SEL$1")
1 1 0 ALL_ROWS
1 1 0 OPT_PARAM('optimizer_dynamic_sampling' 0)
1 1 0 DB_VERSION('11.2.0.2')
1 1 0 OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
C2 1 1 1 INDEX_RS_ASC(@"SEL$1_2" "T"@"SEL$1_2" ("T"."A" "T"."B"))
1 1 1 INDEX_RS_ASC(@"SEL$1_1" "T"@"SEL$1" ("T"."B" "T"."A"))
1 1 0 OUTLINE(@"SEL$1")
1 1 0 OUTLINE_LEAF(@"SEL$1_2")
1 1 0 USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1))
1 1 0 OUTLINE_LEAF(@"SEL$1_1")
1 1 0 OUTLINE_LEAF(@"SEL$1")
1 1 0 ALL_ROWS
1 1 0 OPT_PARAM('optimizer_dynamic_sampling' 0)
1 1 0 DB_VERSION('11.2.0.2')
1 1 0 OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
C3 1 1 1 INDEX_RS_ASC(@"SEL$1_2" "T"@"SEL$1_2" ("T"."A" "T"."B"))
1 1 1 INDEX_RS_ASC(@"SEL$1_1" "T"@"SEL$1" ("T"."B" "T"."A"))
1 1 0 OUTLINE(@"SEL$1")
1 1 0 OUTLINE_LEAF(@"SEL$1_2")
1 1 0 USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1))
1 1 0 OUTLINE_LEAF(@"SEL$1_1")
1 1 0 OUTLINE_LEAF(@"SEL$1")
1 1 0 ALL_ROWS
1 1 0 OPT_PARAM('optimizer_dynamic_sampling' 0)
1 1 0 DB_VERSION('11.2.0.2')
1 1 0 OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
Or, I could have queried from OUTLN.OL$HINTS
SET LINES 110 PAGES 999
BREAK ON ol_name SKIP 1
SELECT ol_name,hint#,category,hint_type,hint_text
,stage#,node#,table_name,table_tin,table_pos
,ref_id,user_table_name,cost,cardinality,bytes
,hint_textoff,hint_textlen
FROM outln.ol$hints
WHERE category IN('C1','C2')
ORDER BY 1,2,3,4,5
/
OL_NAME HINT# CATEGORY HINT_TYPE HINT_TEXT STAGE# NODE#
---------- ----- -------- ---------- ------------------------------------------------------------ ------ -----
TABLE_NAME TABLE_TIN TABLE_POS REF_ID USER_TABLE COST CARDINALITY BYTES HINT_TEXTOFF HINT_TEXTLEN
---------- ---------- ---------- ---------- ---------- ---------- ----------- ---------- ------------ ------------
C1 1 C1 2 FULL(@"SEL$1" "T"@"SEL$1") 1 1
T 1 1 0 SYSADM.T 22.1083368 2 86 15 1
2 C1 1011 OUTLINE_LEAF(@"SEL$1") 1 1
0 0 0 0 0 0 0 0
3 C1 1013 ALL_ROWS 1 1
0 0 0 0 0 0 0 0
4 C1 1012 OPT_PARAM('optimizer_dynamic_sampling' 0) 1 1
0 0 0 0 0 0 0 0
5 C1 54 DB_VERSION('11.2.0.2') 1 1
0 0 0 0 0 0 0 0
6 C1 1009 OPTIMIZER_FEATURES_ENABLE('11.2.0.2') 1 1
0 0 0 0 0 0 0 0
7 C1 1008 IGNORE_OPTIM_EMBEDDED_HINTS 1 1
0 0 0 0 0 0 0 0
C2 1 C2 51 INDEX_RS_ASC(@"SEL$1_2" "T"@"SEL$1_2" ("T"."A" "T"."B")) 1 1
T 1 1 0 SYSADM.T 3.00073364 1 43 15 1
2 C2 51 INDEX_RS_ASC(@"SEL$1_1" "T"@"SEL$1" ("T"."B" "T"."A")) 1 1
1 1 0 SYSADM.T 3.00073196 1 43 15 1
3 C2 1010 OUTLINE(@"SEL$1") 1 1
0 0 0 0 0 0 0 0
4 C2 1011 OUTLINE_LEAF(@"SEL$1_2") 1 1
0 0 0 0 0 0 0 0
5 C2 38 USE_CONCAT(@"SEL$1" 8 OR_PREDICATES(1)) 1 1
0 0 0 0 0 0 0 0
6 C2 1011 OUTLINE_LEAF(@"SEL$1_1") 1 1
0 0 0 0 0 0 0 0
7 C2 1011 OUTLINE_LEAF(@"SEL$1") 1 1
0 0 0 0 0 0 0 0
8 C2 1013 ALL_ROWS 1 1
0 0 0 0 0 0 0 0
9 C2 1012 OPT_PARAM('optimizer_dynamic_sampling' 0) 1 1
0 0 0 0 0 0 0 0
10 C2 54 DB_VERSION('11.2.0.2') 1 1
0 0 0 0 0 0 0 0
11 C2 1009 OPTIMIZER_FEATURES_ENABLE('11.2.0.2') 1 1
0 0 0 0 0 0 0 0
12 C2 1008 IGNORE_OPTIM_EMBEDDED_HINTS 1 1
0 0 0 0 0 0 0 0
Editing Outlines
Adding a Hint
I can add a new hint, thus
REM Insert hint at positition 0
INSERT INTO outln.ol$hints
(ol_name,hint#,category,hint_type,hint_text
,stage#,node#,table_name,table_tin,table_pos
,ref_id,user_table_name,cost,cardinality,bytes
,hint_textoff,hint_textlen)
VALUES
('C2',0,'C2',42,'NO_EXPAND'
,1,1,NULL,0,0
,0,NULL,0,0,0
,0,0)
/
REM increment hint numbers if there is a hint at position 0
UPDATE outln.ol$hints x
SET hint#=hint#+1
WHERE EXISTS (select 'x'
FROM outln.ol$hints y
WHERE y.ol_name = x.ol_name
AND y.hint#=0)
AND ol_name = 'C2'
/
REM update the hint count on the parent record
UPDATE outln.ol$ x
set hintcount = (
SELECT count(*)
FROM outln.ol$hints h
where h.ol_name = h.ol_name)
where ol_name = 'C3'
/
Changing a Hint
However, in this case I want to change an exisiting hint from USE_CONCAT to NO_EXPAND.
UPDATE outln.ol$hints SET hint_text = 'NO EXPAND' WHERE ol_name = 'C3' AND hint_text like 'USE_CONCAT(%)' /
Testing Outlines
Original Outline
CLEAR SCREEN SET AUTOTRACE OFF ALTER SESSION SET statistics_level = ALL; ALTER SYSTEM FLUSH SHARED_POOL; ALTER SESSION SET use_stored_outlines=C2; SELECT * FROM t WHERE a=42 OR b=42 / SELECT * FROM table(dbms_xplan.display_cursor(null,null,'ADVANCED')) /
The unchanged outline C2 still produces the concatenation
Plan hash value: 277049827
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 86 | 6 (0)| 00:00:01 |
| 1 | CONCATENATION | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 43 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T2 | 1 | | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 43 | 3 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | T1 | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("B"=42)
5 - access("A"=42)
filter(LNNVL("B"=42))
Editted Outline
Now, let's try the outline that I updated directly.
ALTER SESSION SET use_stored_outlines=C3; SELECT * FROM t WHERE a=42 OR b=42 / SELECT * FROM table(dbms_xplan.display_cursor(null,null,'ADVANCED')) / ALTER SESSION SET use_stored_outlines=FALSE;
I get a different execution plan that doesn't do CONCATENATION. Note that the outline still doesn't contain the NO_EXPAND hint that I put into the outline.
Plan hash value: 4269684720
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 2 | 86 | 6 (34)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 3 | BITMAP OR | | | | | |
| 4 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 5 | SORT ORDER BY | | | | | |
|* 6 | INDEX RANGE SCAN | T1 | | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
DB_VERSION('11.2.0.2')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
BITMAP_TREE(@"SEL$1" "T"@"SEL$1" OR(1 1 ("T"."A" "T"."B")))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"=42)
filter("A"=42)
Posts
