Finding Unnecessary Effective Date Processing in PS/Query

In PeopleSoft for the Oracle DBA (Ch11, p309) I commented on how PS/Query will automatically add effective date criteria to and EFFDT column, even if it is not a key column. 

PS/Query does warn you that it has added the criteria, but it won't warn that the column is not a key column

If EFFDT is not part of the key, then it is unlikely that you will need this processing.  It is unlikely to change the result of the query, and it incurs additional work. 

Below is an example of the code added to a query by this one criteria. The effective date sub-query will be correlated by the key columns prior to the EFFDT column. If EFFDT is not part of the key, then all the key columns will be used for the correlation conditions. There is only one row for each EMPLID and EMPL_RCD, and the effective date sub-queries will find and return that row, and the result set will remain the same.

...
AND ( a.effdt = 
        (SELECT MAX(a_ed.EFFDT) FROM ps_employees a_ed
        WHERE a.emplid = a_ed.emplid 
          AND a.empl_rcd = a_ed.empl_rcd 
          AND a_ed.EFFDT <= SYSDATE) 
AND a.effseq = 
        (SELECT MAX(a_es.EFFSEQ) FROM ps_employees a_es
        WHERE a.emplid = a_es.emplid 
          AND a.empl_rcd = a_es.empl_rcd 
          AND a.effdt = a_es.EFFDT) )
...

But how prevalent is this problem?  Like most things in PeopleSoft, Queries are stored in the PeopleTools tables.  And it is possible to construct a query to identify queries with effective date criteria on EFFDT columns that are not part of the key on their record.

SELECT  c.oprid, c.qryname, r.recname, r.corrname, f.fieldname 
--,     useedit, bitand(d.useedit,1) 
FROM    psqrycriteria c /*query crieria*/
,       psqryrecord r   /*records in queries*/
,       psqryfield f    /*fields in a queries*/
,       psrecfielddb d  /*fields on records, with sub-records fully expanded*/
WHERE   c.condtype BETWEEN 20 AND 25 /*effdt criteria, so no need to specify column name*/
AND     c.lcrtfldnum = f.fldnum 
-- 
AND     r.oprid = c.oprid 
AND     r.qryname = c.qryname 
AND     r.selnum = c.selnum 
-- 
AND     f.oprid = c.oprid 
AND     f.qryname = c.qryname 
AND     f.selnum = c.selnum 
-- 
AND     f.oprid = r.oprid 
AND     f.qryname = r.qryname 
AND     f.selnum = r.selnum 
AND     f.recname = r.recname 
-- 
AND     d.recname = f.recname 
AND     d.fieldname = f.fieldname 
AND     BITAND(d.useedit,1) = 0 /*a non-key field*/ 

ORDER BY 1,2 
/

On my HR demo database the following delivered queries have effective date criteria on EFFDT columns that are no key columns.

OPRID    QRYNAME                        RECNAME         CORRN FIELDNAME
-------- ------------------------------ --------------- ----- ---------
         JPM_INACTIVE_CONTENT_ITEMS     JPM_JP_CRITM_VW A     EFFDT
         JPM_PERS_PROFILE               JPM_JP_CRITM_VW F     EFFDT
         JPM_PROFILE                    JPM_JP_CRITM_VW F     EFFDT
         NOT001                         FPAEEFLAT_TBL   A     EFFDT

Now, it is possible to review these queries and manually remove the effective date processing if that is appropriate.

Row Level Locking in the PeopleTools Component Processor

My apologies in advance, but this entry is a bit of a rant.  It is one of those things in PeopleSoft about which you do nothing, but it still is interesting to know because it reveals something of how it works under the covers.

In the PeopleSoft for the DBA, I commented on how the columns updated in the UPDATE statements issued by the Component Processor at save time contain only the fields that have been updated in the component.  You get different update statements depending upon what you update in the component, but the alternative is to update all the columns, possibly to the same value, and that would generate additional unnecessary redo.

PeopleSoft uses 'optimistic locking'.  That is to say that it hopes that the data in the database underlying the component hasn't changed between the operator querying data into the component and saving any changes.  At save time, the data is requeried so it can be compared with the results returned by the first query, but this time the row of data is locked by adding the FOR UPDATE clause.  The lock is released by the commit at the end of the save time processing.  You have the overhead of requerying the data, but it is likely to be in the buffer cache after the first query.  However, this avoids the risks of holding a database lock while the user is in the component. 

This is a common approach in many applications.  It is not just reasonable, but essential for scalability.  The Component Processor never holds a lock while waiting for the user to respond.  This is also essential for the Tuxedo application server.  Each server process is stateless and each service request is atomic.  Each server process maintains a persistent database connection.  A database transaction is never left uncommitted at the end of a service request.  There is no guarantee that the next request from the same user will be handled by the same application server process, and it might have to handle requests from other users in the intervening period.

However, PeopleSoft also dynamically generates the column list in the FOR UPDATE clause.  Here are two examples from the timesheet component in Time & Labor.  I have removed most of the select clause for readability.

SQL_ID: 3vdfam8g3f7ca
SELECT EMPLID, EMPL_RCD, ...
FROM PS_TL_RPTD_TIME
WHERE EMPLID=:1 AND EMPL_RCD=:2 AND DUR=TO_DATE(:3,'YYYY-MM-DD') AND SEQ_NBR=:4
FOR UPDATE OF OPRID

SQL ID: d8b5sy4bcgyuh 
SELECT EMPLID, EMPL_RCD, ...
FROM PS_TL_RPTD_TIME
WHERE EMPLID=:1 AND EMPL_RCD=:2 AND DUR=TO_DATE(:3,'YYYY-MM-DD') AND SEQ_NBR=:4
FOR UPDATE OF PUNCH_END, OPRID, RT_SOURCE, OPRID_LAST_UPDT, DTTM_MODIFIED, TL_QUANTITY

This is a totally unnecessary complexity.  Oracle (and other databases) employ row level locking.  They do not lock individual pieces of data. The row would be just as locked with one column in the FOR UPDATE clause as with many or any other.  PeopleSoft could simply have put the first column from the select clause into the FOR UPDATE clause.  Instead, we have different SQL statements with different SQL_IDs, and everything that goes with that.

However, there is nothing you can do about this, just know about it. It shows the dynamic nature of the SQL generated by the Component Processor, and reveals how it must be tracking which fields have been updated by the user and the PeopleCode in a component.