coe

coe

假设生产库有一个sql （select * from test where id=:1;），执行计划如下，我认为它走索引比较合适，但是无论如何加 hint ，它始终还是走全表扫。

SQL>  select * from table(dbms_xplan.display_cursor);  PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f5ug8jqf4msr1, child number 0
-------------------------------------
select * from test where id=1Plan hash value: 1357081020--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST |  1024 | 20480 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("ID"=1)

但是在测试环境 使用 explain plan for select * from test where id=:1; 发现该sql 走索引 ，但是测试环境的v$sql 里面却没有sql_id (f5ug8jqf4msr1) 信息 。

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST   |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_ID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

这时候我用常量替换sql中的变量，在测试库执行，得到的sql_id 及执行计划如下，该执行计划是我想要的。

SQL>  select * from table(dbms_xplan.display_cursor);  PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  41zg1jt50tv8m, child number 0
-------------------------------------
select * from test where id=2Plan hash value: 3297604684----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST   |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_ID |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

在测试环境使用脚本 coe_xfr_sql_profile.sql，生成文件coe_xfr_sql_profile_41zg1jt50tv8m_3297604684.sql

SQL> @coe_xfr_sql_profile.sqlParameter 1:
SQL_ID (required)Enter value for 1: 41zg1jt50tv8mPLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------3297604684        .005Parameter 2:
PLAN_HASH_VALUE (required)Enter value for 2: 3297604684Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "41zg1jt50tv8m"
PLAN_HASH_VALUE: "3297604684"
…………
…………
…………

在生产环境使用脚本 coe_xfr_sql_profile.sql，生成文件coe_xfr_sql_profile_f5ug8jqf4msr1_1357081020.sql

SQL> @coe_xfr_sql_profile.sql;Parameter 1:
SQL_ID (required)Enter value for 1: f5ug8jqf4msr1PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------1357081020        .039Parameter 2:
PLAN_HASH_VALUE (required)Enter value for 2: 1357081020Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "f5ug8jqf4msr1"
PLAN_HASH_VALUE: "1357081020"…………
…………

我们发现生成的文件都有类似下面的一段代码

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('19.1.0')]',
q'[DB_VERSION('19.1.0')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX_RS_ASC(@"SEL$1" "TEST"@"SEL$1" ("TEST"."ID"))]',
q'[BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "TEST"@"SEL$1")]',
q'[END_OUTLINE_DATA]');

将 coe_xfr_sql_profile_f5ug8jqf4msr1_1357081020.sql （生产环境）中的该段代码用 coe_xfr_sql_profile_41zg1jt50tv8m_3297604684.sql（测试环境）中的替换，最后在生产环境执行coe_xfr_sql_profile_f5ug8jqf4msr1_1357081020.sql，保证运行成功。

查看v$sql 视图，我们发现sql 使用 profile（ coe_f5ug8jqf4msr1_1357081020 ），并且PLAN_HASH_VALUE 变成了测试环境sql 的PLAN_HASH_VALUE 了。

SQL>  select EXECUTIONS,plan_hash_value,sql_profile  from v$sql where sql_id='f5ug8jqf4msr1';EXECUTIONS PLAN_HASH_VALUE
---------- ---------------
SQL_PROFILE
----------------------------------------------------------------1      3297604684
coe_f5ug8jqf4msr1_1357081020

执行计划确实走索引了 

Plan hash value: 3297604684----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |       |       |     6 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST   |  1024 | 20480 |     6   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_ID |  1024 |       |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("ID"=1)Note
------ dynamic statistics used: dynamic sampling (level=2)- SQL profile coe_f5ug8jqf4msr1_1357081020 used for this statement