Monday, October 14, 2019

Understanding PeopleTools Object Version Numbers

I was recently asked a question about PeopleSoft version numbers, but before I address that directly, I think it would be useful to explain what is their general purpose.


The PeopleSoft data model and application are mostly stored in the database in PeopleTools tables.  These tables are queried as the application executes.  For example, when you open a component, the component and pages, including all the PeopleCode, the definition of any records used, and so on have to be loaded into the component buffer.  Ultimately this information comes from the PeopleTools tables.  To save the overhead of repeatedly querying these tables, PeopleSoft caches this data locally in physical files the application server and process scheduler domains.  The application servers also cache some of this information in memory to save visiting the local physical cache.  Application Designer also maintains a physical cache.
Over time, as the application executes, the cache files build up.  Occasionally, when it is necessary to delete the cache files and then it becomes clear just how significant is the overhead of the PeopleTools queries as a period of poor performance is seen as the application builds up fresh cache files.
Physical cache files are created in directories in the application server and process scheduler Tuxedo domains.  By default, each process maintains its own private cache.  Separate directories of cache files are created for each type of PeopleSoft server process in each domain.    Pairs of cache files are created in each directory for each object type as needed.  There is also a CACHE.LOK file in each directory that is used to ensure that only one process is accessing that cache directory concurrently.
It is possible to run with a shared physical cache, but then it is read-only and must be pre-generated.  It is very rare to see this implemented, because everyone expects to continuously deliver changes over time, and if you had a shared cache you would have to deliver an updated set of shared cache file to every domain every time you delivered a new PeopleTools object.
The cache files come in pairs.  The name of the cache files is the Object Type Name.  This corresponds to the OBJECTTYPENAME on the PSLOCK and PSVERSION tables.  The .DAT file contains the data to be cached.  The .KEY file is an index for the .DAT file, and it also holds the version number of the cached object.
-rw------- 1 psadm2 oracle  5228492 Jun 12 06:37 RDM.DAT
-rw------- 1 psadm2 oracle    69120 Jun 12 06:37 RDM.KEY
-rw------- 1 psadm2 oracle        0 Oct 26  2015 ROLM.DAT
-rw------- 1 psadm2 oracle    24192 Oct 26  2015 ROLM.KEY
-rw------- 1 psadm2 oracle        0 Oct 26  2015 RSM.DAT
-rw------- 1 psadm2 oracle    24192 Oct 26  2015 RSM.KEY

Version Numbers

Version numbers track when a cached PeopleTools object has been changed, either by Application Designer, or a change in configuration, or the application.  The version numbers are sequences generated from two PeopleTools tables PSLOCK and PSVERSION that hold the highest version number for each type of object.  These two tables have the same structure.
SQL> desc psversion
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECTTYPENAME                            NOT NULL VARCHAR2(8 CHAR)
 VERSION                                   NOT NULL NUMBER(38)
There are now over 100 different version numbers, each with a specific object type name that each track a specific PeopleTools object.  There is a global version number, with the object type name of SYS, that is incremented whenever any other version number is incremented.
I have no idea why two identical tables of version numbers were created.  I can see no reason for this, but it has been like this since the version numbers were changed (if I remember correctly) in PeopleTools 7.  In early versions of PeopleTools, not all version numbers were on both tables, but in at least PeopleTools 8.55 only one object type appears on PSVERSION and not PSLOCK.
When an object is changed, the object and global version numbers are incremented, and the incremented object version number is recorded on the object in the PeopleTools table.  The version number on the object is also stored in the physical cache files when the object is cached.  If the version on the database is higher than that in the cache file, then the PeopleSoft process knows it must query the latest version from the PeopleTools table and update the cache file.

How to Update Version Numbers

It is not generally recommended, nor strictly speaking, supported to update PeopleTools tables directly with SQL.  Apart from the risk of updating them incorrectly, or to invalid values, you also need to ensure that the changes are picked up by PeopleTools processes and that they do not simply continue to read the cached data.  However, occasionally, it is the pragmatic way to doing something.  
Here is an example from Chapter 5 of PeopleSoft for the Oracle DBA that shows how to maintain version numbers so the change is picked up by PeopleTools processes.  I want to mark alternate search key indexes as unique where there is a unique key on a record because they are unique because the unique key is a subset of their columns.  Then Application Designer will build the indexes as unique.  
UPDATE psversion SET version = version + 1
WHERE objecttypename IN('SYS','RDM');

UPDATE pslock SET version = version + 1
WHERE objecttypename IN('SYS','RDM');

UPDATE psrecdefn
SET version = (
SELECT version FROM psversion WHERE objecttypename = 'RDM')
WHERE recname = '';

UPDATE psindexdefn a
SET a.uniqueflag = 1
WHERE a.uniqueflag = 0
AND a.indextype = 3
  SELECT 'x'
  FROM psindexdefn k
  WHERE k.recname = a.recname
  AND k.indexid = '_'
  AND k.indextype = 1
  AND k.uniqueflag = 1)
AND a.recname = '';
I am updating a PeopleTools object (PSINDEXDEFN) that doesn't have a version number, but its parent is PSRECDEFN that does have a version number.  I happen to know that object type RDM (the Record Definition Manager) generates the version number for PSRECDEFN.  I found that out by tracing Application Designer while it saved a record change.  That is the only completely reliable method to determine which sequence is used for which record.  However, I will discuss another less onerous matching method in a subsequent blog post.
I must increment the RDM and SYS version numbers and write the new RDM version number to the updated rows on PSRECDEFN.  Next time a PeopleSoft process needs to read a record definition it will check the version numbers.  The increment of the SYS object tells PeopleSoft than an object number has changed, and then it will detect that the RDM version number has changed so it has to reload and cache objects with version numbers greater than the last cached version number for that object.


Bart22 said...

Hi David, you say: "It is possible to run with a shared physical cache, but then it is read-only and must be pre-generated."
We would like to use the "Load Application Server Cache" application engine, which creates the application server cache files on the process scheduler server, which then need to be copied to a SHARE directory on the application server. I've been testing and tracing its behaviour, and what I found is that it doesn't behave differently from 'classic' cache: if you update an object, initially the (new version) object will be loaded into memory cache - until a PSAPPSRV process is restarted, at which point the application server process will get the object from its application server cache. So it may be read-only initially, but a recycle of PSAPPSRV will update automatically - just like when using non-shared and non-preloaded cache.
Can you confirm this, or am I missing something here?

David Kurtz said...

You are absolutely right. It is perfectly normal that every time the PSAPPSRV process recycles, or crashes and is restarted, the memory cache is lost and has to be reloaded from the physical cache, and if the cache entry for that object is out of date (determined by the version numbers) the object has to be loaded from the PeopleTools tables.
The difference (as I understand it) is that in a non-shared cache, the physical cache is updated at this point (for that directory of cache files, used by that instance of PSAPPSRV) so the object does not need to be loaded again from the database. Whereas, the shared cache is not updated and the object will have to be reloaded from PeopleTools tables every the PSAPPSRV process is restarted and it is accessed for the first time by the new process.
The lower the recycle count on the PSAPPSRV the greater the reload overhead. The catch is that (at least historically) the higher the recycle count the higher the risk of the PSAPPSRV crashing and the user getting an error.

Bart22 said...

Thanks David, for your quick reply.I also read that SHARE cache is read-only, but I'll paste my (partial) trace result here - to me it looks like after a reboot the file cache is actually updated when using shared cache (unless I'm reading it wrong), the object is no longer read from memory:

make change to object (app designer) - don't reboot, access object (PIA):
PSAPPSRV.96138 (2) 1-4215 11.36.38 0.000022 Read Memory: PGM(UV_PBTEST/GBL/ENG)u0.p966012.v1

refresh page (no reboot), access object:
PSAPPSRV.96138 (2) 1-4215 11.36.38 0.000022 Read Memory: PGM(UV_PBTEST/GBL/ENG)u0.p966012.v1

reboot application server, access object:
PSAPPSRV.97543 (1) 1-860 11.40.57 0.000339 File-Cache Read: [PGM(UV_PBTEST/GBL/ENG)], duration: 0.000sec
PSAPPSRV.97543 (1) 1-861 11.40.57 0.000006 Get : File cache PGM(UV_PBTEST/GBL/ENG)u0.p966012.v1 in /u01/app/psoft/cfg/FSXXX/appserv/FSXXX/CACHE/SHARE/

The cache files in /u01/app/psoft/cfg/FSXXX/appserv/FSXXX/CACHE/SHARE/ have permissions 600, owner psoft - so on that level they are not read-only.

Our PeopleTools version is 8.56.20.


David Kurtz said...

I haven't investigated this area for a very long time. My understanding is that the shared physical is not updated by the application server processes. In your example, I can see a File-Cache Read operation and Get File Cache operation. I cannot see anything that suggests the physical cache is being written to.
i) Are the file times on the physical cache files are being updated as the application server runs?
ii) What happens if you change the permissions on the physical cache file to make them (and the directory) read-only? Does the application server raise an error?

Bart22 said...

The file times on the physical cache files are being updated when changing an object in app designer, and reloading the page (with the object) in PIA. Even when changing cache files permissions to read-only (400), the file times are updated - no error occurs. But when I change permissions on the directory (SHARE) to 400, application server uses the 'old' PSAPPSRV_1, PSAPPSRV_2 cache directories etc to get its object - no error, just different behaviour.
I didn't turn on all possible tracing, so an error message may have occurred somewhere in the process.

Bart22 said...

I asked Oracle this question:

Preloaded cache files do not behave differently from 'regular' cache files. Oracle says that preloaded cache files when using shared cache are read-only, but tracing shows no difference in behaviour between preloaded cache files and 'regular' cache: in both cases objects with new versions are being loaded from the database into memory cache, until the moment that an APPSRV process recycles. Then the cache is updated, and next time an object is accessed, it will be from the cache file (not database). This principle is valid for both preloaded and non-preloaded cache. This would also mean that there is no need to recreate the preloaded cache files (by rerunning the preload AE process).
Is this correct?

Oracle's answer:
I have discussed your latest statement internally and it is a correct statement.

Appsian said...

Thank you for sharing your blog, seems to be useful information can’t wait to dig deep!

Bart22 said...

Hi again - update: we were experiencing performance issues with Pivot grids based on PeopleSoft queries after implementing shared cache. It turns out that the cache files for queries are indeed read-only (other cache files are not). We are now still preloading cache, but not using shared cache: we preload the cache files using the PLCACHE job and then copy the files to the individual PSAPPSRV_# cache folders in the application server, using a script. The shared cache option in psappsrv.cfg is switched off. Now we have no performance issues after maintenance, and no issues with read-only cache files.

truffing said...

I'm having a problem right now with an Oracle database. A delivered process is locking pslock and psversion as it updates the cache numbers. When it does this, it locks users from accessing the application. I have tried to increase inittrans, it worked for a very short period of time, then stopped working after so many minutes. How do you get an Oracle database to allow you to read pslock and psversion while the table is being updated by another process?

David Kurtz said...

Locks are used to deliberately serialise processing to prevent things from happening concurrently. In an Oracle database, a lock on a table never blocks a simple query. However, a SELECT ... FOR UPDATE will wait to acquire the lock before it returns a row. The database will report wait on enq: TX - row lock contention.
Increasing INITRANS reserves more space in the data block, to register interested transactions. It can grow to this on-demand if space is available, ie. if the data block is not already full. This can allow more concurrent transactions in a data block. If you run out of space in the interested transaction list, you can end up waiting on enq: TX - allocate ITL entry.
PSLOCK and PSVERSION are small tables and typically fit in a single block. Locks are used to deliberately serialise processing, usually to prevent one process from loading and caching a PeopleTools object while another process is updating it, which might involve updates to several PeopleTools tables. In your case, it sounds like increasing INITRANS simply allowed more transactions to be created until you ran out. The question is what is holding the locks and why.