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.
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.
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.
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.
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.
After running an identify-and-calc and a cancel, we can see how many of the outlines are actually used.
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.
I can then remove the unused outlines.
Used flags on the outlines can be reset, so we later we can see the outlines being used again.
If I want to go back running without outlines, I just disable the trigger
To re-enable outlines, just re-enable the trigger.
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.