Oracle EBS optimization has been carried out this year, causing skull pain

It has been engaged in performance optimization for more than 10 years, including OLTP system, OLAP system, Oracle,MYSQL,PG,Greenplum,Oceanbase,hive,
Dameng and other database optimization projects have done too much... Only EBS system has not been optimized separately, which has always been a pity.
Although I taught several EBS DBA apprentices during network training, they also asked me to optimize EBS SQL, but they were scattered.
At the beginning of this spring, the company received an EBS optimization project (version ebs11i), which is the first real EBS optimization project of the company.
In the early stage, three DBA s, three EBS consultants and project managers were at the customer's site for more than two months, and the progress was slow.
Because the project faces the risk of failure, the project manager can only upgrade and apply for the company's senior resources. I'm here for the natural EBS optimization.
When I arrived at the customer's site, I found out why the progress was slow. At that time, the optimization project of EBS was to do FORM optimization first, and then report optimization

Problems encountered in FORM optimization at that time

1. The SQL in the EBS system has a large number of view sets, view sets, view sets, and always sets... If you want to understand the whole SQL logic, you need to pull out the view source code layer by layer, which is disgusting
2. Some views are standard (generally can't be rewritten), and some views are customized (can be rewritten). Sometimes these views run slowly for independent query... Not to mention that they have to associate other tables and views
3. Expand all views in these SQL, and start with 500 lines of SQL
4.SQL contains a large number of user-defined functions, various scalar subqueries, and various or exists, all of which are not recommended by the SQL programming specification
5. In order to realize unified interface call, it is necessary to deal with various interface parameters. There are a lot of ap.org in the where condition_ ID = NVL(:B1, AP.ORG_ID),OR EXISTS...OR ...OR ...
6. Super complex SQL hard parsing takes more than ten seconds to a few minutes. The customer's requirement is that all FORM interfaces can return results in five seconds
7. Traditional optimization methods: collect statistical information, quote, add HINT, adjust the implementation plan, open parallel... Everything you can think of is useless
8. My reputation for optimization for more than 10 years was almost destroyed by this project. It was too difficult. I went there for 2 days and didn't find a place to start

On the third day, I thought that if I didn't make any achievements today, I'm afraid my reputation would be ruined. So I sank down to find a breakthrough
Find the EBS consultant to initialize the SQL (most EBS SQL needs to be initialized), and pour the temporary table data into the ordinary table for debugging

SQL is too complex, so I won't post it here

Rewrite the or exists, custom functions, scalars, etc. in SQL that can be rewritten. It has a certain effect. The results can be obtained in 10 minutes from no results before
However, this is far from the result in 5 seconds. The customer still can't accept it. Finally, it is located in the EBS standard VIEW. It is because there is a problem with the standard VIEW, so it is very slow to check it
Generally speaking, we won't rewrite the standard VIEW, but there is no solution if we don't change it. After rewriting it, we finally finished it in 3 seconds, so we quickly discussed with EBS consultant whether we can do it like this

After discussing with the customer, the customer said that we would not upgrade in the future anyway, and the customer has rewritten the standard VIEW, so that we can rest assured to change it, but the reason for the change should be indicated for online review
After finding a breakthrough, I quickly pulled three DBA s from my team for remote support. I can't finish so much rewriting work alone. I also asked EBS consultants to send two more EBS developers and let customers participate in EBS development

After two months of hard work, we finally completed the optimization task. This EBS optimization is equivalent to rewriting the whole FORM, report and code of EBS, and rewriting the standard VIEW and customized VIEW from beginning to end. This requires professional optimization DBA (equivalent rewriting is very powerful) and advanced EBS development, and the cooperation of senior EBS function consultants. None of the three parties is indispensable

With this benchmark EBS optimization project, the company took another EBS migration project. Although it is migration, it actually needs to do a lot of EBS report optimization work  
Just now, the SQL code is very simple (the rest are too complex to write articles. Forgive me), so I'll share it with you. The SQL code is as follows:

SELECT AP.INVOICE_ID,
       AP.OU_NAME,
       AP.BUSINESS_NAME,
       AP.ATTRIBUTE15 DEPART_NAME,
       NVL(APS.VENDOR_NAME, HP.PARTY_NAME) VENDOR_NAME,
       DECODE(AP.INVOICE_TYPE_LOOKUP_CODE,
              'PAYMENT REQUEST',
              NULL,
              APS.SEGMENT1) VENDOR_NUMBER,
       ALC.DISPLAYED_FIELD INVOICE_TYPE,
       AP.INVOICE_NUM,
       TO_CHAR(AP.INVOICE_DATE, 'YYYY-MM-DD') INVOICE_DATE,
       TO_CHAR(AP.GL_DATE, 'YYYY-MM-DD') GL_DATE,
       AP.INVOICE_CURRENCY_CODE CURRENCY_CODE,
       AP.INVOICE_AMOUNT ENTERED_AMOUNT,
       ROUND(AP.INVOICE_AMOUNT *
             NGL_ACCT_DETAIL_ACC_PKG.GET_CONVERSION_RATE(AP.INVOICE_CURRENCY_CODE,
                                                         NAP_UNINVOICE_REPORT_OUTPUT_N.GET_CURRENCY_CODE(AP.ORG_ID),
                                                         AP.GL_DATE),
             2) ACCOUNTED_AMOUNT
  FROM (SELECT APIA.*,
               (SELECT HOU.NAME
                  FROM HR_OPERATING_UNITS HOU
                 WHERE HOU.ORGANIZATION_ID = APIA.ORG_ID) OU_NAME,
               (SELECT NBDV.BUSSION_NAME
                  FROM NCM_BU_DEPARTMENT_V NBDV
                 WHERE NBDV.DEPARTMENT_CODE = APIA.ATTRIBUTE15) BUSINESS_NAME
          FROM AP_INVOICES_ALL APIA) AP,
       (SELECT TMP.INVOICE_ID
          FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL AID,
               (SELECT T.NUM1 INVOICE_ID
                  FROM NGL_UNIVERSAL_TMP_DXB T
                 WHERE T.TYPE_STR IN
                       ('INVOICE_BASE_DATA1', 'INVOICE_BASE_DATA2')
                   AND T.NUM2 = NVL(null, T.NUM2)) TMP
         WHERE TMP.INVOICE_ID = AID.INVOICE_ID
           AND AID.ACCRUAL_POSTED_FLAG <> 'Y'
           AND NVL(AID.MATCH_STATUS_FLAG, 'N') <> 'N'
           AND AP_INVOICES_PKG.GET_POSTING_STATUS(TMP.INVOICE_ID) = 'P'
           AND (NOT EXISTS
                (SELECT 1
                   FROM XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE
                  WHERE XE.ENTITY_ID = XTE.ENTITY_ID
                    AND XE.APPLICATION_ID = XTE.APPLICATION_ID
                    AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
                    AND XTE.ENTITY_CODE = 'AP_INVOICES'
                    AND XE.EVENT_STATUS_CODE = 'U'
                    AND (XE.PROCESS_STATUS_CODE = 'U' OR
                        XE.PROCESS_STATUS_CODE = 'I')
                    AND XTE.APPLICATION_ID = 200
                    AND XE.APPLICATION_ID = 200) OR
                (AID.AMOUNT = 0 AND AID.LINE_TYPE_LOOKUP_CODE = 'PREPAY' AND
                EXISTS (SELECT 1
                          FROM XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE
                         WHERE XE.ENTITY_ID = XTE.ENTITY_ID
                           AND XE.APPLICATION_ID = XTE.APPLICATION_ID
                           AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
                           AND XTE.ENTITY_CODE = 'AP_INVOICES'
                           AND XE.EVENT_STATUS_CODE = 'U'
                           AND XE.PROCESS_STATUS_CODE = 'U')) OR
                (EXISTS (SELECT 1
                           FROM AP_PREPAY_HISTORY_ALL APH
                          WHERE APH.INVOICE_ID = AID.INVOICE_ID
                            AND APH.POSTED_FLAG <> 'Y') AND NOT EXISTS
                 (SELECT 1
                    FROM XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE
                   WHERE XE.ENTITY_ID = XTE.ENTITY_ID
                     AND XE.APPLICATION_ID = XTE.APPLICATION_ID
                     AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
                     AND XTE.ENTITY_CODE = 'AP_INVOICES'
                     AND XE.EVENT_STATUS_CODE = 'U'
                     AND (XE.PROCESS_STATUS_CODE = 'U' OR
                         XE.PROCESS_STATUS_CODE = 'I')
                     AND XTE.APPLICATION_ID = 200
                     AND XE.APPLICATION_ID = 200)))
        UNION ALL
        SELECT TMP.INVOICE_ID
          FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL AID,
               (SELECT T.NUM1 INVOICE_ID
                  FROM NGL_UNIVERSAL_TMP_DXB T
                 WHERE T.TYPE_STR IN
                       ('INVOICE_BASE_DATA1', 'INVOICE_BASE_DATA2')
                   AND T.NUM2 = NVL(null, T.NUM2)) TMP
         WHERE TMP.INVOICE_ID = AID.INVOICE_ID
           AND AID.ACCRUAL_POSTED_FLAG = 'Y'
           AND NVL(AID.MATCH_STATUS_FLAG, 'N') <> 'N'
           AND AP_INVOICES_PKG.GET_POSTING_STATUS(TMP.INVOICE_ID) = 'Y'
           AND ((AID.AMOUNT = 0 AND AID.LINE_TYPE_LOOKUP_CODE = 'PREPAY' AND
               EXISTS (SELECT 'x'
                          FROM XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE
                         WHERE XE.ENTITY_ID = XTE.ENTITY_ID
                           AND XE.APPLICATION_ID = XTE.APPLICATION_ID
                           AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
                           AND XE.EVENT_STATUS_CODE = 'U'
                           AND XE.PROCESS_STATUS_CODE = 'U'
                           AND XTE.ENTITY_CODE = 'AP_INVOICES'
                           AND XTE.APPLICATION_ID = 200
                           AND XE.APPLICATION_ID = 200)) OR
               (EXISTS (SELECT 1
                           FROM AP_PREPAY_HISTORY_ALL APH
                          WHERE APH.INVOICE_ID = AID.INVOICE_ID
                            AND APH.POSTED_FLAG <> 'Y')))
        UNION ALL
        SELECT TMP.INVOICE_ID
          FROM (SELECT T.NUM1 INVOICE_ID
                  FROM NGL_UNIVERSAL_TMP_DXB T
                 WHERE T.TYPE_STR IN
                       ('INVOICE_BASE_DATA1', 'INVOICE_BASE_DATA2')
                   AND T.NUM2 = NVL(null, T.NUM2)) TMP,
               AP_INVOICES_ALL AP,
               AP.AP_INVOICE_DISTRIBUTIONS_ALL AID,
               AP_PREPAY_HISTORY_ALL APH
         WHERE TMP.INVOICE_ID = AID.INVOICE_ID
           AND TMP.INVOICE_ID = AP.INVOICE_ID
           AND TMP.INVOICE_ID = APH.INVOICE_ID(+)
           AND AP_INVOICES_PKG.GET_APPROVAL_STATUS(AP.INVOICE_ID,
                                                   AP.INVOICE_AMOUNT,
                                                   AP.PAYMENT_STATUS_FLAG,
                                                   AP.INVOICE_TYPE_LOOKUP_CODE) =
               'APPROVED'
           AND AID.MATCH_STATUS_FLAG = 'N'
           AND EXISTS (SELECT 'x'
                  FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
                       XLA_AE_HEADERS               AH,
                       XLA_AE_LINES                 AL,
                       XLA_EVENTS                   XE
                 WHERE AH.AE_HEADER_ID = AL.AE_HEADER_ID
                   AND AH.APPLICATION_ID = AL.APPLICATION_ID
                   AND XE.ENTITY_ID = XTE.ENTITY_ID
                   AND XE.APPLICATION_ID = XTE.APPLICATION_ID
                   AND AH.EVENT_ID = XE.EVENT_ID
                   AND AH.APPLICATION_ID = XE.APPLICATION_ID
                   AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
                   AND AH.ACCOUNTING_DATE >= to_date('2021-09-01','yyyy-mm-dd')
                   AND AH.ACCOUNTING_DATE < to_date('2021-09-30','yyyy-mm-dd') + 1
                   AND XTE.ENTITY_CODE = 'AP_INVOICES'
                   AND AL.APPLICATION_ID = 200
                   AND XTE.APPLICATION_ID = 200
                   AND AH.APPLICATION_ID = 200
                   AND XE.APPLICATION_ID = 200)) TMP,
       HZ_PARTIES HP,
       AP.AP_SUPPLIERS APS,
       AP_LOOKUP_CODES ALC
 WHERE 1 = 1
   AND AP.INVOICE_ID = TMP.INVOICE_ID
   AND AP.PARTY_ID = HP.PARTY_ID
   AND EXISTS (SELECT 1
          FROM NCM_COMMON_GT_TMP_DXB NCGT
         WHERE NCGT.CHAR1 = AP.ATTRIBUTE15)
  -- AND XY_COM_DEPT_READ.DEPT_READ(AP.ATTRIBUTE15) = 'Y'
   AND AP.VENDOR_ID = APS.VENDOR_ID(+)
   AND AP.INVOICE_TYPE_LOOKUP_CODE = ALC.LOOKUP_CODE(+)
   AND ALC.LOOKUP_TYPE(+) = 'INVOICE TYPE'
   AND AP.ORG_ID = NVL(null, AP.ORG_ID)
 GROUP BY AP.OU_NAME,
          AP.BUSINESS_NAME,
          AP.INVOICE_ID,
          AP.ATTRIBUTE15,
          NVL(APS.VENDOR_NAME, HP.PARTY_NAME),
          DECODE(AP.INVOICE_TYPE_LOOKUP_CODE,
                 'PAYMENT REQUEST',
                 NULL,
                 APS.SEGMENT1),
          ALC.DISPLAYED_FIELD,
          AP.INVOICE_NUM,
          TO_CHAR(AP.INVOICE_DATE, 'YYYY-MM-DD'),
          TO_CHAR(AP.GL_DATE, 'YYYY-MM-DD'),
          AP.INVOICE_CURRENCY_CODE,
          AP.INVOICE_AMOUNT,
          ROUND(AP.INVOICE_AMOUNT *
                NGL_ACCT_DETAIL_ACC_PKG.GET_CONVERSION_RATE(AP.INVOICE_CURRENCY_CODE,
                                                            NAP_UNINVOICE_REPORT_OUTPUT_N.GET_CURRENCY_CODE(AP.ORG_ID),
                                                            AP.GL_DATE),
                2)
 ORDER BY AP.INVOICE_ID,
          AP.ATTRIBUTE15,
          TO_CHAR(AP.INVOICE_DATE, 'YYYY-MM-DD');

The above SQL returns line 0. In the cursor LOOP of a report, it takes hundreds of cycles, and a single execution takes 30-50 seconds. The best optimization method is to remove the LOOP loop and batch process, but this itself is a migration project, not a pure optimization project. The action of changing the LOOP is too large to pay off. And we changed the cycle. The customer asked us to verify the data ourselves. Didn't we find something to do for ourselves? Ha ha, you know. Therefore, the best solution at present is to reduce the single execution speed of SQL from 30-50 seconds to seconds, which also achieves the optimization effect

This SQL is slow in three places, one of which is here:

SELECT TMP.INVOICE_ID
          FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL AID,
               (SELECT T.NUM1 INVOICE_ID
                  FROM NGL_UNIVERSAL_TMP_DXB T
                 WHERE T.TYPE_STR IN
                       ('INVOICE_BASE_DATA1', 'INVOICE_BASE_DATA2')
                   AND T.NUM2 = NVL(null, T.NUM2)) TMP
         WHERE TMP.INVOICE_ID = AID.INVOICE_ID
           AND AID.ACCRUAL_POSTED_FLAG <> 'Y'
           AND NVL(AID.MATCH_STATUS_FLAG, 'N') <> 'N'
           AND AP_INVOICES_PKG.GET_POSTING_STATUS(TMP.INVOICE_ID) = 'P' ---this EBS Standard custom functions slow down the whole process SQL,Remove it 0.0 A few seconds, not three seconds
           AND (NOT EXISTS
                (SELECT 1
                   FROM XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE
                  WHERE XE.ENTITY_ID = XTE.ENTITY_ID
                    AND XE.APPLICATION_ID = XTE.APPLICATION_ID
                    AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
                    AND XTE.ENTITY_CODE = 'AP_INVOICES'
                    AND XE.EVENT_STATUS_CODE = 'U'
                    AND (XE.PROCESS_STATUS_CODE = 'U' OR
                        XE.PROCESS_STATUS_CODE = 'I')
                    AND XTE.APPLICATION_ID = 200
                    AND XE.APPLICATION_ID = 200) OR
                (AID.AMOUNT = 0 AND AID.LINE_TYPE_LOOKUP_CODE = 'PREPAY' AND
                EXISTS (SELECT 1
                          FROM XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE
                         WHERE XE.ENTITY_ID = XTE.ENTITY_ID
                           AND XE.APPLICATION_ID = XTE.APPLICATION_ID
                           AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
                           AND XTE.ENTITY_CODE = 'AP_INVOICES'
                           AND XE.EVENT_STATUS_CODE = 'U'
                           AND XE.PROCESS_STATUS_CODE = 'U')) OR
                (EXISTS (SELECT 1
                           FROM AP_PREPAY_HISTORY_ALL APH
                          WHERE APH.INVOICE_ID = AID.INVOICE_ID
                            AND APH.POSTED_FLAG <> 'Y') AND NOT EXISTS
                 (SELECT 1
                    FROM XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE
                   WHERE XE.ENTITY_ID = XTE.ENTITY_ID
                     AND XE.APPLICATION_ID = XTE.APPLICATION_ID
                     AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
                     AND XTE.ENTITY_CODE = 'AP_INVOICES'
                     AND XE.EVENT_STATUS_CODE = 'U'
                     AND (XE.PROCESS_STATUS_CODE = 'U' OR
                         XE.PROCESS_STATUS_CODE = 'I')
                     AND XTE.APPLICATION_ID = 200
                     AND XE.APPLICATION_ID = 200)))

The whole SQL finally returns 0 rows of data, and this SQL also returns 0 rows. It should be a second kill, but it needs to run for 3 seconds. SQL is slower than EBS standard user-defined functions

So the SQL is rewritten:

SELECT TMP.INVOICE_ID
          FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL AID,
               (SELECT T.NUM1 INVOICE_ID
                  FROM NGL_UNIVERSAL_TMP_DXB T
                 WHERE T.TYPE_STR IN
                       ('INVOICE_BASE_DATA1', 'INVOICE_BASE_DATA2')
                   AND T.NUM2 = NVL(null, T.NUM2)) TMP
         WHERE TMP.INVOICE_ID = AID.INVOICE_ID
           AND AID.ACCRUAL_POSTED_FLAG <> 'Y'
           AND NVL(AID.MATCH_STATUS_FLAG, 'N') <> 'N'
          -- AND AP_INVOICES_PKG.GET_POSTING_STATUS(TMP.INVOICE_ID) = 'P'
           AND EXISTS(SELECT /*+ NO_UNNEST */ NULL FROM DUAL WHERE AP_INVOICES_PKG.GET_POSTING_STATUS(TMP.INVOICE_ID) = 'P') 
           AND (NOT EXISTS
                (SELECT 1
                   FROM XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE
                  WHERE XE.ENTITY_ID = XTE.ENTITY_ID
                    AND XE.APPLICATION_ID = XTE.APPLICATION_ID
                    AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
                    AND XTE.ENTITY_CODE = 'AP_INVOICES'
                    AND XE.EVENT_STATUS_CODE = 'U'
                    AND (XE.PROCESS_STATUS_CODE = 'U' OR
                        XE.PROCESS_STATUS_CODE = 'I')
                    AND XTE.APPLICATION_ID = 200
                    AND XE.APPLICATION_ID = 200) OR
                (AID.AMOUNT = 0 AND AID.LINE_TYPE_LOOKUP_CODE = 'PREPAY' AND
                EXISTS (SELECT 1
                          FROM XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE
                         WHERE XE.ENTITY_ID = XTE.ENTITY_ID
                           AND XE.APPLICATION_ID = XTE.APPLICATION_ID
                           AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
                           AND XTE.ENTITY_CODE = 'AP_INVOICES'
                           AND XE.EVENT_STATUS_CODE = 'U'
                           AND XE.PROCESS_STATUS_CODE = 'U')) OR
                (EXISTS (SELECT 1
                           FROM AP_PREPAY_HISTORY_ALL APH
                          WHERE APH.INVOICE_ID = AID.INVOICE_ID
                            AND APH.POSTED_FLAG <> 'Y') AND NOT EXISTS
                 (SELECT 1
                    FROM XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE
                   WHERE XE.ENTITY_ID = XTE.ENTITY_ID
                     AND XE.APPLICATION_ID = XTE.APPLICATION_ID
                     AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
                     AND XTE.ENTITY_CODE = 'AP_INVOICES'
                     AND XE.EVENT_STATUS_CODE = 'U'
                     AND (XE.PROCESS_STATUS_CODE = 'U' OR
                         XE.PROCESS_STATUS_CODE = 'I')
                     AND XTE.APPLICATION_ID = 200
                     AND XE.APPLICATION_ID = 200)))

 -- AND AP_INVOICES_PKG.GET_POSTING_STATUS(TMP.INVOICE_ID) = 'P'      --- Comment out the custom function and change it to exist / * + No_ unnest */
   AND EXISTS(SELECT /*+ NO_UNNEST */ NULL FROM DUAL WHERE AP_INVOICES_PKG.GET_POSTING_STATUS(TMP.INVOICE_ID) = 'P')

NO_UNNEST will definitely go to FILTER, which is executed in the final stage, which means that the user-defined function is finally executed. I tested it for a few seconds, three seconds before

Another is slow here:

SELECT TMP.INVOICE_ID
  FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL AID,
       (SELECT T.NUM1 INVOICE_ID
          FROM NGL_UNIVERSAL_TMP_DXB T
         WHERE T.TYPE_STR IN ('INVOICE_BASE_DATA1', 'INVOICE_BASE_DATA2')
           AND T.NUM2 = NVL(null, T.NUM2)) TMP
 WHERE TMP.INVOICE_ID = AID.INVOICE_ID
   AND AID.ACCRUAL_POSTED_FLAG = 'Y'
   AND NVL(AID.MATCH_STATUS_FLAG, 'N') <> 'N'
   AND AP_INVOICES_PKG.GET_POSTING_STATUS(TMP.INVOICE_ID) = 'Y' ---EBS Standard custom functions slow down the entire SQL,Remove it 0.0 A few seconds, not three seconds
   AND ((AID.AMOUNT = 0 AND AID.LINE_TYPE_LOOKUP_CODE = 'PREPAY' AND EXISTS
        (SELECT 'x'
            FROM XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE
           WHERE XE.ENTITY_ID = XTE.ENTITY_ID
             AND XE.APPLICATION_ID = XTE.APPLICATION_ID
             AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
             AND XE.EVENT_STATUS_CODE = 'U'
             AND XE.PROCESS_STATUS_CODE = 'U'
             AND XTE.ENTITY_CODE = 'AP_INVOICES'
             AND XTE.APPLICATION_ID = 200
             AND XE.APPLICATION_ID = 200)) OR
       (EXISTS (SELECT 1
                   FROM AP_PREPAY_HISTORY_ALL APH
                  WHERE APH.INVOICE_ID = AID.INVOICE_ID
                    AND APH.POSTED_FLAG <> 'Y')))

Therefore, refer to the above rewriting method and change the SQL to:

SELECT TMP.INVOICE_ID
  FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL AID,
       (SELECT T.NUM1 INVOICE_ID
          FROM NGL_UNIVERSAL_TMP_DXB T
         WHERE T.TYPE_STR IN ('INVOICE_BASE_DATA1', 'INVOICE_BASE_DATA2')
           AND T.NUM2 = NVL(null, T.NUM2)) TMP
 WHERE TMP.INVOICE_ID = AID.INVOICE_ID
   AND AID.ACCRUAL_POSTED_FLAG = 'Y'
   AND NVL(AID.MATCH_STATUS_FLAG, 'N') <> 'N'
  --- AND AP_INVOICES_PKG.GET_POSTING_STATUS(TMP.INVOICE_ID) = 'Y' 
   AND EXISTS(SELECT /*+ NO_UNNEST */ NULL FROM DUAL WHERE AP_INVOICES_PKG.GET_POSTING_STATUS(TMP.INVOICE_ID) = 'Y')
   AND ((AID.AMOUNT = 0 AND AID.LINE_TYPE_LOOKUP_CODE = 'PREPAY' AND EXISTS
        (SELECT 'x'
            FROM XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE
           WHERE XE.ENTITY_ID = XTE.ENTITY_ID
             AND XE.APPLICATION_ID = XTE.APPLICATION_ID
             AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
             AND XE.EVENT_STATUS_CODE = 'U'
             AND XE.PROCESS_STATUS_CODE = 'U'
             AND XTE.ENTITY_CODE = 'AP_INVOICES'
             AND XTE.APPLICATION_ID = 200
             AND XE.APPLICATION_ID = 200)) OR
       (EXISTS (SELECT 1
                   FROM AP_PREPAY_HISTORY_ALL APH
                  WHERE APH.INVOICE_ID = AID.INVOICE_ID
                    AND APH.POSTED_FLAG <> 'Y')))

After the modification, I found that I had to run for 1.5 seconds, compared with 3 seconds before, which improved, but failed to reach 0.0 seconds. After analyzing the reasons, I found that  

 AND EXISTS(SELECT /*+ NO_UNNEST */ NULL FROM DUAL WHERE AP_INVOICES_PKG.GET_POSTING_STATUS(TMP.INVOICE_ID) = 'Y')

I can use FILTER, but there is also an exists OR exists that follows FILTER. Because it is placed in front of exists OR exists, it leads to early filtering. My rewriting purpose is to FILTER finally, so I moved the position:

SELECT TMP.INVOICE_ID
  FROM AP.AP_INVOICE_DISTRIBUTIONS_ALL AID,
       (SELECT T.NUM1 INVOICE_ID
          FROM NGL_UNIVERSAL_TMP_DXB T
         WHERE T.TYPE_STR IN ('INVOICE_BASE_DATA1', 'INVOICE_BASE_DATA2')
           AND T.NUM2 = NVL(null, T.NUM2)) TMP
 WHERE TMP.INVOICE_ID = AID.INVOICE_ID
   AND AID.ACCRUAL_POSTED_FLAG = 'Y'
   AND NVL(AID.MATCH_STATUS_FLAG, 'N') <> 'N'
      -- AND AP_INVOICES_PKG.GET_POSTING_STATUS(TMP.INVOICE_ID) = 'Y'
   AND ((AID.AMOUNT = 0 AND AID.LINE_TYPE_LOOKUP_CODE = 'PREPAY' AND EXISTS
        (SELECT 'x'
            FROM XLA.XLA_TRANSACTION_ENTITIES XTE, XLA_EVENTS XE
           WHERE XE.ENTITY_ID = XTE.ENTITY_ID
             AND XE.APPLICATION_ID = XTE.APPLICATION_ID
             AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
             AND XE.EVENT_STATUS_CODE = 'U'
             AND XE.PROCESS_STATUS_CODE = 'U'
             AND XTE.ENTITY_CODE = 'AP_INVOICES'
             AND XTE.APPLICATION_ID = 200
             AND XE.APPLICATION_ID = 200)) OR
       (EXISTS (SELECT 1
                   FROM AP_PREPAY_HISTORY_ALL APH
                  WHERE APH.INVOICE_ID = AID.INVOICE_ID
                    AND APH.POSTED_FLAG <> 'Y')) AND EXISTS
        (SELECT /*+ NO_UNNEST */
          NULL
           FROM DUAL
          WHERE AP_INVOICES_PKG.GET_POSTING_STATUS(TMP.INVOICE_ID) = 'Y'))

Finally 0.0 seconds. It's done. So the position of my first rewrite should also be changed. Just know what you mean. I'm too lazy to change it. Sometimes I seriously study some problems. Sometimes I know that there are some defects, and I'm too lazy to change them. I can't help it. This is my character. I write articles and books to spread optimization ideas. I don't care too much about details. Just get to my point.

The last slow is here:

SELECT TMP.INVOICE_ID
  FROM (SELECT T.NUM1 INVOICE_ID
          FROM NGL_UNIVERSAL_TMP_DXB T
         WHERE T.TYPE_STR IN ('INVOICE_BASE_DATA1', 'INVOICE_BASE_DATA2')
           AND T.NUM2 = NVL(null, T.NUM2)) TMP,
       AP_INVOICES_ALL AP,
       AP.AP_INVOICE_DISTRIBUTIONS_ALL AID,
       AP_PREPAY_HISTORY_ALL APH
 WHERE TMP.INVOICE_ID = AID.INVOICE_ID
   AND TMP.INVOICE_ID = AP.INVOICE_ID
   AND TMP.INVOICE_ID = APH.INVOICE_ID(+)
   AND AP_INVOICES_PKG.GET_APPROVAL_STATUS(AP.INVOICE_ID, ---EBS Standard custom functions
                                           AP.INVOICE_AMOUNT, ---EBS Standard custom functions
                                           AP.PAYMENT_STATUS_FLAG, ---EBS Standard custom functions
                                           AP.INVOICE_TYPE_LOOKUP_CODE) = 'APPROVED' ---EBS Standard custom functions
   AND AID.MATCH_STATUS_FLAG = 'N'
   AND EXISTS
 (SELECT 'x'
          FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
               XLA_AE_HEADERS               AH,
               XLA_AE_LINES                 AL,
               XLA_EVENTS                   XE
         WHERE AH.AE_HEADER_ID = AL.AE_HEADER_ID
           AND AH.APPLICATION_ID = AL.APPLICATION_ID
           AND XE.ENTITY_ID = XTE.ENTITY_ID
           AND XE.APPLICATION_ID = XTE.APPLICATION_ID
           AND AH.EVENT_ID = XE.EVENT_ID
           AND AH.APPLICATION_ID = XE.APPLICATION_ID
           AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
           AND AH.ACCOUNTING_DATE >= to_date('2021-09-01', 'yyyy-mm-dd')
           AND AH.ACCOUNTING_DATE < to_date('2021-09-30', 'yyyy-mm-dd') + 1
           AND XTE.ENTITY_CODE = 'AP_INVOICES'
           AND AL.APPLICATION_ID = 200
           AND XTE.APPLICATION_ID = 200
           AND AH.APPLICATION_ID = 200
           AND XE.APPLICATION_ID = 200)

Run here for nearly 20 seconds, rewrite it, place the conditions in exists, adjust the execution plan after rewriting, and add HINT optimization.

SELECT /*+ use_nl(tmp,ap) use_nl(aid) use_nl(aph) index(ap AP_INVOICES_U1) */
 TMP.INVOICE_ID
  FROM (SELECT T.NUM1 INVOICE_ID
          FROM NGL_UNIVERSAL_TMP_DXB T
         WHERE T.TYPE_STR IN ('INVOICE_BASE_DATA1', 'INVOICE_BASE_DATA2')
           AND T.NUM2 = NVL(null, T.NUM2)) TMP,
       AP_INVOICES_ALL AP,
       AP.AP_INVOICE_DISTRIBUTIONS_ALL AID,
       AP_PREPAY_HISTORY_ALL APH
 WHERE TMP.INVOICE_ID = AID.INVOICE_ID
   AND TMP.INVOICE_ID = AP.INVOICE_ID
   AND TMP.INVOICE_ID = APH.INVOICE_ID(+)
      --  AND AP_INVOICES_PKG.GET_APPROVAL_STATUS(AP.INVOICE_ID,
      --                                          AP.INVOICE_AMOUNT,
      --                                          AP.PAYMENT_STATUS_FLAG,
      --                                          AP.INVOICE_TYPE_LOOKUP_CODE) =
      --      'APPROVED'
   AND AID.MATCH_STATUS_FLAG = 'N'
   AND EXISTS
 (SELECT /*+ no_unnest */
         'x'
          FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
               XLA_AE_HEADERS               AH,
               XLA_AE_LINES                 AL,
               XLA_EVENTS                   XE
         WHERE AH.AE_HEADER_ID = AL.AE_HEADER_ID
           AND AH.APPLICATION_ID = AL.APPLICATION_ID
           AND XE.ENTITY_ID = XTE.ENTITY_ID
           AND XE.APPLICATION_ID = XTE.APPLICATION_ID
           AND AH.EVENT_ID = XE.EVENT_ID
           AND AH.APPLICATION_ID = XE.APPLICATION_ID
           AND XTE.SOURCE_ID_INT_1 = TMP.INVOICE_ID
           AND AH.ACCOUNTING_DATE >= to_date('2021-09-01', 'yyyy-mm-dd')
           AND AH.ACCOUNTING_DATE < to_date('2021-09-30', 'yyyy-mm-dd') + 1
           AND XTE.ENTITY_CODE = 'AP_INVOICES'
           AND AL.APPLICATION_ID = 200
           AND XTE.APPLICATION_ID = 200
           AND AH.APPLICATION_ID = 200
           AND XE.APPLICATION_ID = 200
           AND AP_INVOICES_PKG.GET_APPROVAL_STATUS(AP.INVOICE_ID,
                                                   AP.INVOICE_AMOUNT,
                                                   AP.PAYMENT_STATUS_FLAG,
                                                   AP.INVOICE_TYPE_LOOKUP_CODE) = 'APPROVED')

Optimize from the previous 10-20 seconds to 0.0 seconds, and finally splice the rewritten SQL. The SQL executes a second kill at a single time, and the entire report is optimized from the previous dozens of minutes to a few minutes. That's all. There's no need to continue optimization.

Some readers will ask, why not change the function? I also want to change it, and I want to kill the LOOP loop. However, EBS developers say that whoever changes will do data verification. I dare not carry this pot. In order to optimize an SQL to the extreme, it is not worth doing so many broken things. Moreover, this is not a special EBS optimization project. It is ok to optimize it.

Recently encountered a lot of pits:

1. LOOP cycles millions of times. The single execution is fast and the comprehensive execution is slow. LOOP needs to be changed, but no one changes it. It's very troublesome to do data verification after the change. Everyone doesn't want to carry the pot
2. select... There are countless standard / user-defined functions in it. Once the amount of data processed by SQL is large, it takes thousands of seconds to run, and the user-defined function is rewritten into a scalar for 60 seconds... But they don't want to change it. Fuck, do you want me to change it? There are hundreds of reports, each report has so many SQL changes
3. There is a DBLINK in the loop loop, which is really 2B. It is strange that it is not slow to transmit back and forth to the remote local network. It has to change the code, but no one has changed it
4. A lot of NVL problems, such as AP.ORG_ID = NVL(:B1, AP.ORG_ID) goes wrong to execute the plan, FUCK. Although it can be solved by adding HINT, there are too many to read
5. There are a lot of temporary tables in SQL. They depend on temporary tables. If you want to optimize and initialize temporary tables, it takes a long time. It's really a fucking torture
six   Various garbage SQL writing methods

It's hard to avoid fatigue after a long time of technology. Hey, add 1800w to the big lotto. Retire early, ha ha.

Tags: Database Oracle Optimize EBS

Posted on Wed, 20 Oct 2021 14:02:30 -0400 by cirma