Cursor Sharing in Scheduled Processes: 2. What happens during SQL Parse? What is a 'hard' parse? What is a 'soft' parse?

This is the second in a series of 4 posts about the selective use of cursor sharing in scheduled processes in PeopleSoft.

1. Introduction
2. What happens during SQL Parse?  What is a 'hard' parse?  What is a 'soft' parse?  The additional overhead of a hard parse.
3. How to set CURSOR_SHARING for specific scheduled processes.
4. How to identify candidate processes for cursor sharing.

To understand why cursor sharing can be beneficial it is necessary to understand

• What happens when Oracle parses and executes a SQL statement?.
• How some PeopleSoft processes dynamically construct SQL statements

SQL Processing: 'Hard' Parse -v- 'Soft' Parse

SQL parse processing is set out in various places in the Oracle database documentation

• SQL Tuning Guide -> Optimizer Fundamentals -> SQL Processing.
• Tuning Guide: Improving Real-World Performance Through Cursor Sharing.
• Reference: Initialisation Parameter Cursor Sharing.
• This diagram appears in the Oracle documentation.

When a statement is submitted to the database it goes through a number of stages of parsing before it is executed. 

• Syntax Check: Is the statement syntactically valid?
• Semantic Check:  Is the statement meaningful?  Do the referenced objects exist and is the user allowed to access them?
• SGA Check: Does the statement already exist in the shared SQL area?  

The database looks for an exact matching statement in the shared SQL area. If it is not found, the database must perform additional tasks called 'hard' parsing.  The stages performed up to this point are referred to as 'soft parsing'.

• Generation of the optimal execution plan
• Row Source Generation - The execution plan is used to generate an iterative execution plan that is usable by the rest of the database.

Thus the Oracle Database must perform a 'hard' parse at least once for every unique DML statement.

What is Cursor Sharing?

A cursor is a name or handle for a private SQL area that contains session-specific information about a statement, including bind variables, state information and result sets.  A cursor in the private area points to the shared SQL area in the library cache that contains the parse tree and execution plan for a statement.  Multiple private areas can reference a single shared SQL area.  This is known as cursor sharing.

• See also SQL Tuning Guide -> Improving Real-World Performance Through Cursor Sharing -> CURSOR_SHARING and Bind Variable Substitution

The database allows only textually identical statements to share a cursor. By default, the CURSOR_SHARING parameter is set to EXACT, and thus is disabled.  "The optimizer generates a plan for each statement based on the literal value."
When CURSOR_SHARING is set to FORCE, the database replaces literal values with system-generated variables.  The database still only exactly matches statements, but after the literal values have been substituted, thus giving the appearance of matching statements that differ only by their literal values.  "For statements that are identical after bind variables replace the literals, the optimizer uses the same plan. Using this technique, the database can sometimes reduce the number of parent cursors in the shared SQL area." The database only performs a soft parse.  
In systems, such as PeopleSoft, that generate many distinct statements, cursor sharing can significantly reduce hard parse, and therefore CPU and time spent on it.

Sources of Hard Parse in PeopleSoft

PeopleSoft has a deserved reputation for suffering from high volumes of SQL hard parse.  Generally, the cause of this is dynamically generated code.  Often each statement has different literal values. 

• In Application Engine, %BIND() resolves to a literal value rather than bind variable in the resulting SQL statement unless the ReUseStatement attribute is enabled.  The problem is that it is disabled by default, and there are limitations to when it can be set.
• Dynamic statements in COBOL processes.  This is effectively the same behaviour as Application Engine, but here the dynamic generation of SQL is hard-coded in the COBOL from a combination of static fragments and configuration data. PeopleSoft COBOL programs generally just embed literal values in such statements because it is easier than creating dynamic SQL statements with possibly varying numbers of bind variables.
• In nVision where 'dynamic selectors' and 'use literal values' tree performance options are selected.  These settings are often preferable because the resulting SQL statements can make effective use of Bloom filters and Hybrid Column Compression (on Exadata).  The penalty is that it can lead to more hard parse operations.

ReUseStatement -v- Cursor  Sharing

Of course, it would be better if PeopleSoft SQL used bind variables more often, rather than literal values. 

In Application Engine, if the ReUseStatement attribute is set on a step, then bind variables in Application Engine remain bind variables in the resulting SQL and are not converted back to literals.  

This can reduce both the amount of time Application Engine spends dynamically generating SQL statements before submitting them to the database as well as the time the database spends parsing them (see Minimising Parse Time in Application Engine with ReUseStatement). 

However, this attribute is not set by default on newly created Application Engine steps in Application Designer.  This was to maintain backward compatibility with programs created in earlier versions of PeopleTools.

However, there are restrictions to where it cannot be used.  Most commonly because %BIND(…NOQUOTES) has been used to dynamically generate part of the statement based on configuration data.

Over the years, PeopleSoft development has got much better at setting this attribute where possible in the delivered application code.  Nonetheless, there are still places where it could be added.  

See also:

• Hinting Dynamically Generated SQL in Application Engine
• PeopleTools -> Development Tools -> Application Engine -> Reusing Statements

However, there are some considerations before we add it ourselves.

• ReUseStatement cannot be introduced across the board, but only on steps that meet certain criteria set out in the documentation.  It doesn't work when dynamic code is generated with %BIND(…,NOQUOTES), or if a %BIND() is used in a SELECT clause.  Worse, setting this attribute incorrectly can cause the application to function incorrectly.  So each change has to be tested carefully.
• When a customer sets the ReUseStatement attribute in the delivered code, it is a customisation to an Application Engine step that has to be migrated using Application Designer.  It then has to be maintained to ensure that subsequent PeopleSoft releases and patches do not revert it.
• There is no equivalent option for PeopleSoft COBOL, SQR, or nVision.  The way that SQL is generated in each is effectively hard-coded.

Recommendation: It is not a case of either ReUseStatement or Cursor Sharing.  It may be both.  If you are writing your own Application Engine code, or customising delivered code anyway, then it is usually advantageous to set ReUseStatement where you can.  You will save non-database execution time as well as database time because you are then using bind variables, and Application Engine does not have to spend time generating the text of a new SQL statement with new literal values for every execution.  You may still benefit from cursor sharing for statements where you cannot set ReUseStatement.  

However, as you will see in the last article in this series, cursor sharing is not always effective, you have to test.

Minimising Parse Time in Application Engine with ReUseStatement

This article explains how to determine the overhead of using literal values rather than bind variables in SQL submitted by PeopleSoft Application Engine programs. Using a combination of PeopleSoft Application Engine Batch Timings and Oracle Active Session History (ASH), it is possible to determine where it is most effective to change to alter this behaviour by setting the ReUse attribute.

ReUse Statement Flag

I 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 Timings

If 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).  

;-------------------------------------------------------------------------
; AE Tracing Bitfield
;
; Bit       Type of tracing
; ---       ---------------
...
; 128       - Timings Report to AET file
...
; 1024      - Timings Report to tables
...
TraceAE=1152
;------------------------------------------------------------------------

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 ReUse

In this example, the number of compilations is equal to the number of executions because ReUseStatement is not set.

                          PeopleSoft Application Engine Timings
                              (All timings in seconds)

                               C o m p i l e    E x e c u t e    F e t c h        Total
SQL Statement                  Count   Time     Count   Time     Count   Time     Time
------------------------------ ------- -------- ------- -------- ------- -------- --------
99XxxXxx.Step02.S                 8453      2.8    8453    685.6       0      0.0    688.4
...

With ReUse Statement enabled, there is now only a single compilation, and most of the time is saved in execution not compilation.

                               C o m p i l e    E x e c u t e    F e t c h        Total
SQL Statement                  Count   Time     Count   Time     Count   Time     Time
------------------------------ ------- -------- ------- -------- ------- -------- --------
99XxxXxx.Step02.S                    1      0.0    8453    342.3       0      0.0    342.3
...

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 Compilations

This 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 2¹⁶ 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.

REM ReUseCand.sql
REM (c)Go-Faster COnsultancy Ltd. 2014
COLUMN detail_id FORMAT a32
COLUMN step_time FORMAT 999990 HEADING 'AE|Step|Secs'
COLUMN compile_count HEADING 'AE|Compile|Count'
COLUMN execute_count HEADING 'AE|Execute|Count'
COLUMN processes HEADING 'Num|Process|Instances'
COLUMN process_name HEADING 'Process|Name'
SPOOL ReUseCand
WITH x AS (
SELECT  l.process_instance, l.process_name
,  l.time_elapsed/1000 time_elapsed
, l.enddttm-l.begindttm diffdttm
,  d.bat_program_name||'.'||d.detail_id detail_id
, d.compile_count, d.compile_time/1000 compile_time
, d.execute_time/1000 execute_time
FROM ps_bat_Timings_dtl d
, ps_bat_timings_log l
WHERE d.process_instance = l.process_instance
AND d.compile_count = d.execute_count 
AND d.compile_count > 1 
AND l.enddttm > SYSDATE-7
), y as (
SELECT  x.*
, GREATEST(0,60*(60*(24*EXTRACT(day FROM diffdttm)
                             +EXTRACT(hour FROM diffdttm))
                             +EXTRACT(minute FROM diffdttm))
                             +EXTRACT(second FROM diffdttm)-x.time_elapsed) delta 
FROM  x)
, z as (
SELECT process_instance, process_name, detail_id
,  CASE WHEN time_elapsed < 0 THEN time_elapsed+delta  
               ELSE time_elapsed END time_elapsed
,  compile_count
, CASE WHEN compile_time < 0 THEN compile_time+delta
      ELSE compile_time END AS compile_time
, CASE WHEN execute_time < 0 THEN execute_time+delta
      ELSE execute_time END AS execute_time
FROM y
), a as (
SELECT  process_name, detail_id
, SUM(compile_time+execute_time) step_time
, SUM(compile_count) compile_count
, COUNT(DISTINCT process_instance) processes
FROM  z
GROUP BY process_name, detail_id)
SELECT * FROM a
WHERE compile_count >= 10000 
ORDER BY step_time DESC
/
SPOOL OFF

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.

Process                                         Step    Compile    Process
Name         DETAIL_ID                          SEcs      Count  Instances
------------ -------------------------------- ------ ---------- ----------
AP_PSTVCHR   AP_PSTCOMMON.ACCT_UPD.Step02.S    12001      40704         10
AP_VCHRBLD   APVEDTMOVE.UPDQTY03.Step03.S       4313      49536         28
FS_VATUPDFS  FS_VATUPDFS.Seq0-b.DJ300-2.S       4057     203704          3
AP_VCHRBLD   APVEDTMOVE.UPDQTY03.Step02.S       2799      49536         28
PC_BI_TO_PC  PC_BI_TO_PC.UPD_PRBI.UPDATE.S      1974      23132         10
FS_VATUPDFS  FS_VATUPDFS.Seq0-a.X0001.D         1960      37368          3
GL_JEDIT_0   FS_CEDT_ECFS.iTSELog.iTSELog.S     1628      13104        519
AP_APY2015   AP_APY2015.V_CREATE.Step14.H       1192      11318         19

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.

REM ReUseCandASH.sql
REM ReUseCandASH.sql
REM (c)Go-Faster Consultancy Ltd. 2014
COLUMN processes       HEADING 'Num|Process|Instances'
COLUMN process_name    HEADING 'Process|Name'
COLUMN detail_id       FORMAT a32
COLUMN step_time       HEADING 'AE|Step|SEcs'    FORMAT 999990 
COLUMN compile_count   HEADING 'AE|Compile|Count'
COLUMN execute_count   HEADING 'AE|Execute|Count'
COLUMN hard_parse_secs HEADING 'Hard|Parse|Secs' FORMAT 99990
COLUMN ash_secs        HEADING 'DB|Time'         FORMAT 99990
SPOOL ReUseCandASH
WITH x AS (
SELECT  l.process_instance, l.process_name
,  l.time_elapsed/1000 time_elapsed
, l.begindttm, l.enddttm
, l.enddttm-l.begindttm diffdttm
,  d.bat_program_name||'.'||d.detail_id detail_id
, d.compile_count, d.compile_time/1000 compile_time
, d.execute_time/1000 execute_time
FROM ps_bat_timings_dtl d
, ps_bat_timings_log l
WHERE d.process_instance = l.process_instance
AND d.compile_count = d.execute_count
AND d.compile_count > 1
AND l.enddttm >= TRUNC(SYSDATE-7,'HH24')
AND l.enddttm < TRUNC(SYSDATE,'HH24') 
), y as (
SELECT  x.*
, GREATEST(0,60*(60*(24*EXTRACT(day    FROM diffdttm)
                             +EXTRACT(hour   FROM diffdttm))
                             +EXTRACT(minute FROM diffdttm))
                             +EXTRACT(second FROM diffdttm)-x.time_Elapsed) delta
FROM  x)
, z as  (
SELECT process_instance, process_name, detail_id
,  CASE WHEN time_elapsed < 0 THEN time_elapsed+delta 
             ELSE time_elapsed END AS time_elapsed
,  compile_count
, CASE WHEN compile_time < 0 THEN compile_time+delta
      ELSE compile_time END AS compile_time
, CASE WHEN execute_time < 0 THEN execute_time+delta
      ELSE execute_time END AS execute_time
, (
 SELECT 10*COUNT(DISTINCT h.sample_id) 
 FROM psprcsque q
 , dba_hist_snapshot x
  , dba_hist_active_sess_history h
 WHERE q.prcsinstance = y.process_instance
  AND  x.begin_interval_time <= y.enddttm
  AND  X.END_INTERVAL_TIME >= y.begindttm
  AND  h.sample_time between y.begindttm and y.enddttm
  AND  h.SNAP_id = x.SNAP_id
  AND  h.dbid = x.dbid
  AND  h.instance_number = x.instance_number
  AND  h.module = 'PSAE.'|| y.process_name||'.'||q.sessionidnum
  AND  h.action = y.detail_id
 AND h.in_hard_parse = 'Y'
 ) hard_parse_secs
, (
 SELECT 10*COUNT(DISTINCT h.sample_id)
 FROM psprcsque q
 , dba_hist_snapshot x
  , dba_hist_active_sess_history h
 WHERE q.prcsinstance = y.process_instance
  AND  x.begin_interval_time <= y.enddttm
  AND  X.END_INTERVAL_TIME >= y.begindttm
  AND  h.sample_time between y.begindttm and y.enddttm
  AND  h.SNAP_id = X.SNAP_id
  AND  h.dbid = x.dbid
  AND  h.instance_number = x.instance_number
  AND  h.module = 'PSAE.'|| y.process_name||'.'||q.sessionidnum
  AND  h.action = y.detail_id
 ) ash_secs
FROM  y
), a AS (
SELECT  /*process_name ,*/ detail_id
, SUM(compile_time+execute_time) step_time
, SUM(compile_count) compile_count
, COUNT(DISTINCT process_instance) processes
, SUM(hard_parse_secs) hard_parse_secs
, SUM(ash_secs) ash_secs
FROM  z
GROUP BY /*process_name,*/ detail_id)
SELECT  a.* 
FROM  a
WHERE  compile_count >= 10000
ORDER BY step_time DESC
/
spool off

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.

                                   Step    Compile    Process      Parse         DB
DETAIL_ID                          Secs      Count  Instances       Secs       Time Comment
-------------------------------- ------ ---------- ---------- ---------- ---------- …………………………………………………………………………………………………………………………………
AP_PSTCOMMON.ACCT_UPD.Step02.S    12001      40704         10      11820      11920 Set ReUseStatement
FS_CEDT_ECMB.4EditCDT.uValCDT.S    5531      10289        679        620       5870 Dynamic SQL, can't set ReUseStatement
APVEDTMOVE.UPDQTY03.Step03.S       4306      49471         27       4020       4100 Set ReUseStatement
FS_VATUPDFS.Seq0-b.DJ300-2.S       4057     203704          3       3150       3860 Dynamic SQL, can't set ReUseStatement
FS_CEDT_ECFS.iTSELog.iTSELog.S     3332      19073        716       2130       3520 Dynamic SQL, can't set ReUseStatement
APVEDTMOVE.UPDQTY03.Step02.S       2796      49471         27       2730       2820 Set ReUseStatement
PC_BI_TO_PC.UPD_PRBI.UPDATE.S      1974      23132         10        230       1920 Set ReUseStatement 
FS_VATUPDFS.Seq0-a.X0001.D         1960      37368          3          0          0 Dynamic SQL, can't set ReUseStatement
FS_CEDT_ECMB.4uAnchCT.uAnchCDT.S   1319      10289        679        510       1290 Dynamic SQL, can't set ReUseStatement
AP_APY2015.V_CREATE.Step14.H       1169      11094         19          0          0 Set ReUseStatement
GL_JETSE.GA100.CHKEDT.S            1121      15776        569        860        930 Dynamic SQL, can't set ReUseStatement
FS_CEDT_ECMB.iTSELog.iTSELog.S      988      10289        679        450        990 Dynamic SQL, can't set ReUseStatement
FS_CEDT_ECMB.uMarkVal.uMarkVal.S    711      10289        679         50        670 Dynamic SQL, can't set ReUseStatement
FS_CEDT_ECMB.uMarkInv.uMarkInv.S    668      10289        679         40        790 Dynamic SQL, can't set ReUseStatement

• 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.

Conclusion

Setting ReUseStatement is very simple because it doesn't involve changing SQL, but there are lots of places where it could be set.  This technique picks out the relatively few places where doing so could potentially have a significant effect.

Performance Benefits of ReUse Statement Flag in Application Engine

I have achieved some significant performance improvements in some Application Engine programs by just enabling the ReUse Statement flag on certain steps. I thought I would share a recent example of how effective this can be. I don't think I can improve on the description of this feature in PeopleBooks: 

"One of the key performance features of PeopleSoft Application Engine is the ability to reuse SQL statements by dedicating a persistent cursor to that statement. Unless you select the ReUse property for a SQL action, %BIND fields are substituted with literal values in the SQL statement. The database has to recompile the statement every time it is executed. However, selecting ReUse converts any %BIND fields into real bind variables (:1, :2, and so on), enabling PeopleSoft Application Engine to compile the statement once, dedicate a cursor, and re-execute it with new data multiple times. This reduction in compile time can result in dramatic improvements to performance. In addition, some databases have SQL statement caching. Every time they receive SQL, they compare it against their cache of previously executed statements to see if they have seen it before. If so, they can reuse the old query plan. This works only if the SQL text matches exactly. This is unlikely with literals instead of bind variables." 

In fact, most databases do this, and Oracle certainly does. 

On Oracle, you could enable CURSOR_SHARING. Then Oracle effectively replaces the literals with bind variables at parse time. However, I certainly would not recommend doing this database-wide. Whenever I have tried this on a PeopleSoft system, it has had severe negative effects elsewhere. I have enabled cursor sharing at session level for specific batch programs (using a trigger), but even then it is not always beneficial. 

Instead, I do recommend using the ReUse Statement flag wherever possible. It cannot just be turned on indiscriminately, the same section in PeopleBooks goes on to describe some limitations (which is probably why the default value for the flag is false). To illustrate the kind of improvement you can obtain, here is a real-life example. 

This is an extract from the batch timings report at the end of the Application Engine trace file. We are interested in statements with the high compile count. ReUse Statement is not enabled on these 4 steps. They account for more that 50% of the overall execution time.

                          PeopleSoft Application Engine Timings
                              (All timings in seconds)

        C o m p i l e    E x e c u t e    F e t c h        Total
SQL Statement                  Count   Time     Count   Time     Count   Time     Time
------------------------------ ------- -------- ------- -------- ------- -------- --------
99XxxXxx.Step02.S                 8453      2.8    8453    685.6       0      0.0    688.4
99XxxXxx.Step03.S                 8453      5.0    8453   2718.8       0      0.0   2723.8
99XxxXxx.Step05.S                 8453      0.9    8453    888.4       0      0.0    889.3
99XxxXxx.Step06.S                 8453      0.4    8453     17.4       0      0.0     17.8

------------------------------------------------------------------------------------------
Total run time                :     8416.4
Total time in application SQL :     8195.0   Percent time in application SQL :       97.4%
Total time in PeopleCode      :      192.7   Percent time in PeopleCode      :        2.3%
Total time in cache           :        8.7   Number of calls to cache        :       8542
------------------------------------------------------------------------------------------

Now, I have enabled ReUse Statement on these steps. I have not changed anything else.

                         C o m p i l e    E x e c u t e    F e t c h        Total
SQL Statement                  Count   Time     Count   Time     Count   Time     Time
------------------------------ ------- -------- ------- -------- ------- -------- --------
99XxxXxx.Step02.S                    1      0.0    8453    342.3       0      0.0    342.3
99XxxXxx.Step03.S                    1      0.0    8453     83.3       0      0.0     83.3
99XxxXxx.Step05.S                    1      0.0    8453      8.7       0      0.0      8.7
99XxxXxx.Step06.S                    1      0.0    8453      7.6       0      0.0      7.6
------------------------------------------------------------------------------------------
Total run time                :     5534.1
Total time in application SQL :     5341.7   Percent time in application SQL :       96.5%
Total time in PeopleCode      :      190.8   Percent time in PeopleCode      :        3.4%
Total time in cache           :        1.1   Number of calls to cache        :         90
------------------------------------------------------------------------------------------

Notice that:

• The number of compilations for each step has gone down to 1, though the number of executions remains the same
• The execution time for the first three statements has fallen by nearly 90%.
• The improvement in the 4th statement is quite modest because it did not contain any bind variables, but clearly, some of the time reported as execution time by Application Engine is associated with the preparation of a new SQL statement.

To emphasise the point, lets look at the effect on the database. The following are extracts from the TKPROF output for Oracle SQL trace files for these processes. First the TKPROF without ReUse Statement:

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse   101063   2600.60    2602.83       6197     661559          4           0
Execute 101232   1817.96    3787.17    1572333   73729207   10617830     4770112
Fetch    96186    385.41    1101.47     374425   25986600          0       96285
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   298481   4803.97    7491.48    1952955  100377366   10617834     4866397

Misses in library cache during parse: 25498
Misses in library cache during execute: 90

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
----------------------------------------   Waited  ----------  ------------
db file sequential read                   1199472        0.36       2601.83
SQL*Net message from client                130345        1.57        296.50
db file scattered read                       8816        0.39        171.47

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse   100002     13.51      13.57         17        820         94           0
Execute 131495     30.00      31.31       7025      29277      21164       74315
Fetch   141837    218.77     295.49     159969    3039304         12      519406
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   373334    262.28     340.38     167011    3069401      21270      593721

160446  user  SQL statements in session.
70478  internal SQL statements in session.
230924  SQL statements in session.

And now with ReUse Statement set on only those four steps

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse    67238     10.24      10.75         47       4415          9           0
Execute 101160   1650.25    4040.88    1766325  129765633   11160830     4781797
Fetch    96123    385.50    1024.50     372737   26097251          0      103844
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   264521   2045.99    5076.14    2139109  155867299   11160839     4885641

Misses in library cache during parse: 73
Misses in library cache during execute: 21

Elapsed times include waiting on following events:
Event waited on                             Times   Max. Wait  Total Waited
----------------------------------------   Waited  ----------  ------------
db file sequential read                   1506834        0.61       2839.19
SQL*Net message from client                130312        1.53        258.81
db file scattered read                       8782        0.37        147.01

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     1331      0.46       0.46          0        173         16           0
Execute   4044      2.72       5.82      12923      33374      24353      113323
Fetch     5697      8.38      13.43      15550      55431         12       13449
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    11072     11.56      19.72      28473      88978      24381      126772

67425  user  SQL statements in session.
3154  internal SQL statements in session.
70579  SQL statements in session.

• Nearly all the saving is in parse time of non-recursive statements, the rest is the reduction of recursive SQL because there is less parsing.
• There is less parsing because there are fewer different SQL statements submitted by Application Engine. The number of user statements has fallen from 160446 to 67425.
• The number of misses on the library cache has fallen from 25498 to just 73.
• There has been a reduction in SQL*Net message from client (database idle time) from 296 seconds to 253 because the Application Engine program spends less time compiling SQL statements.

Conclusion Enabling ReUse Statement can have a very significant effect on the performance of Application Engine batches. It is most effective when SQL statements with %BIND() variables are executed within loops. Otherwise, for each execution of the loop, Application Engine must recompile the SQL statement with different bind variable values, which the database will treat as a new statement that must be parsed. SQL parsing is CPU intensive. Reducing excessive parse also reduces CPU consumption on the database server. It can also reduce physical I/O to the database catalogue. On PeopleSoft 8.x applications that use Unicode, the overhead of parsing is magnified by the use of length checking constraints on all character columns. This is no longer an issue in version 9 applications which use character semantics. If you use Oracle's Automatic Memory Management, excessive parsing can cause the database to allocate more memory to the Shared Pool at the expense of the Block Buffer Cache. This in turn can increase physical I/O and can degrade query performance. Bind Variables are a good thing. You should use them. Therefore, ReUse Statement is also a good thing. You should use that too!