POCFH - Fill Employee Hierarchy Performance Issue (Doc ID 1497113.1)

Last updated on AUGUST 21, 2016

Applies to:

Oracle Purchasing - Version 11.5.10.2 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.
PO_EMPLOYEE_HIERARCHIES_ALL

Symptoms

ACTUAL BEHAVIOR
---------------
When the request 'Fill Employee Hierarchy' is executed it produces hundreds of log switches and these cause performance degredation.

SYMPTOMS:
-------------
1. The trace file shows a bottleneck when inserting rows into po_employee_hieararchies, as follows:

INSERT INTO PO_EMPLOYEE_HIERARCHIES ...................
FROM PER_ASSIGNMENTS_F EASS,
PER_ASSIGNMENTS_F SASS,
PER_ASSIGNMENT_STATUS_TYPES  EPAST,
PER_ASSIGNMENT_STATUS_TYPES SPAST,
FINANCIALS_SYSTEM_PARAMETERS FSP
WHERE ((((((((((EASS.BUSINESS_GROUP_ID || '') = FSP.BUSINESS_GROUP_ID
AND EASS.POSITION_ID = :B3 ) AND EASS.PRIMARY_FLAG = 'Y')
AND EASS.ASSIGNMENT_TYPE = 'E')
AND EASS.ASSIGNMENT_STATUS_TYPE_ID = EPAST.ASSIGNMENT_STATUS_TYPE_ID
AND EPAST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND TRUNC(SYSDATE) BETWEEN  EASS.EFFECTIVE_START_DATE AND EASS.EFFECTIVE_END_DATE)
AND (SASS.BUSINESS_GROUP_ID || '') = FSP.BUSINESS_GROUP_ID)
AND SASS.POSITION_ID = :B5 )
AND SASS.PRIMARY_FLAG = 'Y')
AND SASS.ASSIGNMENT_TYPE = 'E')
AND SASS.ASSIGNMENT_STATUS_TYPE_ID = SPAST.ASSIGNMENT_STATUS_TYPE_ID
AND SPAST.PER_SYSTEM_STATUS IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
AND TRUNC(SYSDATE) BETWEEN SASS.EFFECTIVE_START_DATE AND SASS.EFFECTIVE_END_DATE)

call count cpu elapsed disk query current rows
Parse 15 0.00 0.00 0 0 0 0
Execute 15 135.79 496.55 145831 932160 14594722 4015991
Fetch 0 0.00 0.00 0 0 0 0
total 30 135.79 496.55 145831 932160 14594722 4015991

 

 

 

 

The following select statement confirms that it is the table po_employee_hierarchies_all that contains more than 4 million rows:

SQL> select count(*) from po_employee_hierarchies_all;

2. The following indexes already exist on table po_employee_hieararchies_all:

PO_EMPLOYEE_HIERARCHIES_N2 SUPERIOR_ID
PO_EMPLOYEE_HIERARCHIES_N2 POSITION_STRUCTURE_ID
PO_EMPLOYEE_HIERARCHIES_N2 BUSINESS_GROUP_ID
PO_EMPLOYEE_HIERARCHIES_N2 ORG_ID
PO_EMPLOYEE_HIERARCHIES_U1 EMPLOYEE_ID
PO_EMPLOYEE_HIERARCHIES_U1 SUPERIOR_ID
PO_EMPLOYEE_HIERARCHIES_U1 POSITION_STRUCTURE_ID
PO_EMPLOYEE_HIERARCHIES_U1 BUSINESS_GROUP_ID
PO_EMPLOYEE_HIERARCHIES_U1 ORG_ID


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