Oracle利用coe_load_sql_profile脚本绑定执行计划
coe_load_sql_profile_v2.sql脚本利用的是profile原理，只是做了半自动的形式来使用，下面是测试过程。
coe_load_sql_profile_v2.txt
创建环境，构建测试表：
SQL> create table t1 as select * from dba_objects where object_id is not null;
Table created.
SQL> alter table t1 modify object_id not null;
Table altered.
SQL> create index idx_t1_obj_id on t1(object_id);
Index created.
索引不存储null值
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> select count(*) from t1;
COUNT(*)
----------
87070
SQL> select * from table(dbms_xplan.display_cursor(null,0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 12c0v4my7dvr3, child number 0
-------------------------------------
select count(*) from t1
Plan hash value: 1657298618
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 54 (100)| |
| 1 | SORT AGGREGAT | | 1 | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| 2 | INDEX FAST FULL SCAN| IDX_T1_OBJ_ID | 87070 | 54 (0)| 00:00:01 |
-------------------------------------------------------------------------------
14 rows selected.
会发现是进行的索引快速扫描。
使用hint提示，强行走全表扫描，然后生成执行计划。
SQL> select /*+ full(t1) */ count(*)from t1;
COUNT(*)
----------------------
87070
SQL> select * from table(dbms_xplan.display_cursor(null,0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
SQL_ID 20pat9zfypprh, child number 0
-------------------------------------
select /*+ full(t1) */ count(*)from t1
Plan hash value: 3724264953
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 347 (100)| |
| 1 | SORT AGGREGATE | | 1| | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
| 2 | TABLE ACCESS FULL| T1 | 87070 | 347 (1)| 00:00:05 |
-------------------------------------------------------------------
14 rows selected.
SQL> @coe_load_sql_profile_v2.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: 12c0v4my7dvr3
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1657298618 .141
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 3724264953
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "12c0v4my7dvr3"
PLAN_HASH_VALUE: "3724264953"
SQL>BEGIN
2 IF :sql_text IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
SQL>BEGIN
2 IF :other_xml IS NULL THEN
3 RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
4 END IF;
5 END;
6 /
SQL>SET TERM OFF;
Execute coe_xfr_sql_profile_12c0v4my7dvr3_3724264953.sql
on TARGET system in order to create a custom SQL Profile
with plan 3724264953 linked to adjusted sql_text.
.........省略
COE_XFR_SQL_PROFILE_12c0v4my7dvr3_3724264953 completed
COE_XFR_SQL_PROFILE completed.
SQL>explain plan for select count(*) from t1;
Explained.
SQL>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 3724264953
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 347 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T1 | 87070 | 347 (1)| 00:00:05 |
-------------------------------------------------------------------
Note
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
-----
- SQL profile "coe_12c0v4my7dvr3_3724264953" used for this statement
13 rows selected.
SQL> select name,category,status,sql_text from dba_sql_profiles;
NAME CATEGORY STATUS SQL_TEXT
------------------------------------------- --------------------- ---------------- ------------------------------
coe_12c0v4my7dvr3_3724264953 DEFAULT ENABLED select count(*) from t1
可以看到脚本coe_load_sql_profile_v2.sql将执行计划固定了。
