HCM - Inefficent SQL Built For My Team Direct Reports Count in DB2 (Doc ID 2128759.1)

Last updated on APRIL 19, 2016

Applies to:

PeopleSoft Enterprise HCM Human Resources - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Fluid User Interface

ACTUAL BEHAVIOR
---------------
The SQL built in HR_TEAMS.DR_TEAM.SetDirectReports method is very inefficient in DB2. It results in a tablespace scan of the PS_NAMES table as the outer join. Restructuring the SQL will result in faster response time for the users.

The probelm is not apparent in the DEMO database since there are only 19,000 rows in PS_NAMES. In DEV environment, where the slow response occurs, there are 1.9 million rows. A tablespace scan in DEMO is going to come back a lot quicker than in our Dev environment. There Clicking on the My Team tile can make the wheel spin.

EXPECTED BEHAVIOR
-----------------------
Expect My Team Tile page to appear quickly.

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1.Click on My Team Tile page in Fluid.


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot see their team and page is not loading quickly.

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