明天介绍一个旧版本中固定执行打算的办法：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 SPARE2TEST_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_TEXTLENTEST_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 4TEST_OLN_TAB1 2 TEST_OLN 1011 OUTLINE_LEAF(@"SEL$1") 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB1 3 TEST_OLN 1013 ALL_ROWS 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB1 4 TEST_OLN 54 DB_VERSION('19.1.0') 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB1 5 TEST_OLN 1009 OPTIMIZER_FEATURES_ENABLE('19.1.0') 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB1 6 TEST_OLN 1008 IGNORE_OPTIM_EMBEDDED_HINTS 1 1 0 0 0 0 0 0 0 0TEST_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 4TEST_OLN_TAB2 2 TEST_OLN 1011 OUTLINE_LEAF(@"SEL$1") 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB2 3 TEST_OLN 1013 ALL_ROWS 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB2 4 TEST_OLN 54 DB_VERSION('19.1.0') 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB2 5 TEST_OLN 1009 OPTIMIZER_FEATURES_ENABLE('19.1.0') 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB2 6 TEST_OLN 1008 IGNORE_OPTIM_EMBEDDED_HINTS 1 1 0 0 0 0 0 0 0 0SQL> select * from OUTLN.OL$NODES;OL_NAME CATEGORY NODE_ID PARENT_ID NODE_TYPE NODE_TEXTLEN NODE_TEXTOFF NODE_NAMETEST_OLN_TAB1 TEST_OLN 1 0 45 36 1 SEL$1TEST_OLN_TAB2 TEST_OLN 1 0 45 56 1 SEL$14.把“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 HINTCOUNTTEST_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 6TEST_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 6SQL> 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_TEXTLENTEST_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 4TEST_OLN_TAB1 2 TEST_OLN 1011 OUTLINE_LEAF(@"SEL$1") 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB1 3 TEST_OLN 1013 ALL_ROWS 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB1 4 TEST_OLN 54 DB_VERSION('19.1.0') 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB1 5 TEST_OLN 1009 OPTIMIZER_FEATURES_ENABLE('19.1.0') 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB1 6 TEST_OLN 1008 IGNORE_OPTIM_EMBEDDED_HINTS 1 1 0 0 0 0 0 0 0 0TEST_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 4TEST_OLN_TAB2 2 TEST_OLN 1011 OUTLINE_LEAF(@"SEL$1") 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB2 3 TEST_OLN 1013 ALL_ROWS 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB2 4 TEST_OLN 54 DB_VERSION('19.1.0') 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB2 5 TEST_OLN 1009 OPTIMIZER_FEATURES_ENABLE('19.1.0') 1 1 0 0 0 0 0 0 0 0TEST_OLN_TAB2 6 TEST_OLN 1008 IGNORE_OPTIM_EMBEDDED_HINTS 1 1 0 0 0 0 0 0 0 0SQL> select * from OUTLN.OL$NODES;OL_NAME CATEGORY NODE_ID PARENT_ID NODE_TYPE NODE_TEXTLEN NODE_TEXTOFF NODE_NAMETEST_OLN_TAB1 TEST_OLN 1 0 45 36 1 SEL$1TEST_OLN_TAB2 TEST_OLN 1 0 45 56 1 SEL$16.看看Outline能不能固定执行打算。
[oracle@db1903 ~]$ sqlplus test/test@localhost:1521/pdbSQL> set autot onSQL> set lin 120 pages 999SQL> 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 processedOK!
Summury：
办法尽管好用，然而波及了手动批改外部表数据，不在ORACLE技术支持的范畴，须要自负责任。