Using Global Temporary Tables in Application Engine

One of the new PeopleTools 8.54 features that went probably a bit unnoticed amidst the excitement on the new Fluid interface is the ability of Application Engine programs to take advantage of Global Temporary Tables (GTTs) when using an Oracle Database.

What are GTTs?

The Global Temporary Tables were introduced by Oracle already on the 8i version of its database product. These tables are session specific, meaning that the data inserted in them only lasts until the session is closed (in Oracle Database there is the possibility of using them only until the next commit, but that option is not used by PeopleSoft). The data inserted in the table by each session is not seen by other sessions. In other words, it is a very similar behavior to Application Engine Temporary Tables. The benefit of using a database supported solution rather the traditional temporary tables is better performance, since GTTs are optimized for temporary data.

How is it implemented in PeopleTools?

The implementation in PeopleTools is quite simple. When selecting Temporary Table as the record type, a new option is enabled: "Global Temporary Table (GTT)".

The build SQL generated by PeopleTools is slightly different to traditional tables:

CREATE GLOBAL TEMPORARY TABLE PS_BN_JOB_WRK (PROCESS_INSTANCE

 DECIMAL(10) NOT NULL,

   EMPLID VARCHAR2(11) NOT NULL,

   EMPL_RCD SMALLINT NOT NULL,

   EFFDT DATE,

   EFFSEQ SMALLINT NOT NULL,

   EMPL_STATUS VARCHAR2(1) NOT NULL) ON COMMIT PRESERVE ROWS

 TABLESPACE BNAPP

/

Note: The SQL Build process still creates as many instances of the table as it did with traditional temporary tables. This sounds like a bug to me, as my guess is that the whole idea of using GTTs is to be able to share a table without actually sharing the data, but I may be wrong. In any case, it does not do any harm. Any insight on this? 

Constraints

Due to specific characteristics of GTTs, there are some limitations regarding when they can be used:

• If the Application Engine is run in online mode, then the GTTs cannot be shared between different programs on the same run.
• You cannot use Restart Enabled with GTTs as the data is deleted when the session ends. In its current version, PeopleBooks state otherwise, but I think it is a typo.
• %UpdateStats are not supported. Before Oracle Database 12c, if the statistics would be shared among all the sessions. Oracle Database 12c also supports session specific statistics, which would be the desired behavior in PeopleSoft (from a higher level point of view, programmers are expecting the temporary table to be dedicated to the instance). I guess the %UpdateStats is not supported because Oracle Database 11g is still supported by PeopleTools 8.54 and in that case running statistics would generate unexpected results. Still, the DBA can run statistics outside of the Application Engine program.

Note: As learnt from Oracle OpenWorld 2014, Oracle is evaluating supporting of Oracle Database 12c session specific statistics for GTT’s in a future releases of PeopleTools.

Conclusion

If you are moving to PeopleTools 8.54 and you want to improve the performance of a given Application Engine program, the GTTs may bring good value to your implementation. Please remember that you need to be using an Oracle Database.