My Oracle Support Banner

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

Last updated on FEBRUARY 21, 2019

Applies to:

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


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


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;


  HR_Person_Absence_API.Create_Person_Absence (p_validate => FALSE,
  p_effective_date => TO_DATE('27-APR-2012','DD-MON-YYYY'),
  p_person_id => XXXX,
  p_business_group_id => XXX,
  p_Absence_Attendance_Type_ID => XX,
  p_Abs_Attendance_Reason_ID => XX,
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

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


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.