EGL: 8.9 - Journal Generator Performance Problem at FS_JGEN_JRNL.UDRILL.UDRILL

(Doc ID 655385.1)

Last updated on SEPTEMBER 27, 2017

Applies to:

PeopleSoft Enterprise FIN General Ledger - Version 8.9 and later
Information in this document applies to any platform.

This document was previously published as Customer Connection Solution 201044311


****Checked for Relevance on 08-08-2014****
****Checked for Currency on Feb-11-2013****


Symptoms


Customer is running Journal Generator process from AM and facing performance problems at FS_JGEN_JRNL.UDRILL.UDRILL. The status of the process in still showing as 'Processing'.

Following is the SQL at FS_JGEN_JRNL.UDRILL.UDRILL:

-- 18.30.29 .........................(FS_JGEN_JRNL.uDrill.uDrill) (SQL)
UPDATE PS_DIST_LN_JG_VW SET JOURNAL_ID = :1, JOURNAL_DATE = :2, FISCAL_YEAR = :3, ACCOUNTING_PERIOD = :4, GL_DISTRIB_STATUS = 'D', JOURNAL_LINE = :5 WHERE PROCESS_INSTANCE = 35841 AND GL_DISTRIB_STATUS = 'J'  AND FOREIGN_AMOUNT >=0 AND PROJECT_ID=:6 AND ACCOUNT=:7 AND ALTACCT=:8 AND DEPTID=:9 AND OPERATING_UNIT=:10 AND PRODUCT=:11 AND AFFILIATE=:12 AND LEDGER=:13 AND CURRENCY_CD=:14 AND FOREIGN_CURRENCY=:15

Customer is on PS release 8.9 MP5 and using MS SQL Server 2000 SP4.

Application of bug  11342676 did not improve performance.

They have around millions of rows in AM Accounting line table. Other subsystem do not face the performance problem probably because they do not have as much data.
 

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