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.

Database Feature Overview

Global Temporary tables were introduced in Oracle 8i.  They can be used where an application temporarily needs a working storage tables.  They are named

• Global because the content is private
• Temporary because the definition is permanent

Or if you prefer

• Global because the definition is available to everyone
• Temporary because 
• physical instantiation of the table is temporary, in the temporary segment (so it isn't redo logged and so isn't recoverable),
• but it does generate undo in the undo segment, and there is redo on the undo.
• Each session gets its own private copy of the table in the temp segment.  So you cannot see what is in another session's temporary table, which can make application debugging difficult.
• The physical instantiation of the table is removed either 
• when the session disconnects - on commit preserve
• or when the transaction is terminated with a commit or rollback - on commit delete

This is a very useful database feature (I have been using it in PeopleSoft application ever since it was introduced). 

• Can be used for temporary records in Application Engines where restart is disabled.
• Can be implemented without any application code change.
• Only Application Designer temporary records can be built as global temporary tables.  You cannot make a SQL Table record global temporary.
• The reduction in redo generation during intensive batch processes, such as payroll processing, can bring significant performance benefits.  There is no point logging redo information for temporary working storage tables that you do not ever need to restore.
• Shared temporary tables, such as in the GP calculation process GPPDPRUN that is written in COBOL.  If using payroll streaming (multiple concurrent processes to process in parallel), then concurrent delete/update can cause read consistency problems when using a normal table, but with global temporary tables, each session has its own physical table so there is never any need to read consistency recover to read a global temporary tables.
• Global temporary tables are also an effective way to resolve table high water mark issues that can occur on non-shared temporary tables in on-line application engine.  The PeopleTools %TruncateTable macro still resolves to delete.  You never get high water mark problems with global temporary tables because they are physically created afresh for each new session.  
• There is often a reduction in database size because the tables are not retained after the session terminates.  Although there will be an increased demand for temporary tablespace while the global temporary tables are in use.
• I have occasionally seen performance problems when PeopleSoft systems very frequently truncate tables and experience contention on the RO enqueue.  This problem does not occur with global temporary tables.

Global temporary table are not a licensed database feature and are also available in standard edition.

Global Temporary Tables in PeopleTools

This is the create table DDL created by Application Designer

DROP TABLE PS_ST_RM2_TAO
/
CREATE GLOBAL TEMPORARY TABLE PS_ST_RM2_TAO (PROCESS_INSTANCE
 DECIMAL(10) NOT NULL,
   EMPLID VARCHAR2(11) NOT NULL,
   GRANT_NBR VARCHAR2(10) NOT NULL,
   VEST_DT DATE,
   SHARES_REMAINDER DECIMAL(21, 9) NOT NULL,
   DEC_PLACES SMALLINT NOT NULL) ON COMMIT PRESERVE ROWS TABLESPACE PSGTT01
/
CREATE UNIQUE iNDEX PS_ST_RM2_TAO ON PS_ST_RM2_TAO (PROCESS_INSTANCE,
   EMPLID,
   GRANT_NBR,
   VEST_DT)
/

The first thing to point out is the specification of a tablespace.  This is a new feature in Oracle 11g.  It is not mandatory in Oracle, but it is coded into the PeopleSoft DDL model so you must specify a temporary tablespace on the record otherwise it will fail to build.  A new temporary tablespace PSGTT01 is delivered by Oracle when you upgrade to 8.54, or you could just use the existing temporary tables.

This new feature has been implemented using 2 new DDL models (statement types 6 and 7).

SELECT * FROM psddlmodel WHERE statement_type IN(6,7);

STATEMENT_TYPE PLATFORMID SIZING_SET  PARMCOUNT
-------------- ---------- ---------- ----------
MODEL_STATEMENT
------------------------------------------------------------------------
             6          2          0          0
CREATE GLOBAL TEMPORARY TABLE [TBNAME] ([TBCOLLIST]) ON COMMIT PRESERVE
ROWS TABLESPACE [TBSPCNAME];

             7          2          0          0
CREATE [UNIQUE] INDEX [IDXNAME] ON [TBNAME] ([IDXCOLLIST]);

• All tables created ON COMMIT PRESERVE, but on-line instances could be ON COMMIT DELETE (theory subject to testing) and for ALL application engine programs even if restart is enabled because commits suppressed in on-line application engines.  Instead, commit is done by the component.

If you try adding a global temporary table table to an application engine that is not restart disabled you quite rightly get the following error message. The table will be added, but the program will not execute correctly.

"Global Temporary Tables allocated to this restart enabled AE program will not retain any data when program exits."

Problems:

• There has always been a 13 character limit on temporary records, because there used to be a maximum of 99 non-shared instances, and 2 characters were reserved.  If you try to set the number of instances to greater than 99 in an application Engine (I tried GP_GL_PREP)  you now get the warning message

"Do not support more than 99 instances when select the Temp Table which are not attributed as GTT"

• There is now a maximum length of 11 characters for the name of a record built a global temporary table because from PeopleTools 8.54 there can be up to 9999 non-shared instances of the record.  The restriction applies irrespective of how many instances you are actually using. 
• I have yet to encounter a system where I need more than 99 instances of a temporary table.  I can just about imagine needing 100 non-shared instances, but not 1000.  
• This means that I cannot retrofit global temporary tables into an existing Application Engine processes without changing record names.  There are existing delivered application engine programs with 12 and 13 character temporary record names that cannot now be switched to use global temporary tables managed by application designer.  I don't need to support more instances just because the table is global temporary.
• For example, GP_GL_SEGTMP in GP_GL_PREP is a candidate to be made global temporary because that is a streamed Global Payroll process.  When I tried, I got a record name too long error!

"Record Name is too long. (47,67)"

• Really, if the table is global temporary you don't need lots of instances.  Everyone could use the shared instance, because Oracle gives each session a private physical copy of the table anyway. 
• You could do this by removing the record name from the list of temporary records in the application engine, then the %Table() macro will generate the table name without an instance number.
• There would be a question of how to handle optimizer statistics.  Optimizer statistics collected on a global temporary table in one session could end up being used in another because there is only one place to store them in the data dictionary.
• The answer is not to collect statistics at all and to use Optimizer Dynamic Sampling.  There is a further enhancement in Oracle 12c where the dynamically sampled stats from different sessions are kept separate.
• When Application Designer builds an alter script, it can't tell whether it is global temporary or a normal table, so doesn't rebuild the table if you change it from one to the other.
• The only real runtime downside of global temporary tables is that if you want to debug a process the data is not left behind after the process terminates.  Even while the process is running, you cannot query the contents of a global temporary tables in use by another from your session,

Recommendation

Support for global temporary tables is welcome and long overdue.  It can bring significant run time performance and system benefits due to the reduction in redo and read consistency.  It can be implemented without any code change. 

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.