EPY - Slow performance on Review Paycheck with view PS_ZZ_PAY_CHK_VW
(Doc ID 652620.1)
Last updated on MAY 05, 2021
Applies to:
PeopleSoft Enterprise HCM Payroll for North America - Version 8.9 to 9.2 [Release 8.9 to 9]Information in this document applies to any platform.
PeopleSoft Enterprise HRMS Payroll for North America -
Enterprise, Payroll for North America, Oracle 10g
ISSUE1: Customers who have upgraded to Oracle 10g are experiencing poor performance with viewing the Review Paycheck page. Customers have noticed CPU usage escalate to 100%.
SOLUTION1: <<<
WORKAROUND1: Add Oracle hint NO_UNNEST to subquery logic on base view PS_PERS_SRCH_US.
Modify the subqueries for PS_JOB, PSTREENODE and PS_SCRTY_TBL_DEPT to include the Oracle hint, NO_UNNEST
SELECT /*+ NO_UNNEST */ MAX(JOB3.EFFSEQ) FROM PS_JOB JOB3
SELECT /*+ NO_UNNEST */ 'X' FROM PSTREENODE
SELECT /*+ NO_UNNEST */ 'X' FROM PS_SCRTY_TBL_DEPT
ISSUE2:
We are running HRMS 8.9 on oracle 10g. Pulling up review paycheck online is very slow. I found a possible solution on customer connection, 201033152. But the view PERS_SRCH_US mentioned in this solution does not exist in 8.9. What is the solution for 8.9 on 10g?
WORKAROUND2:
Customer changed the following settings:
alter system set "_optimizer_cost_based_transformation"=off
alter system set "_disable_function_based_index"=true
This helped the paycheck query. I'm not sure yet how this will affect our other performance. The other thing that is caused performance problems is UPPER. For example,
SELECT DISTINCT PAY_END_DT, TO_CHAR(PAY_END_DT,'YYYY-MM-DD'), COMPANY,
PAYGROUP, OFF_CYCLE, PAGE_NUM, LINE_NUM, SEPCHK, FORM_ID, PAYCHECK_NBR, EMPLID,
NAME FROM PS_ZZ_PAY_CHK_VW
WHERE UPPER(NAME) LIKE UPPER('smithj') || '%' ESCAPE '\'
All of our names in the database are already in uppercase so we don't need this. How can
I stop this query from automatically generating the UPPER(NAME) clause?
I customized the name field to make it upper so it took out the upper in the query. We also added oracle hints to every sql statement on the paycheck page and that helped. It is still slow when first going to the page, but then gets faster.
This document was previously published as Customer Connection Solution 201033152
Symptoms
Slow performance on Review Paycheck for view PS_ZZ_PAY_CHK_VW for Oracle 10g
Changes
Cause
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
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |