Wednesday, March 31, 2010

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.

Thursday, March 25, 2010

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.

Thursday, March 04, 2010

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 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