My Oracle Support Banner

EPY - Slow performance on Review Paycheck with view PS_ZZ_PAY_CHK_VW (Doc ID 652620.1)

Last updated on APRIL 02, 2019

Applies to:

PeopleSoft Enterprise HCM Payroll for North America - Version 8.9 and later
Information in this document applies to any platform.
PeopleSoft Enterprise HRMS Payroll for North America - Version: 8.9 - Release: 8.9


Information in this document applies to any platform.



  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:   <<<>>> has been submitted to development to address this problem.

 

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.