Process Mandatory Enrollments Performance Issue After Database Upgrade To 11.2.0.4 (Doc ID 1913158.1)

Last updated on JUNE 16, 2017

Applies to:

Oracle Learning Management - Version 12.1 HRMS RUP5 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, 12.1 HRMS RUP5

Customers have noticed a significant performance issue with the Process Mandatory Enrollments job since database upgrade to 11.2.0.4

OTA_MANDATORY_ENROLL_UTIL otmandatoryenr.pkb 120.4.12010000.5.1201050.3

Patch 9927648 applied.

Previous job was running in about a minute and they have the job scheduled for every three minutes.
The job now takes upwards of 20 minutes to run.



Customer has refused to set parameter on the database: Set parameter : optimizer_features_enable=11.2.0.1.
Customer is not comfortable with action plan one as it is basically a workaround to the actual problem.
The database is now 11.2.0.4 and they have already re-tuned all of their custom code to work with this version of the optimizer.


NO custom indexes on OTA tables

Ran Gather Schema Statistics on OTA and DUAL but there was no improvement.



STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run Process Mandatory Enrollments job


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