The PeopleSoft DBA Blog

PeopleTools 8.52 Application Engine sets MODULE and ACTION

noreply@blogger.com (David Kurtz) — Fri, 23 Nov 2012 16:46:00 +0000

I have written and spoken often about the huge importance of instrumentation in an application. PeopleSoft introduced internal instrumentation to PeopleTools 8.44 for its own Performance Monitor that works on any platform. Since PeopleTools 7.53, when running on an Oracle database, PeopleTools also calls the Oracle supplied package dbms_application_info package to set the module and action for the session. The values set were not particularly useful, so I wrote a PL/SQL package (psftapi) and trigger to set the module and action attributes when a process started.

In PeopleTools 8.50 this instrumentation was enhanced to set module and action to the component and page name in the PIA. These values appear in Oracle Enterprise Manager and Active Session History (ASH). They can be used to profile components.

Now, in PeopleTools 8.52 Application Engine has been enhanced to set the action to the full Application Engine step name.

To illustrate what happens, I have produced an Oracle Extended SQL Trace for the process, but I have shown only the lines in a SQL Trace file with 3 asterisks. So you can see MODULE and ACTION being set (and some other timestamp information). The lines in italic were emitted when module and action were set by my psftapi package, in which I simply set the MODULE to the Application Engine program name.

*** 2012-11-22 21:47:38.282
*** SESSION ID:(7.2237) 2012-11-22 21:47:38.282
*** CLIENT ID:(PS) 2012-11-22 21:47:38.282
*** SERVICE NAME:(HCM91) 2012-11-22 21:47:38.282
*** MODULE NAME:(PSPMCSOSUM) 2012-11-22 21:47:38.282
*** ACTION NAME:(PI=867:Processing) 2012-11-22 21:47:38.282
*** MODULE NAME:(PSAE.PSPMCSOSUM.1448) 2012-11-22 21:47:38.419
*** ACTION NAME:(PSPMCSOSUM) 2012-11-22 21:47:38.419
*** ACTION NAME:(PSPMCSOSUM.MAIN.STATS.S) 2012-11-22 21:47:38.420
*** 2012-11-22 21:47:38.588
*** ACTION NAME:(PSPMCSOSUM.MAIN.CTL.P) 2012-11-22 21:47:38.919
*** ACTION NAME:(PSPMCSOSUM.MAIN.SELECT.C) 2012-11-22 21:47:38.937
*** ACTION NAME:(PSPMCSOSUM.GETCNT.CNT.P) 2012-11-22 21:47:38.963
…
*** ACTION NAME:(PSPMCSOSUM.MAIN.SELECT.C) 2012-11-22 21:48:15.168
*** ACTION NAME:(PSPMCSOSUM.GETCNT.CNT.P) 2012-11-22 21:48:15.168
*** ACTION NAME:(PSPMCSOSUM.MAIN.Close.P) 2012-11-22 21:48:15.197
*** ACTION NAME:(PI=867:Success) 2012-11-22 21:48:15.201

Application Engine also sets the MODULE attribute. In the above example it was set to PSAE.PSPMCSOSUM.1448. The string is composed of three parts.

PSAE indicates that it was running Application Engine. It sets the same value for both stand-alone and Tuxedo server Application Engine processes.
PSPMCSOSUM was the name of the Application Engine Program
1448 was the operating system process ID of the Application Engine process. This value is recorded in the column SESSIONIDNUM on the table PSPRCSQUE.

The inclusion of the SESSIONIDNUM is useful because it is possible to determine the process instance. If you have multiple instances of the same Application Engine program running concurrently, you can work out which ASH samples correspond to which process instance by matching the session ID.

SELECT  r.prcsinstance, h.module, h.action, sum(1) ash_secs
FROM v$active_Session_history h
, psprcsque q
, psprcsrqst r
WHERE r.prcsinstance = q.prcsinstance
AND h.module = 'PSAE.'||q.prcsname||'.'||q.sessionidnum
AND h.sample_time BETWEEN r.begindttm AND NVL(r.enddttm,SYSDATE)
and r.prcsinstance = 867
GROUP BY r.prcsinstance, h.module, h.action
ORDER BY ash_secs DESC
/

PRCSINSTANCE MODULE               ACTION                    ASH_SECS
------------ -------------------- ------------------------- --------
         867 PSAE.PSPMCSOSUM.1448 PSPMCSOSUM.GETCNT.CNT.P          7
         867 PSAE.PSPMCSOSUM.1448 PSPMCSOSUM.MAIN.STATS.S          1

Unfortunately, this change to MODULE also means that it is no longer possible to enable trace by setting a watchpoint (as I blogged previously) now does not work with Application Engine because it is not possible to predict the value of the SESSIONIDNUM in MODULE!

I am testing with 8.52.12 and have found a significant problem. Application Engine doesn't set the action on DO SELECT statements. The first statement in the trace extract below does come from PSPMCSOSUM.MAIN.CTL PeopleCode, however, the second SQL statement actually comes from PSPMCSOSUM.MAIN.SELECT.D, but the absence of another action line would lead you to believe the statement came from the preceding PeopleCode step.

*** ACTION NAME:(PSPMCSOSUM.MAIN.CTL.P) 2012-11-22 21:47:38.919
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-40016373 tim=257220814597
WAIT #0: nam='SQL*Net message from client' ela= 740 driver id=1413697536 #bytes=1 p3=0 obj#=-40016373 tim=257220815371
CLOSE #348647416:c=0,e=33,dep=0,type=1,tim=257220815447
=====================
PARSING IN CURSOR #348528704 len=98 dep=0 uid=45 oct=3 lid=45 tim=257220815496 hv=1424819941 ad='7ff2559e550' sqlid='98af7ppafu1r5'
END OF STMT
PARSE #348528704:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1148792852,tim=257220815496
EXEC #348528704:c=0,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1148792852,tim=257220815572
WAIT #348528704: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-40016373 tim=257220815612
FETCH #348528704:c=0,e=2824,p=0,cr=16,cu=0,mis=0,r=1,dep=0,og=1,plh=1148792852,tim=257220818462
STAT #348528704 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=16 pr=0 pw=0 time=2828 us)'
STAT #348528704 id=2 cnt=3075 pid=1 pos=1 obj=228740 op='INDEX FULL SCAN PS_PSPMCSO_CHART (cr=16 pr=0 pw=0 time=333 us cost=0 size=13 card=1)'
WAIT #348528704: nam='SQL*Net message from client' ela= 123 driver id=1413697536 #bytes=1 p3=0 obj#=-40016373 tim=257220818671
FETCH #348528704:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=1148792852,tim=257220818696
WAIT #348528704: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-40016373 tim=257220818714
WAIT #348528704: nam='SQL*Net message from client' ela= 702 driver id=1413697536 #bytes=1 p3=0 obj#=-40016373 tim=257220819434
CLOSE #349389936:c=0,e=16,dep=0,type=1,tim=257220819496
=====================
PARSING IN CURSOR #349702624 len=555 dep=0 uid=45 oct=3 lid=45 tim=257220819548 hv=1888777338 ad='7ff2559ce90' sqlid='5phgqq9s98x3u'
SELECT DISTINCT OPRID , TO_CHAR(CAST((LOGINDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF') , TO_CHAR(CAST((LOGOUTDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF') , PM_SIGNON_TYPE , PM_SIGNOFF_TYPE FROM PSPMCSOLOG_VW WHERE '1' <> '2' AND TO_DATE(TO_CHAR(LOGINDTTM,'YYYY-MM-DD'),'YYYY-MM-DD') >= TO_DATE('2005-07-06','YYYY-MM-DD') AND TO_DATE(TO_CHAR(LOGOUTDTTM,'YYYY-MM-DD'),'YYYY-MM-DD') >= TO_DATE('2005-07-06','YYYY-MM-DD') AND OPRID NOT IN ( SELECT OPRID FROM PSPMCSOCHRTXOP) AND LOGIPADDRESS NOT IN ( SELECT LOGIPADDRESS FROM PSPMCSOCHRTXIP) ORDER BY 2
END OF STMT
PARSE #349702624:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4226533831,tim=257220819547
EXEC #349702624:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4226533831,tim=257220819669
WAIT #349702624: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-40016373 tim=257220819723
WAIT #349702624: nam='SQL*Net more data to client' ela= 76 driver id=1413697536 #bytes=8145 p3=0 obj#=-40016373 tim=257220831271
FETCH #349702624:c=15600,e=11621,p=0,cr=214,cu=0,mis=0,r=201,dep=0,og=1,plh=4226533831,tim=257220831375
WAIT #349702624: nam='SQL*Net message from client' ela= 988 driver id=1413697536 #bytes=1 p3=0 obj#=-40016373 tim=257220832412
WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-40016373 tim=257220832481
WAIT #0: nam='SQL*Net message from client' ela= 40 driver id=1413697536 #bytes=1 p3=0 obj#=-40016373 tim=257220832541
*** ACTION NAME:(PSPMCSOSUM.MAIN.SELECT.C) 2012-11-22 21:47:38.937

However, MAIN.SELECT.D duly appears in the AE batch timings report.



                               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    
------------------------------ ------- -------- ------- -------- ------- -------- --------
…
AE Program: PSPMCSOSUM

MAIN.CTL.D                           1      0.0       1      0.0       2      0.0      0.0
MAIN.SELECT.D                        1      0.0       1      0.0     556      0.0      0.0
MAIN.STATS.S                         1      0.0       1      0.5       0      0.0      0.5
…

This is a serious problem If you profile the top SQL statements in an Application Engine (using either ASH as I have done above, or by profiling a trace file and looking for the top SQL statement by searching through the raw trace file) it will lead you to the wrong conclusion! Time spent in PSPMCSOSUM.MAIN.SELECT.D will be accounted as having been spent in PSPMCSOSUM.GETCNT.CNT.P. It is not until you look for the specific SQL statement in Application Designer that you realise that the source code doesn't match the instrumentation.

Nevertheless, this instrumentation is a very welcome and significant improvement for Application Engine. It will help to localise performance problems. However, until the ommisions are fixed, you will need to be aware of their implications and avoid falling into the traps.

My thanks to Wolfgang Breitling for telling me about the feature.

Using Two Temporary Tablespaces in PeopleSoft

noreply@blogger.com (David Kurtz) — Fri, 16 Nov 2012 11:50:00 +0000

A longer version of this posting, with all necessary code and a demonstration test, is available as a technical note on the Go-Faster website. I am working with two different PeopleSoft customers who have had challenges with the size of the temporary tablespaces. Critical batch processes have failed because they have exhausted space in the temporary tablespace.

ORA-01652 unable to extend temporary segment...

In one case, the one and only temporary tablespace in a Payroll system has over time been extended to in excess of 360 GB. This has happen in response to PeopleSoft processes that failed because they cannot allocate temporary tablespace because somebody else has consumed it. This treated the symptom rather than the cause. This system has a number of other Oracle database users who are have read-only access to the PeopleSoft data to perform adhoc queries. These users all share the one temporary tablespace. Occasionally, a query will be submitted that runs for many hours, writing many gigabytes of data to the temporary tablespace, when it would have been better to terminate the process
Another system has 64 GB in the PSTEMP temporary tablespace used by SYSADM. All other users already use another temporary tablespace, but PeopleSoft processes sometimes still fail because most of the temporary tablespace has been consumed by an adhoc PS/Query process, and there is nothing left for other processes. This system also has other Oracle database users with read-only access, but here they use the default TEMP temporary tablespace.

If a PeopleSoft system has database users executing adhoc queries, then allocating those users to separate temporary tablespace is a sensible first step.

However, in this document I suggest going further. I propose switching some PeopleSoft processes to use a different temporary tablespace. Regular PeopleSoft processing will continue to use the first temporary tablespace, but adhoc queries will use the second temporary tablespace. Thus, the first temporary tablespace can be sized to cater for normal processes safe in the knowledge that it won’t be consumed by adhoc queries and you won’t get failures due to space errors. Meanwhile, the second tablespace can be limited to a reasonable size and queries that make unreasonable demands on the temporary tablespace will error.

I am going to exploit two features:

When a session sets CURRENT_SCHEMA to another schema it uses the temporary tablespace assigned to that schema rather than its own.
From PeopleTools 8.51 you can direct read-only activity via a second connection to a standby database. If you are using Oracle Active Data Guard you must configure something that looks like a second PeopleSoft database in another schema, but which actually is composed of database synonyms that point to the first schema.

To make PeopleSoft process use an alternative temporary tablespace:

Create a second schema (I'll call it SYSADMRP) in the same database, and specify a different temporary tablespace for the user.
Create synonyms in the second schema for every table and view in the PeopleSoft (SYSADM) schema.

If you are not using Active DataGuard or are below PeopleTools 8.51 then you can set CURRENT_SCHEMA with two triggers.

Create an AFTER LOGON trigger that will set CURRENT_SCHEMA to SYSADM for PSQRYSRV query server processes only.


CREATE OR REPLACE TRIGGER sysadm.psqrysrv
AFTER LOGON
ON sysadm.schema
DECLARE
  l_module VARCHAR2(64);
BEGIN
  SELECT sys_context('USERENV', 'MODULE') 
  INTO   l_module
  FROM   dual;

  IF UPPER(l_module) LIKE 'PSQRYSRV%' THEN --then this is a PSQRYSRV session
    EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=SYSADMRP';
  END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
/

Create another trigger to set current schema for the application engine process that runs scheduled queries (you might want to add others to this list).


CREATE OR REPLACE TRIGGER sysadm.set_current_schema
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
WHEN (  (new.runstatus = '7' OR old.runstatus != '7') --if starting or terminating
     AND new.prcsname IN('PSQUERY')) --restrict to certain programs
DECLARE
BEGIN
  IF :new.runstatus = '7' THEN --if starting set alternative schema
    EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=SYSADMRP';
  ELSE --when process terminates reset to standard schema in case this is a PSAESRV process
    EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=SYSADM';
  END IF;
END;
/

If you are using at least PeopleTools 8.51, you don't need these triggers. Instead you can configure the standby connection but to the same database

PS/Queries will always use the standby connection and hence the alternative tablespace.
Batch processes can be marked as read only to make them connect to the standby connection. Remember that you must use PSAESRV processes.
If you want to make specific PeopleSoft components use the alternative temporary tablespace then you can only do this by perverting the PeopleTools Active Data Guard support and marking components as read only.

Conlusion

Now, you can choose an appropriate size for the one temporary tablespaces that will be sufficient for the regular operation of the application. Adhoc queries will use alternative temporary tablespace. You might choose to set a temporary tablespace size that may still cause queries to fail with a temproray tablespace error, but at least that won’t cause business-as-usual processes to crash.

You could even choose to run with three temporary tablespaces, one for PeopleSoft processes, one for PeopleSoft queries, and one for adhoc users accessing the database directly.

Maintaining Optimizer Statistics on PeopleSoft on Oracle 11g

noreply@blogger.com (David Kurtz) — Sun, 30 Sep 2012 18:30:00 +0000

I have been considering how to collect optimizer statistics for a PeopleSoft system running on an Oracle 11g database. Despite 11g being several years old, most of my current customers are still using 10g, though some are looking at the upgrade to 11g. I believe a slightly different approach is required.

In 2009, I wrote a series of blog postings on the subject of collecting statistics. However these were all based on Oracle 10g. I proposed a PL/SQL package that would use meta-data in a database table to determine how to collect statistics on a table, or deliberate supress collection of statistics.
I also recommended that statistics on tables created for use as temporary records in Application Engine programs should have their statistics deleted and locked to prevent system-wide jobs refreshing their statistics. I proposed a package that collected statistics according to meta-data defined in a table.

IN 2011, Oracle published document 1322888.1 “pscbo_stats - Improving Statistics in Oracle RDBMS for PeopleSoft Enterprise”. It takes a similar approach to my 10g package. A PL/SQL package is used to collect statistics. A number of tables control whether, when and how statistics are collected on each record. Oracle's package is also intended to be used to collect schema-wide statistics.

The psbo_stats package is a attempt to solve a genuine problem, and it has continued to evolve since its initial release. However, I have a number of objections to it.

You are expected to replace the default automatic jobs that collect statistics with pscbo_stats, so it is a move away from standard default maintenance procedures.
pscbo_stats is still fundamentally a 10g solution. It does not use 11g table preferences.
It does use the Oracle automatic sample size in 11g if histograms are not to be collected. Otherwise, it defaults to the previous behaviour of either using 100% sample size for when called by %UpdateStats with the ‘high’ sample size, or a variety of fixed sample sizes based on internal rules and the number of rows in the table.
The package contains a procedure that collects statistics on all objects in the schema that also refreshes statistics that are not stale but which have not been refreshed for a period of time determined by the size of the table.
When it collects histograms it always sets the maximum bucket size of 254. This may not always be desirable for height balanced histograms.
There is no support for collecting aggregated or incremental statistics on partitioned objects.

I think that the 11g table preferences offer better control over collection of statistics.

Oracle considerably enhanced the delivered DBMS_STATS package in 11g. It became possible to specify default values for parameters in the dbms_stats.gather_table_stats program for for each table.

The guiding principle in 11g, and one that is not specific to PeopleSoft, is that instead of calling dbms_stats.gather_table_stats with the desired parameters, we should set table preferences with the desired parameters and then just call dbms_stats without table specific parameters. We can then just leave the default database and schema-wide procedures get on with the job of collecting statistics.

A document that describes the updated approach and the various scripts are available on the Go-Faste website at http://www.go-faster.co.uk/docs.htm#Managing.Statistics.11g.

CAVEAT: This document represents some experimental work that is in progress. It has not been tested against a customer system, let alone been used in production. I would welcome any feedback, and the opportunity to work with someone on a PeopleSoft system on Oracle 11g.

Enabling Oracle Extended SQL Trace by Module and Action

noreply@blogger.com (David Kurtz) — Thu, 20 Sep 2012 17:40:00 +0000

I have written previously about the value of assigning meaningful values to the module and action attributes on a database session (see Using Oracle Enterprise Manager (Grid Control) with PeopleSoft). Oracle added instrumentation to PeopleTools 8.50 (see PeopleTools 8.50 uses DBMS_APPLICATION_INFO to Identify Database Sessions) that sets module and action for on-line and batch sessions. However, I still use my own trigger to set these attributes for processes initiated by the Process Scheduler.

I originally became interested in module and action because it made it possible to analyse performance problems in specific processes with Active Session History (see Practical Use of ASH). However, since Oracle 10g there is also a very easy way to enable Oracle's extended session trace in the sessions relating to specific processes.

In the Oracle supplied PL/SQL package DBMS_MONITOR, there are two programs to specify combinations of module and action for which SQL trace will be enabled. It is rather like setting a watchpoint in a debugger. SERV_MOD_ACT_TRACE_ENABLE is used to create a watchpoint, and SERV_MOD_ACT_TRACE_DISABLE removes it. Trace is then enabled in a session when the module and action is set to a value that matches a watchpoint, and disabled when the module and/or action is changed to a value for which there is no watchpoint.

This screenshot from OEM shows that a query is being run within a PeopleSoft component RECV_PO, page PO_PICK_ORDERS.

It would be a simple matter to have Oracle trace the session for that component by setting a watchpoint.

BEGIN
 sys.dbms_monitor.serv_mod_act_trace_enable
 (service_name=>'P1PPP'
 ,module_name =>'RECV_PO'
 ,waits=>TRUE
 ,binds=>TRUE);
END;
/

Note that the service name must be specified as part of the watchpoint. This will usually be the same as the PeopleSoft database name. Thus, watchpoints copied to another database by cloning won't be effective because the service name will be different.

I could also have specified an action in the example above, but I want to trace all pages in the component and the search dialogue.

As with the other programs in dbms_monitor, information on wait events are included in traces by default. In the above example, I have also requested information on bind variables to be included in the trace.

When setting a SQL trace for an on-line component it is possible that many different users could trigger tracing. Trace will be enabled and disabled for each user. The result is that you will get a trace file for each application server process, and each trace file might contain the activity for more than one user if the application server process handled more than one service request.

Watchpoints can be removed in a similar way.

BEGIN
 sys.dbms_monitor.serv_mod_act_trace_disable
 (service_name=>'P1PPP'
 ,module_name =>'RECV_PO'
 ,action_name =>'PO_PICK_ORDERS');
END;
/

Watchpoints are held the table SYS.WRI$_TRACING_ENABLED.

SELECT * FROM sys.wri$_tracing_enabled
/

TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2   INSTANCE_NAME FLAGS
---------- ---------- ------------- --------------- ------------- -----
         5 P1PPP      RECV_PO       PO_PICK_ORDERS                   12

This technique is also effective for processes initiated by the Process Scheduler. The default behaviour from PeopleTools 8.50 is to set Action to the process name, my trigger sets Module to the process name.

If I specify only the Module in SERV_MOD_ACT_TRACE_ENABLE, then it trace will be enabled for that module and for any action.

BEGIN
 sys.dbms_monitor.serv_mod_act_trace_disable
 (service_name=>'AAAAAAA'
 ,module_name =>'GPPDPRUN');
END;
/

I choose to set the action to the Process Instance number because I find it useful to relate ASH data to a specific batch process. The above screenshot from OEM shows a streamed Global Payroll calculation running with many concurrent GPPDPRUN processes. The watchpoint would cause each and every processes to trace.

However, it has been suggested that if the Action was set to the process run control ID then the watchpoint could be set to the specific run control value used by a user or scheduled job. It would be a simple matter of changing the trigger (I'll leave that as an excercise). Then, a user could be told to use a specific value for run control ID that would then invoke trace.

BEGIN
 sys.dbms_monitor.serv_mod_act_trace_disable
 (service_name=>'AAAAAAA'
 ,action_name =>'TRACEME');
END;
/

Update 24.11.2012 This technique will not work for Application Engine from PeopleTools 8.52 because it now sets MODULE to a value that includes a session number and so cannot be predicted.

Gathering Aggregated Cost-Based Optimiser Statistics on Partitioned Objects

noreply@blogger.com (David Kurtz) — Thu, 19 Jul 2012 18:53:00 +0000

Recently, I have been looking into how to gather cost-based optimizer statistics on composite partitioned objects.

Database partitioning is not used by default in PeopleSoft because it is database specific. Not all databases support partitioning, and where they do it is done in a platform specific manner. Application Designer has no capability to create partitioned objects (although from PeopleTools 8.51 it will preserve partitioning in tables and index that are already partitioned in an Oracle database). It is therefore no surprise that it is not used widely by PeopleSoft customers. However, partitioning is essential in Global Payroll systems that use 'streamed' processing (many concurrent payroll processes). By definition, these are the payroll systems that have large volumes of data where collecting statistics on the results tables can quickly become a challenge. Partitioning can also have application in General Ledger reporting in Financials, particularly where nVision is in use.

It is important that statistics on partitioned tables are both up to date and accurate. However, collecting global statistics on a large partitioned object can be a time-consuming and resource intensive business as Oracle samples all the physical partitions or sub-partitions. Briefly, if you do not collect global statistics on a partitioned table, Oracle will aggegrate the statistics on the physical partitons or sub-partitions to calculate statistics on the logical table and partition segments.

Oracle 10g makes a number of mistakes in its calculation of these aggregated statistics. In particular the number of distinct values on columns by which the table is partitioned have impossibly low values. This is can affect cardinality calculations and so lead the optimizer to choose the wrong execution plan.

I have now published the second of two documents on my website that examine aspects of statistics on partitioned, and in particular composite partitioned tables. The first document examines the problems in 10g, and proposes a procedure to 'correct' the aggregated statistics to at least minimum possible values.

Gathering Aggregated Cost-Based Optimiser Statistics on Partitioned Objects in Oracle 10g

The second document looks at the same issue in 11g and shows that while most of the issues are fixed, one problem remains.

Gathering Aggregated Cost-Based Optimiser Statistics on Partitioned Objects in Oracle 11gR2

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 120
COLUMN run_cntl_id FORMAT a8 heading 'Run|Control'
COLUMN last_run_cntl_id FORMAT a8 heading 'Last|Run|Control'
COLUMN next_run_cntl_id FORMAT a8 heading 'Next|Run|Control'
COLUMN prcsinstance FORMAT 99999 HEADING 'PRCS|INSTANCE'
COLUMN offset_amount FORMAT 9999 HEADING 'Offset|Amount'
COLUMN rownum FORMAT 99 HEADING 'Row|#'
COLUMN prcstype FORMAT a18
COLUMN prcsname FORMAT a12
COLUMN prcsprty FORMAT 9 HEADING 'PRCS|PRTY'
COLUMN prcscategory FORMAT a11 HEADING 'PRCS|CATEGORY'
COLUMN serverassign FORMAT a6 HEADING 'SERVER|ASSIGN'
COLUMN runstatus FORMAT a6 HEADING 'RUN|STATUS' 
 
SELECT run_cntl_id, prcsname, offset_amount, duration, rundttm 
FROM ps_gfc_sleep_rc
/

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

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

I can query what actually happened with the following SQL.

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

In all of the examples below, the first three low priority 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, &Languaged, &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.