EBS 12.2 -- Create Accounting --FAH-- performance problem // real life example

In 12.2 environments, watch out the EBS Create Accounting Program!
Accounting Program/Creating accounting may not end at all.. Due to missing statistics, or because of a Database Layer bug, you may find yourself waiting for couple of days just to see the completion of a daily account process.
Especially, if you have FAH(Financial Account Hub) implementation in your project, this may start a chaos in the customer site, and make you closer to the deadline..

For these type of accounting issues, I strongly recommend you to read the following Oracle Support Doc, it has several recommendations about increasing the thoughput of the process ..

R12 SLA/FAH: How to Improve Performance in Subledger Accounting & Financials Accounting Hub (Doc ID 791049.1)

However, the most dramatic performance increase will be after applying the 11.2.0.3 database patch 14013094 and gathering the full stats using fnd_stats(or via Gather Schema Stats concurrent program)..

Lets have a quick look to the issue;

When the customer will report the performance problem, you will find at least 2 concurrent running;

One of them is parent and the other one is child..

Parent Concurrent runs the following code, waits in a loop for the Child to finish its execution;

DECLARE
l_flag BOOLEAN;
BEGIN
l_flag := XLA_CREATE_ACCT_RPT_PVT.BeforeReport;
IF (l_flag)
THEN
:XDO_OUT_PARAMETER := 1;
END IF;
END;

Child Concurrent runs the following insert and will never be able to complete it.. Thus this insert becomes the actual problem..
INSERT INTO XLA_GLT_69009 (status,
                           ledger_id,
                           user_je_source_name,
                           user_je_category_name,
                           accounting_date,
                           currency_code,
                           date_created,
                           created_by,
                           actual_flag,
                           budget_version_id,
                           encumbrance_type_id,
                           code_combination_id,
                           stat_amount,
                           entered_dr,
                           entered_cr,
                           accounted_dr,
                           accounted_cr,
                           reference1,
                           reference4,
                           reference5,
                           reference10,
                           reference11,
                           subledger_doc_sequence_id,
                           subledger_doc_sequence_value,
                           gl_sl_link_table,
                           gl_sl_link_id,
                           request_id,
                           ussgl_transaction_code,
                           je_header_id,
                           GROUP_ID,
                           period_name,
                           jgzz_recon_ref,
                           reference_date,
                           funds_reserved_flag,
                           reference25,
                           reference26,
                           reference27,
                           reference28,
                           reference29,
                           reference30)
   SELECT /*+ ordered index(aeh,xla_ae_headers_n1) use_nl(jc,led,ael,gps) */
         'NEW',
          aeh.ledger_id,
          :1,
          DECODE (:2, 'Y', jc.je_category_key, jc.user_je_category_name),
          DECODE (:3,
                  'P', gps.end_date,
                  'F', gps.end_date,
                  aeh.accounting_date)      --16967056 groupd by gl period fix
                                      ,
          DECODE (aeh.balance_type_code,
                  'E', led.currency_code,
                  ael.currency_code),
          SYSDATE,
          :4,
          aeh.balance_type_code,
          aeh.budget_version_id,
          ael.encumbrance_type_id                                   -- 4458381
                                 ,
          ael.code_combination_id,
          ael.statistical_amount,
          DECODE (aeh.balance_type_code,
                  'E', ael.accounted_dr,
                  ael.entered_dr)                                   -- 4458381
                                 ,
          DECODE (aeh.balance_type_code,
                  'E', ael.accounted_cr,
                  ael.entered_cr)                                   -- 4458381
                                 ,
          accounted_dr,
          accounted_cr,
          :5                                                     -- Reference1
            ,
             DECODE (reference_date,
                     NULL, NULL,
                     TO_CHAR (reference_date, 'DD-MON-YYYY'))
          || DECODE (:6,
                     'A', TO_CHAR (aeh.accounting_date, 'DD-MON-YYYY'),
                     'P', aeh.period_name,
                     'D', aeh.ae_header_id,
                     'E', TO_CHAR (aeh.accounting_date, 'DD-MON-YYYY') -- added E/F lookup code for bug8681466
                                                                      ,
                     'F', aeh.period_name)                        --Reference4
                                          ,
          DECODE (:7, 'D', SUBSTRB (aeh.description, 1, 240), NULL),
          DECODE (
                DECODE (:8,  'D', 'D',  'E', 'D',  'F', 'D',  'S')
             || ael.gl_transfer_mode_code --added bug 8846459 to show line description
                                         ,
             'SS', NULL,
             SUBSTRB (ael.description, 1, 240)),
          DECODE (:9 || ael.gl_transfer_mode_code,
                  'AS', jgzz_recon_ref,
                  'PS', jgzz_recon_ref,
                  aeh.ae_header_id || '-' || ael.ae_line_num)   -- Reference11
                                                             ,
          aeh.doc_sequence_id,
          aeh.doc_sequence_value,
          ael.gl_sl_link_table,
          ael.gl_sl_link_id,
          :10,
          ael.ussgl_transaction_code,
          aeh.ae_header_id,
          :11,
          aeh.period_name,
          ael.jgzz_recon_ref,
          aeh.reference_date,
          DECODE (
             led.enable_budgetary_control_flag,
             'Y', DECODE (aeh.funds_status_code,
                          'A', 'Y',
                          'S', 'Y',
                          'P', 'Y',
                          NULL),
             'Y'),
          aeh.entity_id,
          aeh.event_id,
          ael.ae_header_id,
          ael.ae_line_num,
          ael.accounted_dr,
          ael.accounted_cr
     FROM xla_ae_headers aeh,
          xla_ae_lines ael,
          gl_je_categories jc,
          gl_period_statuses gps,
          gl_ledgers led
    WHERE     ael.application_id = aeh.application_id
          AND ael.ae_header_id = aeh.ae_header_id
          AND aeh.GROUP_ID = :12
          AND aeh.application_id = :13                               --4769315
          AND aeh.je_category_name = jc.je_category_name
          AND gps.application_id = 101
          AND gps.ledger_id = aeh.ledger_id
          AND led.ledger_id = gps.ledger_id
          AND aeh.period_name = gps.period_name
          AND aeh.gl_transfer_status_code = 'S'

After making the analysis, it can be seen that Oracle have put index hints for the query, but when you look to the execution plan, you will see a Full Table Scan towards to the XLA_AE_LINES table.. 

That is , the execution plan is wrong..

For the solution, you need to apply the database patch 14013094 with all its post installation instructions , and you need to gather stats for All Schemas , not only for XLA schema..
Note that : XLA schema(Subledger Accounting) is where EBS writes its data before GL..

After taking the actions above; when you check the execution plan of that particular insert and you 'll see that now it is not making any Full Table Scans.. 

The cost of the query may increase, but you dont need to mind it.. 

It is because the old execution plan is a wrong execution plan..

After taking the action ; your exection plan will be something like this and it is acceptable..

Okay... while we are reaching to the end of this post,  I 'm happy to say that, these actions made a problematic accounting process (including 24894 events and 68414 lines) to complete in 7 mins.. 

Note that , this was implemented on an EBS 12.2 customer environment with the FAH implementations..

Hope you will find this helpful.