首先看一個查詢：
sys@EBANK>selectobject_name from t where object_id between 1000 and 2000;

1001 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15384 | 345K| 533 (1)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| T | 15384 | 345K| 533 (1)| 00:00:07 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"<=2000 AND "OBJECT_ID">=1000)

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2387 consistent gets
0 physical reads
29996 redo size
27644 bytes sent via SQL*Net to client
1107 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1001 rows processed
對於這個查詢，由於表中統計資訊過舊，錯誤的選擇了全表掃描，加索引提示：

sys@EBANK>sys@EBANK>select/*+ index(t idx_t) */ object_name from t where object_id between 1000 and 2000;

1001 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15384 | 345K| 15421 (1)| 00:03:06 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 15384 | 345K| 15421 (1)| 00:03:06 |
|* 2 | INDEX RANGE SCAN | IDX_T | 15384 | | 35 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID">=1000 AND "OBJECT_ID"<=2000)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
203 consistent gets
0 physical reads
0 redo size
27644 bytes sent via SQL*Net to client
1107 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1001 rows processed

對於上述查詢，顯然加索引提示是合理的，而當無法修改應用時，我們需要從資料庫端來干預此sql的執行計劃，採用sql profile可以完成此功能
我們暫且把未加提示的sql稱之為sql_nohi,加了提示的sql稱之為sql_hi，執行如下儲存過程，該儲存過程的功能首先取得加sql_hi的outline資料，也就是一系列hints，
然後使用這些outline資料來對sql_nohi生成profile，也就是說讓sql_nohi來使用sql_hi的執行計劃:
declare
ar_hint_table sys.dbms_debug_vc2coll;
ar_profile_hints sys.sqlprof_attr := sys.sqlprof_attr();
cl_sql_text clob;
i pls_integer;
begin
with a as (
select
rownum as r_no
, a.*
from
table(
-- replace with
-- DBMS_XPLAN.DISPLAY_AWR
-- if required
dbms_xplan.display_cursor(
'&&good_sql_id'
, null
, 'OUTLINE'
)
-- dbms_xplan.display_awr(
-- '&&1'
-- , null
-- , null
-- , 'OUTLINE'
-- )
) a
),
b as (
select
min(r_no) as start_r_no
from
a
where
a.plan_table_output = 'Outline Data'
),
c as (
select
min(r_no) as end_r_no
from
a
, b
where
a.r_no > b.start_r_no
and a.plan_table_output = ' */'
),
d as (
select
instr(a.plan_table_output, 'BEGIN_OUTLINE_DATA') as start_col
from
a
, b
where
r_no = b.start_r_no + 4
)
select
substr(a.plan_table_output, d.start_col) as outline_hints
bulk collect
into
ar_hint_table
from
a
, b
, c
, d
where
a.r_no >= b.start_r_no + 4
and a.r_no <= c.end_r_no - 1
order by
a.r_no;

select
sql_text
into
cl_sql_text
from
-- replace with dba_hist_sqltext
-- if required for AWR based
-- execution
v$sql
-- sys.dba_hist_sqltext
where
sql_id = '&&bad_sql_id';

-- this is only required
-- to concatenate hints
-- splitted across several lines
-- and could be done in SQL, too
i := ar_hint_table.first;
while i is not null
loop
if ar_hint_table.exists(i + 1) then
if substr(ar_hint_table(i + 1), 1, 1) = ' ' then
ar_hint_table(i) := ar_hint_table(i) || trim(ar_hint_table(i + 1));
ar_hint_table.delete(i + 1);
end if;
end if;
i := ar_hint_table.next(i);
end loop;

i := ar_hint_table.first;
while i is not null
loop
ar_profile_hints.extend;
ar_profile_hints(ar_profile_hints.count) := ar_hint_table(i);
i := ar_hint_table.next(i);
end loop;

dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text
, profile => ar_profile_hints
, name => 'PROFILE_&&1'
-- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
, force_match => false
);
end;
/

此過程可以完成上述功能，測試如下：

首先取得兩條sql的sql_id：
sys@EBANK>selectsql_id,sql_text from v$sql where sql_text like ('select%from t where object_id between 1000 and 2000%');

SQL_ID SQL_TEXT
------------- ------------------------------------------------------------
dmn81fk77n9z6 select object_name from t where object_id between 1000 and 2
000

036w3z13k3wgh select /*+ index(t idx_t) */ object_name from t where object
_id between 1000 and 2000

執行gen_sql_profile.sql:

sys@EBANK>@gen_sql_profile.sql
Enter value for good_sql_id: 036w3z13k3wgh
old 17: '&&good_sql_id'
new 17: '036w3z13k3wgh'
Enter value for bad_sql_id: dmn81fk77n9z6
old 83: sql_id = '&&bad_sql_id';
new 83: sql_id = 'dmn81fk77n9z6';
Enter value for profile_name: test
old 112: , name => 'PROFILE_&profile_name'
new 112: , name => 'PROFILE_test'

PL/SQL procedure successfully completed.

此時再執行未加hints的sql:
sys@EBANK>setautot traceonlysys@EBANK>selectobject_name from t where object_id between 1000 and 2000;

1001 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15384 | 345K| 15421 (1)| 00:03:06 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 15384 | 345K| 15421 (1)| 00:03:06 |
|* 2 | INDEX RANGE SCAN | IDX_T | 15384 | | 35 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID">=1000 AND "OBJECT_ID"<=2000)

Note
-----
- SQL profile "PROFILE_test" used for this statement

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
203 consistent gets
0 physical reads
0 redo size
27644 bytes sent via SQL*Net to client
1106 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1001 rows processed

可以看到，oracle使用了sql profile，sql的執行計劃得以改變，對於上述sql profile，是sql_id相同的條件下生效的，改變一下where條件中的值：
sys@EBANK>execdbms_sqltune.drop_sql_profile('PROFILE_test');

PL/SQL procedure successfully completed.

sys@EBANK>@gen_sql_profile
Enter value for good_sql_id: 036w3z13k3wgh
old 17: '&&good_sql_id'
new 17: '036w3z13k3wgh'
Enter value for bad_sql_id: dmn81fk77n9z6
old 83: sql_id = '&&bad_sql_id';
new 83: sql_id = 'dmn81fk77n9z6';
Enter value for profile_name: TEST
old 112: , name => 'PROFILE_&profile_name'
new 112: , name => 'PROFILE_TEST'

PL/SQL procedure successfully completed.

sys@EBANK>setautot traceonly explainsys@EBANK>selectobject_name from t where object_id between 1000 and 2001;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15399 | 345K| 15436 (1)| 00:03:06 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 15399 | 345K| 15436 (1)| 00:03:06 |
|* 2 | INDEX RANGE SCAN | IDX_T | 15399 | | 35 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID">=1000 AND "OBJECT_ID"<=2001)

Note
-----
- SQL profile "PROFILE_TEST" used for this statement

sys@EBANK>selectobject_name from t where object_id between 1000 and 3000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30736 | 690K| 30809 (1)| 00:06:10 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 30736 | 690K| 30809 (1)| 00:06:10 |
|* 2 | INDEX RANGE SCAN | IDX_T | 30736 | | 69 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID">=1000 AND "OBJECT_ID"<=3000)

Note
-----
-SQL profile "PROFILE_TEST" used for this statement

可見，對於這種沒有使用繫結變數的sql，即使是where條件中的取值不同，仍然能夠使用sql profile，這是由於設定了force_match => true的原因。