ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Linux操作系统下Oracle11g R2 RAC 安装配置教程
Oracle数据库备份恢复高级培训视频(案例、实战、深入、全面)
Oracle数据库DBA高级工程师培训视频(集群容灾,核心深入,备份恢复)

绑定执行计划案例

内容发布:清晨| 发布时间:2016-9-21 11:36:39

案例:
        巡检发现一个sql的执行计划有问题,但是该sql历史只有一个执行计划,常规的只使用coe_xfr_sql_profile.sql脚本绑定,已经无法使用,因为只有一个执行计划,因此我们需要利用“偷梁换柱”来使用 coe_xfr_sql_profile.sql脚本绑定

分析:
sql:sql比较长放在文章底部

历史执行情况:



执行计划:



索引信息:


从索引信息 结合sql的谓词条件  我们很容看出,该sql应该使用(CUSTOMER_NO,TXN_TIME)的组合索引

表T_LOG_ACCOUNT_PAYMENT_HIS 按TXN_TIME月分区,T_LOG_ACCOUNT_PAYMENT  按TXN_TIME天分区

现在的执行计划走的都是TXN_TIME时间字段上的单索引,因此我怀疑又出现了 谓词越界的情况。

T_LOG_ACCOUNT_PAYMENT_HIS  2016-02-26 09:11:12

T_LOG_ACCOUNT_PAYMENT         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');

正确的执行计划如下:



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:

      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




上一篇:oracle常用的连接类型
下一篇:巧用Oracle正则表达式解决查询问题
回复

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表