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.