Search

Top 60 Oracle Blogs

Recent comments

Editing Hints in Stored Outlines

Introduction 
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
         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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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
 

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
UPDATE outln.ol$hints
SET hint_text = 'NO EXPAND'
WHERE ol_name = 'C3'
AND hint_text like 'USE_CONCAT(%)'
/

Testing Outlines 
Original Outline 

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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.

#eeeeee; border: 0px solid #000000; overflow: auto; padding-left: 4px; padding-right: 4px; width: 100%;">
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)