营销业务应用系统应用级优化方案

1、TOC o 1-3 h z u HYPERLINK l _Toc373861668 1.项目简介 PAGEREF _Toc373861668 h 4 HYPERLINK l _Toc373861669 2.应用系统的优化 PAGEREF _Toc373861669 h 5 HYPERLINK l _Toc373861670 2.1.优化概述 PAGEREF _Toc373861670 h 5 HYPERLINK l _Toc373861671 2.2.优化方案 PAGEREF _Toc373861671 h 5 HYPERLINK l _Toc373861672 2.2.1.未使用已有索引 P

2、AGEREF _Toc373861672 h 5 HYPERLINK l _Toc373861673 2.2.2.缺少必要索引 PAGEREF _Toc373861673 h 23 HYPERLINK l _Toc373861674 2.2.3.其它 PAGEREF _Toc373861674 h 38 HYPERLINK l _Toc373861675 2.2.4.临时表优化 PAGEREF _Toc373861675 h 51 HYPERLINK l _Toc373861676 2.3.应用系统架构设计 PAGEREF _Toc373861676 h 51 HYPERLINK l _Toc

3、373861677 2.3.1.业务拆分 PAGEREF _Toc373861677 h 52 HYPERLINK l _Toc373861678 2.3.2.历史数据下线 PAGEREF _Toc373861678 h 53 HYPERLINK l _Toc373861679 3.其它建议 PAGEREF _Toc373861679 h 53 HYPERLINK l _Toc373861680 3.1.数据库实例性能数据采集作业 PAGEREF _Toc373861680 h 53项目简介陕西公司营销业务系统随着业务量与数据量的增大，系统压力越来越大，系统可靠性与高效性降低，影响了对业务支持

4、的质量。为此，南瑞公司和陕西公司及该软件的开发公司东软公司一起合作，对营销业务系统通过先进、标准、可控的技术、工具和方法，监控系统的运行状况，评估系统现状，定位系统运行瓶颈，制定具体的优化实施及操作方案，并对优化实施结果进行评估。实现优化系统性能、提升系统运行质量、挖掘系统潜力、创建系统建设良性循环模式的目的，并有助于提高维护人员的技术水平。本项目分为四个阶段：第一阶段为优化小组在西安市对陕西公司营销业务系统进行现场调研和性能分析，形成性能分析报告，并提出初步优化建议。第二阶段，优化小组和开发厂家在国网公司相关部门的领导下，在西安市是成立联合测试小组，对主要问题的优化方案进行验证。第三阶段，优

5、化小组在西安市实施现场优化实施工作，并在实施完成后进行性能评估。第四阶段，开发厂商完成应用优化的代码调整工作，发布新的版本。应用系统的优化由于本次优化工作中发现的应用优化问题，部分可以通过调整索引的方式得到解决，而部分必须由开发厂商修改应用代码。因此本方案对目前发现的一些主要的应用问题进行了分析，并给出了解决方案。在优化实施过程中，部分修改代码的工作需要开发厂商东软公司完成。优化概述应用程序的优化调整贯穿整个调优项目的始终，本章就部分重点应用的调整进行总结， SQL语句的优化对于提高系统性能十分重要。高开销的SQL占用了大量的系统资源，对这些SQL进行优化，可以有效的提高系统的性能。需要进行调

6、整的SQL语句主要可以分为以下几类：缺乏合适索引的SQL：规范索引设计，添加或调整索引，避免对大表的全表扫描。语法明显不合理的SQL：主要是随意地在字段上增加了转换函数，如TRIM和UPPER等，造成了全表扫描。还有就是在取单条记录时，习惯性地使用MAX等分组函数，而不是使用更高效的rownum=1。这些可以通过改写SQL调整。执行计划不稳定的SQL：找到最佳执行计划，调整表联接顺序或通过HINT固定执行计划。使用更有效的物理结构，比如有些大表如果采用分区表效果可能更好。其它，比如有些SQL语句或存储过程改写后效率可能更好。具体内容见下节。优化方案未使用已有索引Sql-1所属函数或存储过程名称

7、：编号：XA_20131024_00001模块名称：电费收缴及营销账务电费收缴及营销账务电费收缴及营销账务问题描述：1.存在问题的SQLSELECT /*+ FIRST_ROWS */ *FROM (SELECT t.*, ROWNUM AS RN2 FROM (SELECT a_pay_flow.CHARGE_ID AS CHARGE_ID, a_pay_flow.ORG_NO AS ORG_NO, a_pay_flow.TYPE_CODE AS TYPE_CODE, a_pay_flow.RCV_AMT AS RCV_AMT, a_pay_flow.THIS_CHG AS THIS_CHG

8、, a_pay_flow.LAST_CHG AS LAST_CHG, a_pay_flow.CHARGE_EMP_NO AS CHARGE_EMP_NO, a_pay_flow.PAY_MODE AS PAY_MODE, a_pay_flow.SETTLE_MODE AS SETTLE_MODE, a_pay_flow.SETTLE_NOTE_NO AS SETTLE_NOTE_NO, a_pay_flow.SETTLE_BANK_CODE AS SETTLE_BANK_CODE, a_pay_flow.ACCT_NO AS ACCT_NO, a_pay_flow.ACCT_NAME AS A

9、CCT_NAME, (SELECT REAL_NAME FROM sa_user WHERE sa_user.USER_ID = a_pay_flow.CHARGE_EMP_NO AND rownum =1 ) AS REAL_NAME, a_cashchk_flow.CASHCHK_NO AS CASHCHK_NO, a_pay_flow.CHARGE_DATE AS CHARGE_DATE, a_cashchk_flow.ACCT_STATUS_CODE AS ACCT_STATUS_CODE, a_cashchk_flow.ARRIVE_DATE AS ARRIVE_DATE, a_pa

10、y_flow.CHARGE_REMARK AS CHARGE_REMARK, a_pay_flow.CONS_NO AS CONS_NO, (SELECT ORG_NAME FROM sa_org WHERE sa__no = a_pay__no AND rownum =1 ) AS CONS_ORG_NAME, a_cashchk_flow.DISPOSE_DATE AS DISPOSE_DATE FROM a_pay_flow, a_cashchk_flow WHERE (a_cashchk_flow.CASHCHK_ID = a_pay_flow.CASHC

11、HK_ID) AND (a_pay_flow.CHARGE_DATE = :1 AND a_pay_flow.CHARGE_DATE = :6) AND (A_PAY_FLOW.PART_CHG_YM = :7) ORDER BY a_pay_flow.CHARGE_DATE DESC, a_pay_flow.TYPE_CODE ASC, a_pay_flow.RCV_AMT ASC ) t WHERE ROWNUM :9（生产环境真实执行计划）2.导致效率低下的原因：索引 EPM_SN.AC_PAYINTO_AC_FLOW_FK的优化程序统计信息已失效。从执行计划可以看到a_pay_flow

12、.charge_date字段进行了隐式类型转换从而导致该字段上的索引无法使用，隐式类型转换会导致过多cpu的消耗，我们从执行计划的id=15可以看到cpu的消耗是420k建议方案：考虑收集此索引的优化程序统计信息。 execute dbms_stats.gather_index_stats(ownname = EPM_SN, indname = AC_PAYINTO_AC_FLOW_FK, estimate_percent = DBMS_STATS.AUTO_SAMPLE_SIZE);修改程序，把传入变量的类型修改成与字段类型一致，避免隐式的数据类型转换，避免消耗大量cpu资源原执行效果：从原

13、执行计划看到此sql CBO估算的cpu的消耗为420K，每次执行逻辑读：130905，每次执行时间：22s测试效果： （生产环境真实执行计划）对该sql的变量传入相应的数据类型的值之后，再执行查看其执行计划，看到CBO估算的cpu的消耗降低到了4，每次执行时间：2.95s。由此可看cpu和执行时间都大大缩短测试日期：2013年10月24生产库实际效果：每次执行时间：2.95s生产库确认日期：2013年10月24Sql-2所属函数或存储过程名称：编号：XA_20131024_00002模块名称：系统后台模块系统后台模块系统后台模块问题描述：1.存在问题的SQLSQL_ID=8rf106msub

14、9b7 CHILD_NUMBER=0select count(*) from sa_log_login where sa_log_login.ORG_NO like :1 and sa_log_login.LOGIN_TIME :2 and sa_log_login.LOGIN_TIME :1AND a.LOGIN_TIME = nvl(null, 200001) and to_char(a3.charge_date, yyyy-MM-dd) = nvl(:2, 200001) and a0.rcvbl_ym = nvl(:3, 220012) and _no = _n

15、o and u1.user_id = a3.charge_emp_no and y1.PROP_TYPE_ID = acct_status_code and y1.PROP_LIST_ID = a2.acct_status_code group by a0.rcvbl_ym, _no, a2.acct_status_code, a3.charge_emp_no, u1.real_name, _name, y1.PROP_LIST_NAME;2.导致效率低下的原因：经查询在a_pay_flow. charge_date字段上已经存在索引，然而该sql在该字段上添加了函数，

16、导致索引无法使用，从而导致性能严重下降建议方案：改写sql去掉charge_date字段上的函数，进而能够使用该字段上的索引，提升效率原执行效果：测试效果： 测试日期： 2013年10月28日可以看到去掉索引字段上的函数之后，cpu的cost值大大减少，性能得到很大提升Sql-7所属函数或存储过程名称：PRC_DELETE_HUIFANG_YK编号：XA_20131028_00014模块名称： 问题描述：1.存在问题的SQLSELECT A.KEYDATA_IDFROM EPSA_SN.SA_WORKFORM_INST A, EPM_SN.ARC_S_APP BWHERE A.KEYDATA_

17、ID = B.APP_NO AND B.VOLT_CODE IN (AC02202, AC03802)AND A.WORKFORM_TYPE = 002 AND TO_CHAR(A.START_TIME, YYYYMMDD) = TO_CHAR(SYSDATE - 1, YYYYMMDD) AND ROWNUM = trunc(SYSDATE - 1) and a.START_TIME trunc(SYSDATE)AND ROWNUM = trunc(SYSDATE - 1) and a.START_TIME :1AND a.login_time :2AND ( _no LIKE 6

18、1401%)2.导致效率低下的原因：从执行计划中可以看到该sql中sa_log_login表的login_time字段进行了隐式类型转换，导致其上面的索引无法使用，从而导致严重的性能问题建议方案：修改应用sql，把传入到login_time字段中的变量的类型改成和login_time字段类型一致的，这样就可以使用其字段上的索引原执行效果：在30分钟内执行882次，每次逻辑读1764147，每次执行时间15s（生产环境下执行计划）Sql-9所属函数或存储过程名称：编号：XA_20131028_00016模块名称： 问题描述：1.存在问题的SQLSql_id=62qs5xy59346sSchema

19、 Name: EPM_SNSELECT COUNT(DISTINCT a.user_name)FROM sa_log_login_history aWHERE EXISTS (SELECT 1 FROM sa_user b WHERE a.user_name = b.user_name )AND a.LOGIN_TIME :1AND a.LOGIN_TIME 0 and (w.overdued =1 or w.overdued=3)2.导致效率低下的原因：该表碎片严重，需要从新整理碎片，缺少必要索引建议方案：对该表和表上的索引进行碎片整理我们查询在rt_workiteminst表的overdu

20、ed字段上数据分布不均衡，而且该字段的基数很低，值为1或3的值的数据量占很少一部分，所以非常适合创建所以在overdued字段上创建索引，同时搜集该字段上的直方图，告诉CBO该字段上数据的分布情况，以便选择最优的执行计划create index flow_sn.rt_work on FLOW_SN.rt_workiteminst(overdued) parallel 4;alter index flow_sn.rt_work parallel 1;exec dbms_stats.gather_table_stats(flow_sn,rt_workiteminst,estimate_percen

21、t=60,method_opt=for columns overdued size 10,degree=4,cascade=true);原执行效果：每次执行时间：205s每次物理读：1347026测试效果： 从执行计划上看，cost、逻辑读和执行时间都大大减少测试日期：2013年10月24日生产库实际效果：每次逻辑读：6每次执行时间：0.1s生产库确认日期：2013年10月24日Sql-2所属函数或存储过程名称：例:TRANS_ID_TONAME编号：XA_20131025_00005模块名称：电费收缴及营销账务电费收缴及营销账务电费收缴及营销账务问题描述：1.存在问题的SQLSELECT *

22、 FROM v$sql WHERE sql_id=0rm64krm4qd2h;select a_rcvbl_flow.CONS_NO as CONS_NO, c_cons.CONS_NAME as CONS_NAME, c_cons.ELEC_ADDR as ELEC_ADDR, max(c_cons.MR_SECT_NO) as MR_SECT_NO, nvl(SUM(a_rcvbl_flow.RCVBL_AMT) ,0) - nvl(SUM(a_rcvbl_flow.RCVED_AMT) ,0) as OWE_FEE, nvl(SUM(a_rcvbl_flow.T_PQ) ,0) as T

23、_PQ, nvl(SUM(a_rcvbl_flow.RCVBL_AMT) ,0) as RCVBL_AMT, nvl(SUM(a_rcvbl_flow.RCVED_AMT) ,0) as RCVED_AMT, nvl(SUM(a_rcvbl_flow.DISPOSE_AMT) ,0) as DISPOSE_AMT, nvl(SUM(a_rcvbl_flow.rcvbl_penalty), 0) as RCVBL_PENALTY, nvl(SUM(a_rcvbl_flow.rcved_penalty), 0) as RCVED_PENALTY, nvl(SUM(a_rcvbl_flow.disp

24、ose_penalty), 0) as DISPOSE_PENALTY, a_rcvbl_flow.STATUS_CODE as STATUS_CODE, a_rcvbl_flow.PAY_MODE as PAY_MODE, a_rcvbl_flow.RCVBL_YM as RCVBL_YM, a_rcvbl_flow.amt_type AMT_TYPE, -结清标志 ( case when min(a_rcvbl_flow.settle_flag) = max(a_rcvbl_flow.settle_flag) and min(a_rcvbl_flow.settle_flag) = 01 t

25、hen 01 when min(a_rcvbl_flow.settle_flag) = max(a_rcvbl_flow.settle_flag) and min(a_rcvbl_flow.settle_flag) = 03 then 03 else 02 end ) as SETTLE_FLAG, c_cons.elec_type_code as ELEC_TYPE_CODE, c_acct.pay_no as PAY_NO, c_acct.MAIN_CONS_NO as MAIN_CONS_NO, decode(SUM(a_rcvbl_flow.DISPOSE_AMT),SUM(a_rcv

26、bl_flow.RCVBL_AMT),到账,在途) as ACCT_STATUS, (SELECT ORG_NAME FROM sa_org WHERE ORG_NO = c_cons.ORG_NO) as ORG_NAME, -供电单位 (SELECT sa_user.real_name FROM r_oper_activity, sa_user WHERE r_oper_activity.act_code = 03 AND r_oper_activity.operator_no = sa_user.user_id AND r_oper_activity.MR_SECT_NO = c_con

27、s.MR_SECT_NO ) as READER_NAME, - 抄表员 max(c_cons.CONS_SORT_CODE) as CONS_SORT_CODE, -用户类别(01 高压,02 低压非居民,03 低压居民) nvl(SUM(a_rcvbl_flow.RCVBL_AMT) ,0) - nvl(SUM(a_rcvbl_flow.DISPOSE_AMT) ,0) as RCVBL_DISPOSE -欠费(收妥) from a_rcvbl_flow, c_cons, c_acct, c_payment_rela where (a_rcvbl_flow.CONS_NO = c_cons

28、.CONS_NO and c_cons.cons_id = c_payment_rela.cons_id(+) and c__no = c_payment__no(+) and c_payment_rela.acct_id = c_acct.acct_id(+) ) and (a_rcvbl_flow.ORG_NO like 614074125%) and (C_CONS.ORG_NO like 61407%) and a_rcvbl_flow.AMT_TYPE 04 and a_rcvbl_flow.RCVBL_YM = 201310 and a_rcvbl_

29、flow.RCVBL_YM = 201310 and A_RCVBL_FLOW.SETTLE_FLAG 03and (a_rcvbl_flow.INVISIBLE_FLAG is null OR a_rcvbl_flow.INVISIBLE_FLAG =0) group by a_rcvbl_flow.CONS_NO, c_cons.CONS_NAME, c_cons.ELEC_ADDR, a_rcvbl_flow.RCVBL_YM, a_rcvbl_flow.STATUS_CODE, a_rcvbl_flow.PAY_MODE, c_cons.elec_type_code, c_acct.M

30、AIN_CONS_NO, c_acct.pay_no, a_rcvbl_flow.amt_type, c_cons.ORG_NO, c_cons.MR_SECT_NO HAVING 1=1 and nvl(SUM(a_rcvbl_flow.RCVBL_AMT) ,0) - nvl(SUM(a_rcvbl_flow.RCVED_AMT) ,0) 0（生产环境真实执行计划）2.导致效率低下的原因：IDX_A_RCVBL_FLOW_CONS_NO 索引统计信息过期在EPM_SN.A_RCVBL_FLOW表的ORG_NO字段上缺少索引，导致了该表的全表扫描，从而导致性能严重下降建议方案：在EPM_SN

31、.A_RCVBL_FLOW表的ORG_NO字段上创建普通索引，同时对该表和索引进行统计信息的搜集Create index epm_sn.idx_rcvbl_flow_org on EPM_SN.A_RCVBL_FLOW(ORG_NO);Exec dbms_stats.gater_table_stats(EPM_SN,A_RCVBL_FLOW,estimate_percent=60,method_opt=for all columns size repeat,no_invalidate=false,cascade=true,degree=8);原执行效果：每次执行逻辑读：9139每次执行时间：3

32、s测试效果： （测试环境下执行计划）每次逻辑读：1每次时间：0.01s测试日期：2013年10月25日生产库实际效果：每次逻辑读：1每次时间：0.01s生产库确认日期：2013年10月25日Sql-3所属函数或存储过程名称：编号：XA_20131028_00011模块名称： 问题描述：1.存在问题的SQLSql_id=fj847325tn6axSELECT COUNT(1)FROM arc_e_cons_prc_amt, arc_e_cons_snapWHERE (arc_e_cons_prc_amt.CALC_ID = arc_e_cons_snap.CALC_IDAND arc_e_con

33、s_snap.ORG_NO = arc_e_cons_prc_amt.ORG_NOAND arc_e_cons_snap.ym =arc_e_cons_prc_amt.ym)AND (ARC_E_CONS_SNAP.ORG_NO LIKE 614054112%)AND (arc_e_cons_snap.CALC_TYPE IN 01AND arc_e_cons_prc_amt.YM = 201311AND arc_e_cons_prc_amt.YM 60,method_opt=for all columns size repeat,no_invalidate=false,cascade=tru

34、e,degree=8);原执行效果：测试效果： 每次逻辑读：1CBO评估cost：1从测试看前后的执行计划，我们可以看到每次的逻辑读和cost的值都成倍下降测试日期： 2013年10月28日生产库实际效果：每次逻辑读：1CBO评估cost：1生产库确认日期：2013年10月28日Sql-4所属函数或存储过程名称：编号：XA_20131028_00012模块名称： 问题描述：1.存在问题的SQLselect a_rcvbl_flow.CONS_NO as CONS_NO, max(a_rcvbl_flow.ORG_NO) as ORG_NO, (sum(a_rcvbl_flow.rcvbl_am

35、t) - sum(a_rcvbl_flow.rcved_amt) + sum(a_rcvbl_flow.rcvbl_penalty) - sum(a_rcvbl_flow.rcved_penalty) as TOTLE, a_rcvbl_flow.CONS_NO as CONS_NO1, nvl(max(prepay_bal),0) as ACCT_BAL, max(CONS_NAME) as CONS_NAME, c_cons.CONS_NO as CONS_NO2, max(c_cons.ORG_NO) as ORG_NO2, sum(rcvbl_amt - rcved_amt) as T

36、OTLE_FEE, sum(rcvbl_penalty - rcved_penalty) as TOTLE_PENALTY, c_cons.MR_SECT_NO as MR_SECT_NO from a_rcvbl_flow, c_cons ,a_acct_bal where a_rcvbl_flow.CONS_NO = c_cons.CONS_NO and c_cons.cons_no = a_acct_bal.cons_no and a_rcvbl_flow.amt_type in (0202, 01, 02, 05, 07, 09, 12) and a_rcvbl_flow.settle

37、_flag in (01, 02) AND (A_RCVBL_FLOW.RCVBL_AMT - A_RCVBL_FLOW.RCVED_AMT + A_RCVBL_FLOW.RCVBL_PENALTY - A_RCVBL_FLOW.RCVED_PENALTY) 0 and a_acct_bal.prepay_bal 0 and a_acct_bal.rema_type in (01) and a_acct__no like 614094102% and a_acct__no like 614094102% AND A_RCVBL_FLOW.RCVBL_YM 60,me

38、thod_opt=for all columns size repeat,no_invalidate=false,cascade=true,degree=8);原执行效果：（生产环境下执行计划）每次逻辑读：136747每次执行时间：1.83测试效果： 每次逻辑读：84每次执行时间：0.01s测试日期： 2013年10月28日生产库实际效果：每次逻辑读：84每次执行时间：0.01s生产库确认日期：2013年10月28日Sql-5所属函数或存储过程名称：PRC_DELETE_HUIFANG_YK编号：XA_20131028_00017模块名称： 问题描述：1.存在问题的SQLSchema Name

39、: EPC_SNupdate epc_sn.s_95598_wkst a set a.accept_mode=01 where a.accept_mode is null; 2.导致效率低下的原因：该sql的执行计划是选择了对该表的全表扫描，缺少必要索引，导致执行效率低下建议方案：我们查看accept_mode字段为null的值非常少，所以在该字段上创建一个和常量进行组合的组合索引原执行效果：测试效果： 未建索引前，执行时间为5s，cost值为5995，逻辑读为26983创建索引之后，执行时间为0.02s，cost值为2，逻辑读为14执行效率提升了200多倍测试日期： 2013年10月28日生

40、产库实际效果：执行时间为5s，cost值为5995，逻辑读为26983生产库确认日期：2013年10月28日Sql-6所属函数或存储过程名称：PRC_DELETE_HUIFANG_YK编号：XA_20131028_00018模块名称： 问题描述：1.存在问题的SQLINSERT INTO EPM_READ.SA_WORKTASK_KF_BAKSELECT *FROM EPSA_SN.SA_WORKTASK TWHERE T.KEYDATA_ID = :B1AND T.WORKTASK_NAME LIKE %业扩报装回访%DELETE FROM EPSA_SN.SA_WORKTASK T WHE

41、RE T.KEYDATA_ID = :B1 AND T.WORKTASK_NAME LIKE %业扩报装回访%2.导致效率低下的原因：经查询，该表大小为232m，由于缺少必要索引，所以导致查询该表使用了全表扫描，导致了大量逻辑和物理读，从而使该sql的性能大大降低建议方案：我们查看该表的数据基数SQL select count(*),count(distinct KEYDATA_ID) from EPSA_SN.SA_WORKTASK ; COUNT(*) COUNT(DISTINCTKEYDATA_ID) 853695 811120查看该字段的选择性SQL select 811120/853

42、695 from dual;811120/853695 .950128559通过以上可以看出改字段的选择性很高，但是该字段上并未创建索引建议创建索引Create index epsa_sn. idx_SA_WORKTASK on EPSA_SN.SA_WORKTASK(KEYDATA_ID);搜集统计信息，如果搜集统计信息，数据库会使用动态采用功能，这样会消耗资源Exec dbms_stats.gather_table_stats(EPSA_SN,SA_WORKTASK,estimate_percent=100,method_opt=for all columns size repeat,ca

43、scade=true,no_invalidate=false,degree=4);Sql-7所属函数或存储过程名称：编号：XA_20131028_00018模块名称： 问题描述：1.存在问题的SQLSELECT DISTINCT b.user_id, b.real_name, c.dept_name, _name, e.max_login_time login_timeFROM sa_log_login a, sa_user b, sa_dept c, sa_org d, (SELECT user_name, MAX (login_time) max_login_time FROM

44、sa_log_login GROUP BY user_name ) eWHERE a.user_name = b.user_nameAND a.user_name = e.user_nameAND b.dept_id = c.dept_idAND _no = _noAND a.login_time to_date(2013-01-01,yyyy-mm-dd)AND a.login_time = V_FEE_DAY_FIRST AND B.SEND_DATE 100,METHOD_OPT=FOR ALL COLUMNS SIZE REPEAT,CASCADE=TRUE,DEG

45、REE=4);exec dbms_stats.gather_table_stats(EPM_SN,D_IO_TASK,ESTIMATE_PERCENT=100,METHOD_OPT=FOR ALL COLUMNS SIZE REPEAT,CASCADE=TRUE,DEGREE=4);搜集统计信息的时候，对于小表我们一般的采样率是100，同时对那些已经存在直方图的字段进行搜集直方图，同时对表上的索引也进行统计信息的搜集。或者直接添加hint来进行固定执行计划select /*+ leading(d_dist_task) use_nl(d_dist_task d_io_task) */ d_dis

46、t_task.EQUIP_NUM AS COUNT, 0 AS ALREADY_NUM, d_io_task.task_id AS TASK_ID, d_dist_task.equip_categ AS EQUIP_CATEG, sa_d_flow_item.rela_id AS APP_NO from D_DIST_TASK, D_IO_TASK,SA_D_FLOW_ITEM WHERE d_dist_task.det_id = 1 and d_dist_task.RCV_DEPT_NO = 6140341 （变量） AND d_dist_task.task_id = d_io_task.r

47、ela_id and d_io_task.task_id = sa_d_flow_item.task_id;由于数据库中没有对表和索引进行定时统计信息搜集的任务，所以最好要把该执行计划固定，通过hint这种方式需要修改应用程序代码。原执行效果：每次执行产生逻辑读：62817455,每次执行时间在5.6分钟测试效果： （生产环境真实执行计划）测试日期：2013年10月24日生产库实际效果：每次执行0.7S每次逻辑读68214从前后的执行计划中看到，执行时间从原来的5.6分钟，到目前的0.7s，逻辑读从62817455降低到68214，性能的到了极大的提升生产库确认日期：2013年10月24日Sq

48、l-4所属函数或存储过程名称：编号：XA_20131024_00002模块名称：计量资产管理计量资产管理计量资产管理问题描述：1.存在问题的SQLsql_id=8tu81ynfa8bn2SELECT /*+ FIRST_ROWS */ *FROM (SELECT t.*, ROWNUM AS RN2 FROM (SELECT e_rs_log.RS_ID AS RS_ID, e_rs_log.APP_CODE AS APP_CODE, e_rs_log.CONS_ID AS CONS_ID, e_rs_log.ORG_NO AS ORG_NO, e_rs_log.RS_FLAG AS RS_F

49、LAG, e_rs_log.RS_DATE AS RS_DATE, e_rs_log.ERR_DATE AS ERR_DATE, e_rs_log.RS_REMARK AS RS_REMARK, e_rs_log.MERGE_FLAG AS MERGE_FLAG, e_rs_log.ACCT_MERGE_MON AS ACCT_MERGE_MON, e_rs_log.MERGE_MON AS MERGE_MON, e_rs_log.PF_FLAG AS PF_FLAG, e_rs_log.TL_FLAG AS TL_FLAG, e_rs_log.MR_UPD_FLAG AS MR_UPD_FL

50、AG, e_rs_log.ORGN_PRC_CODE AS ORGN_PRC_CODE, e_rs_log.ORGN_PRC_VN AS ORGN_PRC_VN, e_rs_log.TR_BGN AS TR_BGN, e_rs_log.TR_END AS TR_END, e_rs_log.H_AMT_HANDLE_MODE AS H_AMT_HANDLE_MODE, e_rs_log.CALC_TIMES AS CALC_TIMES, e_rs_log.EFFECT_FLAG AS EFFECT_FLAG, e_rs_log.OLD_CALC_ID AS OLD_CALC_ID, e_rs_l

51、og.APPLY_TYPE AS APPLY_TYPE, e_rs_log.APPLY_STATUS AS APPLY_STATUS, e_rs_log.CONS_NO AS CONS_NO, e_rs_log.CONS_NAME AS CONS_NAME, e_rs_log.ELEC_ADDR AS ELEC_ADDR, c_cons.MR_SECT_NO AS MR_SECT_NO, (SELECT sa_org.ORG_NAME FROM sa_org WHERE org_no = e_rs__no ) AS ORG_NAME, e_rs_log.POLICY_FLAG A

52、S POLICY_FLAG, e_rs_log.CUR_PRC_CODE AS CUR_PRC_CODE, e_rs_log.CUR_PRC_VN AS CUR_PRC_VN, AS APP_NO, AS RELA_APP_NO, e_rs_log.OLD_CALC_TYPE AS OLD_CALC_TYPE, e_rs_log.OLD_CALC_TIMES AS OLD_CALC_TIMES, e_rs_log.APPLY_MAN AS APPLY_MAN, (SELECT SA_USER.REAL_NAME FROM SA_USER WHERE E_RS_LOG.APPLY_MAN = S

53、A_USER.USER_ID ) AS APPLY_NAME, e_rs_log.APPLY_DATE AS APPLY_DATE, e_rs_log.APP_RS_PQ AS APP_RS_PQ, e_rs_log.APP_RS_AMT AS APP_RS_AMT, 0 AS T_SETTLE_PQ, 0 AS T_AMT, AS IS_ARC, (SELECT p_list_name FROM E_RS_SORT_RELA t, sa_prop_list s WHERE t.rs_id = e_rs_log.rs_id AND t.rs_type =0 AND s.PROP_TY

54、PE_ID=mistake_type_code AND p_list_id=t.type_code ) AS TYPE_NAME_1, (SELECT p_list_name FROM E_RS_SORT_RELA t, sa_prop_list s WHERE t.rs_id = e_rs_log.rs_id AND t.rs_type =2 AND s.PROP_TYPE_ID=fault_level_code AND p_list_id=t.type_code ) AS TYPE_NAME_2, (SELECT MAX(REAL_NAME) FROM SA_

55、USER, R_OPER_ACTIVITY, C_CONS WHERE USER_ID =R_OPER_ACTIVITY.OPERATOR_NO AND R_OPER_ACTIVITY.ACT_CODE = 03 AND R_OPER_ACTIVITY.MR_SECT_NO = C_CONS.MR_SECT_NO AND C_CONS.CONS_NO = E_RS_LOG.CONS_NO AND C_CONS.ORG_NO LIKE SUBSTR(e_rs__no,1,5) |% ) AS REAL_NAME, (SELECT MAX(REAL_NAME) FROM SA_USE

56、R, R_OPER_ACTIVITY, C_CONS WHERE USER_ID =R_OPER_ACTIVITY.OPERATOR_NO AND R_OPER_ACTIVITY.ACT_CODE = 07 AND R_OPER_ACTIVITY.MR_SECT_NO = C_CONS.MR_SECT_NO AND C_CONS.CONS_NO = E_RS_LOG.CONS_NO AND C_CONS.ORG_NO LIKE SUBSTR(e_rs__no,1,5) |% ) AS CHECKER_REAL_NAME, AS SEND_DATE FROM e_rs_log, c

57、_cons WHERE (e_rs_log.cons_no = c_cons.cons_no AND e_rs_log.app_code NOT IN (SELECT a.app_code FROM e_rs_log a, r_plan b WHERE a.app_code = b.rela_app_no AND _no = _no AND a.apply_status != 9- AND (a.apply_status 9 OR A.APPLY_STATUS9 OR A.APPLY_STATUS9) ) AND (E_RS_LOG.ORG_NO LIKE 61401420

58、1%) AND (C_CONS.ORG_NO LIKE 61401%) ) t WHERE ROWNUM 0;2.导致效率低下的原因： 经查看其原始执行计划，发现其CBO选择的执行计划不是最优的。建议方案：修改sql 使用hint use_nl(e_rs_log c_cons) 强制其走嵌套循环SELECT /*+ FIRST_ROWS */ *FROM (SELECT t.*, ROWNUM AS RN2 FROM (SELECT /*+ use_nl(e_rs_log c_cons) */ e_rs_log.RS_ID AS RS_ID, e_rs_log.APP_CODE AS APP_

59、CODE, e_rs_log.CONS_ID AS CONS_ID, e_rs_log.ORG_NO AS ORG_NO, e_rs_log.RS_FLAG AS RS_FLAG, e_rs_log.RS_DATE AS RS_DATE, e_rs_log.ERR_DATE AS ERR_DATE, e_rs_log.RS_REMARK AS RS_REMARK, e_rs_log.MERGE_FLAG AS MERGE_FLAG, e_rs_log.ACCT_MERGE_MON AS ACCT_MERGE_MON, e_rs_log.MERGE_MON AS MERGE_MON, e_rs_

60、log.PF_FLAG AS PF_FLAG, e_rs_log.TL_FLAG AS TL_FLAG, e_rs_log.MR_UPD_FLAG AS MR_UPD_FLAG, e_rs_log.ORGN_PRC_CODE AS ORGN_PRC_CODE, e_rs_log.ORGN_PRC_VN AS ORGN_PRC_VN, e_rs_log.TR_BGN AS TR_BGN, e_rs_log.TR_END AS TR_END, e_rs_log.H_AMT_HANDLE_MODE AS H_AMT_HANDLE_MODE, e_rs_log.CALC_TIMES AS CALC_T