案例：
巡检发现一个sql的执行计划有问题，但是该sql历史只有一个执行计划，常规的只使用coe_xfr_sql_profile.sql脚本绑定，已经无法使用，因为只有一个执行计划，因此我们需要利用“偷梁换柱”来使用 coe_xfr_sql_profile.sql脚本绑定
分析：
sql:
select count(*)
from (select TXX_SEQ_NO, CUXXXXER_NO, xxxxxxx_NO, xxxxxxx_TYPE,
TXX_TIME, BUSINESS_TYPE, TXX_TYPE, TXX_DSCPT,
TXX_CHANNEL, ACCEPT_ORG_COXX, ACCEPT_ORG_TYPE, TXX_AMT, BEFORE_AMT, AFTER_AMT,
AREA_COXX, CITY_COXX, TRANS_SEQ_TYPE, SSSSDDDD_OBJ_NO,
SSSSDDDD_OBJ_TYPE RESV_FLD1, RESV_FLD2, RESV_FLD3,
RESV_FLD4, RESV_FLD5 from t_log_xxxxxxx_sssssss
union all
select TXX_SEQ_NO, CUXXXXER_NO, xxxxxxx_NO, xxxxxxx_TYPE,
TXX_TIME, BUSINESS_TYPE, TXX_TYPE, TXX_DSCPT,
TXX_CHANNEL, ACCEPT_ORG_COXX, ACCEPT_ORG_TYPE, TXX_AMT,
BEFORE_AMT, AFTER_AMT, AREA_COXX, CITY_COXX,
TRANS_SEQ_TYPE, SSSSDDDD_OBJ_NO, SSSSDDDD_OBJ_TYPE RESV_FLD1,
RESV_FLD2, RESV_FLD3, RESV_FLD4, RESV_FLD5
from t_log_xxxxxxx_sssssss_his)
where CUXXXXER_NO = :1 and xxxxxxx_NO = :2 and TXX_TIME >= :3 and TXX_TIME < :4

历史执行情况：
Enter value for sql_id: d1q2wdycus5r4
DTIME SNAP_ID I_N PLAN_HASH_VALUE USERNAME EXECUTIONS ROWSP ROWSPEXEC GETSPEXEC READSPEXEC CPUTPEXEC ELAPTPEXEC
------------------------- ---------- --- --------------- ------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
11-APR-16 08.00.12.045 AM 27893 2 1208602768 BTUPAYPROD 3 3 1 31956974.7 11046.33 141.844 967.771
11-APR-16 08.00.11.868 AM 27893 1 1208602768 BTUPAYPROD 3 3 1 31517169.7 417612.33 132.173 958.991
11-APR-16 05.00.13.577 AM 27890 1 1208602768 BTUPAYPROD 2 1 .5 32418049 516206.5 121.216 736.167
11-APR-16 05.00.13.406 AM 27890 2 1208602768 BTUPAYPROD 1 1 1 32428417 90210 151.16 734.354
11-APR-16 03.00.02.257 AM 27888 2 1208602768 BTUPAYPROD 6 5 .83 29782084.8 12563 102.745 776.79
11-APR-16 03.00.02.104 AM 27888 1 1208602768 BTUPAYPROD 6 4 .67 30289906.8 167780.17 100.332 783.961
11-APR-16 01.00.33.959 AM 27886 2 1208602768 BTUPAYPROD 4 4 1 31716563 15529.75 116.485 793.939
11-APR-16 01.00.33.791 AM 27886 1 1208602768 BTUPAYPROD 2 2 1 32614937.5 491051 128.52 829.446

执行计划:
Plan hash value: 1208602768
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 37 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | VIEW | | 2 | 74 | 8 (0)| 00:00:01 | | |
| 4 | UNION-ALL | | | | | | | |
|* 5 | FILTER | | | | | | | |
| 6 | PARTITION RANGE ITERATOR | | 1 | 45 | 4 (0)| 00:00:01 | KEY | KEY |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| T_LOG_xxxxxxx_SSSSDDD | 1 | 45 | 4 (0)| 00:00:01 | KEY | KEY |
|* 8 | INDEX RANGE SCAN | I_xxxxxxx_SSSSDDDHIS_1 | 1 | | 3 (0)| 00:00:01 | KEY | KEY |
|* 9 | FILTER | | | | | | | |
| 10 | PARTITION RANGE ITERATOR | | 1 | 45 | 4 (0)| 00:00:01 | KEY | KEY |
|* 11 | TABLE ACCESS BY LOCAL INDEX ROWID| T_LOG_xxxxxxx_SSSSDDD_HIS | 1 | 45 | 4 (0)| 00:00:01 | KEY | KEY |
|* 12 | INDEX RANGE SCAN | I_xxxxxxx_SSSSDDDHIS_TXXTIME6 | 1 | | 3 (0)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:3<:4)
5 - filter(:3<:4)

PLAN_TABLE_OUTPUT
7 - filter(("xxxxxxx_NO"=:2 AND "CUXXXXER_NO"=:1))
8 - access("TXX_TIME">=:3 AND "TXX_TIME"<:4)
9 - filter(:3<:4)
11 - filter(("xxxxxxx_NO"=:2 AND "CUXXXXER_NO"=:1))
12 - access("TXX_TIME">=:3 AND "TXX_TIME"<:4)
索引信息:
INDEX_OWNER INDEX_NAME COLUMN_NAME COLUMN_POSITION DESC TABLE_NAME
------------- ------------------------------ ------------------------- --------------- ---- ------------------------------
T_LOG_xxxxxxx_SSSSDDDD_HIS：
BTUPAYPROD I_xxxxxxx_SSSSDDDDHIS_xxxxxxx6 xxxxxxx_NO 1 ASC T_LOG_xxxxxxx_SSSSDDDD_HIS
BTUPAYPROD I_xxxxxxx_SSSSDDDDHIS_TXXTIME6 TXX_TIME 1 ASC T_LOG_xxxxxxx_SSSSDDDD_HIS
BTUPAYPROD I_LOG_A_P_H_CT1 CUXXXXER_NO 1 ASC T_LOG_xxxxxxx_SSSSDDDD_HIS
BTUPAYPROD I_LOG_A_P_H_CT1 TXX_TIME 2 ASC T_LOG_xxxxxxx_SSSSDDDD_HIS
T_LOG_xxxxxxx_SSSSDDDD：
BTUPAYPROD I_xxxxxxx_SSSSDDDDHIS_1 TXX_TIME 1 ASC T_LOG_xxxxxxx_SSSSDDDD
BTUPAYPROD I_xxxxxxx_SSSSDDDDHIS_xxxxxxx4 xxxxxxx_NO 1 ASC T_LOG_xxxxxxx_SSSSDDDD
BTUPAYPROD I_xxxxxxx_SSSSDDDD_CLEARING_1 CLEARING_TXX_SEQ_NO 1 ASC T_LOG_xxxxxxx_SSSSDDDD
BTUPAYPROD I_LOG_A_P_H_CT_1 CUXXXXER_NO 1 ASC T_LOG_xxxxxxx_SSSSDDDD
BTUPAYPROD I_LOG_A_P_H_CT_1 TXX_TIME 2 ASC T_LOG_xxxxxxx_SSSSDDDD
BTUPAYPROD PK_T_LOG_xxxxxxx_SSSSDDDD1 TXX_SEQ_NO 1 ASC T_LOG_xxxxxxx_SSSSDDDD
从索引信息结合sql的谓词条件我们很容看出，该sql应该使用（CUXXXXER_NO，TXX_TIME）的组合索引
表T_LOG_xxxxxxx_SSSSDDDD_HIS 按TXX_TIME月分区，T_LOG_xxxxxxx_SSSSDDDD 按TXX_TIME天分区
现在的执行计划走的都是TXX_TIME时间字段上的单索引，因此我怀疑又出现了谓词越界的情况，
T_LOG_xxxxxxx_SSSSDDDD_HIS 2016-02-26 09:11:12
T_LOG_xxxxxxx_SSSSDDDD 2016-03-26 09:18:56
通过抓取绑定变量的值我们可以发现确实如此，查询的是4月份的数据，而此2个表的统计信息收集时间均很老

到此，我们便可以知道解决此问题的方法：
（一）绑定正确的执行计划：
优点：立即生效，改sql的执行计划不在依赖统计信息是否及时收集
缺点：只对该sql有效，涉及这2个表的其他sql 可能执行计划还有问题
（二）收集统计信息：
优点：涉及这2个表的其他sql 有问题的执行计划有可能都修正了
缺点：后续的sql，还可能会因为统计信息的不及时收集，出现此问题。

针对此问题解决方法：
绑定执行计划
（一）sql profile：
A:通过hint等生成正确的执行计划：
set autot traceonly exp
select /*gtt*/ count(*)
from (select /*+ index(t_log_xxxxxxx_sssssss I_LOG_A_P_H_CT_1)*/ TXX_SEQ_NO,
CUXXXXER_NO, xxxxxxx_NO, xxxxxxx_TYPE, TXX_TIME,
BUSINESS_TYPE, TXX_TYPE, TXX_DSCPT, TXX_CHANNEL,
ACCEPT_ORG_COXX, ACCEPT_ORG_TYPE, TXX_AMT, BEFORE_AMT,
AFTER_AMT, AREA_COXX, CITY_COXX, TRANS_SEQ_TYPE,
SSSSDDDD_OBJ_NO, SSSSDDDD_OBJ_TYPE RESV_FLD1, RESV_FLD2, RESV_FLD3,
RESV_FLD4, RESV_FLD5
from t_log_xxxxxxx_sssssss t
union all
select TXX_SEQ_NO, CUXXXXER_NO, xxxxxxx_NO, xxxxxxx_TYPE,
TXX_TIME, BUSINESS_TYPE, TXX_TYPE, TXX_DSCPT,
TXX_CHANNEL, ACCEPT_ORG_COXX, ACCEPT_ORG_TYPE, TXX_AMT,
BEFORE_AMT, AFTER_AMT, AREA_COXX, CITY_COXX,
TRANS_SEQ_TYPE, SSSSDDDD_OBJ_NO, SSSSDDDD_OBJ_TYPE RESV_FLD1,
RESV_FLD2, RESV_FLD3, RESV_FLD4, RESV_FLD5
from t_log_xxxxxxx_sssssss_his)
where CUXXXXER_NO = '3177000490818608'
and xxxxxxx_NO = '7111596045919156'
and TXX_TIME >= to_timestamp('20160401','yyyymmdd')
and TXX_TIME < to_timestamp('20160411','yyyymmdd');
---------------------------------------------------------------------------------------------------------------
Plan hash value: 3013354339
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 26 (100)| | | |
| 1 | SORT AGGREGATE | | 1 | 37 | | | | |
| 2 | VIEW | | 2 | 74 | 26 (0)| 00:00:01 | | |
| 3 | UNION-ALL | | | | | | | |
| 4 | PARTITION RANGE ITERATOR | | 1 | 45 | 22 (0)| 00:00:01 | 430 | 439 |
|* 5 | TABLE ACCESS BY LOCAL INDEX ROWID| T_LOG_xxxxxxx_SSSSDDD | 1 | 45 | 22 (0)| 00:00:01 | 430 | 439 |
|* 6 | INDEX RANGE SCAN | I_LOG_A_P_H_CT_1 | 1 | | 21 (0)| 00:00:01 | 430 | 439 |
| 7 | PARTITION RANGE SINGLE | | 1 | 45 | 4 (0)| 00:00:01 | 17 | 17 |
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID| T_LOG_xxxxxxx_SSSSDDD_HIS | 1 | 45 | 4 (0)| 00:00:01 | 17 | 17 |
|* 9 | INDEX RANGE SCAN | I_LOG_A_P_H_CT1 | 1 | | 3 (0)| 00:00:01 | 17 | 17 |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("xxxxxxx_NO"='7111596045919156')
6 - access("CUXXXXER_NO"='3177000490818608' AND "TXX_TIME">=TIMESTAMP' 2016-04-01 00:00:00.000000000' AND
"TXX_TIME"<TIMESTAMP' 2016-04-11 00:00:00.000000000')
8 - filter("xxxxxxx_NO"='7111596045919156')
9 - access("CUXXXXER_NO"='3177000490818608' AND "TXX_TIME">=TIMESTAMP' 2016-04-01 00:00:00.000000000' AND
"TXX_TIME"<TIMESTAMP' 2016-04-11 00:00:00.000000000')
SELECT * FROM gV$sql where sql_text like '%/*gt5*/%index%';--93rfq9yt10cxu
B:通过coe_xfr_sql_profile.sql 生成各自的脚本文件：
APPADMIN@ spaydbwr1>@coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: 93rfq9yt10cxu
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
3013354339 .261
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 3013354339
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "93rfq9yt10cxu"
PLAN_HASH_VALUE: "3013354339"
Execute coe_xfr_sql_profile_93rfq9yt10cxu_3013354339.sql
---------------------------------------------------------------------------------------
SQL>@coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: d1q2wdycus5r4
pLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1208602768 585.925
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1208602768
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "d1q2wdycus5r4"
PLAN_HASH_VALUE: "1208602768"
Execute coe_xfr_sql_profile_d1q2wdycus5r4_1208602768.sql
C:偷梁换柱：
把coe_xfr_sql_profile_93rfq9yt10cxu_3013354339.sql 里面：

替换coe_xfr_sql_profile_d1q2wdycus5r4_1208602768.sql

把coe_xfr_sql_profile_d1q2wdycus5r4_1208602768.sql里面 force_match => TRUE
D：最后执行：coe_xfr_sql_profile_d1q2wdycus5r4_1208602768.sql
E : 检查sql的执行计划是否修订：
SELECT * FROM dba_sql_profiles where name like '%d1q2wdycus5r4%';
select s.INST_ID,s.sql_id, s.child_number , s.plan_hash_value,s.EXECUTIONS,a.VERSION_COUNT ,a.OPEN_VERSIONS,s.BUFFER_GETS/s.EXECUTIONS avgBuf ,s.DISK_READS/s.EXECUTIONS avgRead,
s.ELAPSED_TIME /s.EXECUTIONS / 1000 timeexec, s.first_LOAD_TIME ,s.last_load_time,s.LAST_ACTIVE_TIME ,s.SQL_PROFILE profile,s.SQL_PLAN_BASELINE baseline
from gV$sql s ,GV$sqlarea a where a.sql_id = '&sql_id' and a.sql_id=s.sql_id and s.inst_id=a.inst_id order by 1, s. last_load_time ,s.inst_id；

（二）spm：
A: 针对目标sql 使用DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE 手工生成其指定的初始话 sql plan baseline：
set serveroutput on long 100000
declare
ints int;
begin
ints := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id =>'d1q2wdycus5r4',plan_hash_value =>'1208602768');
dbms_output.put_line(ints);
end;
/
select sql_id,SQL_PLAN_BASELINE from V$sql where sql_id='d1q2wdycus5r4';
SQL_ID SQL_PLAN_BASELINE
------------- ------------------------------
d1q2wdycus5r4 SQL_PLAN_9cwztjtg756xcf9426629
select sql_handle from dba_sql_plan_baselines where plan_name='SQL_PLAN_9cwztjtg756xcf9426629';
SQL_9673f98e5e729bac

B:通过加入hints等措施生成正确的执行计划，并找到对应的sql（我们称为中间sql gtcy1xu5nmxrk 1779766307）
set serveroutput on long 100000
declare
ints int;
begin
ints := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id =>'93rfq9yt10cxu',plan_hash_value =>'3013354339',
sql_handle=>'SQL_9673f98e5e729bac');
dbms_output.put_line(ints);
end;
/
C:删除A步骤中生成的 sql plan baseline ：
set serveroutput on long 100000
declare
ints int;
begin
ints := DBMS_SPM.DROP_SQL_PLAN_BASELINE( sql_handle=>'SQL_9673f98e5e729bac',plan_name=>'SQL_PLAN_9cwztjtg756xcf9426629');
dbms_output.put_line(ints);
end;
/

处理该分区列上的时间索引：
with obj as (select object_id from dba_objects
where object_name = upper('&objname') and object_type = upper('&objtype'))
select distinct * from (
SELECT sql_id,plan_hash_value
FROM V$sql_plan where object# in ( select * from obj)
union all
SELECT sql_id,plan_hash_value
FROM dba_hist_sql_plan where object# in ( select * from obj));
通过以上sql检查使用该索引的执行计划，观察该索引是否可以删除，若可以先invisible，观察一段时间后再drop；

实际案列小插曲：
原sql没有别名
而自己hint出来的sql 对表添加了别名绑定完毕后
dba_sql_profiles本身没有问题并且查询sql执行计划发现note显示使用到了这个sql_profile
只不过sql实际上用不了这个sql_profile所指定的执行计划（原因是outline信息中出现了别名无法识别）
建议：
通过hints改sql时若原始的sql 表名没有使用别名，则加hints的sql 也不要使用

ITPub博客

绑定执行计划案例

最新文章