[p=25, null, left][color=rgb(62, 62, 62)]案例：
巡检发现一个sql的执行计划有问题，但是该sql历史只有一个执行计划，常规的只使用coe_xfr_sql_profile.sql脚本绑定，已经无法使用，因为只有一个执行计划，因此我们需要利用“偷梁换柱”来使用 coe_xfr_sql_profile.sql脚本绑定[p=25, null, left][color=rgb(62, 62, 62)]分析：
sql:sql比较长放在文章底部[p=25, null, left][color=rgb(62, 62, 62)]历史执行情况：[p=25, null, left][color=rgb(62, 62, 62)][img]http://mmbiz.qpic.cn/mmbiz/VkC3lgPs6cZQmeynTV3DOjxr0LciakITNSKXEWJ4icibMXT7VQNV7N8SoaOR0hmhhwNXvs9jYfIBJIFUN7hEnbFTA/0?wx_fmt=jpeg[/img]

[p=25, null, left][color=rgb(62, 62, 62)]执行计划:[p=25, null, left][color=rgb(62, 62, 62)][img]http://mmbiz.qpic.cn/mmbiz/VkC3lgPs6cZQmeynTV3DOjxr0LciakITNxj74o83hBmORSOQoYMun52btuI9URxicReV2czjOtG8qOwiaMibcwYy4g/0?wx_fmt=jpeg[/img]
[p=25, null, left][color=rgb(62, 62, 62)][img]http://mmbiz.qpic.cn/mmbiz/VkC3lgPs6cZQmeynTV3DOjxr0LciakITNSVUJsjScUle4zaHupcLAdS3yD6bSzz381bktqHQ2OWuNYUDDqz31tA/0?wx_fmt=jpeg[/img]
[p=25, null, left][color=rgb(62, 62, 62)]索引信息:[p=25, null, left][color=rgb(62, 62, 62)][img]http://mmbiz.qpic.cn/mmbiz/VkC3lgPs6cZQmeynTV3DOjxr0LciakITNJibG3KuS9gEMYcOFyZUIzISmKGu8SGd7Jzu8OblxxUwWibcqvu5NQgEw/0?wx_fmt=jpeg[/img]
[p=25, null, left][color=rgb(62, 62, 62)][p=25, null, left][color=rgb(62, 62, 62)]从索引信息 结合sql的谓词条件 我们很容看出，该sql应该使用（CUSTOMER_NO，TXN_TIME）的组合索引[p=25, null, left][color=rgb(62, 62, 62)]表T_LOG_ACCOUNT_PAYMENT_HIS 按TXN_TIME月分区，T_LOG_ACCOUNT_PAYMENT 按TXN_TIME天分区
[p=25, null, left][color=rgb(62, 62, 62)]现在的执行计划走的都是TXN_TIME时间字段上的单索引，因此我怀疑又出现了 谓词越界的情况。[p=25, null, left][color=rgb(62, 62, 62)]T_LOG_ACCOUNT_PAYMENT_HIS 2016-02-26 09:11:12[p=25, null, left][color=rgb(62, 62, 62)]T_LOG_ACCOUNT_PAYMENT 2016-03-26 09:18:56[p=25, null, left][color=rgb(62, 62, 62)]通过抓取绑定变量的值 我们可以发现确实如此，查询的是4月份的数据，而此2个表的统计信息收集时间均很老：[p=25, null, left][color=rgb(62, 62, 62)][img]http://mmbiz.qpic.cn/mmbiz/VkC3lgPs6cZQmeynTV3DOjxr0LciakITNWcYmCSslyf2FiaNiawwtCibB0fWuaUlojtMMXXVKomNPP5Mic1z0mzmnBA/0?wx_fmt=jpeg[/img]
[p=25, null, left][color=rgb(62, 62, 62)]到此，我们便可以知道解决此问题的方法：[p=25, null, left][color=rgb(62, 62, 62)]（一）绑定正确的执行计划：[p=25, null, left][color=rgb(62, 62, 62)]优点：立即生效，改sql的执行计划不在依赖统计信息是否及时收集[p=25, null, left][color=rgb(62, 62, 62)]缺点：只对该sql有效，涉及这2个表的其他sql 可能执行计划还有问题 [p=25, null, left][color=rgb(62, 62, 62)]（二）收集统计信息：[p=25, null, left][color=rgb(62, 62, 62)][p=25, null, left][color=rgb(62, 62, 62)]优点：涉及这2个表的其他sql 有问题的执行计划有可能都修正了 [p=25, null, left][color=rgb(62, 62, 62)]缺点：后续的sql，还可能会因为统计信息的不及时收集，出现此问题。[p=25, null, left][color=rgb(62, 62, 62)]
[p=25, null, left][color=rgb(62, 62, 62)]针对此问题解决方法：[p=25, null, left][color=rgb(62, 62, 62)]绑定执行计划[p=25, null, left][color=rgb(62, 62, 62)]（一）sql profile：[p=25, null, left][color=rgb(62, 62, 62)]A:通过hint等生成正确的执行计划：[p=25, null, left][color=rgb(62, 62, 62)]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');[p=25, null, left][color=rgb(62, 62, 62)]正确的执行计划如下：[p=25, null, left][color=rgb(62, 62, 62)][img]http://mmbiz.qpic.cn/mmbiz/VkC3lgPs6cZQmeynTV3DOjxr0LciakITNBeWAVdjdpQx8aSdajq9AaWDia4ZCvgOiagu3QHYpRjjfVSgHF6cp1vWQ/0?wx_fmt=jpeg[/img] [p=25, null, left][color=rgb(62, 62, 62)][img]http://mmbiz.qpic.cn/mmbiz/VkC3lgPs6cZQmeynTV3DOjxr0LciakITNpYfh6EAYNbpQIZy2ibuHwqvv7D1jZcnrDdea4xIWUOzQjG54xsIKEEw/0?wx_fmt=jpeg[/img] 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
[p=25, null, left][color=rgb(62, 62, 62)]C:偷梁换柱：
把coe_xfr_sql_profile_93rfq9yt10cxu_3013354339.sql 里面：[p=25, null, left][color=rgb(62, 62, 62)][img]http://mmbiz.qpic.cn/mmbiz/VkC3lgPs6cZQmeynTV3DOjxr0LciakITNhmlqXib0pVDb27R3mSeMCiciaCufKZcj13dQueyyhm5yJGNBD1smiaNNbg/0?wx_fmt=jpeg[/img]
替换coe_xfr_sql_profile_d1q2wdycus5r4_1208602768.sql[p=25, null, left][color=rgb(62, 62, 62)][img]http://mmbiz.qpic.cn/mmbiz/VkC3lgPs6cZQmeynTV3DOjxr0LciakITNQJxJVEMohLafVwAriaXVeprY8ibs6NM5c2lvsN4wUZibHicCjV76BRU3Zw/0?wx_fmt=jpeg[/img]
把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； [p=25, null, left][color=rgb(62, 62, 62)] （二）spm：[p=25, null, left][color=rgb(62, 62, 62)]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;
/[p=25, null, left][color=rgb(62, 62, 62)]
[p=25, null, left][color=rgb(62, 62, 62)]
[p=25, null, left][color=rgb(62, 62, 62)]处理该分区列上的时间索引：
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；[p=25, null, left][color=rgb(62, 62, 62)]
[p=25, null, left][color=rgb(62, 62, 62)]原始sql：[p=25, null, left][color=rgb(62, 62, 62)] 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