R120 AP: XLAACCPB Create Accounting "Hash Unique" Performance Issue (Doc ID 869323.1)

Symptoms

On R12.0.6, the create accounting request is consuming too much time when trying to run it for
invoices with many lines.

The Create Accounting program, whatever is the sub ledger involved (AP,AR,FA,etc), is suffering from
the hash unique performance issue as described in the RDBMS <bug 4926618>
(HASH UNIQUE TAKES 100 TIMES MORE TIME TO SORT THAN SORT UNIQUE).

The problem specifically surfaces when a high number of distribution lines or AE lines are being
manipulated by the accounting program; specifically in the package xla_ae_code_combination_pkg
(xlajecci.pkb 120.53.12), whenever a statement UPDATE …. SELECT DISTINCT … is involved.

Naturally, this performance is hitting the roof only when the number of lines manipulated is huge; when such an invoice is accounted, the accounting program runs for hours.

The tkprof will show the following time consuming statements:

BEGIN xla_accounting_pkg.unit_processor_batch(:errbuf,:rc,:A0,:A1,:A2,:A3,:A4,
:A5,:A6,:A7,:A8,:A9,:A10,:A11,:A12,:A13,:A14); END;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 6989.03 8945.90 2833190 46773738 31565681 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 6989.03 8945.90 2833190 46773738 31565681 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 44

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 0.00 0.00
Streams AQ: waiting for messages in the queue
1 3.23 3.23
log file sync 1 0.01 0.01
********************************************************************************

UPDATE /*+ dynamic_sampling(1) */ XLA_AE_LINES_GT TEMP SET (
TEMP.CODE_COMBINATION_ID ,TEMP.SEGMENT1 ,TEMP.SEGMENT2 ,TEMP.SEGMENT3 ,
TEMP.SEGMENT4 ,TEMP.SEGMENT5 ,TEMP.SEGMENT6 ,TEMP.SEGMENT7 ,TEMP.SEGMENT8 ,
TEMP.SEGMENT9 ,TEMP.SEGMENT10 ,TEMP.SEGMENT11 ,TEMP.SEGMENT12 ,
TEMP.SEGMENT13 ,TEMP.SEGMENT14 ,TEMP.SEGMENT15 ,TEMP.SEGMENT16 ,
TEMP.SEGMENT17 ,TEMP.SEGMENT18 ,TEMP.SEGMENT19 ,TEMP.SEGMENT20 ,
TEMP.SEGMENT21 ,TEMP.SEGMENT22 ,TEMP.SEGMENT23 ,TEMP.SEGMENT24 ,
TEMP.SEGMENT25 ,TEMP.SEGMENT26 ,TEMP.SEGMENT27 ,TEMP.SEGMENT28 ,
TEMP.SEGMENT29 ,TEMP.SEGMENT30 ,TEMP.CODE_COMBINATION_STATUS_CODE) = (
SELECT /*+ INDEX (XTA XLA_TRANSACTION_ACCTS_GT_N1) LEADING (XTA) */
DISTINCT DECODE(GAMI.ERROR_CODE, NULL, GAMI.TO_CCID, -1) ,
NVL(TEMP.SEGMENT1 , GAMI.TO_SEGMENT1) , NVL(TEMP.SEGMENT2 ,
GAMI.TO_SEGMENT2) , NVL(TEMP.SEGMENT3 , GAMI.TO_SEGMENT3) ,
NVL(TEMP.SEGMENT4 , GAMI.TO_SEGMENT4) , NVL(TEMP.SEGMENT5 ,
GAMI.TO_SEGMENT5) , NVL(TEMP.SEGMENT6 , GAMI.TO_SEGMENT6) ,
NVL(TEMP.SEGMENT7 , GAMI.TO_SEGMENT7) , NVL(TEMP.SEGMENT8 ,
GAMI.TO_SEGMENT8) , NVL(TEMP.SEGMENT9 , GAMI.TO_SEGMENT9) ,
NVL(TEMP.SEGMENT10, GAMI.TO_SEGMENT10) , NVL(TEMP.SEGMENT11,
GAMI.TO_SEGMENT11) , NVL(TEMP.SEGMENT12, GAMI.TO_SEGMENT12) ,
NVL(TEMP.SEGMENT13, GAMI.TO_SEGMENT13) , NVL(TEMP.SEGMENT14,
GAMI.TO_SEGMENT14) , NVL(TEMP.SEGMENT15, GAMI.TO_SEGMENT15) ,
NVL(TEMP.SEGMENT16, GAMI.TO_SEGMENT16) , NVL(TEMP.SEGMENT17,
GAMI.TO_SEGMENT17) , NVL(TEMP.SEGMENT18, GAMI.TO_SEGMENT18) ,
NVL(TEMP.SEGMENT19, GAMI.TO_SEGMENT19) , NVL(TEMP.SEGMENT20,
GAMI.TO_SEGMENT20) , NVL(TEMP.SEGMENT21, GAMI.TO_SEGMENT21) ,
NVL(TEMP.SEGMENT22, GAMI.TO_SEGMENT22) , NVL(TEMP.SEGMENT23,
GAMI.TO_SEGMENT23) , NVL(TEMP.SEGMENT24, GAMI.TO_SEGMENT24) ,
NVL(TEMP.SEGMENT25, GAMI.TO_SEGMENT25) , NVL(TEMP.SEGMENT26,
GAMI.TO_SEGMENT26) , NVL(TEMP.SEGMENT27, GAMI.TO_SEGMENT27) ,
NVL(TEMP.SEGMENT28, GAMI.TO_SEGMENT28) , NVL(TEMP.SEGMENT29,
GAMI.TO_SEGMENT29) , NVL(TEMP.SEGMENT30, GAMI.TO_SEGMENT30) , CASE WHEN
GAMI.ERROR_CODE IS NULL THEN CASE TEMP.CODE_COMBINATION_STATUS_CODE WHEN
:B2 THEN :B5 WHEN :B4 THEN :B3 ELSE TEMP.CODE_COMBINATION_STATUS_CODE END
ELSE :B2 END FROM GL_ACCTS_MAP_INT_GT GAMI , XLA_TRANSACTION_ACCTS_GT XTA
WHERE
XTA.AE_HEADER_ID = TEMP.AE_HEADER_ID AND XTA.TEMP_LINE_NUM =
TEMP.TEMP_LINE_NUM AND XTA.LEDGER_ID = TEMP.LEDGER_ID AND
XTA.SL_COA_MAPPING_ID = TEMP.SL_COA_MAPPING_ID AND GAMI.FROM_CCID =
XTA.CODE_COMBINATION_ID AND GAMI.COA_MAPPING_ID = XTA.SL_COA_MAPPING_ID AND
XTA.PROCESSING_STATUS_CODE = 'MAP_CCID' AND XTA.SIDE_CODE IN ('ALL',
'CREDIT','NA') AND XTA.SL_COA_MAPPING_ID = :B1 AND TEMP.CODE_COMBINATION_ID
IS NULL ) WHERE TEMP.CODE_COMBINATION_ID IS NULL AND TEMP.SL_COA_MAPPING_ID
= :B1 AND TEMP.BALANCE_TYPE_CODE <> 'X' AND EXISTS (SELECT 'x' FROM
XLA_TRANSACTION_ACCTS_GT T WHERE T.AE_HEADER_ID = TEMP.AE_HEADER_ID AND
T.TEMP_LINE_NUM = TEMP.TEMP_LINE_NUM AND T.LEDGER_ID = TEMP.LEDGER_ID AND
T.SL_COA_MAPPING_ID = TEMP.SL_COA_MAPPING_ID AND T.PROCESSING_STATUS_CODE =
'MAP_CCID' AND T.SL_COA_MAPPING_ID = :B1 )


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 4345.37 4309.31 20129 1683564 685467 202810
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 4345.37 4309.31 20129 1683564 685467 202810

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 44 (recursive depth: 1)

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.