R12: GLPPOS Posting Performance Issue for Batches with Large Number of Journals and Sequence Generation (Doc ID 1478696.1)

Last updated on SEPTEMBER 09, 2016

Applies to:

Oracle General Ledger - Version 12.1.3 and later
Information in this document applies to any platform.
Executable:GLPPOS - Posting
Posting, Auto-Post, Performance, glpsqn(), glpfdel()

Symptoms

On 12.1.3 version, the Posting of a batch with over 85,000 journal headers takes about 1.5 hours.
The most time is spent in the following modules:

1) >> glpsqs() about 20 minutes

2) >> glpsqn() about 30 minutes

3) glpfdel()  more than 40 minutes

The trace file shows the most expensive statement as below:

UPDATE FUN_SEQ_BATCH_GT GT
SET GT.SEQ_VERSION_ID = :B3 , GT.ASSIGNMENT_ID = :B2 , GT.SEQ_VALUE = :B1
WHERE GT.SOURCE_ID = :B4

----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id   | Operation                  | Name                       | Starts  |  E-Rows | A-Rows |   A-Time     | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------------------------------------------------
|    1 |  UPDATE                   | FUN_SEQ_BATCH_GT |  67284 |            |          0 |00:18:19.31 |      44M |       1 |
|*  2 |  TABLE ACCESS FULL  |  FUN_SEQ_BATCH_GT |  67284 |         7 |   67284 |00:18:09.28 |      43M |       0 |
----------------------------------------------------------------------------------------------------------------------------------------------------------------

update GL_JE_HEADERS JEH
set (posting_acct_seq_version_id, posting_acct_seq_assign_id, posting_acct_seq_value)=
   (select seq_version_id , assignment_id , seq_value
    from FUN_SEQ_BATCH_GT
    where (source_id=JEH.je_header_id and status_code in ('SEQ_VER_FOUND', 'DO_NOT_SEQUENCE')))
where JEH.je_batch_id in
   (select JEB.je_batch_id
    from GL_JE_BATCHES JEB
    where ((JEB.status='I' and JEB.chart_of_accounts_id=:b0) and JEB.posting_run_id=:b1))

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms