The PeopleSoft DBA Blog

Deferred Segment Creation in PeopleSoft

noreply@blogger.com (David Kurtz) — Sun, 26 Jun 2011 19:46:00 +0000

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.

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

noreply@blogger.com (David Kurtz) — Thu, 03 Mar 2011 23:30:00 +0000

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 modelling 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 120COLUMN 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 a18COLUMN prcsname FORMAT a12COLUMN 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                            OffsetControl  PRCSNAME     RUNDTTM  Amount   DURATION-------- ------------ -------- ------ ----------1        AE_SLEEP1                  5        1752        AE_SLEEP2                 10        1753        AE_SLEEP3                 15        1754        AE_SLEEP4                 20        1755        AE_SLEEP5                 25        1756        AE_SLEEP6                 30        1757        AE_SLEEP7                 35        1758        AE_SLEEP8                 40        1759        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.prcscategoryFROM psprcsrqst r, psprcsque qWHERE 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 process 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 and 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

noreply@blogger.com (David Kurtz) — Thu, 17 Feb 2011 19:26:00 +0000

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

noreply@blogger.com (David Kurtz) — Wed, 19 Jan 2011 12:13:00 +0000

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

noreply@blogger.com (David Kurtz) — Tue, 18 Jan 2011 08:02:00 +0000

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.

PeopleTools 8.50 uses DBMS_APPLICATION_INFO to Identify Database Sessions

noreply@blogger.com (David Kurtz) — Thu, 04 Nov 2010 20:56:00 +0000

I recently worked on a PeopleTools 8.50 system in production for the first time and was able to make use of the new Oracle specific instrumentation in PeopleTools.

PeopleTools now uses the DBMS_APPLICATION_INFO package to set module and action session attributes. This data is then copied into the Active Session History (ASH).

Within the PIA, the application server sets module and action to the name of the current component and page within the current components
For Integration Broker messages they are set to service and queue name.
For Application Engine processes the module is set to PSAE and the action is set to the program name.

The first two of these three changes are very welcome, but I think the instrumentation of Application Engine is inadequate. I have a better suggestion which can be implemented with a database trigger.

Enterprise Manager
In Oracle Enterprise Manager, I can see the top SQL statements and group them by Module and Action, so I now can see which components are consuming the most time. I can also produce an AWR for a specific component or page within a component.

OEM Screenshot of a PeopleTools 8.50 system.

I can query the ASH data to profile which pages consume the most time on the database.

MODULE                 ACTION                   ASH_SECS 
---------------------- ---------------------- ---------- 
RECV_PO                PO_PICK_ORDERS                240 
XXX_REQ_INQUIRY        xyzzy                         170 
XXX_REQ_WRKLST         XXX_REQ_WORKLIST              170 
VCHR_EXPRESS           VCHR_LINE_RECV_WRK            170 
XXX_FIN_WORKLIST       XXX_FIN_WORKLIST              160 
VCHR_EXPRESS           VCHR_EXPRESS1                 160 
PURCHASE_ORDER_EXP     PO_EXPRESS                    140 
XXX_HOME_PAGE          XXX_HOME_PAGE                 140 
RECV_PO                RECV_WPO                      130 
VCHR_EXPRESS           xyzzy                         120 
XXX_PUR_WORKLIST       XXX_PUR_WRKLST                120 
CDM_RPT                CDM_RPT_INDEX                 100 
…
----------
sum                                                  2820

(Updated 19.11.2010) For some components the action is set to ‘xyzzy’. This seems to be a default value set when the component is opened, but before any of the pages are processed. Therefore, it refers to activity in the search dialogue, including processing of :

look ups to obtain values for search criteria
SQL issued during SearchSave PeopleCode to validate the search criteria.
the query on the Component Search record

Batch Processes
Now, I want to look at what happens in batch processes. In previous versions of PeopleTools, the module was set to the same value as the program name, and action was left blank. This is not particularly helpful. In 8.50 module is set to PSAE for Application Engine processes, and action is set to the name of the program.

MODULE                       ACTION                      ASH_SECS
---------------------------- ------------------------- ----------
PSNVS.EXE                                                   10220
PSAE                         AP_PSTPYMNT                     3100
PSAE                         AP_MATCH                        2690
sqrw.exe                                                     1770
PSAE                         PO_RECVACCR                     1390
PSQRYSRV.exe                                                  880
PSAE                         FS_STREAMLN                      870
PSPUBDSP.exe                                                  850
PSBRKDSP.exe                                                  740
PSPRCSRV.exe                                                  690
PSSUBDSP.exe                                                  620

This is certainly better than in previous versions. However, its weakness is that if I have multiple concurrent instances of the same process, although I could tell that the ASH data had come from different sessions, I would not be determine which came from which session. This situation could occur, for example, in Global Payroll if ‘streaming’ was configured, where the payroll calculation can be broken into many processes that run concurrently.

In another blog posting, Using Oracle Enterprise Manager (Grid Control) with PeopleSoft, I proposed a trigger that writes name of the PeopleSoft process name to module, and the Process Instance number into action. Furthermore, this trigger works for all processes scheduled by the Process Scheduler. I think that that trigger is still useful in PeopleTools 8.50.

Performance Overhead of Multiple SQL calls in SQR

noreply@blogger.com (David Kurtz) — Wed, 15 Sep 2010 07:13:00 +0000

I was asked to look at a fairly simple SQR program that reported on payroll data. It pivoted data for each employee on which it reported. It makes 21 calls to two procedures that each obtain a value by summing data across different sets of data in one of two payroll result tables.

The ASH data shows that most of the database time, 184 out of 192 seconds is spent in the two statements that aggregate that data. These statements are in the procedures that are called within the driving query.

SQL_ID        SQL_PLAN_HASH_VALUE  EXEC_SECS   ASH_SECS
------------- ------------------- ---------- ----------
515d3buvaf8us          1162018321        615        133
55a20fnkwv0ht          3972836246        615         51
...
                                             ----------
sum                                                 192

However, more significantly, only 192 seconds out of a total elapsed run time of 615 seconds is spent in the database. That is just 31%. So most of the time is spent executing code within the SQR program.

We need to look at the code to see exactly what is happening.

This is the driving query. It returns each employee who is paid in a specified payroll, and then for each row the procedure get_gp_acum is used to fetch the sum of certain accumulators for that payroll and employee

Begin-Select
a.emplid
a.cal_run_id
a.cal_id
a.gp_paygroup
a.orig_cal_run_id
a.rslt_seg_num
a.tax_code_uk
a.gpgb_ni_categorye.
n.name

  Let $pin_name = 'GBR AC GRTX SEG'  !Taxable Gross 
  do get_gp_acum
  Let #Taxable_gross = &b.calc_rslt_val
  
  Let $pin_name = 'GBR AC NIBL SEG'  !Nlable Gross
  do get_gp_acum
  Let #Niable_gross1 = &b.calc_rslt_val
  
…
from ps_gpgb_ee_rslt a, ps_person_name n
where a.emplid = n.emplid
and a.cal_run_id = $PNL_CAL_RUN_ID1
and a.empl_rcd = 0
and a.gp_paygroup = $PNL_PYGRP1
and a.cal_id = $PNL_CAL_ID1
and a.orig_cal_run_id = a.cal_run_id
order by a.emplid,a.gp_paygroup
End-Select
End-Procedure

This is one of the two procedures that is called to obtain each value. It simply sums the data for that employee.

Begin-Procedure get_gp_acum
begin-select
sum(b.calc_rslt_val) &b.calc_rslt_val
from ps_xx_gpacum_rpt1 b, ps_gp_pin c
where b.emplid    = &a.emplid
and b.empl_rcd    = 0
and b.cal_run_id  = &a.cal_run_id
and b.gp_paygroup = &a.gp_paygroup
and b.cal_id      = &a.cal_id
and b.orig_cal_run_id = &a.orig_cal_run_id
and b.rslt_seg_num    = &a.rslt_seg_num  
and b.orig_cal_run_id = b.cal_run_id
and b.pin_num = c.pin_num
and c.pin_nm = $pin_name
end-select
End-Procedure

This code is very clear, well structured, and easy to maintain. The only trouble is that it is slow. Each SQL calls makes SQR do a lot of work, and that takes time.

In this case there was not much procedural code in the SQR and so I was able to coalesce the SQL from the called procedures into the driving query.

If the called procedures had been simple single row look-ups I could have used an outer-join. However, as they are using a group function (sum), I put the query into a scalar query (a query within in the select clause that returns only one row and one column). Each call to a procedure was replaced with a separate scalar query. I ended up with 21 scalar queries.

During this rewrite I encounted an SQR quirk; if the scalar query was placed in the main select clause, SQR produces errors because it is expecting an expression, and it complains that the SELECT keyword is not a variable. I then had to wrap the query in an in-line view. Each scalar query must be given a column alias, and the column alias can be referenced in the SQR select clause.

Begin-Procedure MAIN-REPORT
Begin-Select
a.emplid
a.cal_run_id
a.cal_id
a.gp_paygroup
a.orig_cal_run_id
a.rslt_seg_num
a.tax_code_uk
a.gpgb_ni_category
n.name

a.gbr_ac_grtx_seg
 Let #Taxable_gross = &a.gbr_ac_grtx_seg

a.gbr_ac_nibl_seg
 Let #Niable_gross1 = &a.gbr_ac_nibl_seg

…  
from (
select a.emplid, a.cal_run_id, a.cal_id, a.gp_paygroup, a.orig_cal_run_id, a.rslt_seg_num, a.tax_code_uk, a.gpgb_ni_category
,NVL((SELECT sum(b.calc_rslt_val) from ps_xx_gpacum_rpt1 b, ps_gp_pin c 
      where b.emplid = a.emplid and b.empl_rcd = 0 and b.cal_run_id = a.cal_run_id 
      and b.gp_paygroup = a.gp_paygroup and b.cal_id = a.cal_id 
      and b.orig_cal_run_id = a.orig_cal_run_id and b.rslt_seg_num = a.rslt_seg_num 
      and b.orig_cal_run_id = a.cal_run_id and b.pin_num = c.pin_num 
      and c.pin_nm = 'GBR AC GRTX SEG'),0) gbr_ac_grtx_seg
,NVL((SELECT sum(b.calc_rslt_val) from ps_xx_gpacum_rpt1 b, ps_gp_pin c 
      where b.emplid = a.emplid and b.empl_rcd = 0 and b.cal_run_id = a.cal_run_id 
      and b.gp_paygroup = a.gp_paygroup and b.cal_id = a.cal_id 
      and b.orig_cal_run_id = a.orig_cal_run_id and b.rslt_seg_num = a.rslt_seg_num 
      and b.orig_cal_run_id = a.cal_run_id and b.pin_num = c.pin_num 
      and c.pin_nm = 'GBR AC NIBL SEG'),0) gbr_ac_nibl_seg
…
from ps_gpgb_ee_rslt a
where a.empl_rcd = 0
and a.orig_cal_run_id = a.cal_run_id
) a
,ps_person_name n
where a.cal_run_id = $PNL_CAL_RUN_ID1
and a.gp_paygroup = $PNL_PYGRP1
and a.cal_id = $PNL_CAL_ID1
and a.emplid = n.emplid
order by a.emplid,a.gp_paygroup
End-Select

The SQL looks much more complicated, as does the execution plan. However, the effect on performance was dramatic.

SQL_ID        SQL_PLAN_HASH_VALUE  EXEC_SECS   ASH_SECS
------------- ------------------- ---------- ----------
f9d03ffbftv81          1694704409        154        114
5d2x9mqvvyrjk           989254841        154          2
3v550ghn6z8jv          1521271881        154          1
                                             ----------
sum                                                 117

The response of just the combined SQL at 117 seconds is better than the separate SQLs at 154 seconds. Much more significantly, the amount of time spent in SQR (rather than the database) has fallen from 432 seconds to just 37. Therefore, 90% of the SQR response time was spent on submitting the SQL calls in the called procedures.

Conclusions

SQL calls in SQR are expensive. The cost of making lots of calls inside a loop or another driving query can add up to a significant amount of time. SQRs that consume time in this way will also be consuming CPU and memory on the server where the Process Scheduler is located.

In this case, combining SQL statements also improved SQL performance, but that will not always be the case.

There are times when better performance can be achieved at the cost of more convoluted code. In each case there is a judgement to be made as to whether improvement in performance is worth the increase in complexity.

PeopleSoft Run Control Purge Utility

noreply@blogger.com (David Kurtz) — Fri, 10 Sep 2010 11:45:00 +0000

Run Control records are used to pass parameters into processes scheduled processes. These tables tend to grow, and are rarely purged. Generally, once created a run control is not deleted. When operator accounts are deleted, the Run Controls remain, but are no longer accessible to anyone else.

I have worked on systems where new Run Controls, whose IDs contain either a date or sequence number, are generated for each process. The result is that the Run Control tables, especially child tables, grow quickly and if not regularly managed will become very large. On one system, I found 18 million rows on one table!

RECNAME         FIELDNAME            NUM_ROWS     BLOCKS
--------------- ------------------ ---------- ----------
TL_RUN_CTRL_GRP RUN_CNTL_ID          18424536     126377
AEREQUESTPARM   RUN_CNTL_ID           1742676      19280
AEREQUESTTBL    RUN_CNTL_ID            333579       3271
XPQRYRUNPARM    RUN_CNTL_ID            121337       1630
TL_TA_RUNCTL    RUN_CNTL_ID            112920        622
…

I have written a simple Application Engine process, GFC_RC_ARCH, that purges old Run Controls from these tables. It can be downloaded from my website.

Run Control records are easily identified. They are characterised by:

the first column of these tables is always OPRID, and the second is either RUNCNTLID or RUN_CNTL_ID,
these two columns are also the first two columns of the unique key,
the Run Control tables appear on pages of components that are declared as the process security component for that process.

I have decided that if the combination of OPRID and RUN_CNTL_ID does not appear in the process scheduler request table, PSPRCSRQST, then the Run Control record should be deleted. Thus, as the delivered Process Scheduler Purge process, PRCSPURGE, deletes rows from the Process Scheduler tables, so my purge process will delete rows from the Run Control tables.

I have chosen to make these two Application Engine processes mutually exclusive, so the Process Scheduler will not run both at the same time, but that configuration cannot be delivered in an Application Designer project.

Announcing the Co-Operative PeopleTools Table Reference

noreply@blogger.com (David Kurtz) — Mon, 12 Jul 2010 14:14:00 +0000

I have created a reference to the PeopleTools tables and views on my website.

In the course of my work on PeopleSoft, I spend a lot of time looking at the PeopleTools tables. They contain meta-data about the PeopleSoft application. Much of the application is stored in various tables that are maintained by Application Designer. Some tables provide information about the Data Model. Others contain configuration data that is maintained via PeopleTools components in the PIA.

Many of my utility scripts query information from PeopleTools tables, and some also update them. Of course, that is strictly not supported, but if you understand how the tables fit together it can be done relatively safely. So, it is very helpful to be able to understand what is in these tables.

In PeopleSoft for the Oracle DBA, I discussed some of the PeopleTools tables that are of regular interest. I included the tables that correspond to the database catalogue, and I discussed what happens during the PeopleSoft login procedure, submission of process requests to the Process Scheduler and PS/Query. The tables that are maintained by the process scheduler are valuable because they contain information about who ran what process when, and how long they ran.

I am not the only person to have started to document the PeopleTools tables on their website or blog, most people have picked a few tables that are of particular interest. However, I want to tackle the problem in a slightly different way. There are over 3000 PeopleTools tables and views (as defined by the PeopleTools object security group in PSOBJGROUP). Tackling all of them manually would be a monumental task.

Nevertheless, I do want a complete reference. So, I have written code to dynamically generate a page for each PeopleTools table and view, and I have put as much information about these records as I can find in the PeopleTools tables themselves. Reference to related objects, including objects referenced in the text of views, appear as links to those pages.

I have started to manually add my own annotation to the generated pages. So far I have only added descriptions to a few tables (marked with an asterisk). However, I would like to make this a collaborative project, and I have already had updates to some pages.

There is a page for each PeopleTools table and view. If you save that page, add descriptions, and return it to me, I will upload it to the site.
You can also add linked to related websites and blog pages to your entries.
Please put your name and, if you wish, a link to your website to the bottom of the pages you author.
Let me know if you think you have found a mistake.

I hope you find it useful.

Configuring Large PeopleSoft Application Servers

noreply@blogger.com (David Kurtz) — Thu, 17 Jun 2010 11:47:00 +0000

Occasionally, I see very large PeopleSoft systems running on large proprietary Unix servers with many CPUs. In an extreme case, I needed to configure application server domains with up to 14 PSAPPSRV processes per domain (each domain was on a virtual server with 8 CPU cores, co-resident with the Process Scheduler).

The first and most important point to make is don't have too many server processes. If you run out of CPU or if you fully utilise all the physical memory and start to page memory from disk, then you have too many server processes. It is better to queue on a Tuxedo queue rather than the CPU run queue, or disk queue during paging.

Multiple APPQ/PSAPPSRV Queues

A piece of advice that I originally got from BEA (prior to their acquisition by Oracle) was that you should not have more than 10 server processes on a single queue in Tuxedo. Otherwise, you are likely to suffer from contention on the IPC queue structure because processes must acquire exclusive access to the queue in order to enqueue a service request to the queue or dequeue a request from it. Instead multiple queues should be configured that are both serviced by the same server processes and so advertise the same services.

If you look at the 'large' template delivered by PeopleSoft, you will see that it produces a domain that runs between 9 and 15 PSAPPSRV processes. This does not conform to the advice I received from BEA. I repeated this advice in PeopleSoft for the Oracle DBA. Though I cannot now find the source for it, I stand by it. I have recently been able to conduct some analysis to confirm it on a real production system. Domains with two queues of 8 PSAPPPSRV server process each out performed domains with only a single queue.

Load Balancing Across Queues

If the same service is advertised on multiple queues, then Tuxedo recommended that you should specify realistic service loads and use Tuxedo load balancing to determine where to enqueue requests. I want to emphasise that I am talking about load balancing across queues within a Tuxedo domain, and not about load balancing across Tuxedo domains in the web server.

This is what the Tuxedo documentation says about load balancing:

"Load balancing is a technique used by the BEA Tuxedo system for distributing service requests evenly among servers that offer the same service. Load balancing avoids overburdening some servers while leaving others idle or infrequently used. Before sending a request to a service routine, the BEA Tuxedo system identifies all servers capable of handling the request and selects the one most appropriate for maintaining a balanced load across all the servers in the configuration.

You can control whether a load-balancing algorithm is used on the system as a whole. Such as algorithm should be used only when necessary, that is, only when a service is offered by servers that use more than one queue. Services offered by only one server, or by multiple servers in a Multiple Server, Single Queue (MSSQ) do not need load balancing. The LDBAL parameter for these services should be set to N. In other cases, you may want to set LDBAL to Y."

It doesn't state that load balancing is mandatory for multi-queue domains, and only hints that it might improve performance. If load balancing is not used, the listener process puts the messages on the first empty queue (one where no requests are queued). If all queues have requests the listener round-robins between the queues.

You could consider giving ICScript, GetCertificate and other services with small service times a higher Tuxedo Service priority. This means they jump the queue 9 times out of 10. ICScript is generally used during navigation, GetCertificate is used at log on. Giving these services higher priority will mean they perform well even when the system is busy. Users often need to do several mouse clicks to navigate around the system, but these services are usually quick. This will improve the user experience without changing the overall performance of the system.

Data

I have recently been able to test the performance of a domains with up to 14 PSAPPSRVs on a single IPC queue, versus domains with two queues with up to 7 PSAPPSRVs each, both with and without Tuxedo queue balancing. These results come from a real production system where the multiple queue configuration was implemented on 2 of the 4 application servers. The system has a short-lived weekly peak period of on-line processing. During that time Tuxedo spawns additional PSAPPSRV processes, and so I get different sets of times for different numbers of process.

The timings are produced from transactions sampled by PeopleSoft Performance Monitor. I capture the number of spawned processes using the Tuxmon scripts on my website that use tmadmin to collect Tuxedo metrics.

Server Processes per Queue	Number of Services	Mean ICPanel Service Time	Server Processes per Queue	Number of Services	Mean ICPanel Service Time
1 Queue			2 Queue
6	2,616	1.33	3	6945	1.05
7	1,949	0.97
8	1,774	1.06	4	7595	1.16
9	1,713	1.02
10	1,553	1.25	5	4629	1.17
11	1,250	1.30
12	969	1.32	6	3397	1.16
13	427	1.21
14	1,057	1.10	7	3445	1.13
Total	13,308	1.17		26011	1.13

The first thing to acknowledge is that this data is quite noisy because it comes from a real production system, and the effects we are looking for are quite small.

I am satisfied that the domains with two PSAPPSRV queues generally perform better under high load, than those under 1. Not only does the queue time increase on the single queue domain, the service time also increases.

However, I cannot demonstrate that Tuxedo Load Balancing makes a significant difference in either direction.

My results suggest that domains with multiple queues for requests handled by PSAPPSRV process perform slightly better without load balancing if there is no queue of requests, but perform slightly better if there is a queue of pending requests. However, the difference is small. It is not large enough to be statistically significant in my test data.

Conclusion

If you have a busy system with lots of on-line users, and sufficient hardware to resource it, then you might reach a point when you need more than 10 PSAPPSRVs. In which case, I recommend that you configure multiple Tuxedo queues.

On the whole, I would recommend that Tuxedo Load Balancing should be configured. I would not expect it to improve performance, but it will not degrade it either.

Life Cycle of a Process Request

noreply@blogger.com (David Kurtz) — Fri, 11 Jun 2010 18:15:00 +0000

Oracle's Flashback Query facility lets you query a past version of a row by using the information in the undo segment. The VERSIONS option lets you seen all the versions that are available. Thus, it is possible to write a simple query to retrieve the all values that changed on a process request record through its life cycle.

The Oracle parameter undo_retention determines how long that data remains in the undo segment. In my example, it is set to 900 seconds, so I can only query versions in the last 15 minutes. If I attempt to go back further than this I will get an error.

column prcsinstance heading 'P.I.' format 9999999
column rownum heading '#' format 9 
column versions_starttime format a22
column versions_endtime format a22

SELECT rownum, prcsinstance
, begindttm, enddttm
, runstatus, diststatus
, versions_operation, versions_xid
, versions_starttime, versions_endtime
FROM sysadm.psprcsrqst
VERSIONS BETWEEN timestamp
systimestamp - INTERVAL '15' MINUTE AND
systimestamp 
WHERE prcsinstance = 2920185
/

#     P.I. BEGINDTTM           ENDDTTM             RU DI V VERSIONS_XID     VERSIONS_STARTTIME    VERSIONS_ENDTIME
-- -------- ------------------- ------------------- -- -- - ---------------- --------------------- ----------------------
 1  2920185 10:51:13 11/06/2010 10:52:11 11/06/2010 9  5  U 000F00070017BD63 11-JUN-10 10.52.10 AM
 2  2920185 10:51:13 11/06/2010 10:52:11 11/06/2010 9  5  U 001A002C001CB1FF 11-JUN-10 10.52.10 AM 11-JUN-10 10.52.10 AM
 3  2920185 10:51:13 11/06/2010 10:52:11 11/06/2010 9  7  U 002C001F000F87C0 11-JUN-10 10.52.10 AM 11-JUN-10 10.52.10 AM
 4  2920185 10:51:13 11/06/2010 10:52:11 11/06/2010 9  1  U 000E000A001771CE 11-JUN-10 10.52.10 AM 11-JUN-10 10.52.10 AM
 5  2920185 10:51:13 11/06/2010 10:52:02 11/06/2010 9  1  U 002A000F00125D89 11-JUN-10 10.52.01 AM 11-JUN-10 10.52.10 AM
 6  2920185 10:51:13 11/06/2010                     7  1  U 0021000B00132582 11-JUN-10 10.51.10 AM 11-JUN-10 10.52.01 AM
 7  2920185                                         6  1  U 0004002000142955 11-JUN-10 10.51.10 AM 11-JUN-10 10.51.10 AM
 8  2920185                                         5  1  I 0022002E0013F260 11-JUN-10 10.51.04 AM 11-JUN-10 10.51.10 AM

Now, I can see each of the committed versions of the record. Note that each version is the result of a different transaction ID.
Reading up from the last and earliest row in the report, you can see the history of this process request record.

At line 8 it was inserted (the value of psuedocolumn VERSION_OPERATION is 'I') at RUNSTATUS 5 (queued) by the component the operator used to submit the record.
At line 7, RUNSTATUS was updated to status 6 (Initiated) by the process scheduler.
At line 6 the process begins and updates the BEGINDTTM with the current database time, and sets RUNSTATUS to 7 (processing).
At line 5 the process completes, updates ENDDTTM to the current database time, and sets RUNSTATUS to 9 (success).
At line 4 the ENDDTTM is updated again. This update is performed by the Distribution Server process in the Process Scheduler domain as report output is posted to the report repository. Note that the value is 1 second later than the VERSIONS_ENDTIME, therefore this time stamp is based on the operating system time for the host running the process scheduler. This server's clock is slightly out of sync with that of the database server.
At lines 3 to 1 there are 3 further updates as the distribution status is updated twice more.

For me, the most interesting point is that ENDDTTM is updated twice; first with the database time at which the process ended, and then again with the time at which any report output was successfully completed.

I frequently want measure the performance of a processes. I often write script that calculate the duration of the process as being the difference between ENDDTTM and BEGINDTTM, but now it is clear that this includes the time taken to post the report and log files to the report repository.

For Application Engine processes, you can still recover the time when the process ended. If batch timings are enabled and written to the database, the BEGINDTTM and ENDDTTM are logged in PS_BAT_TIMINGS_LOG.

select * from ps_bat_timings_log where process_instance = 2920185

PROCESS_INSTANCE PROCESS_NAME OPRID                          RUN_CNTL_ID
---------------- ------------ ------------------------------ ------------------------------
BEGINDTTM           ENDDTTM             TIME_ELAPSED TIME_IN_PC TIME_IN_SQL TRACE_LEVEL
------------------- ------------------- ------------ ---------- ----------- -----------
TRACE_LEVEL_SAM
---------------
         2920185 XXX_XXX_XXXX 52630500                       16023
10:51:12 11/06/2010 10:52:02 11/06/2010        49850      35610       13730        1159
            128

You can see above that ENDDTTM is the time when the process ended.

That gives me some opportunities. For Application Engine programs, I can measure the amount of time taken to posting report content, separately from the process execution time. This query shows me that this particular process took 49 seconds, but the report output took a further 9 seconds to post.

SELECT r.begindttm begindttm
, NVL(l.enddttm, r.enddttm) enddttm
, (NVL(l.enddttm, r.enddttm)-r.begindttm)*86400 exec_secs
, r.enddttm posttime
, (r.enddttm-NVL(l.enddttm, r.enddttm))*86400 post_secs
FROM sysadm.psprcsrqst r
    LEFT OUTER JOIN sysadm.ps_bat_timings_log l
    ON l.process_instance = r.prcsinstance
WHERE r.prcsinstance = 2920185

BEGINDTTM           ENDDTTM              EXEC_SECS POSTTIME             POST_SECS
------------------- ------------------- ---------- ------------------- ----------
10:51:13 11/06/2010 10:52:02 11/06/2010         49 10:52:11 11/06/2010          9

For more detail on the Flashback Query syntax see the Oracle SQL Reference.

Oracle Plan Stability (Stored Outlines) in PeopleSoft Global Payroll

noreply@blogger.com (David Kurtz) — Wed, 31 Mar 2010 19:25:00 +0000

A longer version of this posting, with experimental results, is available on my website.

In PeopleSoft for the Oracle DBA, I wrote a page (p. 291) explaining why stored outlines were not suitable for use in PeopleSoft. Five years later, my view has not significantly changed. Essentially, stored outlines work best with shared SQL, and there isn't much shared SQL in PeopleSoft, because a lot of it is dynamically generated.

Code generated by the component processor is dynamically generated. At save time, only fields that have changed are updated.
PeopleCode can written in such a way that where clauses are dynamically assembled
nVision reports have variable numbers of criteria on literal tree node IDs in the queries.
By default in Application Engine, bind variables are converted to literals before the SQL is submitted to the database. Even if this is overridden by enabling ReUseStatement in Application Engine or by using Cursor Sharing in the database, the code still wouldn’t be sharable. Different instances of Application Engine executing the same program can use different instances on non-shared temporary records, so the tables in otherwise identical SQL statements are not the same. You would get one version of the statement per temporary table instance.

However, there are very limited exceptions to this rule (otherwise I wouldn't have a story to tell). The SQL in COBOL and SQR programs are more likely to be shareable. Although some programs are coded to generate SQL dynamically, bind variables are passed through to SQL statements, and they use regular tables for working storage and not PeopleSoft temporary records.
A Global Payroll customer came to me with a problem where the payroll calculation (GPPDPRUN) would usually run well, but sometimes, the execution plan of a statement would change and the calculation would take additional several hours. It is significant that the Global Payroll engine is written in COBOL. My usual response to this sort of problem in Global Payroll is to add a hint to the stored statement. Usually, I find that only a few statements that are affected. However, after this happened a couple of times in production, it was clear that we couldn't continue to react to these problems. We needed to proactively stop this happening again. This is exactly what stored outlines are designed to do.

Using Stored Outlines in the PeopleSoft GP Engine

Earlier I said that we could apply stored outlines to the Global Payroll calculation engine (GPPDPRUN) because it generally doesn’t use dynamic code with embedded literal values.
While outlines are being created, the following privilege needs to be granted. It can be revoked later.

GRANT CREATE ANY OUTLINE TO SYSADM;

We can create a trigger to collect the stored outlines for a payroll calculation, thus:

The trigger fires when a payroll calculation process starts or finishes.
At the start a payroll process it starts collecting stored outlines in a category called the same as the process; GPPDPRUN.
When the process finishes, outline collection is disabled by setting it back to false.

CREATE OR REPLACE TRIGGER sysadm.gfc_create_stored_outlines
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
WHEN (new.prcsname = 'GPPDPRUN' AND (new.runstatus = 7 OR old.runstatus = 7))
DECLARE
 l_sql VARCHAR2(100);
BEGIN
  l_sql := 'ALTER SESSION SET create_stored_outlines = ';
  IF :new.runstatus = 7 THEN
    EXECUTE IMMEDIATE l_sql||:new.prcsname;
  ELSIF :old.runstatus = 7 THEN
    EXECUTE IMMEDIATE l_sql||'FALSE';
  END IF;
EXCEPTION WHEN OTHERS THEN NULL; --because I dont want to crash the process scheduler  
END;
/

The exact number of outlines that are collected during this process will vary depending upon configuration, and which employees are processed as different payroll rules are invoked.
If no more outlines are to be collected the CREATE ANY OUTLINE privilege can be revoked. This does not prevent the outlines from being used.

REVOKE CREATE ANY OUTLINE FROM SYSADM;

Then, the category of outlines can be used in subsequent executions by replacing the trigger above with the one below, and the execution plans cannot change so long as the SQL doesn’t change.

CREATE OR REPLACE TRIGGER sysadm.gfc_use_stored_outlines
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
WHEN (new.prcsname = 'GPPDPRUN' AND (new.runstatus = 7 OR old.runstatus = 7))
DECLARE
 l_sql VARCHAR2(100);
BEGIN
  l_sql := 'ALTER SESSION SET use_stored_outlines = ';
  IF :new.runstatus = 7 THEN

EXECUTE IMMEDIATE l_sql||:new.prcsname;
  ELSIF :old.runstatus = 7 THEN
    EXECUTE IMMEDIATE l_sql||'FALSE';
  END IF;
EXCEPTION WHEN OTHERS THEN NULL; --because I dont want to crash the process scheduler 
END;
/

After running an identify-and-calc and a cancel, we can see how many of the outlines are actually used.

SELECT category, count(*) outlines
, sum(decode(used,'USED',1,0)) used
FROM user_outlines
GROUP BY category
ORDER BY 1
/

I have a large number of unused outlines because of additional recursive SQL generated because OPTIMIZER_DYNAMIC_SAMPLING was set 4. This does not occur if this parameter is set to the default of 2.

CATEGORY                         OUTLINES       USED
------------------------------ ---------- ----------
GPPDPRUN                              572        281

I can then remove the unused outlines.

EXECUTE dbms_outln.drop_unused;

Used flags on the outlines can be reset, so we later we can see the outlines being used again.

BEGIN
 FOR i IN (SELECT * FROM user_outlines WHERE category = 'GPPDPRUN') LOOP
  dbms_outln.clear_used(i.name);
 END LOOP;
END;
/

If I want to go back running without outlines, I just disable the trigger

ALTER TRIGGER sysadm.stored_outlines DISABLE;

To re-enable outlines, just re-enable the trigger.

ALTER TRIGGER sysadm.stored_outlines ENABLE;

Conclusions

Stored Outlines have very limited application in a PeopleSoft system. However, they can easily be collected and used with the PeopleSoft Global Payroll engine. It is just a matter of granting a privilege and using the database triggers on the process request table.
Testing that they actually have the desired effect is quite difficult, because you are trying to prove a negative. I don’t think it is adequate simply to say that the outline has been used.

First you would need an environment where payroll calculation performs well, where you could collect outlines.
Then you would need a payroll calculation that performs poorly because the execution plan for at least one SQL statement is different

Either, on a second environment with exactly the same code.
Or in the same environment on a different set of data.

Then, it would be possible to demonstrate that applying the outline causes the execution plan to revert and restores the performance. This can be confirmed by comparison of the ASH data for the various scenarios.

Even if you don’t want to use a stored outline immediately, it might be advantageous to collect them, and have them available when you do encounter a problem.

Capturing DDL for Database Objects Not Managed by PeopleTools

noreply@blogger.com (David Kurtz) — Thu, 25 Mar 2010 19:34:00 +0000

I have written before about the challenges of managing database objects and attributes of database objects that are not managed by PeopleTools Application Designer. I proposed a DDL trigger to prevent such objects being dropped or altered. However, sometimes it is necessary to temporarily disable this DDL trigger, such as during patch or upgrade release it is necessary to disable this trigger to apply the changes.

Now, I have another DDL trigger and a packaged procedure that captures the DDL to recreate objects that are recursively dropped (such as DML triggers on tables). The DDL is stored in a database table. This trigger can remain permanently enabled, and the table it maintains can be used to see what objects are missing, as well as holding the SQL to rebuild them.

An common example of where this is can be valuable is where a system uses database triggers to capture audit data. This method is often preferred because it generally performs better than having the application server generate additional DML to the audit table, and also captures updates made in other processes. PeopleSoft even deliver processes to generate the DML triggers that write to the audit tables. However, if you alter the table in Application Designer, perhaps only because you are applying a PeopleSoft fix, and apply the changes to the database by recreating the table, then the trigger will be lost. It is then up to the customer to make sure the audit trigger is replaced. There is absolutely nothing to warn you that the trigger is lost, and the application will still function without the trigger, but your updates will not be audited.

When a table is dropped, the trigger calls a procedure in the package that checks for:

indexes that are not managed by PeopleTools (such as function-based indexes),
triggers not managed by PeopleTools (other than the PSU triggers created for mobile agents),
materialised view logs.
If the table is partitioned or global temporary the DDL for the object being dropped is also captured.

When an index is dropped the index check is performed. Similarly the DDL to rebuild partitioned indexes or indexes on Global Temporary tables is also captured.

When an object for which the DDL has been captured is explicitly dropped, this is indicated on the table GFC_REL_OBJ by storing the time at which it was dropped. When it is recreated this time-stamp is cleared. Thus it is possible to decide whether something was deliberately or accidentally dropped.

Hinting Dynamically Generated SQL in Application Engine

noreply@blogger.com (David Kurtz) — Thu, 04 Mar 2010 20:22:00 +0000

One of the clever things you can do in Application Engine is dynamically generate parts of a SQL statement. However, this can produce challenges should decide that you also need to add an optimiser hint to a SQL statement. In this post I want to demonstrate how you can also use the same techniques to dynamically generate valid Optimizer hints.

The following statement was generated by a step in a delivered Application Engine that had not been previously been changed. The PS_XXX tables are custom tables, but the references are in custom meta-data that is used to dynamically generate the SQL. I have added the hints and comments.

The specific hints and comments are not important here, the point is how I managed to generate them.

Note that:

I have added a comment that contains the name of the application engine step that generated this SQL. This has can be added automatically with the aeid.sql script on my website. It can be difficult to identify which step generated which static SQL statement. It can be impossible to do that with dynamic SQL. This identifying comment appears in the SQL.
I have added a QB_NAME hint to the sub-query, and then the hint can refer to that sub-query (see related blog entry Hinting Sub-Queries on Oracle).
Rather than specify the index name in the INDEX hint I have use the table_name(column_list) construction to specify an index on a named table that starts with the named columns. This is good general practice, the hint remains valid in the case that an index name changes. However, it is also useful here because there is no meta-data that I can use to construct the name of the index.

UPDATE /*+INDEX(@SUB1 ln@SUB1 PS_XXX_SPLT_TA2(process_instance,resource_id))          INDEX(PS_XXX_SPLT_TA1 PS_XXX_SPLT_TA1(process_instance,iu_line_type))*/    /*ID-IU_PROCESSOR.P150.P150-010.S*/     PS_XXX_SPLT_TA1 SET    iu_line_type='U'WHERE iu_line_type='2' AND process_instance=12345678 AND setid_iu='XXXXX' AND ledger_group = 'ACTUALS' AND EXISTS (    SELECT /*+QB_NAME(SUB1)*/ 'X'     FROM PS_XXX_SPLT_TA2 ln    where ln.EMPLID2 = PS_XXX_SPLT_TA1.EMPLID2     and ln.SEQ_NUM = PS_XXX_SPLT_TA1.SEQ_NUM     and ln.BUSINESS_UNIT = PS_XXX_SPLT_TA1.BUSINESS_UNIT     and ln.RESOURCE_ID = PS_XXX_SPLT_TA1.RESOURCE_ID     AND ln.setid_iu = 'XXXXX'    AND ln.ledger_group = 'ACTUALS'     AND ln.process_instance = 12345678     GROUP BY ln.BUSINESS_UNIT_GL , ln.ledger, ln.OPERATING_UNIT, ln.BUSINESS_UNIT, ln.RESOURCE_ID, ln.EMPLID2, ln.SEQ_NUM     HAVING SUM(ln.RESOURCE_AMOUNT) <> 0)

So how did I get those hints into the SQL?

First of all remember that Application Engine doesn't know anything about SQL. An Application Engine step is just a string of characters that will be submitted to the database. Any PeopleCode macros are executed as the step is prepared and the resultant string is then set to the database.

The name of the PeopleSoft record being updated is in a variable line_wrk2_rec. I can introduce the contents of the variable line_wrk2_rec with the %BIND() macro. Normally a string bind variable is delimited by single quotes because it is used in a function or predicate, but the quotes can be suppressed with the NOQUOTES option. I can convert the PeopleSoft record name to the database table name with the %Table() PeopleCode macro.

So, in this example

Code in Application Engine Step	Expansion
%BIND(line_wrk_rec)	'XXX_SPLT_TA1'
%BIND(line_wrk_rec,NOQUOTES))	XXX_SPLT_TA1
%Table(%BIND(line_wrk_rec,NOQUOTES))	PS_XXX_SPLT_TA1

Note that delivered PeopleSoft SQL never specifies a row source alias on the table being updated because this would be invalid SQL on SQL Server. Thus one SQL statement can be used on multiple platforms. Although it is possible to have platform specific steps in Application Engine, PeopleSoft development avoid this wherever possible because it increases their development overheads. So the row source alias is the table name.

I have used the expression %Table(%BIND(line_wrk_rec,NOQUOTES)) twice; once for the table alias and then again when I specify the index. I want to force the use of an index on PS_XXX_SPLT_TA1 that leads on columns PROCESS_INSTANCE and RESOURCE_ID. This is the code in the Application Engine step that generates the SQL statement above.

UPDATE /*+INDEX(@SUB1 ln@SUB1 %Table(%BIND(line_wrk2_rec,NOQUOTES))(process_instance,resource_id))        INDEX(%Table(%BIND(line_wrk_rec,NOQUOTES)) %Table(%BIND(line_wrk_rec,NOQUOTES))(process_instance,iu_line_type))*/    /*ID-IU_PROCESSOR.P150.P150-010.S*/%Table(%BIND(line_wrk_rec,NOQUOTES))  SET iu_line_type='U'  WHERE iu_line_type='2'    AND process_instance=%BIND(process_instance)    AND setid_iu=%BIND(iu_proc_002_aet.setid_ledger)    AND ledger_group = %BIND(iu_proc_002_aet.ledger_group)    AND EXISTS (     SELECT /*+QB_NAME(SUB1)*/ 'X'       FROM %Table(%BIND(line_wrk2_rec,NOQUOTES)) ln         %BIND(iu_where_aet.iu_where_sql,NOQUOTES)%BIND(iu_group_by_aet.iu_group_by_sql,NOQUOTES)         %BIND(iu_proc_002_aet.where_bu,NOQUOTES)        AND ln.setid_iu = %BIND(iu_proc_002_aet.setid_ledger)        AND ln.ledger_group = %BIND(iu_proc_002_aet.ledger_group)        AND ln.process_instance = %ProcessInstance      GROUP BY ln.%BIND(iu_sys_tran_aet.fieldname_bu_gl,NOQUOTES), ln.ledger%BIND(iu_proc_001_aet.iu_group_by_sql,NOQUOTES)      HAVING SUM(ln.%BIND(iu_sys_tran_aet.fieldname_base_amt,NOQUOTES)) <> 0)

There is a problem here (at least there is in theory). If this statement executes for a different table the index hint will instruct the SQL to look for an index on that different table on the same columns. The predicates in the where clauses are also derived from dynamic code. If I was being absolutely rigorous, I would have added some procedural code in preceding steps to build this part of the hints dynamically too, however, I am also trying to keep the customisation to a minimum in an area of code that is otherwise vanilla.

I have accepted a compromise. If the step executes for a different table, the hint will probably be invalid because there is probably no such index on these columns on that table. There is a risk that such an index does exist and so the hint could be valid but totally inappropriate to the situation because the predicates are totally different. The result could be very poor performance. However, in this case, in practice, this risk is zero, and the hint results in appropriate behaviour in all scenarios. Although this is something that you need to consider on a case by case basis

Performance Metrics and XML Reporting in PeopleSoft

noreply@blogger.com (David Kurtz) — Wed, 20 Jan 2010 22:57:00 +0000

I am working with a PeopleSoft system that makes extensive use of XML Publisher reporting. Generally these reports are based on SQL queries that are defined in the PeopleSoft PS/Query utility.

These queries are run by an Application Engine program PSXPQRYRPT that runs the SQL query and produces the report in a single process. This is a generic application engine program that runs any XML report. Line 45 (in PeopleTools 8.49) of step MAIN.ExecRPT executes the report with the ProcessReport PeopleCode command.

&oRptDefn.ProcessReport(&TemplateId, &LanguageCd, &AsOfDate, &oRptDefn.GetOutDestFormatString(&OutDestFormat));

Batch Timings
Analysis of the Application Engine batch timings indicate that nearly all the time in this Application Engine program is spent in PeopleCode, and that this not SQL execution time. This is misleading. The ProcessReport command is PeopleCode, but behind the scenes it also issues the SQL in the report data source. Not all the time is SQL, but the Application Engine Batch Timings does not count any of this as SQL because it is not in the PeopleCode SQL Class.

Let’s look at an example Batch Timings report (I have edited it down, removing zero and insignificant timings).

PeopleSoft Application Engine Timings
                                 (All timings in seconds)

                               C o m p i l e    E x e c u t e    F e t c h        Total           
SQL Statement                  Count   Time     Count   Time     Count   Time     Time    
------------------------------ ------- -------- ------- -------- ------- -------- --------
PeopleCode
Record.SelectByKey PSDBFIELD                          8      0.1       8      0.0      0.1
SELECT PSPRCSRQST                                     2      0.1       2      0.0      0.1
SELECT PSXPTMPLDEFN                                   3      0.1       3      0.0      0.1
                                                                                  --------
                                                                                       0.3
AE Program: PSXPQRYRPT
MAIN.ExecRpt.H                       1      0.0       1      0.0       1      0.0      0.0
                                                                                  --------
                                                                                       0.0
------------------------------------------------------------------------------------------
                               Call    Non-SQL  SQL      Total   
PeopleCode                     Count   Time     Time     Time    
------------------------------ ------- -------- -------- --------
AE Program: PSXPQRYRPT
MAIN.ExecRpt                         1    643.2      0.3    643.5
                                       -------- -------- --------
                                          643.2      0.3    643.5
------------------------------------------------------------------------------------------
                                                        E x e c u t e   
PEOPLECODE Builtin/Method                               Count   Time    
------------------------------------------------------- ------- --------
Boolean(Type 5) BuiltIns                                     90      0.1
DateTime(Type 11) BuiltIns                                    1      0.1
SQL(Type 524290) Methods                                     19      0.1
SQL(Type 524290) BuiltIns                                     9      0.1
Record(Type 524291) Methods                                1104      0.1
Session(Type 524303) Methods                                207    633.2
JavaObject(Type 524315) BuiltIns                              6      2.2
PostReport(Type 524324) Methods                               2      0.7
------------------------------------------------------------------------------------------
Total run time                :      644.0
Total time in application SQL :        0.3   Percent time in application SQL :        0.0%
Total time in PeopleCode      :      643.2   Percent time in PeopleCode      :       99.9%
------------------------------------------------------------------------------------------

The total execution time of is 644 seconds.
643.2s are reported as being in PeopleCode, and as coming from the MAIN.ExecRpt step.
Only 0.3s of that is SQL time, and that comes from PeopleCode functions in the SQL Class (CreateRecord , CreateSQL etc.)
The ProcessReport method does not appear in the PeopleCode SQL analysis section of the report, because it is not classed as SQL.

Identifying the Report ID and Report Source
So, the next question is how can I find out which report this process is running. Different instances of this report may be running different queries.

You can get the list files generated by a process from the Content Management PeopleTools table PS_CDM_FILE_LIST. Application Engine processes usually produce various log files (with extensions .aet, .trc and .log), the name of the other file is the same as the name of the report followed by an extension that will vary depending on format.

You can look at the report definition on-line under Reporting Tools -> XML Publisher -> Report Definition, and that will show you the Data Source ID

The Data Source is defined in a component accessible from the previous entry in the same menu.

Analysing Execution Times
I have written this query to aggregate execution time for PSXPQRYRPT by output file name.

PSPRCSRQST is the process request table, from which I get the run time of the process.
PS_CDM_FILE_LIST lists the files generated by the process which would be posted to the Report Repository. If I exclude the usual trace files, I am left with the report ID.
PSXPRPTDEFN is the report definition record, from which I can get the data source ID
PSXPDATSRC specifies the data source. A data source type (DS_TYPE) of QRY indicates a PeopleSoft PS/Query. If it is a private query, the OPRID will have a value.

column report_defn_id heading 'Report ID'      format a12 
column ds_type        heading 'Type'           format a4 
column ds_id          heading 'Data Source ID' format a30 
column oprid          heading 'Owner'          format a10 
column processes      heading 'Prcs'           format 990 
column reprots        heading 'Reps'           format 9,990 
column secs           heading 'Total|Seconds'  format 999,990 
column median         heading 'Median|Seconds' format 999,990 
column variance       heading 'Variance'       format 9990.0
SELECT d.report_defn_id, d.ds_type, d.ds_id, d.oprid 
,      SUM(y.processes) processes
,      SUM(y.reports) reports
,      SUM(y.secs) secs
,      median(y.secs) median
,      variance(y.secs) variance
FROM (
 SELECT x.prcsinstance
 ,      x.filename
 ,      COUNT(DISTINCT x.prcsinstance) processes
 ,      COUNT(*) reports
 ,      SUM(x.secs) secs
 FROM   (
  SELECT r.prcsinstance
  ,      f.filename
  ,      86400*(r.enddttm-r.begindttm)*ratio_to_report(1) over (partition by r.prcsinstance) secs
  FROM   sysadm.psprcsrqst r
  ,      sysadm.ps_cdm_file_list f
  WHERE  r.prcsname = 'PSXPQRYRPT'
  AND    r.prcsinstance = f.prcsinstance
  AND    NOT f.cdm_file_type IN('AET','TRC','LOG')
  AND    r.begindttm >= TRUNC(SYSDATE)
  ) x
 GROUP BY x.prcsinstance, x.filename
 ) y
,      sysadm.psxprptdefn d
WHERE  d.report_defn_id = SUBSTR(y.filename,1,instr(y.filename,'.')-1)
GROUP BY d.report_defn_id, d.ds_type, d.ds_id, d.oprid 
ORDER BY secs DESC
/

I can now see which report process has been executed run how many times, how many copies of the report have been produced, and where the processing time is being spent, and so which one I should look at first.

Total   Median
Report ID    Type Data Source ID                 Owner      Prcs   Reps  Seconds  Seconds Variance
------------ ---- ------------------------------ ---------- ---- ------ -------- -------- --------
XXX_WK_LATE  QRY  XXX_WKLY_LATENESS_RPT                       20     20    2,973      148   3702.9
XXX_HRAM_CON QRY  XXX_HRPD_CNT_AMD_RPT_QRY                    92     92    2,677       27    108.4
XXX_CKOUT_RP QRY  XXX_CHECKOUT_REPORT                         47     47    2,043       41    347.7
XXX_BNK_RPT  QRY  XXX_BNK_DTLS_SCH_QRY                         1     75      141      141      0.0
…

Conclusion

If you have a SQL performance problem with an XML report because the source PS/Query performs poorly, the batch timings will lead you to believe that you have a PeopleCode problem and not a SQL problem.

Beware, this may not be the case.

Most of the execution time for XML Reports sourced from PS/Queries is almost certain to be SQL execution time. The above query will tell you which queries are consuming how much time, and so inform your tuning effort.

Automatically Identifying Stored Statements & Using DBMS_STATS in PeopleSoft Cobol

noreply@blogger.com (David Kurtz) — Sun, 10 Jan 2010 12:50:00 +0000

It is not possible to reference a long column in a Oracle database trigger, but it is possible to reference LOBs. From PeopleSoft v9 Applications, the long columns have become CLOBs. Hence, it is now possible to have a database trigger fire on insert into the Stored Statement table and so automatically make certain changes to Stored Statements as they are loaded by Data Mover. Previously, these changes could be made by a PL/SQL script, but you had to remember to run in after any Stored Statements were reloaded. I have published a new script (gfc_stmtid_trigger.sql) on my website that creates two such database triggers.

Trigger GFC_STMTID

In PeopleSoft for the Oracle DBA (listing 11-23, page 280), I proposed a PL/SQL procedure to add identifying comments to stored statements (see blog entry: Identifying Application Engine Source Code), so that the statements can be identified in traces or Oracle Enterprise Manager.

Trigger GFC_STMTSTATS

PeopleSoft Cobol programs do not use the PeopleSoft DDL models that Application Engine users when it processes the %UPDATESTATS macro. In another blog posting, I showed how to change the stored statements to call the wrapper package, so that the program uses the Oracle supplied dbms_stats procedure instead of the depreciated ANALYZE command. This trigger automatically replaces the %UPDATESTATS macro in the stored statement with a call to the wrapper package.

Hinting Sub-Queries on Oracle

noreply@blogger.com (David Kurtz) — Thu, 17 Dec 2009 20:24:00 +0000

This posting is a purely Oracle RDBMS discussion about how to correctly apply hints to sub-queries. However, it is particularly relevant to PeopleSoft, which it makes extensive use of correlated sub-queries. The point of this story is not the particular hints that I applied, but where I placed the hints, and how I scoped them to the sub-queries.

The following SQL extract is from the delivered Global Payroll GPGB_EDI process (although I have already made some minor changes, and PS_GP_RSLT_PIN is partitioned). Notice that each sub-query joins two tables together. PS_GP_RSLT_PIN is second largest of Global Payroll result tables. PS_GP_PIN is a look-up table, and the criterion on PIN_CODE will only return a single row.

UPDATE Table(GPGB_EDIE_TMP) X
  SET X.GPGB_WK53_IND = ( 
 SELECT %TrimSubstr(%Sql(FUNCLIB_HR_CHAR,A.CALC_RSLT_VAL),1,2) 
  FROM PS_GP_RSLT_PIN A 
  ,PS_GP_PIN B 
 WHERE A.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID 
...

AND A.PIN_NUM = B.PIN_NUM 
   AND B.PIN_CODE = 'TAX VR PERIOD GBR' 
   AND A.SLICE_BGN_DT = ( 
 SELECT MAX(D.SLICE_BGN_DT) 
  FROM PS_GP_RSLT_PIN D 
 WHERE D.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID 
...
   AND D.INSTANCE = A.INSTANCE 
   AND D.PIN_NUM = B.PIN_NUM) 
...
   )
 WHERE EXISTS ( 
...
   )
AND PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

This is part of the initial SQL execution plan. The problem is that the sub-query starts by scanning through the PS_GP_RSLT_PIN table, 4 times because there are three correlated sub-queries, and only at the very end does it look up the PIN_CODE by the PIN_NUM.

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| 
---------------------------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                              |                   |     1 |    65 |   113M (93)| 39:44:51 |       |     
|   1 |  UPDATE                                       | PS_GPGB_EDIE_TMP4 |       |       |            |          |       |     
|*  2 |   FILTER                                      |                   |       |       |            |          |       |     
|*  3 |    TABLE ACCESS FULL                          | PS_GPGB_EDIE_TMP4 |   673K|    41M|  9967   (6)| 00:00:13 |      
|   4 |    NESTED LOOPS                               |                   |     1 |   108 |     4   (0)| 00:00:01 |       |     
|   5 |     PARTITION RANGE SINGLE                    |                   |     1 |    83 |     3   (0)| 00:00:01 |   KEY | 
|   6 |      PARTITION LIST SINGLE                    |                   |     1 |    83 |     3   (0)| 00:00:01 |   KEY | 
|*  7 |       INDEX RANGE SCAN                        | PS_GP_RSLT_PIN    |     1 |    83 |     3   (0)| 00:00:01 |   KEY | 
|   8 |        SORT AGGREGATE                         |                   |     1 |    72 |            |          |       |     
|   9 |         PARTITION RANGE SINGLE                |                   |     1 |    72 |     3   (0)| 00:00:01 |   KEY | 
|  10 |          PARTITION LIST SINGLE                |                   |     1 |    72 |     3   (0)| 00:00:01 |   KEY | 
|* 11 |           INDEX RANGE SCAN                    | PS_GP_RSLT_PIN    |     1 |    72 |     3   (0)| 00:00:01 |   KEY | 
|  12 |            SORT AGGREGATE                     |                   |     1 |    83 |            |          |       |     
|  13 |             PARTITION RANGE SINGLE            |                   |     1 |    83 |     3   (0)| 00:00:01 |   KEY | 
|  14 |              PARTITION LIST SINGLE            |                   |     1 |    83 |     3   (0)| 00:00:01 |   KEY | 
|  15 |               FIRST ROW                       |                   |     1 |    83 |     3   (0)| 00:00:01 |       |     
|* 16 |                INDEX RANGE SCAN (MIN/MAX)     | PS_GP_RSLT_PIN    |     1 |    83 |     3   (0)| 00:00:01 | 
|  17 |                 SORT AGGREGATE                |                   |     1 |    83 |            |          |       |     
|  18 |                  PARTITION RANGE SINGLE       |                   |     1 |    83 |   158  (99)| 00:00:01 |   KEY | 
|  19 |                   PARTITION LIST SINGLE       |                   |     1 |    83 |   158  (99)| 00:00:01 |   KEY | 
|  20 |                    FIRST ROW                  |                   |     1 |    83 |   158  (99)| 00:00:01 |       |     
|* 21 |                     INDEX RANGE SCAN (MIN/MAX)| PS_GP_RSLT_PIN    |     1 |    83 |   158  (99)| 00:00:01 |   KEY |
|* 22 |     INDEX RANGE SCAN                          | PSAGP_PIN         |     1 |    25 |     1   (0)| 00:00:01 |       |     
...
---------------------------------------------------------------------------------------------------------------------------

It would be much better if we started with the PS_GP_PIN table, looked up the PIN_NUM with the PIN_CODE (there is a suitable index on this column) and used the PIN_NUM value as a part of the lookup on PS_GP_RSLT_PIN (again PIN_CODE is in the unique index on that table).

It is tempting to add LEADING hints to the sub-queries, but such a hint does not work because the hint is not scoped to the sub-query and is considered to be invalid because the entire query cannot start at this point.

The only supported place in the query to add a LEADING hint would be the first query, in this case after the UPDATE keyword.

In this case, I have named the query blocks in the sub-queries with the QB_NAME hint. It is valid to put this hint into the sub-query. Then I added LEADING hints for each sub-query after the UPDATE keyword, but I specified their scope using the name of the sub-query specified in the QB_NAME hint. Each sub-query must now start with the PS_GP_PIN table.

UPDATE /*+LEADING(@SUB1 B@SUB1) LEADING(@SUB2 B@SUB2)*/ %Table(GPGB_EDIE_TMP) X
  SET X.GPGB_WK53_IND = ( 
 SELECT /*+QB_NAME(SUB1)*/ %TrimSubstr(%Sql(FUNCLIB_HR_CHAR,A.CALC_RSLT_VAL),1,2) 
  FROM PS_GP_RSLT_PIN A 
  ,PS_GP_PIN B 
 WHERE A.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID 
...
   AND A.PIN_NUM = B.PIN_NUM 
   AND B.PIN_CODE = 'TAX VR PERIOD GBR' 
   AND A.SLICE_BGN_DT = ( 
 SELECT MAX(D.SLICE_BGN_DT) 
  FROM PS_GP_RSLT_PIN D 
 WHERE D.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID 
...
   AND D.INSTANCE = A.INSTANCE 
   AND D.PIN_NUM = B.PIN_NUM) 
...
   )
 WHERE EXISTS ( 
 SELECT /*+QB_NAME(SUB2)*/ 'X' 
  FROM PS_GP_RSLT_PIN A1 
  ,PS_GP_PIN B1 
 WHERE A1.EMPLID = %Table(GPGB_EDIE_TMP).EMPLID 
...
   AND A1.PIN_NUM = B1.PIN_NUM 
   AND B1.PIN_CODE = 'TAX VR PERIOD GBR' 
...
   )
AND PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

This is the new execution plan. The sub-query starts with an access of index PSAGP_PIN (which leads on PIN_CODE) on PS_GP_PIN, and then does the lookups on PS_GP_RSLT_PIN. The cost is the same, but the execution time was considerably reduced.

----------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| 
----------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                              |                   |     1 |    65 |   113M (93)| 39:44:51 |       |     
|   1 |  UPDATE                                       | PS_GPGB_EDIE_TMP4 |       |       |            |          |       |     
|*  2 |   FILTER                                      |                   |       |       |            |          |       |     
|*  3 |    TABLE ACCESS FULL                          | PS_GPGB_EDIE_TMP4 |   673K|    41M|  9967   (6)| 00:00:13 |       |
|   4 |    NESTED LOOPS                               |                   |     1 |   108 |     4   (0)| 00:00:01 |       |     
|*  5 |     INDEX RANGE SCAN                          | PSAGP_PIN         |     1 |    25 |     2   (0)| 00:00:01 |       |     
|   6 |     PARTITION RANGE SINGLE                    |                   |     1 |    83 |     2   (0)| 00:00:01 |   KEY | 
|   7 |      PARTITION LIST SINGLE                    |                   |     1 |    83 |     2   (0)| 00:00:01 |   KEY | 
|*  8 |       INDEX RANGE SCAN                        | PS_GP_RSLT_PIN    |     1 |    83 |     2   (0)| 00:00:01 |   KEY | 
|   9 |        SORT AGGREGATE                         |                   |     1 |    72 |            |          |       |     
|  10 |         PARTITION RANGE SINGLE                |                   |     1 |    72 |     3   (0)| 00:00:01 |   KEY | 
|  11 |          PARTITION LIST SINGLE                |                   |     1 |    72 |     3   (0)| 00:00:01 |   KEY | 
|* 12 |           INDEX RANGE SCAN                    | PS_GP_RSLT_PIN    |     1 |    72 |     3   (0)| 00:00:01 |   KEY | 
|  13 |            SORT AGGREGATE                     |                   |     1 |    83 |            |          |       |     
|  14 |             PARTITION RANGE SINGLE            |                   |     1 |    83 |     3   (0)| 00:00:01 |   KEY | 
|  15 |              PARTITION LIST SINGLE            |                   |     1 |    83 |     3   (0)| 00:00:01 |   KEY | 
|  16 |               FIRST ROW                       |                   |     1 |    83 |     3   (0)| 00:00:01 |       |     
|* 17 |                INDEX RANGE SCAN (MIN/MAX)     | PS_GP_RSLT_PIN    |     1 |    83 |     3   (0)| 00:00:01 |       |
|  18 |                 SORT AGGREGATE                |                   |     1 |    83 |            |          |       |     
|  19 |                  PARTITION RANGE SINGLE       |                   |     1 |    83 |   158  (99)| 00:00:01 |   KEY | 
|  20 |                   PARTITION LIST SINGLE       |                   |     1 |    83 |   158  (99)| 00:00:01 |   KEY | 
|  21 |                    FIRST ROW                  |                   |     1 |    83 |   158  (99)| 00:00:01 |       |     
|* 22 |                     INDEX RANGE SCAN (MIN/MAX)| PS_GP_RSLT_PIN    |     1 |    83 |   158  (99)| 00:00:01 |   KEY |
|  23 |   TABLE ACCESS BY LOCAL INDEX ROWID           | PS_GP_RSLT_PIN    |     1 |    86 |     3   (0)| 00:00:01 |       |
...
---------------------------------------------------------------------------------------------------------------------------

Controlling How PeopleSoft Cobol Collects Statistics

noreply@blogger.com (David Kurtz) — Wed, 25 Nov 2009 08:23:00 +0000

In previous postings, I have proposed locking statistics on temporary working storage tables and changing the DDL model for %UpdateStats to call my own PL/SQL Package. That works for Application Engine programs, but PeopleSoft COBOL can also update object statistics, and they use a different mechanism.

In the case of the Global Payroll calculation engine, GPPDPRUN, the run control component has a secondary page with a check box to enable statistics collection during the process.

However, to get streamed processing in Payroll (where the population of employees is broken into ranges of employee IDs that are each processed by a different concurrent process) to work effectively I change the working storage tables to be Global Temporary Tables, and then because the different physical instances would still share statistics (Global Temporary Tables Share Statistics Across Sessions) I delete and lock the statistics on these tables.

If the payroll calculation is run with the Update Statistics option it generates the following error.

Application Program Failed
 Action Type     : SQL UPDATE
 In Pgm Section  : SQLRT: EXECUTE-STMT                                 
 With Return Code: 38029 
 Error Message   : ORA-38029: object statistics are locked
 Stored Stmt     : GPPSERVC_U_STATS  
 SQL Statement   : ANALYZE TABLE PS_GP_PYE_STAT_WRK ESTIMATE STATISTICS

COBOL issued an ANALYZE command, and did not use the DDL model defined in the table PSDDLMODEL. However, the command came from a stored statement, in this case GPPSERVC_U_STATS. The stored statement is defined as follows in the gppservc.dms.

STORE GPPSERVC_U_STATS
%UPDATESTATS(PS_GP_PYE_STAT_WRK)
;

So, the expansion of %UPDATESTATS in the stored statement to the ANALYZE command is hard coded somewhere in the delivered executable code. I would not suggest attempting to change that.

However, it is perfectly possible to change the stored statement to call the wrapper package (www.go-faster.co.uk/scripts/wrapper848meta.sql).

STORE GPPSERVC_U_STATS
BEGIN wrapper.ps_stats(p_ownname=>user, p_tabname=>'PS_GP_PYE_STAT_WRK'); END;;

In all there are 5 statements in HR 9.0 that call %UPDATESTATS that all relate to GPPDPRUN. I generated a data mover script to replace them with calls to my replacement package using the following SQL.script.

set head off feedback off long 5000
spool updatestats_after.dms
select 'STORE '||pgm_name||'_'||stmt_type||'_'||stmt_name
||CHR(10)
||'BEGIN wrapper.ps_stats(p_ownname=>user,p_tabname=>'''
||substr(stmt_text
 , INSTR(stmt_text,'(')+1
 , INSTR(stmt_text,')')-INSTR(stmt_text,'(')-1
 )
||'''); END;;'
from ps_sqlstmt_tbl
where stmt_text like '%UPDATESTATS(%'
/
spool off

I have only tested this against HR9.0. Note that long columns such as PS_SQLSTMT_TBL.STMT_TEXT only become CLOBs when the application version reaches 9.0. You cannot use the LIKE operation on the long column.

Database Links and PeopleSoft

noreply@blogger.com (David Kurtz) — Wed, 28 Oct 2009 08:19:00 +0000

I have seen Oracle database links used in conjuction with PeopleSoft at a number of customer sites over the years. I think some scenarios are examples where it is reasonable to use a database link, some are not.
In Oracle RDBMS, a database link (see also the Oracle Concepts Manual) specifies how a database server can create a session on another database in order to perform a remote operation. The remote database may be another Oracle database, or another type of database. The remote operation may be a query, or it may modify remote data (DML operation), in which case a two-phase commit mechanism ensures transaction integrity across the databases.

PeopleSoft does not use database links between databases because they are a database platform specific technology. Instead, the PeopleSoft methodology is to replicate data between databases with an application message. This works well with small data sets, and with larger data sets that change slowly. However, there are scenarios with very large data volumes where Application Messaging will struggle to keep up, and Oracle RDMBS specific replication technologies are an appropriate alternative.

Data can be replicated between databases with Materialised Views (formerly known as Snapshots). This is a SQL based technology. Materialised Views Logs on the source database track changes in the source table. An incremental or full refresh process is run on the target, usually as a scheduled job.

Oracle introduced an alternative replication technology in Oracle 9i called ‘Streams’. This uses supplementary redo logging on the source database which can then be applied to the target. Additional database server processes transmit, receive and apply this redo stream. This technology is aimed a large volume replication for data warehouses. I know of one site where several hundred database tables are replicated from PeopleSoft HR to a data warehouse.

I think the clearest way is to explain the good and bad use of links is by example.

At two different sites with HR and Finance, I have seen similar examples of both good and bad use of database links (one was on PeopleTools 7.0, before Application Messaging was available).

GL data needs to be sent from Payroll in the HR database to GL in the Financials database. The PeopleSoft delivered mechanism was to use generate and reload an interface file. This customer changed replaced the interface table in HR with a view that referenced a table with the same name and structure on the Financial system via a link. The payroll GL extract process now inserted directly into the table on Financials. I think this is a perfectly reasonable use of a database link. It was simple. It performed better than the interface file, and would certainly have been faster than application messaging.

However, the same customer also used the employee expenses module in Financials. This required employee personal data. So they made the PS_PERSONAL_DATA table in Financials a view of the corresponding table in HR. The result was that every time somebody opened the expenses component in Financials, the application server process referenced the database link to HR. There is a limit on the maximum number of database links that a session can concurrently open (OPEN_LINKS) and that the whole database instance can concurrently open (OPEN_LINKS_PER_INSTANCE). They both default is 4, which gives an indication of how Oracle expect you to use this feature. This system ran out of database links. No Oracle errors were generated, instead sessions wait to be allowed to make the connection to the remote database. There are specific database link wait events that report the amount of time lost. Eventually you reach the point where Tuxedo services timeout and then users receive errors. I think this is an example of the wrong way to use a database link. Instead I think that the data should have been replicated from HR to Financials. In a modern PeopleSoft system this should be done with an application message (on the 7.0 system a Materialised View could have been used).

Not only were database links used to provide data to components, but the same views, that referenced remote objects, were used in queries and reports resulting in complex distributed queries. When multiple remote objects are referenced in a single SQL, I have seen Oracle decide to copy the whole of one or more of these objects to the local temporary tablespace. This does not result in good performance. Remember also, that even query only operations via a database link create a transaction on the remains until a commit or rollback is issued.

I worked on another site where Materialised Views were used to incrementally replicate data from CRM and HR to EPM databases. Processes in the EPM database then referenced the Materialised Views. In this system, there was never any question of EPM processes referencing objects via the links. The data volumes were such that Application Messaging would never have coped. In this case only the Materialised View refresh process references the database links, and so the DBA can manage the usage of links. This is a system where (after upgrade to Oracle 10g) Streams could also have been used to replicate the data.

Conclusion

My view is that database links should not be used to directly support functionality in either on-line components or reporting. When this is done in reports and queries it presents the problem of tuning distributed queries, and having to decide whether local or remote database should drive the query. Instead data should be replicated to the local database, preferably by PeopleSoft messaging, but if necessary by Oracle replication. However, I think that it can be reasonable to use a database link in an occasional batch process that moves data between systems.

Aggregating & Purging Batch Timings

noreply@blogger.com (David Kurtz) — Sun, 11 Oct 2009 16:55:00 +0000

Application Engine can collect timing information for the programs being execution. These 'batch timings' can be written to log file and/or tables in the database. I always recommend that this is enabled in all environments. The runtime overhead is very low, and this data is extremely valuable to determine the performance of a system over a period of time, and to identify the pieces of SQL or PeopleCode code that account for the most time. The timing data collected for individual processes can be viewed directly within the Process Monitor component.

The Process Scheduler purge process does not delete batch timings, so this data remains in the database indefinitely, although it can no longer be accessed via the Process Monitor. Over time, on a busy system, a large volume of data can accumulate. In some ways this is a good thing. There are good reasons to purge the Process Scheduler as aggressively as the business will permit. The batch timings can still be analysed by direct SQL query. However, the sheer volume of data is likely to result in queries that can take quite a while to execute. After a while, you are less likely to be interested in the performance of individual processes, but are more likely to want to aggregate the data. So, it makes sense to hold the data at least partly aggregated.

I have produced a very simple Application Engine program (GFC_TIM_ARCH) to address this problem. This program is available for download from the Go-Faster website as a PeopleTools Application Designer Project. Please note that this process has been written using Oracle RDBMS specific SQL syntax.

Application Engine stores batch timing data in three table PS_BAT_TIMINGS_LOG, PS_BAT_TIMINGS_FN and PS_BAT_TIMINGS_DTL.
I have created three new tables PS_GFC_TIMINGS_LOG, PS_GFC_TIMINGS_FN and PS_GFC_TIMINGS_DTL.
GFC_TIM_ARCH aggregates the data in each of these tables by the day on which the process begin, by the process name, and (where applicable) by the 'detail_id' column. The aggregated data is put into the GFC_TIMINGS% tables, the original data is removed from the BAT_TIMINGS% tables.
It finds the earliest three days for which timing data exists that is older than the longest Process Scheduler retention limit. The idea is that the process should be run daily (the delivered 'Daily Purge' recurrence is suitable), but if it doesn't run for some reason it will catch up the next day.
Only one instance of WMS_TIM_ARCH is permitted to run concurrently.

On one system, where I tested this process, BAT_TIMINGS_DTL was growing by over 1 million rows per day. This became around 5000 rows per day in GFC_TIMINGS_DTL.

If you have been running with batch timings for a while, then when you first introduce this process you will probably have a large backlog of data to be aggregated and purged. The easiest option is to run this process repeatedly until the data has been processed (possibly using the recurrence that causes a program to run every minute). After the backlog has been cleared the BAT_TIMINGS% tables should be rebuilt or shrunk in order to release the space left in the tables by the deleted rows. This will help queries that scan the BAT_TIMINGS_DTL record, otherwise these scans still need to include the empty rows because they are below the tables High Water Mark.

Once the backlog has been cleared, the GFC_TIM_ARCH process can run daily and, not withstanding variations in the load on the system, the rolling volume of data retained in the BAT_TIMINGS% tables should be fairly constant, and it should not be necessary to rebuild them frequently. Space freed by the daily delete should be used by new rows that are inserted into the table as AE processes run.

Identifying Application Engine Source Code

noreply@blogger.com (David Kurtz) — Thu, 10 Sep 2009 18:40:00 +0000

One of the recurring challenges of performance tuning PeopleSoft systems is that having identified a problem SQL statement with the aid of a database utility, such as Oracle Enterprise Manager or session trace, you often need to identify the source so that you can implement a change.

Stored Outlines and SQL Profiles do not work well with PeopleSoft. Bind variables in Application Engines often become literals before the SQL statement is submitted to the database, unless the ReUse Statement feature has been enabled, which it is not by default.

I wrote about this in PeopleSoft for the Oracle DBA, it contains a script called stmtid.sql (listing 11-23, page 280) that adds an identifying comment to each of the stored statements used by PeopleSoft COBOL programs.

Now, I have produced a similar script for Application Engine programs. The script aeid.sql adds identification comments containing the name of object. It directly modifies the PeopleTools tables for Application Engine steps and PeopleSoft SQL objects. These comments will be seen in database tools and traces. So now when your database tools find a problem statement, it is easy to find the source.

Below, is part of a screen shot from Oracle Enterprise Manager. You can see the identifying comment in the SQL, which was added by this script

The comment identifies the name of the Application Engine program, section, step and step type. In this example, the SQL is from an Application Engine ID GPGB_EDI_PRC, in section called EDI_PRC, in a SQL type step called Step01.

For example, if you need adjust this SQL statement, perhaps add a hint to it, you don't have to spend time working out where it came from. It is not uncommon to find many similar SQL statements in a program. Also, where dynamic code is used to generate the SQL statement, it can be very tricky to find the exact source.

Using aeid.sql

You will need to edit the script each time, to specify which Application Engine programs and SQL objects are to be commented. The script is not perfect. It does not handle some very long statements, so you cannot simply instrument the entire system in one go.
The script is designed to run in SQL*Plus.
It produces a report of statements that it has altered.
The script does not commit. You should either commit or rollback the update depending upon whether you are satisfied with the output.
I suggest that the script only be run in pre-production environments. The comments will of course be migrated along with any other changes.

Automatic Segment Space -v- Freelist Management for PeopleSoft Temporary Records

noreply@blogger.com (David Kurtz) — Tue, 28 Jul 2009 05:39:00 +0000

Earlier this year, I wrote about some research into the effects of concurrent TRUNCATE TABLE operations in concurrent PeopleSoft Application Engine process. Since then I have been prompted to look at the effect of Automatic Segment Space -v- Freelist (or Manual) Management.

ASSM was introduced in Oracle 9i for Locally Managed Tablespaces, and from Oracle 10g it is default option, It was designed for high concurrency systems, and avoids some of the problems, such as contention on header blocks, that can occur with Freelist Management. It uses tracking blocks to indicate utilisation of data blocks. The ASSM structure is similar to a B-tree index (see Tanel Poder's presentation: Freelists -v- ASSM in Oracle 9i).

Normally, using ASSM is preferable in multi-user OLTP systems. The reduction in contention on the freelists should outweigh the additional I/O for the tracking blocks.

However, non-shared instances of PeopleSoft Temporary Records are only used by a single Application Engine process at any one time. Therefore, there would be no contention on freelists for these objects, and so DML operations on these objects will not derive much benefit from ASSM. In fact, the contrary could easily be true. The additional tracking blocks are also maintained during DML and are cleared out by local writes during a truncate operation. Truncate operations are serialised in Oracle on the RO enqueue. If you run multiple concurrent Application Engine programs you can get contention between the truncate operations. The additional I/O on the ASSM tracking blocks causes the truncate to take longer and in extreme cases can aggravate truncate contention.

The local write operations that occur during truncate operations cannot be deferred. Significant truncate contention can be a symptom of disk contention. However, switching back to Freelist Management saves the I/O to the tracking blocks, and so improves the performance of truncate.

I ran a test where I truncated a table with 5000 rows. I examined an Oracle trace with waits of the truncate. I tested it in an ASSM tablespace, and Freelist Managed tablespace. I tested truncating just the table on its own, and sometimes with an index.

Number of Wait EventsTable only, without any indexes
With a single primary key index
Tablespace Typedb file sequential readenq: RO - fast object reuselocal writesdb file sequential readenq: RO - fast object reuselocal writes
Automatic Segment Space Management8
1
4
16
2
9
Freelist Management3
1
1
6
2
3

Number of Wait Events	Table only, without any indexes	With a single primary key index
Tablespace Type	db file sequential read	enq: RO - fast object reuse	local writes	db file sequential read	enq: RO - fast object reuse	local writes
Automatic Segment Space Management	8	1	4	16	2	9
Freelist Management	3	1	1	6	2	3

You can see from this table that the truncates in the ASSM tablespace required more single block reads (db_file_sequential_read) and more local writes.

Recommendation

In addition to my previous recommendation to move the all (non-shared instances) of temporary working storage tables, and their indexes, to tablespaces with a larger block size, I also recommend that those tablespaces should be specified with MANUAL segment space management.

PeopleSoft and the Oracle Recycle Bin

noreply@blogger.com (David Kurtz) — Thu, 23 Jul 2009 18:41:00 +0000

If you are running PeopleSoft on Oracle 10g, what do you do about the Recycle Bin? It is a new feature in Oracle 10g, and it is enabled by default. So you are using it, unless you have taken a decision to the contrary.

It works just like the Windows recycle bin. You can drop a table and then flash it back (they didn't call it UNDROP because Oracle marketing now calls everything Flashback). So when you drop a table, Oracle marks it as dropped, and renames it with a system generated name beginning with BIN$. You can look at the contents of the Recycle Bin through a catalogue view.

>create table t (a number);
>drop table t;
>select * from user_recyclebin

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
TS_NAME                        CREATETIME          DROPTIME               DROPSCN
------------------------------ ------------------- ------------------- ----------
PARTITION_NAME                   CAN CAN    RELATED BASE_OBJECT PURGE_OBJECT      SPACE
-------------------------------- --- --- ---------- ----------- ------------ ----------
BIN$ZCLja8iAA9LgRAAOf+3h5A==$0 T                                DROP      TABLE
PSDEFAULT                      2009-03-02:13:35:12 2009-03-02:13:35:14 9.7561E+12
                          YES YES     776642      776642       776642          4

If you don't want a table to go into the recycle bin when you drop it, you can code DROP TABLE ... PURGE - but PeopleSoft doesn't do this.

PeopleSoft alter scripts usually drop and recreate tables. In a production system this is often the best option, otherwise you run the risk of causing rows to migrate to other blocks when you add new columns. In one system, I found 17000 objects in the recycle bin, occupying 1.3Gb.

My Opinion

Personally, I would disable the recycle bin by setting the initialisation parameter RECYCLEBIN = OFF in all PeopleSoft environments, with the possible exception of the development environment.

The RECYCLEBIN parameter can also be set dynamically at session or system level. You could perhaps turn it on prior to upgrade/data migration procedures, and then when satisfied turn it off again and manually purge the recycle bin.

I think Oracle features should be used knowingly. It doesn't matter whether you decide to use a feature or not. It is important that in making that decision you have thought about how to deal with the implications, rather than be surprised later.

Oracle Terminated Connection Timeout

noreply@blogger.com (David Kurtz) — Tue, 14 Jul 2009 09:18:00 +0000

I have recently come across situations on two different PeopleSoft sites where ad-hoc queries continue to run on the Oracle database server long after the Application Server process, which is the Oracle session client, has terminated. Often, queries perform poorly because they are poorly coded, but that is another story. To help guard against this situation Oracle has mechanism called Terminated Connection Timeout (also known as Dead Connection Detection (DCD) when it was introduced in Net8).

Oracle Support Note 615782.1 explains the mechanism. "DCD is initiated on the server when a connection is established. At this time SQL*Net reads the SQL*Net parameter files and sets a timer to generate an alarm. The timer interval is set by providing a non-zero value in minutes for the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file on the database server side. When the timer expires, SQL*Net on the server sends a 'probe' packet, essentially an empty SQL*Net packet, to the client. If the client end of the connection is still active, the probe is discarded, and the timer mechanism is reset. If the client has terminated abnormally, the server will receive an error from the send call issued for the probe, and SQL*Net on the server will signal the operating system to release the connection's resources."

Thus, if a PeopleSoft operator initiates an ad-hoc query that runs on the PSQRYSRV server for longer than the ICQuery service time-out (default 1200 seconds), then Tuxedo will terminate and restart the busy server process. However, the query will continue run on the database server until the current fetch operation returns. In the case of a query performing a large sort or hash operation, it might be a long time before the first row is returned. All the while, the query is continuing to consume resources on the database.

PeopleTools 8.44, also introduced the ability to kill a query via the Query Monitor that had reached a maximum run time. This is one of the functions of the PSMONITORSRV server process (see Oracle Support Note 624339.1. The maximum run time is specified in a permission list (see Security Administration PeopleBook Permission List Query Profile) and then the ability to kill queries that have timed out can be enabled or disabled system-wide (see PeopleSoft Query PeopleBook Query Administration. It will kill the application server process that submitted the query, but for the same reasons, the query may continue to run on the database.

Thus, setting Terminated Connection Timeout is not merely a good idea for a PeopleSoft system running on Oracle, it is effectively mandatory. Otherwise. some PeopleSoft functionality simply won't work as intended.

What is an appropriate value for SQLNET.EXPIRE_TIME?

The value for this parameter is the time between successive SQL*Net probes sent by the Oracle shadow server process to the client. Setting it is a balance between the maximum time that a query can be left to consume resources after a client process terminates, against the additional overhead of every client process sending a probe every few minutes.

The SQL*Net documents often talk about additional network traffic generated by DCD. This was a consideration in the past on client-server applications that ran across a wide area network. However, it is rarely a consideration in relatively modern systems such as PeopleTools 8, the database connections are made by Application Server and Process Scheduler, which are usually physically close to the database server.

The time-out can be set independently of any of the other time-outs for the Application Server and Web Server. Documents on Metalink often suggest 5 or 10 minutes, and I don't think that is unreasonable.

My thanks to Colin Kilpatrick who prompted me to look at this again.

Dynamically Switching PeopleSoft Temporary Records between Global Temporary and Normal Tables during Application Engine Programs

noreply@blogger.com (David Kurtz) — Mon, 06 Jul 2009 21:48:00 +0000

I am working on a Time & Labour system. We run the main T&L process in different modes. There is a company wide overnight batch process, but individual parts of the company can run the process for their section during the day to process exceptions and to generate schedules. This is done with a custom AE program that calls the delivered TL_TIMEADMIN.

Running on Oracle 10gR2, we have faced performance problems caused by contention between concurrent truncate operations in these processes (see Factors Affecting Performance of Concurrent Truncate of Working Storage Tables).

One way to evade this problem is to convert the working storage tables to be Global Temporary Tables (GTTs). The contention problem caused by serialisation of truncate operations does not occur with GTTs. Last year I wrote a blog entry (Global Temporary Tables and PeopleSoft Temporary Records) that discussed when it was possible to use GTTs in Application Engine (AE) programs. Essentially, you can only use GTTs when restart is disabled for all AE programs that reference a temporary records.

So we could consider running the small but frequent daytime processes with restart disabled. Then we can make the non-shared instances of the temporary records into Global Temporary tables for the non-restartable daytime processes. However, we want to continue to run the overnight processes with restart enabled so that we have the option to restart a process that fails overnight from where it crashed, rather than going back to the beginning. The same non-shared instances may need to be Global Temporary during the day but normal recoverable tables by night.

Previously, I have only converted tables to Global Temporary where they are not referenced by a restartable AE program. I have now devised a way of switching a table to being a GTT if it is allocated to a process for which restart is disabled, and switching it back to a normal table if not. This is the best of both worlds.

When a non-shared instance of a temporary record is allocated to a particular process instance, a row is inserted into PS_AETEMPTBLMGR. The value of the restart disable flag is also stored in PS_AETEMPTBLMGR. I have created a trigger that switches a non-shared instance of a temporary record from a normal table to a GTT if restart is disabled, or switches it back to a normal table if restart is enabled. The trigger will create the GTT and any indexes on it if necessary, and will rename the tables as necessary so that the correct version has the default name expected by the process.

So if, for example, I have instance number 11 of a record called WRK_SHIFT_TAO, then the table will be called PS_WRK_SHIFT_TAO11. If that is allocated to a non-restartable AE program, the trigger will check for a table called GT_WRK_SHIFT_TAO11. If it doesn't exist the trigger will create it, dynamically generating the DDL with the dbms_metadata package. Thus, the structure of the GTT will be identical to PS_WRK_SHIFT_TAO11, the leading PS will also be changed to GT. The same indexes will also be created. The original normal table PS_WRK_SHIFT_TAO11 will be renamed to XX_WRK_SHIFT_TAO11, and then the GTT, at that point called GT_WRK_SHIFT_TAO11 will be renamed to PS_WRK_SHIFT_TAO11. The indexes are not renamed. If later the same instance is allocated to a restartable process, the change will be reversed by renaming again. The GTT will not be dropped so that it does not need to be recreated again the next time the non-restartable program uses it.

All DDL generated and executed by the trigger is written to the Message Log for that process (click on the screen shot to enlarge).

The shared instance of a record (the one without a instance number suffix) is never rebuilt as a Global Temporary table because it is possible that a restartable and non-restartable process might both use the shared instance at the same time.

One complication is how to handle changes to the temporary records. Application Designer will only create normal tables. So, if the table is to be rebuilt, them it needs to be switched back to a normal table, and the corresponding GTT created by the trigger should then be dropped. The Application Designer can recreate the table in the usual way. Next time the non-restartable AE runs, it will recreate the GTT with the new structure.

An updated version of T_LOCK is available which handles PeopleSoft temporary records and prevent DDL on PS_ tables with corresponding GT_ tables. The commands which swap these tables back and forth are explicitly permitted by the new version of this trigger.