dbms_outln.create_outline在10.2.0.5中创建outline所包含的执行计划并不正确

Posted on by YongJing(敬勇)

Oracle 10g中想要固定执行计划只能使用outline,sql profile不能起固定sql执行的效果，但是在Oracle 10.2.0.5中使用dbms_outln.create_outline通过使用共享池中的游标来创建outline,发现创建的outline与游标中的执行计划并不一致，而在oracle 10.2.0.4与oracle 11.2.0.4中是通过游标来创建的outline与cursor的实际执行计划是一致的。这应该是BUG.

Oracle 10.2.0.5中的测试如下:
定义绑定变量

SQL> var x varchar2(20)
SQL> exec :x:='Kabab';

PL/SQL procedure successfully completed.

执行查询

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     79999 Kabab

查看实际的执行计划

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7runhd24kgqsf, child number 0
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 141743202

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1            |     1 |    11 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1_T_MEAL |     1 |       |     1   (0)| 00:00:01 |

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


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

   2 - access("T_MEAL"=:X)


19 rows selected.

可以看到执行计划使用的是索引范围扫描

查询SQL语句的SQL_ID.hash_value,child_number

SQL> select hash_value, child_number, sql_text,sql_id from v$sql where sql_text like 'select * from t1%';

HASH_VALUE CHILD_NUMBER SQL_TEXT                                                                         SQL_ID
---------- ------------ -------------------------------------------------------------------------------- -------------
2301090574            0 select * from t1 where t_meal=:x                                                 7runhd24kgqsf

使用游标来创建outline

SQL> exec dbms_outln.create_outline(2301090574,0);

PL/SQL procedure successfully completed.

SQL> select name,owner,category,used from dba_outlines;

NAME                           OWNER                             CATEGORY                       USED
------------------------------ ------------------------------    -----------------------------  ------
SYS_OUTLINE_16060116155127504  JY                                DEFAULT                        UNUSED

查询outline的hint信息，可以看到没有index hint而是full这说明是全表扫描

SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060116155127504';

NAME                           OWNER                                NODE   STAGE      JOIN_POS   HINT
------------------------------ ------------------------------ ----------   ---------- ---------- -------------------------------------------------------------------------------

SYS_OUTLINE_16060116155127504  JY                                      1         1          1    FULL(@"SEL$1" "T1"@"SEL$1")
SYS_OUTLINE_16060116155127504  JY                                      1         1          0    OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_16060116155127504  JY                                      1         1          0    ALL_ROWS
SYS_OUTLINE_16060116155127504  JY                                      1         1          0    OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
SYS_OUTLINE_16060116155127504  JY                                      1         1          0    IGNORE_OPTIM_EMBEDDED_HINTS

启用outline,并重新执行sql语句

SQL> alter session set use_stored_outlines=true;

Session altered.
SQL> select * from t1 where t_meal=:x
  2  ;

      T_ID T_MEAL
---------- --------------------
     79999 Kabab

查询使用了outline的执行计划发现却是全表扫描，并不是游标中的索引范围扫描

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0c2v6n4c0sj6v, child number 0
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    47 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    11 |    47   (5)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

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

   1 - filter("T_MEAL"=:X)

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


22 rows selected.

在oracle 10.2.0.5中如果是使用自动创建outline，那么outline所包含的执行计划与游标中的执行计划是一致的，测试如下:
在会话级启用自动为查询语句创建outline

SQL> alter session set create_stored_outlines=true;

Session altered.

执行查询

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     79999 Kabab

禁用自动创建outline

SQL> alter session set create_stored_outlines=false;

Session altered.

查看语句的执行计划，使用了索引范围扫描

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7runhd24kgqsf, child number 1
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 141743202

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1            |     1 |    11 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1_T_MEAL |     1 |       |     1   (0)| 00:00:01 |

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


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

   2 - access("T_MEAL"=:X)


19 rows selected.

查询自动创建outline是否成功

SQL> select name,owner,category,used from dba_outlines;

NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
SYS_OUTLINE_16060117095505105  JY                             DEFAULT                        UNUSED

SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060117095505105';

NAME                           OWNER                                NODE      STAGE   JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ---------- --------------------------------------------------------------------------------
SYS_OUTLINE_16060117095505105  JY                                      1          1          1 INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."T_MEAL"))
SYS_OUTLINE_16060117095505105  JY                                      1          1          0 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_16060117095505105  JY                                      1          1          0 ALL_ROWS
SYS_OUTLINE_16060117095505105  JY                                      1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
SYS_OUTLINE_16060117095505105  JY                                      1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS

启用outline

SQL> alter session set use_stored_outlines=true;

Session altered.

重新执行查询

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     79999 Kabab

查看使用outline的执行计划使用了索引范围扫描

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0c2v6n4c0sj6v, child number 0
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 141743202

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1            |     1 |    11 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1_T_MEAL |     1 |       |     1   (0)| 00:00:01 |

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


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

   2 - access("T_MEAL"=:X)


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
   - outline "SYS_OUTLINE_16060117095505105" used for this statement


23 rows selected.

Oracle 10.2.0.4中的测试如下:
定义绑定变量

SQL> var x varchar2(20)
SQL> exec :x:='1';

PL/SQL procedure successfully completed.

执行查询

SQL> select * from t1 where c1=:x;

C1
--------------------
1

查看语句的执行计划

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 0
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1629967410

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("C1"=:X)


18 rows selected.

查询语句的hash_value与sql_id

SQL> select hash_value, child_number, sql_text,sql_id from v$sql where sql_text like 'select * from t1%';

HASH_VALUE CHILD_NUMBER  SQL_TEXT                                                                          SQL_ID
---------- ------------  --------------------------------------------------------------------------------  -------------
1607074836            0  select * from t1 where c1=:x                                                      0m63029gwn10n

使用游标来创建outline

SQL> exec dbms_outln.create_outline(1607074836,0);

PL/SQL procedure successfully completed.

查看outline是否创建成功

SQL> select name,owner,category,used from dba_outlines;

NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                  DEFAULT                        UNUSED

查询outline的hint可以看到有index hint，这说明使用了索引

SQL> select * from dba_outline_hints where name='SYS_OUTLINE_16060115381869401';

NAME                           OWNER                                NODE  STAGE      JOIN_POS   HINT
------------------------------ ------------------------------ ----------  ---------- ---------- --------------------------------------------------------------------------------
SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          1   INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   OUTLINE_LEAF(@"SEL$1")

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   ALL_ROWS

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   OPT_PARAM('optimizer_index_caching' 90)

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   OPT_PARAM('optimizer_index_cost_adj' 20)

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   IGNORE_OPTIM_EMBEDDED_HINTS


7 rows selected.

启用outline并重新执行sql语句

SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> select * from t1 where c1=:x;

C1
--------------------
1

查询使用outline后的执行计划，确实是使用的索引范围扫描与游标中的执行计划一致

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 1
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1629967410

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("C1"=:X)

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


22 rows selected.

Oracle 11.2.0.4的测试如下:
定义绑定变量

SQL> var x number
SQL> exec :x:=1

PL/SQL procedure successfully completed.

执行查询

SQL> select * from t1 where c1=:x;

        C1
----------
         1

查看执行计划

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 0
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("C1"=:X)


18 rows selected.

查询语句的hash_value,child_number,sql_id

SQL> select hash_value, child_number, sql_text,sql_id from v$sql where sql_text like 'select * from t1%';

HASH_VALUE CHILD_NUMBER SQL_TEXT                                                                         SQL_ID
---------- ------------ -------------------------------------------------------------------------------- -------------
1607074836            0 select * from t1 where c1=:x                                                     0m63029gwn10n

使用游标来创建outline

SQL> exec dbms_outln.create_outline(1607074836,0);

PL/SQL procedure successfully completed.

查看outline是否创建成功

SQL> select name,owner,category,used from dba_outlines;

NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
SYS_OUTLINE_16060115345355101  JY                             DEFAULT                        UNUSED

查询outline的hint信息可以看到index hint信息这说明使用了索引

SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060115345355101';

NAME                           OWNER                                NODE   STAGE      JOIN_POS   HINT
------------------------------ ------------------------------ ----------   ---------- ---------- --------------------------------------------------------------------------------
SYS_OUTLINE_16060115345355101  JY                                      1         1          1    INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    OUTLINE_LEAF(@"SEL$1")

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    ALL_ROWS

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    DB_VERSION('11.2.0.4')

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    IGNORE_OPTIM_EMBEDDED_HINTS


6 rows selected.

启用outline并重新执行SQL语句

SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> select * from t1 where c1=:x;

        C1
----------
         1

查询使用outline后的执行计划使用了索引，与游标中的执行计划一致

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 1
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("C1"=:X)

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


22 rows selected.

从测试结果来看，要在10.2.0.5中创建outline固定执行计划不要使用dbms_outln.create_outline这种方法，因为这种方法生成了outline所包含的执行计划并不正确。

发表评论

电子邮件地址不会被公开。

Proudly powered by WordPress | Indrajeet by Sus Hill.