Performance Issue With OIC Calculation on ON FND_CONCURRENT_REQUESTS (Doc ID 1080362.1)

Last updated on SEPTEMBER 02, 2016

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



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

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