Oracle Plan Stability (Stored Outlines) in PeopleSoft Global Payroll

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

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

One of the clever things you can do in Application Engine is dynamically generate parts of a SQL statement.  However, this can produce challenges that require you to add an optimiser hint to a gSQL statement.  In this post, I want to demonstrate how you can 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 used 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 were being absolutely rigorous, I would have added some procedural code in the 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 correctly 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

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

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.