ReUse Statement FlagI originally wrote about the Performance Benefits of ReUseStatement in Application Engine over 5 years ago, and the problem is no less significant today than it was then. There are still many places in the delivered PeopleSoft application that would benefit from it. However, it is not possible to simply set the attribute across all Application Engine programs because it will cause errors in steps where SQL is dynamically generated, so each case must be considered. Where the ReUse attributed is changed, it must be tested and migrated like any other customisation.
Recently, I have been encountering problems in a number of places on a Financials system which were resolved by enabling ReUseStatement. So I started by calculating how much time was lost by not setting it.
Application Engine Batch TimingsIf an Application Engine step is not reused, then it is compiled prior to every execution during which the Application Engine bind variables are resolved to literals. The number and duration of compilations and executions are reported in the Batch Timings, the production of which are controlled with the TraceAE flag in the Process Scheduler configuration file (psprcs.cfg).
Whatever other TraceAE flags you set, I would always recommend that you set 128 and 1024 so that batch timings are always emitted to both file and database. The overhead of enabling them is negligible because they are managed in memory at run time and physically written once when the Application Engine program ends.
NB: The settings in the configuration file can be overridden by command line options. If you specify -TRACE parameter in the Process Scheduler definitions remember to also set these flags.
Batch timings are written to the AE Trace file at end of an Application Engine program, and to PS_BAT_TIMINGS PeopleTools tables at the successful end of an Application Engine program.
It can be useful to have batch timings in the trace file of an Application Engine that failed because they are not written to the database. As your system runs, you will build up batch timings for all of your successful Application Engine processes (which will be most of them. This is a useful source of performance metrics.
Compilations, Execution and ReUseIn this example, the number of compilations is equal to the number of executions because ReUseStatement is not set.
With ReUse Statement enabled, there is now only a single compilation, and most of the time is saved in execution not compilation.
So we can use the batch timings to identify steps where ReUseStatement is not set because they have as many compilations as executions, and then we can profile the top statements.
Profile CompilationsThis query produces a simple profile of batch timings for statements.
- In sub-query x it extract batch timings for statements with more than one compilation in processes that ended in the last 7 days.
- There is a long-standing bug in batch timings where negative timings can be returned when the clock that returns milliseconds recycles back to zero every 216 milliseconds. Sub-query y calculates an adjustment that is applied in sub-query z if the timing is negative.
- Arbitrarily, I am only looking at statements with more than a total of 10000 compilations.
So now I have a list of steps with lots of compilations. I know how long they took, but I don't know how much time I might save by enabling ReUseStatement. That will save some time in Application Engine, but it will also cut down database parse time. So now I need determine the parse time from ASH data.
This query is based on the previous one, but includes scalar queries on the ASH data for each step.
- WARNING: This query can run for a long period because it has to scan ASH data for each entry in BAT_TIMINGS_DTL.
- This time in sub-query x I am looking for a rolling 7-day period up to the last hour, because ASH data will have been flushed to the ASH repository.
- In sub-query y there are two scalar queries that retrieve the DB time spent on hard parse, and all DB time for each batch timing entry. These queries count 10 seconds for each distinct sample ID to estimate elapsed time rather than total DB time.
- The query does not group by process name because one step can be called from many Application Engine programs and I want to aggregate the total time across all of them.
Now we can also see how much time the database is spending on hard parse on each step, and it is this time that is likely to be saved by enabling ReUseStatement.
However, before we can enable the ReUseStatement attribute we must first manually inspect each step in Application Designer and determine whether doing so would break anything. The Comment column in this profile was added manually as I did that. Some statements I can change, some I have to accept the overhead.
- Due to a bug in the instrumentation of Application Engine, the session's action attribute is not set for Do Select (type D) and Do When (type H) steps. ASH data cannot therefore be matched for them.
- More DB Time is reported for FS_CEDT_ECMB.uMarkInv.uMarkInv.S than is reported by batch timings. This is a consequence of ASH sampling, where we count 10 seconds for each sample.