PeopleSoft CACHE Analysis - Part 2

We are trying to understand the difference caching makes in PeopleSoft, to improve the page performance. We have analysed some of the SQLs that PeopleSoft executes when there is no caching by triggering an SQL trace and grabbing the SQLs executed by the application server. Let us continue this analysis of the remaining SQLs in this post. I have covered only the important SQLs executed and queries like selects on PSVERSION table of PeopleSoft, are skipped.

 

select PT_CTI_AGENTID, PT_CTI_QUEUE, PT_CTI_CONFIGID from PS_PT_CTI_AGENT A where OPRID = :1 AND EFFDT = (SELECT MAX(EFFDT) FROM PS_PT_CTI_AGENT B WHERE A.OPRID= B.OPRID AND EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))

 

select MCFUQAGENTID from PSMCFUQAGENTQ WHERE MCFUQAGENTID = :1

 

SELECT ALTCONTNUM, CONTFMT, VERSION, CONTSTYLE, DESCR, URL, COMPALG, AUXFLAGMASK, LASTUPDOPRID, TO_CHAR(LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"') FROM PSCONTDEFN WHERE CONTNAME = :1 AND CONTTYPE = :2 ORDER BY ALTCONTNUM

=> style sheet specific queries

 

SELECT DESCR, ACTIONS, VERSION, SEARCHRECNAME, ADDSRCHRECNAME,  SEARCHPNLNAME, LOADLOC, SAVELOC, DISABLESAVE, PRIMARYACTION, DFLTACTION, DFLTSRCHTYPE,  DEFERPROC, EXPENTRYPROC, REQSECURESSL, INCLNAVIGATION, FORCESEARCH, ALLOWACTMODESEL, PNLNAVFLAGS,  TBARBTNS, SHOWTBAR, ADDLINKMSGSET, ADDLINKMSGNUM, SRCHLINKMSGSET, SRCHLINKMSGNUM,  SRCHTEXTMSGSET, SRCHTEXTMSGNUM, OBJECTOWNERID, TO_CHAR(LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'), LASTUPDOPRID,  DESCRLONG   FROM PSPNLGRPDEFN WHERE PNLGRPNAME = :1 AND MARKET = :2

==> search record details

 

SELECT PNLNAME, ITEMNAME, HIDDEN, ITEMLABEL, FOLDERTABLABEL, SUBITEMNUM FROM PSPNLGROUP WHERE PNLGRPNAME = :1 AND MARKET = :2 ORDER BY SUBITEMNUM

=> Actual labels associated with the page to be displayed

 

SELECT MENULABEL, MENUGROUP, GROUPORDER, MENUORDER, VERSION, INSTALLED, GROUPSEP, MENUSEP, MENUTYPE, OBJECTOWNERID, LASTUPDOPRID, TO_CHAR(LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'), DESCR, DESCRLONG FROM PSMENUDEFN WHERE MENUNAME = :1

 

SELECT BARNAME, ITEMNAME, BARLABEL, ITEMLABEL, MARKET, ITEMTYPE, PNLGRPNAME, SEARCHRECNAME, ITEMNUM, XFERCOUNT FROM PSMENUITEM WHERE MENUNAME = :1 ORDER BY ITEMNUM

 

=> Menu details and order retrieved here.

 

SELECT VERSION, FIELDCOUNT, RECTYPE, RECUSE, OPTTRIGFLAG, AUDITRECNAME, SETCNTRLFLD, RELLANGRECNAME, OPTDELRECNAME, PARENTRECNAME, QRYSECRECNAME, SQLTABLENAME, BUILDSEQNO, OBJECTOWNERID, TO_CHAR(LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'), LASTUPDOPRID, SYSTEMIDFIELDNAME, TIMESTAMPFIELDNAME, RECDESCR, AUXFLAGMASK, DESCRLONG  FROM PSRECDEFN WHERE RECNAME = :1

 

==> Search record specific information is being retrieved in this step. In my case, the search record name that was passed is INSTALLATION

 

SELECT LANGUAGE_CD, RECDESCR, DESCRLONG FROM PSRECDEFNLANG WHERE RECNAME = :1

 

=> Related language record information is fetched from PSRECDEFNLANG table.

 

SELECT VERSION, A.FIELDNAME, FIELDTYPE, LENGTH, DECIMALPOS, FORMAT, FORMATLENGTH, IMAGE_FMT, FORMATFAMILY, DISPFMTNAME, DEFCNTRYYR,IMEMODE,KBLAYOUT,OBJECTOWNERID, DEFRECNAME, DEFFIELDNAME, CURCTLFIELDNAME, USEEDIT, USEEDIT2, EDITTABLE, DEFGUICONTROL, SETCNTRLFLD, LABEL_ID, TIMEZONEUSE, TIMEZONEFIELDNAME, CURRCTLUSE, RELTMDTFIELDNAME, TO_CHAR(B.LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'), B.LASTUPDOPRID, B.FIELDNUM, A.FLDNOTUSED, A.AUXFLAGMASK, B.RECNAME FROM PSDBFIELD A, PSRECFIELD B WHERE B.RECNAME = :1 AND A.FIELDNAME = B.FIELDNAME AND B.SUBRECORD = 'N' ORDER BY B.RECNAME, B.FIELDNUM

 

SELECT FIELDNUM, FIELDNAME, TO_CHAR(LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'), LASTUPDOPRID, RECNAME FROM PSRECFIELD WHERE RECNAME = :1 AND SUBRECORD = 'Y' ORDER BY RECNAME, FIELDNUM

 

SELECT FIELDNAME, LABEL_ID, LONGNAME, SHORTNAME, DEFAULT_LABEL FROM PSDBFLDLABL WHERE FIELDNAME IN (SELECT A.FIELDNAME FROM PSDBFIELD A, PSRECFIELD B WHERE B.RECNAME = :1 AND A.FIELDNAME = B.FIELDNAME) ORDER BY FIELDNAME, LABEL_ID

 

==> Information at search record field level, including field labels.

 

SELECT INDEXID, INDEXTYPE, UNIQUEFLAG, CLUSTERFLAG, ACTIVEFLAG, CUSTKEYORDER, IDXCOMMENTS, PLATFORM_SBS, PLATFORM_DB2, PLATFORM_ORA, PLATFORM_INF, PLATFORM_DBX, PLATFORM_ALB, PLATFORM_SYB, PLATFORM_MSS, PLATFORM_DB4, RECNAME FROM PSINDEXDEFN WHERE RECNAME = :1 ORDER BY RECNAME, INDEXTYPE, INDEXID

 

==> Index details for the search record.

 

SELECT PNLFLDID, FIELDTYPE, EDITSIZE, FIELDLEFT, FIELDTOP, FIELDRIGHT, FIELDBOTTOM, EDITLBLLEFT, EDITLBLTOP, EDITLBLRIGHT, EDITLBLBOTTOM, DSPLFORMAT, DSPLFILL, LBLTYPE, LBLLOC, LBLPADSIZE, LABEL_ID, LBLTEXT, FIELDUSE, FIELDUSETMP, DEFERPROC, OCCURSLEVEL, OCCURSCOUNT1, OCCURSCOUNT2, OCCURSCOUNT3, OCCURSOFFSET1, OCCURSOFFSET2, OCCURSOFFSET3, PNLFIELDNAME, RECNAME, FIELDNAME, SUBPNLNAME, ONVALUE, OFFVALUE, ASSOCFIELDNUM, FIELDSTYLE, LABELSTYLE, FIELDSIZETYPE, LABELSIZETYPE, PRCSNAME, PRCSTYPE, FORMATFAMILY, DISPFMTNAME, PROMPTFIELD, POPUPMENU, TREECTRLID, TREECTRLTYPE, MULTIRECTREE, NODECOUNT, GRDCOLUMNCOUNT, GRDSHOWCOLHDG, GRDSHOWROWHDG, GRDODDROWSTYLE, GRDEVENROWSTYLE, GRDACTIVETABSTYLE, GRDINACTIVETABSTYL, GRDNAVBARSTYLE, GRDLABELSTYLE, GRDLBLMSGSET, GRDLBLMSGNUM, GRDLBLALIGN, GRDACTTYPE, TABENABLE, PBDISPLAYTYPE, OPENNEWWINDOW, URLDYNAMIC,  URL_ID, GOTOPORTALNAME, GOTONODENAME, GOTOMENUNAME, GOTOPNLGRPNAME,  GOTOMKTNAME, GOTOPNLNAME, GOTOPNLACTION, SRCHBYPNLDATA, SCROLLACTION, TOOLACTION, CONTNAME, CONTNAMEOVER, CONTNAMEDISABLE, PTLBLIMGCOLLAPSE, PTLBLIMGEXPAND, SELINDICATORTYPE, PTADJHIDDENFIELDS, PTCOLLAPSEDATAAREA, PTDFLTVIEWEXPANDED, PTHIDEFIELDS, SHOWCOLHIDEROWS, PTLEBEXPANDFIELD, SHOWTABCNTLBTN, SECUREINVISIBLE, ENABLEASANCHOR, URLENCODEDBYAPP, USEDEFAULTLABEL, GRDALLOWCOLSORT, PNLNAME, FIELDNUM FROM PSPNLFIELD WHERE PNLNAME = :1 ORDER BY FIELDNUM

 

==> Page and Page field position information is retrieved here. Search record is bypassed in our case.

 

SELECT VERSION, FIELDCOUNT, RECTYPE, RECUSE, OPTTRIGFLAG, AUDITRECNAME, SETCNTRLFLD, RELLANGRECNAME, OPTDELRECNAME, PARENTRECNAME, QRYSECRECNAME, SQLTABLENAME, BUILDSEQNO, OBJECTOWNERID, TO_CHAR(LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'), LASTUPDOPRID, SYSTEMIDFIELDNAME, TIMESTAMPFIELDNAME, RECDESCR, AUXFLAGMASK, DESCRLONG  FROM PSRECDEFN WHERE RECNAME = :1

 

==> Record field information used in the page are retrieved now.

 

More to come in the next post..