GB Pensions Automatic Enrolment Process Taking Very Long Time To Run: (Doc ID 1523577.1)

Last updated on JUNE 20, 2017

Applies to:

Oracle HRMS (UK) - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms


When running the GB Pensions Automatic Enrollment process, a performance issue is hit, with the worst sql script being:

SELECT DISTINCT PAAF.ASSIGNMENT_ID
FROM
 PER_ALL_ASSIGNMENTS_F PAAF , PER_ASSIGNMENT_STATUS_TYPES PAST WHERE
  PAAF.PAYROLL_ID = :B5 AND PAAF.BUSINESS_GROUP_ID=:B4 AND PAAF.PERSON_ID
  BETWEEN :B3 AND :B2 AND PAAF.ASSIGNMENT_STATUS_TYPE_ID =
  PAST.ASSIGNMENT_STATUS_TYPE_ID AND PAST.PER_SYSTEM_STATUS ='ACTIVE_ASSIGN'
  AND PAAF.ASSIGNMENT_TYPE = 'E' AND :B1 BETWEEN PAAF.EFFECTIVE_START_DATE
  AND PAAF.EFFECTIVE_END_DATE ORDER BY ASSIGNMENT_ID


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 87 0.00 0.00 0 0 0 0
Fetch 86 836.95 1386.76 0 48872768 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 173 836.95 1386.77 0 48872768 0 1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 48 (APPS) (recursive depth: 2)

Rows Execution Plan
------- ---------------------------------------------------
  0 SELECT STATEMENT MODE: ALL_ROWS
  0 SORT (UNIQUE)
  0 FILTER
  0 NESTED LOOPS
  0 NESTED LOOPS
  0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
  'PER_ALL_ASSIGNMENTS_F' (TABLE)
  0 INDEX MODE: ANALYZED (RANGE SCAN) OF
  'PER_ASSIGNMENTS_F_N7' (INDEX)
  0 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
  'PER_ASSIGNMENT_STATUS_TYPE_PK' (INDEX (UNIQUE))
  0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
  'PER_ASSIGNMENT_STATUS_TYPES' (TABLE)

 




Steps to Reproduce:
The issue can be reproduced at will with the following steps:


1. From the UK HRMS Manager responsibility
2. Submit the concurrent request
3. Await its completion.

Prior to running the PAE concurrent request, please follow the relevant details for generating a sql trace on a concurrant request from:

    How to get a Trace first And Begin to Analyze a E-Business Applications Performance Issue (Doc ID 117129.1)

Please then compare the worst performing sql script with the details above.

If you feel that you have the same issue, please can you create a new Service Request, and upload your tkprofed sql trace.

 

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