假设生产库有一个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