Oracle — Outline of fixed execution plan

Time:2021-6-21

Today we introduce an old method of fixing execution plan: outline.
Although this method is relatively old, because there is no version limit, Se can also be used, so it can be used in certain scenarios.

The following are the detailed steps and test results:

1. Test is made of table.

create table tab1(c1 number, c2 number, c3 varchar2(10));
declare
  a number;
begin
  a := 1;
  for i in 1 .. 50 loop
    for j in 1 .. 100 loop
      insert into tab1 values(a,j,'a');
      commit;
      a := a+1;
    end loop;
  end loop;
end;
/
create index ind1_1 on tab1(c2);
exec dbms_stats.gather_table_stats(ownname=>'TEST',tabname=>'TAB1',cascade=>TRUE);

2. Make two outlines.

CREATE OUTLINE test_oln_tab1 for category test_oln ON select count(*) from tab1 where c2=1;

CREATE OUTLINE test_oln_tab2 for category test_oln ON select /*+ FULL( tab1 ) */ count(*) from tab1 where c2=1;

3. Check the outline.

SQL> select * from OUTLN.OL$;

OL_NAME    SQL_TEXT    TEXTLEN    SIGNATURE    HASH_VALUE    HASH_VALUE2    CATEGORY    VERSION    CREATOR    TIMESTAMP    FLAGS    HINTCOUNT    SPARE1    SPARE2
TEST_OLN_TAB1    select count(*) from tab1 where c2=1    36    DFCF0A3CF8B2F9EF5D90A595EF5F2B16    1484405676    2172588166    TEST_OLN    19.0.0.0.0    TEST    2/22/2021 15:25    0    6        
TEST_OLN_TAB2    select /*+ FULL( tab1 ) */ count(*) from tab1 where c2=1    56    FC573ABD6E39A44C51808D67718F873C    2518267384    3225357337    TEST_OLN    19.0.0.0.0    TEST    2/22/2021 15:25    0    6    

SQL> select * from 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
TEST_OLN_TAB1    1    TEST_OLN    1001    INDEX(@"SEL$1" "TAB1"@"SEL$1" ("TAB1"."C2"))    1    1    TAB1    1    1    0    TEST.TAB1    1.000607    50    150    22    4
TEST_OLN_TAB1    2    TEST_OLN    1011    OUTLINE_LEAF(@"SEL$1")    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB1    3    TEST_OLN    1013    ALL_ROWS    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB1    4    TEST_OLN    54    DB_VERSION('19.1.0')    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB1    5    TEST_OLN    1009    OPTIMIZER_FEATURES_ENABLE('19.1.0')    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB1    6    TEST_OLN    1008    IGNORE_OPTIM_EMBEDDED_HINTS    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB2    1    TEST_OLN    2    FULL(@"SEL$1" "TAB1"@"SEL$1")    1    1    TAB1    1    1    0    TEST.TAB1    5.04028051    50    150    42    4
TEST_OLN_TAB2    2    TEST_OLN    1011    OUTLINE_LEAF(@"SEL$1")    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB2    3    TEST_OLN    1013    ALL_ROWS    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB2    4    TEST_OLN    54    DB_VERSION('19.1.0')    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB2    5    TEST_OLN    1009    OPTIMIZER_FEATURES_ENABLE('19.1.0')    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB2    6    TEST_OLN    1008    IGNORE_OPTIM_EMBEDDED_HINTS    1    1        0    0    0        0    0    0    0    0

SQL> select * from OUTLN.OL$NODES;

OL_NAME    CATEGORY    NODE_ID    PARENT_ID    NODE_TYPE    NODE_TEXTLEN    NODE_TEXTOFF    NODE_NAME
TEST_OLN_TAB1    TEST_OLN    1    0    45    36    1    SEL$1
TEST_OLN_TAB2    TEST_OLN    1    0    45    56    1    SEL$1

4. Put “test” into practice_ OLN_ “Tab1” and “test”_ OLN_ The hint of TAB2 “is interchanged to add the hint“ FULL( tab1 ) ”The execution plan of is fixed to the SQL document without hint.

SQL> update OUTLN.OL$HINTS set OL_NAME='TEST_OLN_TAB3' where OL_NAME='TEST_OLN_TAB1';

6 lines to update.

SQL> update OUTLN.OL$HINTS set OL_NAME='TEST_OLN_TAB1' where OL_NAME='TEST_OLN_TAB2';

6 lines to update.

SQL> update OUTLN.OL$HINTS set OL_NAME='TEST_OLN_TAB2' where OL_NAME='TEST_OLN_TAB3';

6 lines to update.

SQL> commit;

It's over.

5. Check out the outline after grafting.

SQL> select * from OUTLN.OL$;

OL_NAME    SQL_TEXT    TEXTLEN    SIGNATURE    HASH_VALUE    HASH_VALUE2    CATEGORY    VERSION    CREATOR    TIMESTAM    FLAGS    HINTCOUNT
TEST_OLN_TAB1    select count(*) from tab1 where c2=1    36    DFCF0A3CF8B2F9EF5D90A595EF5F2B16    1484405676    2172588166    TEST_OLN    19.0.0.0.0    TEST    21-02-22    0    6
TEST_OLN_TAB2    select /*+ FULL( tab1 ) */ count(*) from tab1 where c2=1    56    FC573ABD6E39A44C51808D67718F873C    2518267384    3225357337    TEST_OLN    19.0.0.0.0    TEST    21-02-22    0    6

SQL> select * from 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
TEST_OLN_TAB1    1    TEST_OLN    2    FULL(@"SEL$1" "TAB1"@"SEL$1")    1    1    TAB1    1    1    0    TEST.TAB1    5.04028051    50    150    42    4
TEST_OLN_TAB1    2    TEST_OLN    1011    OUTLINE_LEAF(@"SEL$1")    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB1    3    TEST_OLN    1013    ALL_ROWS    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB1    4    TEST_OLN    54    DB_VERSION('19.1.0')    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB1    5    TEST_OLN    1009    OPTIMIZER_FEATURES_ENABLE('19.1.0')    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB1    6    TEST_OLN    1008    IGNORE_OPTIM_EMBEDDED_HINTS    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB2    1    TEST_OLN    1001    INDEX(@"SEL$1" "TAB1"@"SEL$1" ("TAB1"."C2"))    1    1    TAB1    1    1    0    TEST.TAB1    1.000607    50    150    22    4
TEST_OLN_TAB2    2    TEST_OLN    1011    OUTLINE_LEAF(@"SEL$1")    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB2    3    TEST_OLN    1013    ALL_ROWS    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB2    4    TEST_OLN    54    DB_VERSION('19.1.0')    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB2    5    TEST_OLN    1009    OPTIMIZER_FEATURES_ENABLE('19.1.0')    1    1        0    0    0        0    0    0    0    0
TEST_OLN_TAB2    6    TEST_OLN    1008    IGNORE_OPTIM_EMBEDDED_HINTS    1    1        0    0    0        0    0    0    0    0

SQL> select * from OUTLN.OL$NODES;

OL_NAME    CATEGORY    NODE_ID    PARENT_ID    NODE_TYPE    NODE_TEXTLEN    NODE_TEXTOFF    NODE_NAME
TEST_OLN_TAB1    TEST_OLN    1    0    45    36    1    SEL$1
TEST_OLN_TAB2    TEST_OLN    1    0    45    56    1    SEL$1

6. See if outline can fix the execution plan.

[[email protected] ~]$ sqlplus test/[email protected]:1521/pdb
SQL> set autot on
SQL> set lin 120 pages 999

SQL> ALTER SESSION SET USE_STORED_OUTLINES = TEST_OLN;

It's the first time that you're going to change.

SQL> select count(*) from tab1 where c2=1;

  COUNT(*)
----------
        50


Planning for travel
----------------------------------------------------------
Plan hash value: 1117438016

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| TAB1 |    50 |   150 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C2"=1)

Note
-----
   - outline "TEST_OLN_TAB1" used for this statement


Statistics
----------------------------------------------------------
          3  recursive calls
         25  db block gets
          3  consistent gets
          0  physical reads
        868  redo size
        573  bytes sent via SQL*Net to client
        398  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

OK!

Summury:
Although the method is easy to use, it involves manual modification of internal table data, which is not in the scope of Oracle Technical support and needs to be modifiedSelf responsibility