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 the 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 concatenation 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 existing 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))
Edited 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)