PeopleSoft Recruiting - Browse Job Openings too slow

An HCM 8.9 customer recently complained to me of unreasonably long waits to browse Job Openings.  Skeptical and hasty, I assumed the problem was the individual's user ID or their computer.  Maybe too many Job Openings related to their ID, or perhaps clearing the browser cache would help?

Wrong and wrong.

My own user ID had no Job Openings related to it (in the SJT tables) and it still took almost 10 seconds for a blank search list to appear, when navigating  to Recruiting > Browse Job Openings. Reluctantly I performed a SQL trace which lead me to the view PS_HRS_JO_ALL_I.

In running the raw View SQL, indeed, the wait time was long...30 seconds. And no, the Solaris/Oracle server wasn't really doing anything else at the time. Out of humble common sense, I checked Oracle Metalink for any postings regarding this view, and it turns out HRS_JO_ALL_I has a history of performance problems that were supposedly fixed in Bundle 14 back in 2008.  That may be so, but it didn't help my customer.

Interestingly, I found that the WHERE clause for the same view in HCM 9.1 worked much faster. It has some key joins related to the 2 Security Sets used: 'RSOPN' & 'PPLJOB'.  That was the big difference. In my customer's patch level of HCM 8.9, only one set of joins to the Security Join Tables SJT_CLASS_ALL & HRS_SJT_JO were used for both Security Sets, while in HCM 9.1 they improve the logic to join these tables once for each Security Set.

Using the HCM 9.1 WHERE clause logic tested out remarkably fast. My customer's wait time went from 60 seconds to less than 5. Problem solved!

Original HCM 8.9 WHERE clause:

WHERE A.HRS_JOB_OPENING_ID IN (  SELECT JO.HRS_JOB_OPENING_ID   FROM PS_HRS_SJT_JO JO
                                  WHERE (JO.EMPLID = OPR.EMPLID    AND JO.EMPLID <> ' ')
    OR EXISTS (  SELECT 'X'   FROM PS_SJT_CLASS_ALL CLS   , PS_SJT_OPR_CLS RSC
                                WHERE RSC.CLASSID = CLS.CLASSID
                                AND RSC.OPRID = OPR.OPRID
                                AND ( ( CLS.SCRTY_SET_CD = 'RSOPN'  AND CLS.SCRTY_TYPE_CD = JO.SCRTY_TYPE_CD)
                                   OR ( CLS.SCRTY_SET_CD = 'PPLJOB' AND CLS.SCRTY_TYPE_CD = '001' )
                                     )
                  AND CLS.SCRTY_KEY1 = JO.SCRTY_KEY1
                  AND CLS.SCRTY_KEY2 = JO.SCRTY_KEY2
                  AND CLS.SCRTY_KEY3 = JO.SCRTY_KEY3
                  AND JO.EMPLID = ' '
                )
                                                       )

HCM 9.1 WHERE clause:

WHERE A.HRS_JOB_OPENING_ID IN (  SELECT JO.HRS_JOB_OPENING_ID   FROM PS_HRS_SJT_JO JO
                                  WHERE JO.EMPLID = OPR.EMPLID   AND JO.EMPLID <> ' ' )
    OR A.HRS_JOB_OPENING_ID IN (  SELECT JO.HRS_JOB_OPENING_ID   FROM PS_HRS_SJT_JO JO   ,PS_SJT_CLASS_ALL CLS   , PS_SJT_OPR_CLS RSC
                                   WHERE RSC.CLASSID = CLS.CLASSID
                                   AND RSC.OPRID = OPR.OPRID
                                   AND CLS.SCRTY_SET_CD = 'RSOPN'
                                   AND CLS.SCRTY_TYPE_CD = JO.SCRTY_TYPE_CD
                                   AND CLS.SCRTY_KEY1 = JO.SCRTY_KEY1
                                   AND CLS.SCRTY_KEY2 = JO.SCRTY_KEY2
                                   AND CLS.SCRTY_KEY3 = JO.SCRTY_KEY3
                                   AND JO.EMPLID = ' ' )
    OR A.HRS_JOB_OPENING_ID IN ( SELECT JO.HRS_JOB_OPENING_ID   FROM PS_HRS_SJT_JO JO   ,PS_SJT_CLASS_ALL CLS   , PS_SJT_OPR_CLS RSC
                                 WHERE RSC.CLASSID = CLS.CLASSID
                                   AND RSC.OPRID = OPR.OPRID
                                   AND CLS.SCRTY_SET_CD = 'PPLJOB'
                                   AND CLS.SCRTY_TYPE_CD = '001'
                                   AND CLS.SCRTY_KEY1 = JO.SCRTY_KEY1
                                   AND CLS.SCRTY_KEY2 = JO.SCRTY_KEY2
                                   AND CLS.SCRTY_KEY3 = JO.SCRTY_KEY3
                                   AND JO.EMPLID = ' ' )