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将执行计划固定了。
