Standard Package PER_PER_BUS.RETURN_LEGISLATION_CODE Performing Full Table Scan (Doc ID 1478281.1)

Last updated on JULY 18, 2016

Applies to:

Oracle Human Resources - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On Production: 12.1.3 version, Performance


Encounter performance issue when running any of the following APIs


Query performs full table scan and results in very poor performance.


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

1. Create the API with the following code

DECLARE

v_absence_id NUMBER;
v_Object_Version_Number NUMBER :=1;
v_occurrence NUMBER;
v_Dur_Days_Less_Warn BOOLEAN;
v_Dur_Hrs_Less_Warn BOOLEAN;
v_Exceeds_PTO_Entit_Warn BOOLEAN;
v_Excceds_Run_Total_Warn BOOLEAN;
v_Abs_Overlap_Warn BOOLEAN;
v_Abs_Day_After_Warn BOOLEAN;
v_Dur_overwritten_Warn BOOLEAN;
v_duration NUMBER;
v_absence_days NUMBER;

BEGIN

  HR_Person_Absence_API.Create_Person_Absence (p_validate => FALSE,
  p_effective_date => TO_DATE('27-APR-2012','DD-MON-YYYY'),
  p_person_id => 4426,
  p_business_group_id => 202,
  p_Absence_Attendance_Type_ID => 71,
  p_Abs_Attendance_Reason_ID => 93,
p_Time_Start => '07:00',
  p_Time_End => '18:00',
  p_Date_Start => TO_DATE('25-APR-2012','DD-MON-YYYY'),
  p_Date_End => TO_DATE('26-APR-2012','DD-MON-YYYY'),
  p_Sickness_Start_Date => TO_DATE('25-APR-2012','DD-MON-YYYY'),
  p_Sickness_End_Date => TO_DATE('26-APR-2012','DD-MON-YYYY'),
  p_Date_Notification => TO_DATE('27-APR-2012','DD-MON-YYYY'),
  p_Absence_Hours => v_Duration,
  p_absence_days => v_absence_days,
  p_Absence_Attendance_ID => v_Absence_id,
  p_Object_Version_Number => v_Object_Version_Number,
  p_Occurrence => v_Occurrence,
  p_Dur_Dys_Less_Warning => v_Dur_Days_Less_Warn,
  p_Dur_Hrs_Less_Warning => v_Dur_Hrs_Less_Warn,
  p_Exceeds_PTO_Entit_Warning => v_Exceeds_PTO_Entit_Warn,
  p_Exceeds_Run_Total_Warning => v_Excceds_Run_Total_Warn,
  p_Abs_Overlap_Warning => v_Abs_Overlap_Warn,
  p_Abs_Day_After_Warning => v_Abs_Day_After_Warn,
  p_Dur_overwritten_Warning => v_Dur_overwritten_Warn
  );
END;
/

2. Run the code in a SQL session on the server - this completes but there is performance overhead.

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