PeopleSoft Learning

PSQuery Performance issues with Query Security Tables

We see PSQuery having bad performance due to many Query Security Join tables that the PSQuery tool adds to the PSQuery SQL for security.


We have few options to work around to easily improve the performance without much involving tunning from the DBA perspective which can time consuming.

 If security is not required for this query 

 Change the Query type to 'Process'. Process queries override the automatic row-level query security logic that is applied to all other types of queries.
   
    It will be useful for the PSQuery in Pagelet specific to the logged-in users where security is not important.

   If you are using Campus Solution and you don't know Campus solution specific query tables, you don't need Security to join tables.

 If Security is required

  Select the Security optimization checkbox, it will eliminate duplicate security.
  if still, more than one Security join tables are added and need improvement in performance, 
  one security join should be fine usually and for other records view of the table without security join table.

Identify navigation in PeopleSoft by a Process name and/or Job name

There are many SQL statements in different blogs to identify the navigation for a process.
But I have modified that to get the Navigation for a Job.
Single SQL works for both Process and Job.


SELECT DISTINCT RTRIM( REVERSE ( SYS_CONNECT_BY_PATH(REVERSE (portal_label), '>-')) ,' >- ') PATH
      FROM PSPRSMDEFN
     WHERE portal_name = 'EMPLOYEE'
       AND portal_prntobjname = 'PORTAL_ROOT_OBJECT'
START WITH portal_uri_seg2 IN (
                       SELECT DISTINCT pnlgrpname
                       FROM ps_prcsdefnpnl
                       WHERE prcsname = UPPER(:1)
                       UNION
                       Select Distinct PNLGRPNAME
                       FROM PS_PRCSJOBPNL
                       Where PRCSJOBNAME = UPPER(:1) )
CONNECT BY PRIOR portal_prntobjname = portal_objname;


Hope this will be useful.

SQL to Identify detailed Security access for a Page or Component.

When you want details about a single page in the component.

select a.menuname,a.barname,a.baritemname,rc.rolename, rd.descr , a.classid, c.classdefndesc,a.Displayonly,ac.pageaccessdescr
from psauthitem a, psclassdefn c, psroleclass rc, psroledefn rd, PSPGEACCESSDESC ac
where a.pnlitemname = :1
and a.classid = c.classid
and a.classid = rc.classid
and rc.rolename = rd.rolename
and a.Authorizedactions = ac.Authorizedactions
Order by rc.rolename;


When you want details about a component.

select a.menuname,a.barname,a.baritemname,rc.rolename, rd.descr , a.classid, c.classdefndesc,a.Displayonly,ac.pageaccessdescr
from psauthitem a, psclassdefn c, psroleclass rc, psroledefn rd, PSPGEACCESSDESC ac
where a.BARITEMNAME = :1
and a.classid = c.classid
and a.classid = rc.classid
and rc.rolename = rd.rolename
and a.Authorizedactions = ac.Authorizedactions
Order by rc.rolename;