My Oracle Support Banner

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

Last updated on FEBRUARY 14, 2018

Applies to:

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


On : 9.2 version, Fluid User Interface

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.

Expect My Team Tile page to appear quickly.

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

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


To view full details, 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 a vibrant support community of peers and Oracle experts.