Deferred Segment Creation in PeopleSoft

This note has been in my to do folder for a while since I found these two excellent blog entries about Deferred Segment Creation by Chistian Antognini.

• http://antognini.ch/2009/09/deferred-segment-creation/
• http://antognini.ch/2010/10/deferred-segment-creation-as-of-11-2-0-2/

They made me think about the use of this feature in PeopleSoft. In most PeopleSoft systems there are lots of empty tables, sometimes because not all the modules delivered in the database are in use, but also because not all the temporary table instances have ever been used. This blog entry from Tom Kyte feels very close to home

• http://tkyte.blogspot.com/2011/02/deferred-segment-creation.html

Deferred segment creation is available from Oracle 11gR1. From Oracle 11.2.0.2 this becomes the default, so there is no need to reconfigure anything in PeopleSoft in order use this Oracle feature. You may want to drop empty segments created under previous versions of the database.

More Process Priority Levels for the Process Scheduler

This note started out as an idea for how to add more priority levels to the PeopleSoft Process Scheduler to improve control over prioritisation of processes in a complex batch. While testing I found some interesting behaviour that I had not expected. I will also explain how I did some of my tests, because they are easily reproducible.

More Priority Levels

By default, PeopleSoft process can be given priority 1 (low), 5 (medium), and 9 (high). If the Process Scheduler is too busy to start all the processes scheduled to be started, it starts the higher priority ones in preference to the lower priority.

That’s fine, but sometimes in a very complex batch environment 3 process levels are not enough, and it would be useful to have more priority levels so that it is possible to define the hierarchy of processing to a finer level.

There does not appear to be anything special about the priorities delivered by PeopleSoft. They do not appear to be hard-coded anywhere within the SQL submitted Process Scheduler. As you might expect, some of the queries are sorted in descending order of priority.

The priority of a process in the Process Scheduler is defined by the single character field PRCSPRIORITY on the record PRCSDEFN. All you have to do is define addition translate values

The new priorities can be assigned via the delivered components.

I started with the intention of applying the new priority level to processes through the delivered component. However, I encountered some quirky behaviour from the Process Scheduler and Master Process Scheduler. There is something happening inside the programs rather than the SQL where they do not always take the process with the highest priority if that priority is not one of the delivered values. Therefore, I do not recommend using the new priorities on process definitions.

However, Server Categories also have priorities. Testing has shown that the priority of the Process Category take precedence over the priority on the Process Definition.

The new server categories should be defined in the Process Category Administration page of the Process Scheduler System Settings component

Then priorities and maximum concurrencies can be assigned to the categories in each Process Scheduler Server definition.

Testing the Additional Priorities
I have (with some assistance) constructed a simple test harness. I have an Application Engine program, AE_SLEEP, that does nothing, but sleeps for a period of time by calling the Java Sleep() method in a PeopleCode step.

Local JavaObject &Obj;Local ProcessRequest &RQST;
MessageBox(0, "", 0, 0, "Sleep Begin for " | GFC_SLEEP_AET.DURATION.Value | " seconds ");

&Obj = CreateJavaObject("java.lang.Thread");&Obj.start();
&Obj.sleep(GFC_SLEEP_AET.DURATION.Value * 1000);

MessageBox(0, "", 0, 0, "Sleep END");

I have a number of other Application engine programs that have different priorities, or that are in categories with different priorities, that call AE_SLEEP.

Finally, there is a process AE_SLEEP_RUN which submits requests for the other processes based on a control table. In this test I will be setting up some test data to demonstrate how the Process Scheduler behaves, in the next article, I will be modeling the behaviour of a real batch load from a real system

TRUNCATE TABLE ps_gfc_sleep_rc
/
INSERT INTO ps_gfc_sleep_rc
(run_cntl_id, prcstype, prcsname, offset_amount, duration, rundttm, last_run_cntl_id, next_run_cntl_id)
SELECT level, 'Application Engine', 'AE_SLEEP'||level, 5*level, 175, NULL, ' ', ' '
FROM dual CONNECT BY level <= 9
/
COMMIT
/ 
ALTER SESSION SET NLS_DATE_FORMAT='hh24:mi:ss';
set lines 120
COLUMN run_cntl_id FORMAT a8 heading 'Run|Control'
COLUMN last_run_cntl_id FORMAT a8 heading 'Last|Run|Control'
COLUMN next_run_cntl_id FORMAT a8 heading 'Next|Run|Control'
COLUMN prcsinstance FORMAT 99999 HEADING 'PRCS|INSTANCE'
COLUMN offset_amount FORMAT 9999 HEADING 'Offset|Amount'
COLUMN rownum FORMAT 99 HEADING 'Row|#'
COLUMN prcstype FORMAT a18
COLUMN prcsname FORMAT a12
COLUMN prcsprty FORMAT 9 HEADING 'PRCS|PRTY'
COLUMN prcscategory FORMAT a11 HEADING 'PRCS|CATEGORY'
COLUMN serverassign FORMAT a6 HEADING 'SERVER|ASSIGN'
COLUMN runstatus FORMAT a6 HEADING 'RUN|STATUS' 
 
SELECT run_cntl_id, prcsname, offset_amount, duration, rundttm 
FROM ps_gfc_sleep_rc
/

So the AE_SLEEP_RUN will schedule one process every 5 seconds. The lowest priority processes are scheduled first.

Run                            Offset
Control  PRCSNAME     RUNDTTM  Amount   DURATION
-------- ------------ -------- ------ ----------
1        AE_SLEEP1                  5        175
2        AE_SLEEP2                 10        175
3        AE_SLEEP3                 15        175
4        AE_SLEEP4                 20        175
5        AE_SLEEP5                 25        175
6        AE_SLEEP6                 30        175
7        AE_SLEEP7                 35        175
8        AE_SLEEP8                 40        175
9        AE_SLEEP9                 45        175

I can query what actually happened with the following SQL.

SELECT rownum, a.* 
FROM (
   SELECT SYSDATE, q.prcsinstance, q.prcsname, q.prcsprty, q.rundttm
   , r.begindttm, r.enddttm, q.serverassign, q.runstatus
   --, r.prcscategory
   FROM psprcsrqst r, psprcsque q
   WHERE r.prcsinstance = q.prcsinstanceAND r.prcsname like 'AE_SLEEP_'
   AND r.prcsinstance > (
      SELECT MAX(prcsinstance)
      FROM psprcsrqst
      WHERE prcsname = 'AE_SLEEP_RUN' AND runstatus = '9')
ORDER BY r.begindttm, q.prcsprty DESC, q.rundttm, r.prcsinstance) a
/

In all of the examples below, the first three low-priority processes run first because they are scheduled to run and therefore start before the higher priority processes are scheduled. The Process Scheduler is then blocked until all the other processes are due to have run, and so priority determines while processes the scheduler starts next.

However, due to a quirk in Process Scheduler, the processes are not assigned to the Process Scheduler in time order. In the following example, AE_SLEEP5 was executed before AE_SLEEP6.

Row           PRCS
#   SYSDATE   INSTANCE PRCSNAME     RUNDTTM  BEGINDTTM ENDDTTM
--- --------- -------- ------------ -------- --------- --------
  1 13:14:17      4583 AE_SLEEP1    13:03:27 13:03:55  13:07:08
  2 13:14:17      4584 AE_SLEEP2    13:03:32 13:03:55  13:07:08
  3 13:14:17      4585 AE_SLEEP3    13:03:37 13:03:55  13:07:08
  4 13:14:17      4591 AE_SLEEP9    13:04:07 13:07:12  13:10:27
  5 13:14:17      4587 AE_SLEEP5    13:03:47 13:07:13  13:10:27
  6 13:14:17      4588 AE_SLEEP6    13:03:52 13:07:13  13:10:27
  7 13:14:17      4586 AE_SLEEP4    13:03:42 13:10:31  13:13:46
  8 13:14:17      4589 AE_SLEEP7    13:03:57 13:10:47  13:14:01
  9 13:14:17      4590 AE_SLEEP8    13:04:02 13:10:47  13:14:01

But if the non-standard priority jobs are put in a different category, they are executed in the right order. It would appear that there is some logic in the Master Process Scheduler (and not in the SQL queries it submits) that works category by category.

Row              PRCS              PRCS                            SERVER RU
  # SYSDATE  INSTANCE PRCSNAME     PRTY RUNDTTM  BEGINDTT ENDDTTM  ASSIGN ST PRCSCATEGORY
--- -------- -------- ------------ ---- -------- -------- -------- ------ -- ------------
  1 17:44:21     4681 AE_SLEEP5       5 17:31:10 17:31:24 17:34:39 PSNT   9  Default
  2 17:44:21     4682 AE_SLEEP5       5 17:31:13 17:31:24 17:34:39 PSNT   9  Default
  3 17:44:21     4683 AE_SLEEP5       5 17:31:16 17:31:40 17:34:56 PSNT   9  Default
  4 17:44:21     4690 AE_SLEEP7       7 17:32:43 17:34:43 17:37:57 PSNT   9  Priority 9
  5 17:44:21     4689 AE_SLEEP7       7 17:32:44 17:34:43 17:37:57 PSNT   9  Priority 9
  6 17:44:21     4688 AE_SLEEP7       7 17:32:45 17:34:59 17:38:14 PSNT   9  Priority 9
  7 17:44:21     4687 AE_SLEEP7       7 17:32:46 17:38:01 17:41:16 PSNT   9  Priority 9
  8 17:44:21     4684 AE_SLEEP5       5 17:31:19 17:38:01 17:41:16 PSNT   9  Default
  9 17:44:21     4685 AE_SLEEP5       5 17:31:22 17:38:17 17:41:33 PSNT   9  Default 
 10 17:44:21     4686 AE_SLEEP5       5 17:31:25 17:41:20 17:44:20 PSNT   9  Default

If there are two new priorities in the new category, then they do not run in the right order.

Row              PRCS              PRCS                            SERVER RUN
  # SYSDATE  INSTANCE PRCSNAME     PRTY RUNDTTM  BEGINDTT ENDDTTM  ASSIGN STATUS PRCSCATEGORY
--- -------- -------- ------------ ---- -------- -------- -------- ------ ------ ------------
  1 19:01:58     4716 AE_SLEEP5       5 18:06:58 18:07:26 18:10:40 PSNT   9      Default
  2 19:01:58     4718 AE_SLEEP5       5 18:07:04 18:07:26 18:10:40 PSNT   9      Default
  3 19:01:58     4719 AE_SLEEP5       5 18:07:07 18:07:26 18:10:40 PSNT   9      Default
  4 19:01:58     4723 AE_SLEEP6       6 18:08:25 18:10:44 18:13:43 PSNT   9      Priority 9
  5 19:01:58     4722 AE_SLEEP6       6 18:08:30 18:10:44 18:13:43 PSNT   9      Priority 9
  6 19:01:58     4717 AE_SLEEP5       5 18:07:01 18:10:44 18:13:43 PSNT   9      Default
  7 19:01:58     4724 AE_SLEEP7       7 18:08:40 18:14:02 18:17:00 PSNT   9      Priority 9
  8 19:01:58     4725 AE_SLEEP7       7 18:08:45 18:14:02 18:17:00 PSNT   9      Priority 9
  9 19:01:58     4720 AE_SLEEP5       5 18:07:10 18:14:02 18:17:00 PSNT   9      Default
 10 19:01:58     4721 AE_SLEEP5       5 18:07:13 18:17:18 18:20:17 PSNT   9      Default

However, if I create a separate category for each priority, and assign the priority to that category in the server definition, then the processes run in the desired order.

Row              PRCS              PRCS                            SERVER RUN
  # SYSDATE  INSTANCE PRCSNAME     PRTY RUNDTTM  BEGINDTT ENDDTTM  ASSIGN STATUS PRCSCATEGORY
--- -------- -------- ------------ ---- -------- -------- -------- ------ ------ ------------
  1 19:38:00     4740 AE_SLEEP5       5 19:25:10 19:25:23 19:28:23 PSNT   9      Default
  2 19:38:00     4741 AE_SLEEP5       5 19:25:13 19:25:39 19:28:38 PSNT   9      Default
  3 19:38:00     4742 AE_SLEEP5       5 19:25:16 19:25:39 19:28:38 PSNT   9      Default
  4 19:38:00     4748 AE_SLEEP7       7 19:26:52 19:28:41 19:31:41 PSNT   9      Priority 7
  5 19:38:00     4749 AE_SLEEP7       7 19:26:57 19:28:58 19:31:56 PSNT   9      Priority 7
  6 19:38:00     4747 AE_SLEEP6       6 19:26:37 19:28:58 19:31:56 PSNT   9      Priority 6
  7 19:38:00     4746 AE_SLEEP6       6 19:26:42 19:32:00 19:34:59 PSNT   9      Priority 6
  8 19:38:00     4743 AE_SLEEP5       5 19:25:19 19:32:16 19:35:15 PSNT   9      Default
  9 19:38:00     4744 AE_SLEEP5       5 19:25:22 19:32:16 19:35:16 PSNT   9      Default
 10 19:38:00     4745 AE_SLEEP5       5 19:25:25 19:35:02 19:37:59 PSNT   9      Default

Conclusion
The Process Scheduler does not reliably take process priorities into account when scheduling processes,  so I do not recommend using the additional priorities on processes, just the three delivered priorities (1, 5, and 9).

However, all the priorities do work properly on process categories. The priority of the server class does not appear to have any effect (I have not produced results for those tests here).

The priority of the category to which a process belongs takes precedence over the priority of the process. Thus a low priority process in a high priority category will be executed by the Process Scheduler in preference to a high priority process in a low priority category. Naturally if two processes are in the same category, or categories of equal priority, the higher priority process will be scheduled first.

Acknowledgements
My thanks to Andy Mason of Business Integrations for contributing to the initial concept and the Java code to make Application Engine sleep and assistance with the test harness.

Further Reading
I have published a paper on my website that describes how to extend this technique to model a real production batch, and predict the impact of Process Scheduler configuration changes.

Unlocking Temporary Table Instances from Deleted Process Requests

In a previous blog entry, I discussed how to detect whether an Application Engine process had run out of non-shared Temporary Table instances by querying the message log table for the entries written when this happens.

This can happen because processes that have locked all available non-shared table instances are currently processing. However, if a restartable Application Engine program terminates with an error, the tables remain locked to that process instance so that it can be restarted. If that process instance is neither cancelled nor restarted so that it subsequently runs to success then the tables will remain locked, and in time the Process Scheduler archive process may purge the process request record.

However, that archive process does not unlock the temporary table instances (by deleting the registration of the table with the instance from the record AETEMPTBLMGR), and once the process request has been deleted it cannot be cancelled in the Process Monitor. Therefore, there is no way for an operator to unlock the tables via the web interface.  Over time this can build up and new processes are forced to use the shared instance.  In my experience this is more common on development and test environments than production.

The problem is that is a silent and insidious problem that can gradually degrade batch performance.  Forcing processes onto the shared instance forces them to delete rather than truncate working storage tables.  So there is additional redo, and high water marks are not reset.  Concurrent processes can contend as they share the same physical table.  There may be more work to preserve read consistency, and more activity on the undo segment.

The way to prevent this problem is, of course, to make sure you have sufficient instance of temporary table, but before you create new instances check that there an no existing instances that should not still be locked. 

Update 7.3.2011: Phil's comment below correctly reminds me that PeopleSoft provided a way to remove locks without issuing SQL directly at PeopleTools -> Application Engine -> Review Temp Table Usage.  This component clears records from PS_AETEMPTBLMGR, PS_AERUNCONTROL and PS_AERUNCONTROLPC.  This feature is also described at PeopleSoft Tips & Notes: PS_AERUNCONTROL.

However, you can only remove locks for one process instance at a time.  If the problem has got out of hand, you might still chose to delete the rows from these three table where there is no longer a process request record. 

DELETE FROM ps_aetemptblmgr t
WHERE NOT EXISTS(
 SELECT 'x'
 FROM psprcsrqst r
 WHERE r.prcsinstance = t.process_instance)
/

DELETE FROM ps_aeruncontrol t
WHERE NOT EXISTS(
 SELECT 'x'
 FROM psprcsrqst r
 WHERE r.prcsinstance = t.process_instance)
/

DELETE FROM ps_aeruncontrolpc t
WHERE NOT EXISTS(
 SELECT 'x'
 FROM psprcsrqst r
 WHERE r.prcsinstance = t.process_instance)
/ 

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.