My Oracle Support Banner

GL9.2: When Running Budget Check FS_BP Process From Commitment Control / Third Party Transactions / Budget Check HR Payroll This Is Not Using ReUse Statement Property For SQL Steps and The Bind Values Are Passed As Literals And Performance Is Impacted (Doc ID 2988328.1)

Last updated on JANUARY 03, 2024

Applies to:

PeopleSoft Enterprise FIN General Ledger - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

While executing Budget Check using the below navigation, the AE statement timing trace (-TRACE 128) shows several AE Steps where the Compile Count is very high because the AE SQL Step in the AE definition (app designer) does not have the ReUse Statement set to 'Yes'.

Commitment Control > Third Party Transactions > Budget Check HR Payroll

As a result, the Bind values are passed as literals.

One example is FS_BP_POST.UpdLedg.uLedger SQL step. In the AET file, it has Compile Count = 12128.
In the database trace (tkprof), searching for UPDATE PS_LEDGER_KK results in 12128 distinct SQL Plan Ids and each has literal values instead of Bind values.
If ReUse Statement is set to 'Yes' for the step in the AE definition, then the Compile Count will be 1 and execute count will be 12128 both in AET and database trace.
There are several other places: FS_BP_CHECK.ProcBudg.GetAmt SQL step.

 

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