ORACLE EBS 标准sql 优化

zhuhgjava

EBS系统有一标准化的请求（创建成批增加）运行特别慢；发现是以下这段SQL导致的。那位大虾有看看有啥优化的方法：
      SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N4)*/ --8236268
        APID.INVOICE_DISTRIBUTION_ID,
       APID.INVOICE_ID,
       APID.INVOICE_LINE_NUMBER,
       APID.PO_DISTRIBUTION_ID,
       APID.ORG_ID,
       APID.ACCOUNTING_EVENT_ID,
       APID.DESCRIPTION,
       APID.ASSET_CATEGORY_ID,
       APID.QUANTITY_INVOICED,
       APID.HISTORICAL_FLAG,
       APID.CORRECTED_QUANTITY,
       APID.DIST_CODE_COMBINATION_ID,
       APID.LINE_TYPE_LOOKUP_CODE,
       APID.DISTRIBUTION_LINE_NUMBER,
       APID.ACCOUNTING_DATE,
       APID.CORRECTED_INVOICE_DIST_ID,
       APID.RELATED_ID,
       APID.CHARGE_APPLICABLE_TO_DIST_ID,
       NVL(APID.ASSET_BOOK_TYPE_CODE, ITEM.ASSET_BOOK_TYPE_CODE),
       APID.SET_OF_BOOKS_ID
        FROM AP_INVOICE_DISTRIBUTIONS     APID,
             AP_INVOICE_DISTRIBUTIONS_ALL ITEM
       WHERE APID.ACCOUNTING_DATE <= P_ACCTG_DATE
         AND APID.ASSETS_ADDITION_FLAG = 'U'
         AND DECODE(APID.LINE_TYPE_LOOKUP_CODE,
                    'ITEM',
                    APID.ASSETS_TRACKING_FLAG,
                    'ACCRUAL',
                    APID.ASSETS_TRACKING_FLAG,
                    NVL(ITEM.ASSETS_TRACKING_FLAG, 'N')) = 'Y'
            -- bug 9174645: add start
         AND DECODE(APID.LINE_TYPE_LOOKUP_CODE,
                    'ITEM',
                    'Y',
                    'ACCRUAL',
                    'Y',
                    NVL(ITEM.ASSETS_ADDITION_FLAG, 'U')) <> 'N'
            -- bug 9174645: add end
            -- bug 9001504
         AND DECODE(APID.LINE_TYPE_LOOKUP_CODE,
                    'ITEM',
                    1,
                    'ACCRUAL',
                    1,
                    NVL(NVL(APID.CHARGE_APPLICABLE_TO_DIST_ID,
                            APID.RELATED_ID),
                        APID.CORRECTED_INVOICE_DIST_ID)) IS NOT NULL
         AND DECODE(APID.LINE_TYPE_LOOKUP_CODE,
                    'ITEM',
                    NULL,
                    'ACCRUAL',
                    NULL,
                    NVL(NVL(APID.CHARGE_APPLICABLE_TO_DIST_ID,
                            APID.RELATED_ID),
                        APID.CORRECTED_INVOICE_DIST_ID)) =
             ITEM.INVOICE_DISTRIBUTION_ID(+)
            -- bug 9001504
         AND (APID.PROJECT_ID IS NULL OR
             (SELECT DECODE(PTYPE.PROJECT_TYPE_CLASS_CODE,
                             'CAPITAL',
                             'P',
                             'U') --Call Expanded for Bug 7284987 / 7392117
                 FROM PA_PROJECT_TYPES_ALL PTYPE,
                      PA_PROJECTS_ALL      PROJ
                WHERE PROJ.PROJECT_TYPE = PTYPE.PROJECT_TYPE
                  AND PTYPE.ORG_ID = PROJ.ORG_ID
                  AND PROJ.PROJECT_ID = APID.PROJECT_ID) <> 'P')
         AND APID.POSTED_FLAG = 'Y'
         AND APID.SET_OF_BOOKS_ID = P_LEDGER_ID
            -- bug 8690407: add start
         AND (APID.ASSET_BOOK_TYPE_CODE = P_BT_CODE OR
             APID.ASSET_BOOK_TYPE_CODE IS NULL)


该贴已经同步到 zhuhgjava的微博

sumury

把执行计划贴出来看看。

zhuhgjava

突然看到很久以前的自己发的一个贴子，自己当时解决了，未回复！
对于这个请求运行越来越慢，找到原因是我们公司特有的公司间关联交易，致使，每个月产生的AP发票记录数超过亿记。而这段SQL 中有一小段SQL 是：APID.ACCOUNTING_DATE <= P_ACCTG_DATE 。就是这个条件致使这个请求会随着时间的增加，发票数越来越多，请求运行速度越来越慢，最后还会产生“回滚段太小的问题”。
对于这段SQL ，本人的优化方法是：
结合业务情况，跟业务部门协商，给这个段SQL 加上一个限制条件。
把其中的：APID.ACCOUNTING_DATE <= P_ACCTG_DATE
换成了  
AND  APID.ACCOUNTING_DATE <= P_ACCTG_DATE+5
AND  APID.ACCOUNTING_DATE >= P_ACCTG_DATE+5

这个条件加上后，还真的解决了这个问题，原来运行“创建成批增加”请求要超过24小时，加上这个条件后，最多只要半小时内，就能把数据从AP传到FA.