My Oracle Support Banner

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

Last updated on FEBRUARY 25, 2019

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

NOTE: In the images below and/or the attached document, user details / company name / address / email / telephone number represent a fictitious sample (based upon made up data used in the Oracle Demo Vision instance).  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

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))

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


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