Applicant Hire and Rehire is causing performance issue - internal server error (Doc ID 2211077.1)

Last updated on JANUARY 16, 2017

Applies to:

Oracle Self-Service Human Resources - Version 12.1.3 to 12.1 HRMS RUP4 [Release 12.1]
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Manager Self Services

When attempting to hire an applicant from self service,
the following error occurs.

ERROR
-----------------------
Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator, and inform them of the time the error occurred,
and anything you might have done that may have caused the error.
More information about this error may be available in the server error log.



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

1. Using Manager Self Service responsibility

2. Select Applicant Hire and Rehire function

3. Query the applicant using below selection criteria:
  Applicant number:
  Vacancy:
  Effective date:
  Person Type: All applicants

4. Got above error after 10-15 minutes

To identify the problematic query generated RAW SQL Trace with Binds and Waits files in Self Service while reproducing the issue, as per <Note 190685.1>:

Run TKPROF procedure on ALL the RAW trace files generated: 

tkprof <raw_trace_file.trc> ~/trace1.tkt explain=apps/apps sort='prsela,exeela,fchela'

In this case ...

the problematic query appears to contain the following statement:

(...)

Select 'e' From hr_api_transaction_steps ts, wf_item_attribute_values sfl
Where ts.api_name = 'HR_PROCESS_PERSON_SS.PROCESS_API'
and ts.item_type = sfl.item_type
and ts.item_key = sfl.item_key
and sfl.name = 'TRAN_SUBMIT' and sfl.text_value = 'Y'
and exists (Select 'e' From hr_api_transaction_values p
Where p.transaction_step_id = ts.transaction_step_id
and p.name = 'P_PERSON_ID' and p.number_value = ppf.person_id)),
'e','Y','N') pending_approval,

(...)

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