优化案例二

来源：互联网发布：上古世纪捏脸数据精灵编辑：程序博客网时间：2024/05/16 10:40

1.问题发现

最近几天，都有 "****"报表的语句成为top 1 sql，语句单次执行消耗很大。虽然这个程序现在平均跑一次只需要不到3分钟，不过仍然需要我们对其进行优化，减少其消耗。尤其是现在，一期财务系统面临fch和保监会报数程序的压力，系统消耗越来越高，这些都严重冲击着系统的稳定性和高性能。

CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value--------------- ------------ -------------- ------ -------- --------- ---------- 19,248,033 3 6,416,011.0 3.8 275.27 269.10 397472776Module: CASHITEM SELECT GCC.SEGMENT1, ACA.STATUS_LOOKUP_CODE, ACA.CHECK_DATE, ACA .VOID_DATE, ACA.BASE_AMOUNT, ACA.ATTRIBUTE1, ACA.CHECK_ID FROM A P_CHECKS_ALL ACA, AP_BANK_ACCOUNTS_ALL ABA, GL_CODE_COMBINATIONS GCC, FND_FLEX_VALUES FV, HR_OPERATING_UNITS OOD, FND_ID_FLEX_SE GMENTS_VL FVL, GL_SETS_OF_BOOKS GSB WHERE ACA.BANK_ACCOUNT_ID =

2.原因分析

如下是完整的语句和执行计划。从中我们可以看到，该表有多大7个对象的关联，而且其中有些对象是视图，他们本身就由多个table 关联而来。这种情况下，CBO 分析的时候，会有非常大的复杂度(因为随着对象增多，排列组合的总数成指数级的增大），此时要在里面选一个最优的计划就非常困难），这个时候也往往很难得到令人满意的执行计划。

从这个执行计划上看，在访问 HR_ORGANIZATION_INFORMATION 和 GCC 之前就访问了 AP_CHECKS_ALL , AP_CHECKS_ALL 其实是这个语句的最主要的表，其他的表基本上都是为了条件过滤而被加进来的，并且，从数据量上我们可以可以看到 AP_CHECKS_ALL 表是这几个表中最大的表。显然这就有问题了，没有用小数据去驱动大数据，层层过滤，层层推进，而是直接就访问了大数据，然后再过滤。

SELECT GCC.SEGMENT1, ACA.STATUS_LOOKUP_CODE, ACA.CHECK_DATE, ACA.VOID_DATE, ACA.BASE_AMOUNT, ACA.ATTRIBUTE1, ACA.CHECK_ID FROM AP_CHECKS_ALL ACA, AP_BANK_ACCOUNTS_ALL ABA, GL_CODE_COMBINATIONS GCC, FND_FLEX_VALUES FV, HR_OPERATING_UNITS OOD, FND_ID_FLEX_SEGMENTS_VL FVL, GL_SETS_OF_BOOKS GSB WHERE ACA.BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID AND ACA.ORG_ID = ABA.ORG_ID AND ABA.ASSET_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND OOD.ORGANIZATION_ID = ACA.ORG_ID AND FV.ENABLED_FLAG = 'Y' AND FV.SUMMARY_FLAG = 'N' AND FV.FLEX_VALUE = GCC.SEGMENT2 AND TO_CHAR(FV.ATTRIBUTE3) = 'Y' AND FV.FLEX_VALUE_SET_ID = FVL.FLEX_VALUE_SET_ID AND FVL.ID_FLEX_NUM = GSB.CHART_OF_ACCOUNTS_ID AND FVL.ID_FLEX_CODE = 'GL#' AND FVL.APPLICATION_COLUMN_NAME = 'SEGMENT2' AND GSB.SET_OF_BOOKS_ID = OOD.SET_OF_BOOKS_ID AND ACA.ATTRIBUTE1 IS NOT NULL AND OOD.SET_OF_BOOKS_ID = :B3 AND ((TO_CHAR(ACA.CHECK_DATE, 'MON-YYYY') = :B2) OR (TO_CHAR(ACA.VOID_DATE, 'MON-YYYY') = :B2 AND ACA.STATUS_LOOKUP_CODE = 'VOIDED')) AND ((GCC.SEGMENT1 = :B1 AND :B1 IS NOT NULL) OR (:B1 IS NULL))

ID OPERATION OPTIONS OBJECT_NAME OPTIMIZER COST1 0 SELECT STATEMENT CHOOSE 522 1 NESTED LOOPS 523 2 NESTED LOOPS 514 3 NESTED LOOPS 515 4 NESTED LOOPS 496 5 NESTED LOOPS 497 6 MERGE JOIN CARTESIAN 298 7 TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES ANALYZED 29 8 NESTED LOOPS 1210 9 NESTED LOOPS 1011 10 HASH JOIN 1012 11 NESTED LOOPS 513 12 TABLE ACCESS BY INDEX ROWID GL_SETS_OF_BOOKS ANALYZED 114 13 INDEX UNIQUE SCAN GL_SETS_OF_BOOKS_U2 ANALYZED 15 14 TABLE ACCESS FULL HR_ORGANIZATION_INFORMATION ANALYZED 416 15 TABLE ACCESS FULL FND_ID_FLEX_SEGMENTS ANALYZED 417 16 INDEX UNIQUE SCAN FND_ID_FLEX_SEGMENTS_TL_U1 ANALYZED 18 17 INDEX RANGE SCAN FND_FLEX_VALUES_N3 ANALYZED 119 18 BUFFER SORT 2720 19 TABLE ACCESS FULL AP_BANK_ACCOUNTS_ALL ANALYZED 1721 20 TABLE ACCESS BY INDEX ROWID AP_CHECKS_ALL ANALYZED 2022 21 INDEX RANGE SCAN AP_CHECKS_N11 ANALYZED 123 22 INDEX UNIQUE SCAN HR_ORGANIZATION_UNITS_PK ANALYZED 24 23 TABLE ACCESS BY INDEX ROWID HR_ORGANIZATION_INFORMATION ANALYZED 225 24 INDEX RANGE SCAN HR_ORGANIZATION_INFORMATIO_FK2 ANALYZED 126 25 INDEX UNIQUE SCAN HR_ALL_ORGANIZATION_UNTS_TL_PK ANALYZED 27 26 TABLE ACCESS BY INDEX ROWID GL_CODE_COMBINATIONS ANALYZED 128 27 INDEX UNIQUE SCAN GL_CODE_COMBINATIONS_U1 ANALYZED

SQL> select owner,segment_name ,segment_type,bytes/1024/1024 from dba_segments where segment_name in ( 2 2 rtrim('GL_SETS_OF_BOOKS '), 3 rtrim('HR_OPERATING_UNITS '), 4 rtrim('AP_CHECKS_ALL '), 5 rtrim('AP_BANK_ACCOUNTS_ALL '), 6 rtrim('GL_CODE_COMBINATIONS '), 7 rtrim('FND_FLEX_VALUES '), 8 rtrim('FND_ID_FLEX_SEGMENTS_VL') 9 ); OWNER SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024------------------------------ -------------------------------------------------------------------------------- ------------------ ---------------AP AP_BANK_ACCOUNTS_ALL TABLE 0.875AP AP_CHECKS_ALL TABLE 11.375GL GL_CODE_COMBINATIONS TABLE 5.875APPLSYS FND_FLEX_VALUES TABLE 1GL GL_SETS_OF_BOOKS TABLE 0.125 SQL>

3.解决方案

其实作为程序开发，我们要尽量避免将很多的表放在一起join。对于这个问题的解决，我们可以使用ordered 这个hint，来强制oracle 按照我们的意图去按顺序由小到大的去层层join 表。

带入条件做了个测试，修改之前需要跑159s 的语句，修改后1s多就可以跑出来。

--修改后的语句

SELECT /*+ ORDERED */ GCC.SEGMENT1, ACA.STATUS_LOOKUP_CODE, ACA.CHECK_DATE, ACA.VOID_DATE, ACA.BASE_AMOUNT, ACA.ATTRIBUTE1, ACA.CHECK_ID FROM GL_SETS_OF_BOOKS GSB, HR_OPERATING_UNITS OOD, FND_ID_FLEX_SEGMENTS_VL FVL, FND_FLEX_VALUES FV, GL_CODE_COMBINATIONS GCC, AP_BANK_ACCOUNTS_ALL ABA, AP_CHECKS_ALL ACA WHERE ACA.BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID AND ACA.ORG_ID = ABA.ORG_ID AND ABA.ASSET_CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND OOD.ORGANIZATION_ID = ACA.ORG_ID AND FV.ENABLED_FLAG = 'Y' AND FV.SUMMARY_FLAG = 'N' AND FV.FLEX_VALUE = GCC.SEGMENT2 AND TO_CHAR(FV.ATTRIBUTE3) = 'Y' AND FV.FLEX_VALUE_SET_ID = FVL.FLEX_VALUE_SET_ID AND FVL.ID_FLEX_NUM = GSB.CHART_OF_ACCOUNTS_ID AND FVL.ID_FLEX_CODE = 'GL#' AND FVL.APPLICATION_COLUMN_NAME = 'SEGMENT2' AND GSB.SET_OF_BOOKS_ID = :B3 AND ACA.ATTRIBUTE1 IS NOT NULL AND OOD.SET_OF_BOOKS_ID = :B3 AND ((TO_CHAR(ACA.CHECK_DATE, 'MON-YYYY') = :B2) OR (TO_CHAR(ACA.VOID_DATE, 'MON-YYYY') = :B2 AND ACA.STATUS_LOOKUP_CODE = 'VOIDED')) AND ((GCC.SEGMENT1 = :B1 AND :B1 IS NOT NULL) OR (:B1 IS NULL))