关于数据库:Oracle-使用Outline固定执行计划

6次阅读

共计 5198 个字符，预计需要花费 13 分钟才能阅读完成。

明天介绍一个旧版本中固定执行打算的办法：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 技术支持的范畴，须要 自负责任。

正文完