Writing Efficient App Engine Program

Few high-level guidelines to be followed:

a) Apply Set Processing wherever it can be applied:

• Use Group by, Having, Exists clauses effectively
• Take extra care while writing sub queries and complex joins
• Don't forget to join PROCESS_INSTANCE, in case you've made it a key
• Fine tune the SQL ( Refer: Oracle Documentation)

b) Use Temp Table

• It improves AE performance significantly- Best Suited for Set Based Processing
• Facilitates parallel processing
• Custom Indexes can be created to achieve faster result
• Practice to make PROCESS_INSTANCE a key and employ the %Table meta-SQL
• Automatic data cleanse
• Dedicated Temp Table is preferred

c) Use Meta-SQL

• Make a habit of using Meta-SQL like %Join, %CurrentDateIn, %Table. It gives program more flexibility.
• It makes AE program more robust and platform independent and improves performance.
• Be aware of the limitation of these Meta-SQL, e.g %EffdtCheck doesn't work well with PS_JOB table
• %TruncateTable is faster than bulk delete statement. In case of Temp table use %TruncateTable(%Table(XXX_TAO))

d) Drop/Rebuild Indexes:

• If you're planning for massive Insert, you may wish to drop indexes and triggers from the table first and recreate them once the insert is done.
• It makes AE processing much faster. Just be careful that dropping indexes makes it prone to Duplicate rows. (You may leave primary key untouched).

e) Refrain using PeopleCode

• If the goal can be achieved using SQL, do not use PeopleCode.

f) Setting Commits

• It's recommended to use frequent and early Commit in case of Set based processing. It reduces load from Database server and enhance performance.

g) Reuse Statement

• Valid Only for SQL actions
• By dedicating a persistent cursor to that statement we can reuse the SQL Statement.
• When we select the ReUse property for a SQL action, %BIND fields is converted into real bind variables (like :1, :2, etc). This enables PeopleSoft Application Engine to compile the statement only once and dedicate a cursor, and re-execute it with new data multiple times. This reduction in compile time can result in dramatic improvements to performance.

h) Use %UpdateStats

• For better performance, refresh statistics on temp tables using %UpdateStats(record name ,[HIGH/LOW]) after each bulk load of data.

i) Use CollectGarbage()

• Specific to AE using App Classes.
• Use the CollectGarbage function to remove any unreachable application objects created by the Application Classes and hence release the memory. ( Also Refer: Unix Process Limit for PeopleSoft)
  

j) Use SQL Hints ( Tuning SQL)

• SQL Hints e.g. /* +APPEND */,/* +FIRST_ROWS(n) */, provides a mechanism which instruct the CBO to choose a certain query execution plan based on the specific criteria. ( Refer: Oracle Documentation)
  

k) Streaming

• Streaming is a process in which a large chunk of data is divided into multiple streams and all the stream processes in parallel.

l) Use Trace File

• Use trace file generated by Trace/TOOLSTRACESQL/TOOLSTRACEPC advisably. Find out areas where process is taking longer processing-time or where performance can be improved.

%UpdateStats( ) and PLAN_TABLE

We all are well aware of the 'power' of Application Engine Trace. No matter its a simple or most difficult AE program, (most of the time) we put a Trace parameter to get one or more out of the followings :

• Application Engine Step and SQL trace
• Application Engine Statement Timings trace
• PeopleCode Detail Timings trace
• Database Optimizer trace

Recently, I was working on a custom AE program with over 100 sql actions. Temp Tables, Meta SQL, HINTS, %UpdateStats() and other standard approaches were followed to enhance the performance. During the course of fine-tuning, as I wanted to get most out the trace functionality, I put a trace value - TRACE 4351. To my surprise, the process went to no success. The error log read:
"SQL Error: ORA-00604: error occurred at recursive SQL level 1 ORA-12899: value too large for column "SYSADM"."PLAN_TABLE"."STATEMENT_ID" (actual: 31, maximum: 30) "

What went wrong ? After an hour of analysis my conclusion was:

a) Trace Value 4096( which I was using)—Initiates the database optimizer "explain", storing the results in the Explain Plan Table of the current database. I was on Oracle, so it was PLAN_TABLE.
b) When outputting to a table, PeopleSoft updates the trace rows as follows:
* EXPLAIN PLAN SET STATEMENT_ID: PeopleSoft updates the STATEMENT ID column:
EXPLAIN PLAN SET STATEMENT_ID = ApplId.Section.Step.Type FOR sqlstmt
* PLAN_TABLE’s REMARKS column: PeopleSoft updates the REMARKS column:
PLAN_TABLE's REMARKS column = 'ProcessInstance-RunControlId(QueryNo)'
queryno is a count of how many SQL statements have been traced up to a particular point.
c) In PLAN_TABLE, the field STATEMENT_ID is VARCHAR(30). One can see the table schema or refer Oracle Documentation.
d) PeopleSoft recommends - To output the trace to a table on Oracle, (i) a PLAN_TABLE table must exist, and (ii) the STATEMENT_ID must be of type VarChar2(254), instead of VarChar2(30).

Hence the Problem !