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.
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:
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.
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;
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.
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.