Performance Issue With OIC Calculation on ON FND_CONCURRENT_REQUESTS
Last updated on FEBRUARY 09, 2018
Applies to:
Oracle Incentive Compensation - Version 12.0.0 to 12.0.5 [Release 12.0]Information in this document applies to any platform.
Symptoms
The Calculation process is taking more than 1 hour and 20 minutes to run 2000 transactions.
The profile OIC: Frequency of Batch Runners Status Check is set at correct value - passed it from 15 to 60 seconds
The tkprof'd trace file shows the explain plan with PL/SQL Lock Timer events
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
library cache lock 1 0.00 0.00
row cache lock 12 0.00 0.00
library cache pin 14 0.00 0.00
PL/SQL lock timer 315 180.01 32761.70
**********************************************************
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
library cache lock 1 0.00 0.00
row cache lock 12 0.00 0.00
library cache pin 14 0.00 0.00
PL/SQL lock timer 315 180.01 32761.70
**********************************************************
The issue can be reproduced at will with the following steps:
(R) Compensation Manager
(N) Calculate Compensation
The select statement that is causing the problem is:
SELECT PHASE_CODE, STATUS_CODE, COMPLETION_TEXT, PHASE.LOOKUP_CODE,
STATUS.LOOKUP_CODE, PHASE.MEANING, STATUS.MEANING
FROM
FND_CONCURRENT_REQUESTS R, FND_CONCURRENT_PROGRAMS P, FND_LOOKUPS PHASE,
FND_LOOKUPS STATUS WHERE PHASE.LOOKUP_TYPE = :B3 AND PHASE.LOOKUP_CODE =
DECODE(STATUS.LOOKUP_CODE, 'H', 'I', 'S', 'I', 'U', 'I', 'M', 'I',
R.PHASE_CODE) AND STATUS.LOOKUP_TYPE = :B2 AND STATUS.LOOKUP_CODE =
DECODE(R.PHASE_CODE, 'P', DECODE(R.HOLD_FLAG, 'Y', 'H',
DECODE(P.ENABLED_FLAG, 'N', 'U', DECODE(SIGN(R.REQUESTED_START_DATE -
SYSDATE),1,'P', R.STATUS_CODE))), 'R', DECODE(R.HOLD_FLAG, 'Y', 'S',
DECODE(R.STATUS_CODE, 'Q', 'B', 'I', 'B', R.STATUS_CODE)), R.STATUS_CODE)
AND (R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID AND
R.PROGRAM_APPLICATION_ID= P.APPLICATION_ID ) AND REQUEST_ID = :B1
STATUS.LOOKUP_CODE, PHASE.MEANING, STATUS.MEANING
FROM
FND_CONCURRENT_REQUESTS R, FND_CONCURRENT_PROGRAMS P, FND_LOOKUPS PHASE,
FND_LOOKUPS STATUS WHERE PHASE.LOOKUP_TYPE = :B3 AND PHASE.LOOKUP_CODE =
DECODE(STATUS.LOOKUP_CODE, 'H', 'I', 'S', 'I', 'U', 'I', 'M', 'I',
R.PHASE_CODE) AND STATUS.LOOKUP_TYPE = :B2 AND STATUS.LOOKUP_CODE =
DECODE(R.PHASE_CODE, 'P', DECODE(R.HOLD_FLAG, 'Y', 'H',
DECODE(P.ENABLED_FLAG, 'N', 'U', DECODE(SIGN(R.REQUESTED_START_DATE -
SYSDATE),1,'P', R.STATUS_CODE))), 'R', DECODE(R.HOLD_FLAG, 'Y', 'S',
DECODE(R.STATUS_CODE, 'Q', 'B', 'I', 'B', R.STATUS_CODE)), R.STATUS_CODE)
AND (R.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID AND
R.PROGRAM_APPLICATION_ID= P.APPLICATION_ID ) AND REQUEST_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