Tuning Tips for Application Engine - PeopleSoft

Application Engine programming has become the order of the day for most of the Custom development. Due to its very nature in its design, Application Engine has successfully replaced COBOL & to some extent SQR as main coding medium. Here are some of the tips that can be considered for improving the performance of Application Engine Programs. 

Temporary tables:

The Temporary tables can be used to create sets of data for processing especially in parallel processing. The process of improving the performance using Temp tables in App Engine Programs is

•  Truncate temp table in the beginning of the program as part of initialization routine

•  Populate temp table

•  Analyze temp table to generate statistics for optimizers (Use %UpdateStats meta-SQL)

•  Process as required

•  Some performance related points about Temporary tables are

PROCESS_INSTANCE

• If this field is keyed into temp table and application SQL includes it in the WHERE clause, then the table can be shared by multiple processes, this will hurt performance. Dedicated temporary table instance is the best way to go.

Defining Temporary tables

•  If the program accesses a set of rows repeatedly from a larger table, insert the necessary row into a temporary table in the beginning of the program. More on this in Set Processing section.

Statistics at Runtime for Temp tables

Temp tables experience heavy data volume changes during the course of a program run. To effectively gather statistics, for tuning them, %UpdateStats Meta-SQL can be used. Once the data is inserted into the temp table and committed then use this command, which will be interpreted by Oracle as ‘ANALYZE TABLE … ESTIMATE STATISTICS’ command. 

PeopleSoft stores the default syntax for ANALYZE command in a table PSDDLMODEL. Modification to that can be done through the script DDLORA.DMS, like adding a required SAMPLE ROWS/PERCENT for ESTIMATE clause

• Once batch-process runs are stabilized then turn off the %UpdateStats by the following two ways

           --> At Program level: Inactivate the steps in App Engine

                     --> Installation level: in Process Scheduler Configuration set DbFlags = 1 to turn of collecting statistics  

• Oracle PLAN_TABLE is wrapped automatically around all SQL statements in AE programs creating

Setting up number of Temporary table instances

• Switch on the App Engine SQL & Timings trace, if the command “UPDATE PS_AEONLINEINST… “is taking more time, then the number of temp table instances needs to be checked. 

•  Temp table instances [online] should be very small number (in PeopleTools Options page).

• Runtime box in Program Properties dialog box displays two options, if ‘Continue’ option is used, then base table will be used when all the temp table instances are assigned. If ‘Abort’ is selected then the program will abort when there are no temp tables to be assigned.

• Create temp tables in separate Tablespace and spread data files on multiple disks for minimizing I/O. Hardware disk striping is another way, check with DBA

• Create temp tables as Locally Managed Tablespace with fixed-extent size (See chapter on DISK I/O)

• When there is no commit before truncate step, AE converts Truncate command into DELETE statement. 

•  Identify such tables and manually truncate them at regular intervals to release buffer block & maximize performance.

• Build table instances:

          --> Do not execute the DDL script after building it in App Designer, Modify the script so that you can  

               place different sets of temp table on different tablespaces as per the instance number.

Global Temporary Tables in Oracle (v 8.x & higher)

Oracle creates Global Temporary tables which can be used for batch processing, they are session-specific and created at runtime in user’s Temporary Tablespace and are dropped once session is closed. At the time of creation, an option is given whether to preserve or delete the rows after commit. There are pros and cons for use of this feature

Pros:

· Reduction in redo

· Faster full scans – High Water Mark is set to zero (similar to %Truncate command)

· Faster Truncates due to space management in temporary segment

· Easier table management, since entire temp tables instances need not be created upfront. 

Cons:

· Table Statistics can not be gotten on these tables, will impact access paths and execution times

· Temporary table sizing should be done properly to avoid any runtime errors due to lack of extents

· ReStart option can not be used since data is lost when the session ends (similar to choosing  

      DERIVED/WORK record as State Record)

Indirect approach to using this feature is

• In Temp Tables tab in App Engine Properties window, set Instance Count to 0 and select ‘Continue’ radio button for RunTime Settings

• Build the Temp table and modify the script to create the tables as Global Temporary Tables

• When multiple runs of the program occurs, due to ‘Continue’ option selected, it’ll use the base temp table, which in turn uses Global Temporary Tables

Parallel Processing

Parallel Processing can be defined as having multiple instances of the same program running at the same time - each instance processing a discreet subset of the data. It is to be used when multiple rows of data need to be updated or processed; it is highly efficient in environments where there are multiple CPUs and partitioned data. Partition the data among multiple concurrent runs of a program each with its own dedicated version of temporary table using run control parameters. Steps involved in parallel processing are

• Define & Save temporary table records in App Designer (need not run SQL build process at this point)

• Assign temporary tables to App Engine programs and set instance counts dedicated for each program. Use %Table meta-SQL construct, to resolve table references to assigned temporary table instance dynamically at runtime. Run control parameters passed to each instance of temp table enable it to identify which input rows belong to it and each program instance inserts the rows from source into temp table instance using %Table

• Set number of total & online temporary table instances in PeopleTools Options page

• Build Temporary table in App Designer

ReUse Statement

• Used to Optimize SQL in batch programs, it converts any %BIND references to state records fields into real bind variables enabling the program to compile once at runtime, dedicate a cursor and re-execute with new data as often as program requires.

• ReUse is valid for SQL Actions

• Use ReUse only if you don’t use Bind variables for column names (since it converts %BIND fields to Bind variables)

• Use ReUse only if you have no %BINDs in the Select list.

• If SQL is dynamic, as in you are using %BIND to resolve to a value other than a standard Bind value and contents of Bind change each time the statement gets executed, then SQL statements don’t match with old queries in the cache, hence ReUse can’t be used. 

• If NOQUOTES modifier is used inside %BIND, its implied STATIC. For dynamic SQL substitution, %BIND has CHAR field and NOQUOTES to insert SQL rather than a literal value. If ReUse is enabled, then value of CHAR field gets substituted inline, instead of Bind marker. System executes SQL which is same as before even if the value of a static bind has changed. 

• %ClearCursor meta-SQL is used to recompile the statement.

Bulk Insert option is most effective in INSERT statements when ReUse statement is used. This will enable to store a large amount of data in the buffer before inserting in one shot. A flush also occurs before each commit. System ignores Bulk Insert setting in the following conditions:

·         SQL is not an INSERT

·         INSERT/SELECT, UPDATE, DELETE ignored

·         SQL does not have VALUES clause

·         SQL does not have a field list before VALUES clause.

·         All the three conditions are true: Oracle is the database, EFFDT is a field, and Record contains a mobile trigger. Because Oracle doesn’t allow reading of mutating tables in a row trigger.

Usage of One Row Records

• Suppose you have a single Insert statement into a table, followed by an %UpdateStats. If the stats were current before the Insert statement, and the Insert statement affects no rows, then the %UpdateStats is unnecessary

Planning to program for maximum performance

• Avoid DO loops as much as possible

•  Minimize your PeopleCode as much as possible to reduce the time in PeopleTools cache

• Avoid %BIND() without the ReUse option, since AE will pass literal values to database in its place

• Tune SQL and use parallel and set processing wherever possible

• Use Truncating command instead of delete command where there is no filter condition

ALTER SESSION Command

• To ensure Oracle Optimizer choose the right execution path every time and get optimal performance, try altering sessions in various parts of the program

• ‘SQLExec(“ALTER SESSION SET cursor_sharing = FORCE”);’

·  This will ensure that Oracle first checks the shared pool to see if there is an identical statement in shared pool

·  The two options for ‘cursor_sharing’ parameter are ‘FORCE’ & ‘SIMILAR’. The difference between the  two is that ‘SIMILAR’ forces similar statements to share SQL area without deteriorating the execution plans

Trace

• Setting Trace on AE programs can tell a lot about how the programs are being executed. In production environments, trace will add additional overhead to performance, ensure that trace value is set to zero once trace file is generated. Typical trace value is “TRACEAE = 2179”