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

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所包含的执行计划并不正确。