Continuing our cache analysis on PeopleSoft, we are looking into the SQLs executed by PeopleSoft when the page is cached on the application server and when the page is not.
If you are directly landing on this page from the internet you may wish to check part 1 and part 2 of the posts before moving forward. Let us continue our analysis on the SQLs executed, when the objects are not cached.
SELECT COUNT(*) FROM PSRECDEFNLANG WHERE RECNAME = :1
In this step, the related language record definition existence, for the page fields is obtained by querying PSRECDEFNLANG table. This is done for all the page fields by PeopleSoft, but in our case we have only one test record on the page, and hence the query gets executed for only one record.
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
Record, field and edit table information are grabbed in this step.
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
Label details of all the page fields are obtained in this step.
SELECT OBJECTID1,OBJECTVALUE1, OBJECTID2,OBJECTVALUE2, OBJECTID3,OBJECTVALUE3, OBJECTID4,OBJECTVALUE4, OBJECTID5,OBJECTVALUE5, OBJECTID6,OBJECTVALUE6, OBJECTID7,OBJECTVALUE7 FROM PSPCMPROG WHERE OBJECTID1 = :1 AND OBJECTVALUE1 = :2 ORDER BY OBJECTID1,OBJECTVALUE1, OBJECTID2,OBJECTVALUE2, OBJECTID3,OBJECTVALUE3, OBJECTID4,OBJECTVALUE4, OBJECTID5,OBJECTVALUE5, OBJECTID6,OBJECTVALUE6, OBJECTID7,OBJECTVALUE7
All PeopleCode events are grabbed in this step. The OBJECTVALUE1 is the record name that is passed, and all PeopleCode events are grabbed in this query.
SELECT VERSION, PROGRUNLOC, NAMECOUNT, PROGLEN, PROGTXT, LICENSE_CODE, TO_CHAR(LASTUPDDTTM,'YYYY-MM-DD-HH24.MI.SS."000000"'), LASTUPDOPRID, PROGFLAGS, PROGSEQ FROM PSPCMPROG WHERE OBJECTID1 = :1 AND OBJECTVALUE1 = :2 AND OBJECTID2 = :3 AND OBJECTVALUE2 = :4 AND OBJECTID3 = :5 AND OBJECTVALUE3 = :6 AND OBJECTID4 = :7 AND OBJECTVALUE4 = :8 AND OBJECTID5 = :9 AND OBJECTVALUE5 = :10 AND OBJECTID6 = :11 AND OBJECTVALUE6 = :12 AND OBJECTID7 = :13 AND OBJECTVALUE7 = :14 ORDER BY PROGSEQ
SELECT RECNAME, REFNAME, NAMENUM FROM PSPCMNAME WHERE OBJECTID1 = :1 AND OBJECTVALUE1 = :2 AND OBJECTID2 = :3 AND OBJECTVALUE2 = :4 AND OBJECTID3 = :5 AND OBJECTVALUE3 = :6 AND OBJECTID4 = :7 AND OBJECTVALUE4 = :8 AND OBJECTID5 = :9 AND OBJECTVALUE5 = :10 AND OBJECTID6 = :11 AND OBJECTVALUE6 = :12 AND OBJECTID7 = :13 AND OBJECTVALUE7 = :14 ORDER BY NAMENUM
The exact PeopleCode associated with the event is obtained in this step. The field PROGTXT stores the PeopleCode in LONG RAW format.
SELECT OBJECTID1,OBJECTVALUE1, OBJECTID2,OBJECTVALUE2, OBJECTID3,OBJECTVALUE3, OBJECTID4,OBJECTVALUE4, OBJECTID5,OBJECTVALUE5, OBJECTID6,OBJECTVALUE6, OBJECTID7,OBJECTVALUE7 FROM PSPCMPROG WHERE OBJECTID1 = :1 AND OBJECTVALUE1 = :2 ORDER BY OBJECTID1,OBJECTVALUE1, OBJECTID2,OBJECTVALUE2, OBJECTID3,OBJECTVALUE3, OBJECTID4,OBJECTVALUE4, OBJECTID5,OBJECTVALUE5, OBJECTID6,OBJECTVALUE6, OBJECTID7,OBJECTVALUE7
Almost similar to the previous query, however, instead of passing the record definitions that are used in the page, PeopleSoft passes the page name to get any associated events. (Page Activate)
SELECT OBJNAME, FLAG, PTCUSTOMFORMAT FROM PSUSEROBJTYPE WHERE MENUNAME = :1 AND PNLGRPNAME = :2 AND PNLNAME = :3 AND OPRID = :4 AND FIELDTYPE = :5
Interesting, in this step, PeopleSoft gets the user level customizations that is done for the definition that is being loaded.( for component and page)
SELECT VERSION, DESCR FROM PSMSGSETDEFN WHERE MESSAGE_SET_NBR = :1
SELECT MESSAGE_NBR, MESSAGE_TEXT, MSG_SEVERITY, DESCRLONG, TO_CHAR(LAST_UPDATE_DTTM,'YYYY-MM-DD-HH24.MI.SS."000000"') FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = :1 ORDER BY MESSAGE_NBR
Interesting again, we have an Error statement placed in the field change code of our test page. Here, PeopleSoft loads the standard messages that it needs to display at runtime from the message set "Panel Runtime Msgs and Strings" [ Message set : 126 ]
Also, items like Next in List, Previous in List etc are grabbed from this message set. [ Find, View ALL ]. After this PeopleSoft loads the items pertaining to message set 15. [Application Processor]. This message set contains entries specific to the component processor and includes very frequently displayed messages like
1) Invalid value -- press the prompt button or hyperlink for a list of valid values
2) No Set Control Information was found for the Set ID of the tree.
3) Think-time PeopleCode event (%1), but a SQL update has occurred in the commit interval.
4) DSTSTART and DSTEND are required for time zones that observe DST
5) A Related Edit field should be from the same table as the prompt table for the control field.
6) An Edit Control field must have a prompt table.
and so on..The next message set that gets loaded is 40. [PSTOOLS Messages]. This contains messages that are specific PeopleTools and has the following
1) The system does not understand the date you entered.
2) Invalid Run Control ID.
3) You are not authorized to access this component.
4) Unable to run this request with defaults - select from Process Request dialog.
and so on..
After this, all style sheets gets loaded and PeopleSoft executes a number of queries to get the style sheet definitions into its cache. I'm not going to produce all of them, but it would be sufficient to say, that the list is huge and depends on the style sheet that is currently in use.
That completes that without cache loading part. From here, we are ready to move on to the section that discusses WITH CACHE results.
No comments:
Post a Comment