oracle移花接木,Oracle ---- 固定执行计划之Outline

今天介绍一个旧版本中固定执行计划的方法：Outline。

这个方法虽然比较老，但是因为没有版本限制，SE也可以使用，所以还在一定的场景中是能用到的。

下面是详细的步骤和测试结果：

1.TEST用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.做成两个Outline。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.查看一下做成的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.把“TEST_OLN_TAB1”和“TEST_OLN_TAB2”的Hint进行互换，达到把加了Hint“FULL( tab1 )”的执行计划固定给没加Hint的SQL文。SQL> update OUTLN.OL$HINTS set OL_NAME='TEST_OLN_TAB3' where OL_NAME='TEST_OLN_TAB1';

6行が更新されました。

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

6行が更新されました。

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

6行が更新されました。

SQL> commit;

コミットが完了しました。

5.查看一下移花接木后的Outline。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.看看Outline能不能固定执行计划。[oracle@db1903 ~]$ sqlplus test/test@localhost:1521/pdb

SQL> set autot on

SQL> set lin 120 pages 999

SQL> ALTER SESSION SET USE_STORED_OUTLINES = TEST_OLN;

セッションが変更されました。

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

COUNT(*)

----------

50

実行計画

----------------------------------------------------------

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

統計

----------------------------------------------------------

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：

方法虽然好用，但是涉及了手动修改内部表数据，不在ORACLE技术支持的范围，需要自负责任。