Friday, March 02, 2012

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 than 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 having 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 a 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 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)
Posted by at No comments :
Labels:
Subscribe to: Posts ( Atom )