测试目的：
1，假设下面的两个语句分别为A语句和B语句：
A语句： select count(*) from lunar where extent_id=1;
B语句： select /*+ FULL(lunar) */ count(*) from lunar where extent_id=1;
2，使用coe的脚本（在SQLT中可以知道整套coe脚本）手工为A语句指定B语句的执行计划，看看什么效果
（即，让原本正常走索引的A语句使用全表扫描的B语句的执行计划）
3，使用dbms_sqltune.import_sql_profile为A语句指定执行计划，看看什么效果
首先，做一个测试表：
[oracle@lunar /]$ ss SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 7 21:29:52 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Welcome Lunar's oracle world! Love you , baby ! Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning and Real Application Testing options SYS@lunar>conn lunar/lunar Welcome Lunar's oracle world! Love you , baby ! Connected. LUNAR@lunar>create table lunar as select * from dba_extents; Table created. Elapsed: 00:00:04.61
在extent_id列上创建非唯一索引：
LUNAR@lunar>create index idx_lunar_extent_id on lunar(extent_id) ; Index created. Elapsed: 00:00:00.02 LUNAR@lunar>EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'LUNAR',TABNAME => 'LUNAR',DEGREE => 5, 2 CASCADE => TRUE,ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE); PL/SQL procedure successfully completed. Elapsed: 00:00:01.45
可以看到下面的语句正确的使用了索引（把这条语句称之为A语句）：
LUNAR@lunar>select count(*) from lunar where extent_id=1;
COUNT(*)
----------
323
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1050880347 ---这里是A语句的执行计划的plan hash value
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | INDEX RANGE SCAN| IDX_LUNAR_EXTENT_ID | 84 | 252 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EXTENT_ID"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LUNAR@lunar>
我使用hint指定该语句必须走全表扫描，姑且把这条语句称为B语句：
LUNAR@lunar>select /*+ FULL(lunar) */ count(*) from lunar where extent_id=1;
COUNT(*)
----------
323
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 291688323 ---这里是B语句的执行计划的plan hash value
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 16 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| LUNAR | 84 | 252 | 16 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EXTENT_ID"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
50 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LUNAR@lunar>
找到A语句和B语句的sql_id:
LUNAR@lunar>select sql_id from v$sql where sql_text like '%FULL(lunar)%'; SQL_ID ------------- 078qb40kcsnnu 7f8h3z6pj9w52 05538tk3vkzu3 Elapsed: 00:00:00.10 LUNAR@lunar>select sql_id,sql_text from v$sql where sql_text like '%extent_id=1%'; SQL_ID ------------- SQL_TEXT ---------------------------------------------------------------------------------------------------------------------------------------------------- 078qb40kcsnnu EXPLAIN PLAN SET STATEMENT_ID='PLUS271153' FOR select /*+ FULL(lunar) */ count(*) from lunar where extent_id=1 39sr1xpxpkbmv select sql_id,sql_text from v$sql where sql_text like '%extent_id=1%' 05538tk3vkzu3 ---------注意，这里是B语句的sql_id select /*+ FULL(lunar) */ count(*) from lunar where extent_id=1 fvrmp2a2t38dc ---------注意，这里是A语句的sql_id select count(*) from lunar where extent_id=1 g4bcvwhrgradr EXPLAIN PLAN SET STATEMENT_ID='PLUS271153' FOR select count(*) from lunar where extent_id=1 g4bcvwhrgradr EXPLAIN PLAN SET STATEMENT_ID='PLUS271153' FOR select count(*) from lunar where extent_id=1 6 rows selected. Elapsed: 00:00:00.03 LUNAR@lunar>
使用coe的脚本来固定执行计划，291688323是B语句的执行计划：
LUNAR@lunar>@coe_xfr_sql_profile fvrmp2a2t38dc 291688323
Parameter 1:
SQL_ID (required)
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1050880347 .039
Parameter 2:
PLAN_HASH_VALUE (required)
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "fvrmp2a2t38dc"
PLAN_HASH_VALUE: "291688323"
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_fvrmp2a2t38dc_291688323.sql
on TARGET system in order to create a custom SQL Profile
with plan 291688323 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed.
SQL>@coe_xfr_sql_profile_fvrmp2a2t38dc_291688323.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_fvrmp2a2t38dc_291688323.sql 11.4.4.4 2014/06/08 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM coe_xfr_sql_profile_fvrmp2a2t38dc_291688323.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID fvrmp2a2t38dc based on plan hash
SQL>REM value 291688323.
SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM SQL> START coe_xfr_sql_profile_fvrmp2a2t38dc_291688323.sql;
SQL>REM
SQL>REM NOTES
SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_fvrmp2a2t38dc_291688323');
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM for the Oracle Tuning Pack.
SQL>REM 6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM By doing so you can create a custom SQL Profile for the original
SQL>REM SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 PROCEDURE wa (p_line IN VARCHAR2) IS
5 BEGIN
6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
7 END wa;
8 BEGIN
9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
11 -- SQL Text pieces below do not have to be of same length.
12 -- So if you edit SQL Text (i.e. removing temporary Hints),
13 -- there is no need to edit or re-align unmodified pieces.
14 wa(q'[select count(*) from lunar where extent_id=1]');
15 DBMS_LOB.CLOSE(sql_txt);
16 h := SYS.SQLPROF_ATTR(
17 q'[BEGIN_OUTLINE_DATA]',
18 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
19 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
20 q'[DB_VERSION('11.2.0.3')]',
21 q'[ALL_ROWS]',
22 q'[OUTLINE_LEAF(@"SEL$F5BB74E1")]',
23 q'[MERGE(@"SEL$2")]',
24 q'[OUTLINE(@"SEL$1")]',
25 q'[OUTLINE(@"SEL$2")]',
26 q'[FULL(@"SEL$F5BB74E1" "LUNAR"@"SEL$2")]',
27 q'[END_OUTLINE_DATA]');
28 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
29 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
30 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
31 sql_text => sql_txt,
32 profile => h,
33 name => 'coe_fvrmp2a2t38dc_291688323',
34 description => 'coe fvrmp2a2t38dc 291688323 '||:signature||' '||:signaturef||'',
35 category => 'DEFAULT',
36 validate => TRUE,
37 replace => TRUE,
38 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
39 DBMS_LOB.FREETEMPORARY(sql_txt);
40 END;
41 /
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
12989549146262258752
SIGNATUREF
---------------------
11568928226314596944
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_fvrmp2a2t38dc_291688323 completed
SQL>
根据提示执行COE_XFR_SQL_PROFILE_fvrmp2a2t38dc_291688323.sql脚本:
SQL>@coe_xfr_sql_profile_fvrmp2a2t38dc_291688323.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_fvrmp2a2t38dc_291688323.sql 11.4.4.4 2014/06/08 carlos.sierra $
SQL>REM
SQL>REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL>REM
SQL>REM AUTHOR
SQL>REM carlos.sierra@oracle.com
SQL>REM
SQL>REM SCRIPT
SQL>REM coe_xfr_sql_profile_fvrmp2a2t38dc_291688323.sql
SQL>REM
SQL>REM DESCRIPTION
SQL>REM This script is generated by coe_xfr_sql_profile.sql
SQL>REM It contains the SQL*Plus commands to create a custom
SQL>REM SQL Profile for SQL_ID fvrmp2a2t38dc based on plan hash
SQL>REM value 291688323.
SQL>REM The custom SQL Profile to be created by this script
SQL>REM will affect plans for SQL commands with signature
SQL>REM matching the one for SQL Text below.
SQL>REM Review SQL Text and adjust accordingly.
SQL>REM
SQL>REM PARAMETERS
SQL>REM None.
SQL>REM
SQL>REM EXAMPLE
SQL>REM SQL> START coe_xfr_sql_profile_fvrmp2a2t38dc_291688323.sql;
SQL>REM
SQL>REM NOTES
SQL>REM 1. Should be run as SYSTEM or SYSDBA.
SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege.
SQL>REM 3. SOURCE and TARGET systems can be the same or similar.
SQL>REM 4. To drop this custom SQL Profile after it has been created:
SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_fvrmp2a2t38dc_291688323');
SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license
SQL>REM for the Oracle Tuning Pack.
SQL>REM 6. If you modified a SQL putting Hints in order to produce a desired
SQL>REM Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL>REM By doing so you can create a custom SQL Profile for the original
SQL>REM SQL but with the Plan captured from the modified SQL (with Hints).
SQL>REM
SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL>REM
SQL>VAR signature NUMBER;
SQL>VAR signaturef NUMBER;
SQL>REM
SQL>DECLARE
2 sql_txt CLOB;
3 h SYS.SQLPROF_ATTR;
4 PROCEDURE wa (p_line IN VARCHAR2) IS
5 BEGIN
6 DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
7 END wa;
8 BEGIN
9 DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
10 DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
11 -- SQL Text pieces below do not have to be of same length.
12 -- So if you edit SQL Text (i.e. removing temporary Hints),
13 -- there is no need to edit or re-align unmodified pieces.
14 wa(q'[select count(*) from lunar where extent_id=1]');
15 DBMS_LOB.CLOSE(sql_txt);
16 h := SYS.SQLPROF_ATTR(
17 q'[BEGIN_OUTLINE_DATA]',
18 q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
19 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
20 q'[DB_VERSION('11.2.0.3')]',
21 q'[ALL_ROWS]',
22 q'[OUTLINE_LEAF(@"SEL$F5BB74E1")]',
23 q'[MERGE(@"SEL$2")]',
24 q'[OUTLINE(@"SEL$1")]',
25 q'[OUTLINE(@"SEL$2")]',
26 q'[FULL(@"SEL$F5BB74E1" "LUNAR"@"SEL$2")]',
27 q'[END_OUTLINE_DATA]');
28 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
29 :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
30 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
31 sql_text => sql_txt,
32 profile => h,
33 name => 'coe_fvrmp2a2t38dc_291688323',
34 description => 'coe fvrmp2a2t38dc 291688323 '||:signature||' '||:signaturef||'',
35 category => 'DEFAULT',
36 validate => TRUE,
37 replace => TRUE,
38 force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
39 DBMS_LOB.FREETEMPORARY(sql_txt);
40 END;
41 /
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
12989549146262258752
SIGNATUREF
---------------------
11568928226314596944
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_fvrmp2a2t38dc_291688323 completed
再次查询，验证一下A语句是否已经按照我们指定的那样采用了B语句的全表扫描的执行计划：
SQL>select count(*) from lunar where extent_id=1;
COUNT(*)
----------
323
Execution Plan
----------------------------------------------------------
Plan hash value: 1050880347
--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 1 | 3 | 1 (0)| 0
0:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | |
|
|* 2 | INDEX RANGE SCAN| IDX_LUNAR_EXTENT_ID | 84 | 252 | 1 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EXTENT_ID"=1)
Note
-----
- SQL profile "coe_fvrmp2a2t38dc_291688323" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
结论：我们的猜测是错误的，使用coe的脚本固定执行计划，必须是从该sql_id的已经有的所有执行计划中挑选其一，而不能凭空指定一个你认为合适的。
下面，我们使用其他方法固定SQL的执行计划。首先找出B语句的执行计划和outline：
SQL>set lines 1000
SQL>set pages 10000
SQL>select * from table(dbms_xplan.display_cursor('05538tk3vkzu3',null,'outline'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 05538tk3vkzu3, child number 0
-------------------------------------
select /*+ FULL(lunar) */ count(*) from lunar where extent_id=1
Plan hash value: 291688323
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 16 (100)| |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| LUNAR | 84 | 252 | 16 (0)| 00:00:01 |
----------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "LUNAR"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EXTENT_ID"=1)
33 rows selected.
SQL>
使用下面的脚本为A语句指定B语句的outline：
LUNAR@lunar>declare
2 v_hints sys.sqlprof_attr;
3 sql_txt clob;
4 begin
5 v_hints:=sys.sqlprof_attr(
6 'BEGIN_OUTLINE_DATA',
7 'IGNORE_OPTIM_EMBEDDED_HINTS',
8 'OPTIMIZER_FEATURES_ENABLE(''11.2.0.3'')',
9 'DB_VERSION(''11.2.0.3'')',
10 'ALL_ROWS',
11 'OUTLINE_LEAF(@"SEL$1")',
12 'FULL(@"SEL$1" "LUNAR"@"SEL$1")',
13 'END_OUTLINE_DATA'
14 );
15 select sql_fulltext into sql_txt from v$sql where sql_id='fvrmp2a2t38dc';
16 dbms_sqltune.import_sql_profile(sql_text => sql_txt,
17 profile => v_hints,name => 'SQLPROF_Lunar',
18 replace => TRUE,force_match => TRUE);
19 end;
20 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.19
LUNAR@lunar>
再次测试，发现语句A已经采用了我们指定的outline，即使用了B语句的全表扫描的执行计划：
LUNAR@lunar>select count(*) from lunar where extent_id=1;
COUNT(*)
----------
323
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 291688323
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 16 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|* 2 | TABLE ACCESS FULL| LUNAR | 84 | 252 | 16 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("EXTENT_ID"=1)
Note
-----
- SQL profile "SQLPROF_Lunar" used for this statement 注意，这里已经采用了SQLPROF_Lunar
Statistics
----------------------------------------------------------
10 recursive calls
0 db block gets
57 consistent gets
1 physical reads
0 redo size
527 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LUNAR@lunar>
结论：
1，使用coe的脚本固定执行计划，必须是从该sql_id的已经有的所有执行计划中挑选其一，而不能凭空指定一个你认为合适的。
因此，如果需要从某条sql已经执行过的执行计划中挑选一个合适的，那么coe的脚本简单轻巧，非常合适。
2，如果要手工构造一个该SQL语句从未使用的执行计划，可以采用dbms_sqltune.import_sql_profile的方式手工设置outline，从而改变执行计划。
在这种需求下，coe的脚本是无能为力的。
