PeopleSoft CACHE and NO CACHE - Part 1

The power of the cache in PeopleSoft, enables the pages to load faster. But, to understand how this really creates a difference to PeopleSoft performance, let us try a simple SQL trace against a page that contains a single record / field and trace the activity on a page load.

 

We will try to grab a trace on the page load (i.e. after clicking on the link to the test page ). I'm trying to run a SQL trace with the following settings

 

SQL Trace Settings:-

 

SQL statements

SQL statement variables

SQL connect, disconnect, commit, rollback

SQL fetch

All other SQL API calls except SSBs

Set select buffer calls (SSBs)

Database-specific API calls

 

 

Results without cache

 

The size of the trace file is 577193 bytes. The number of SQLs executed by PeopleSoft is far far more than the number of SQLs in the trace file with it is done with cache. Some SQLs are given below

 

SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'

SELECT OBJECTTYPENAME, VERSION FROM PSVERSION

SELECT COUNT(*) FROM PSMSGNODEDEFN

SELECT MSGNODENAME FROM PSMSGNODEDEFN ORDER BY MSGNODENAME

SELECT PORTAL_OBJNAME, PORTAL_CNTPRV_NAM, PORTAL_URLTEXT  FROM PSPRSMDEFN  WHERE PORTAL_NAME = :1 AND PORTAL_REFTYPE = :2 AND PORTAL_URL_CHKSUM = :3

 

===> The value in PORTAL_URLTEXT is getting added to the URL ==> In my case it was c/CS_MNU_MSG.CS_CMP.GBL

 

SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'

SELECT TO_CHAR(LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'), LASTUPDOPRID, OBJECTOWNERID, VERSION, PORTAL_URL_CHKSUM, PORTAL_PRNTOBJNAME, PORTAL_SEQ_NUM, PORTAL_LABEL, PORTAL_CNTPRV_NAM, PORTAL_CREF_USGT, PORTAL_CREF_TMPT, PORTAL_CREF_STGT, PORTAL_CREF_URLT, DESCR254, PORTAL_PRODUCT, OPRID, TO_CHAR(PORTAL_EFFDT,'YYYY-MM-DD'), TO_CHAR(PORTAL_CREATION_DT,'YYYY-MM-DD'), TO_CHAR(PORTAL_EXPIRE_DT,'YYYY-MM-DD'), PORTAL_TEMPL_NAME, PORTAL_STG_NAME, PORTAL_ISPUBLIC, PORTAL_LINK_PORTAL, PORTAL_LINKOBJNAME, PORTAL_URI_SEG1, PORTAL_URI_SEG2, PORTAL_URI_SEG3, PORTAL_URI_SEG4, PORTAL_URLTEXT FROM PSPRSMDEFN WHERE PORTAL_NAME = :1 AND PORTAL_REFTYPE = :2 AND PORTAL_OBJNAME = :3

 

Some count SQLs running after this

 

SELECT COUNT(*) FROM PSPRSMATTR WHERE PORTAL_NAME = :1 AND PORTAL_REFTYPE = :2 AND PORTAL_OBJNAME = :3

SELECT COUNT(*) FROM PSPRSMSYSATTR WHERE PORTAL_NAME = :1 AND PORTAL_REFTYPE = :2 AND PORTAL_OBJNAME = :3

SELECT COUNT(*) FROM PSPRSMDEFNLANG WHERE PORTAL_NAME = :1 AND PORTAL_REFTYPE = :2 AND PORTAL_OBJNAME = :3

SELECT COUNT(*) FROM PSPRSMPERM WHERE PORTAL_NAME = :1 AND PORTAL_REFTYPE = :2 AND PORTAL_OBJNAME = :3

SELECT COUNT(*) FROM PSPRSMHPASGPGLT WHERE PORTAL_NAME = :1 AND PORTAL_REFTYPE = :2 AND PORTAL_OBJNAME = :3

SELECT COUNT(*) FROM PSPRSMSYNC WHERE PORTAL_NAME = :1 AND PORTAL_REFTYPE = :2 AND PORTAL_OBJNAME = :3

SELECT COUNT(*) FROM PSPRSMCTRBEXCL WHERE PORTAL_NAME = :1 AND PORTAL_REFTYPE = :2 AND PORTAL_OBJNAME = :3

SELECT COUNT(*) FROM PSPRSMWSRPCONS WHERE PORTAL_NAME = :1 AND PORTAL_REFTYPE = :2 AND PORTAL_OBJNAME = :3

SELECT COUNT(*) FROM PSPRSMWSRPPROD WHERE PORTAL_NAME = :1 AND PORTAL_REFTYPE = :2 AND PORTAL_OBJNAME = :3

 

--- Ends here.

 

SELECT PORTAL_PERMNAME, PORTAL_ISCASCADE, PORTAL_PERMTYPE FROM PSPRSMPERM WHERE PORTAL_NAME = :1 AND PORTAL_REFTYPE = :2 AND PORTAL_OBJNAME = :3 ORDER BY PORTAL_PERMNAME, PORTAL_PERMTYPE

==> permissions are obtained here.. In my case, it came as ALLPAGES , and permission type is "P"

 

SELECT COUNT(*) FROM PSPRSMDEFN     WHERE ((PORTAL_LINK_PORTAL = :1 and PORTAL_NAME = :2) or PORTAL_LINK_PORTAL = :2) AND PORTAL_REFTYPE = :3 AND PORTAL_LINKOBJNAME = :4

 

Intermittent PSVERSION selects?

 

SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'

 

SELECT TO_CHAR(LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'), LASTUPDOPRID, OBJECTOWNERID, VERSION, PORTAL_URL_CHKSUM, PORTAL_PRNTOBJNAME, PORTAL_SEQ_NUM, PORTAL_LABEL, PORTAL_CNTPRV_NAM, PORTAL_CREF_USGT, PORTAL_CREF_TMPT, PORTAL_CREF_STGT, PORTAL_CREF_URLT, DESCR254, PORTAL_PRODUCT, OPRID, TO_CHAR(PORTAL_EFFDT,'YYYY-MM-DD'), TO_CHAR(PORTAL_CREATION_DT,'YYYY-MM-DD'), TO_CHAR(PORTAL_EXPIRE_DT,'YYYY-MM-DD'), PORTAL_TEMPL_NAME, PORTAL_STG_NAME, PORTAL_ISPUBLIC, PORTAL_LINK_PORTAL, PORTAL_LINKOBJNAME, PORTAL_URI_SEG1, PORTAL_URI_SEG2, PORTAL_URI_SEG3, PORTAL_URI_SEG4, PORTAL_URLTEXT FROM PSPRSMDEFN WHERE PORTAL_NAME = :1 AND PORTAL_REFTYPE = :2 AND PORTAL_OBJNAME = :3

 

For the third bind input, DEFAULT_TEMPLATE is passed. The columns PORTAL_URI_SEG1, PORTAL_URI_SEG2, PORTAL_URI_SEG3, PORTAL_URI_SEG4 are interesting here. Together, they form a function to be invoked.

 

WEBLIB_PT_NAV. ISCRIPT1.FieldFormula < function name : IScript_PT_NAV_TPL_FRAME> The same set of COUNT SQLs followed by select on PSVERSION runs after this; the template name being DEFAULT_TEMPLATE.

 

I will stop this here and continue in my next post.