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!

Do You Need More Temporary Table Instances?

When an Application Engine loads a program prior to execution, it attempts to allocate an instance of each temporary record specified in the program to itself. If the allocation of a table fails because there are no available instances, Application Engine is will use the shared instance (unless the program is configured to abort if non-shared tables cannot be assigned). In this case it will write an entry to the message log to warn that the shared instance of the record has been used.

When processes use the shared tables performance is likely to be degraded by contention on the table. The %TruncateTable() PeopleCode macro generates a DELETE by process instance on the shared table instead of a TRUNCATE command.
The problem is that unless you look in the message log, you will not know that this is happening. However, it easy to write a query that will look at the message log table and report whenever this has occurred.

REM tr_moreinst.sql
select  p.message_parm recname, r.prcsname
,  count(*) occurances
,  max(l.dttm_stamp_sec) last_occurance
,  max(p.process_instance) process_instance
from  ps_message_log l
,  ps_message_logparm p
 left outer join psprcsrqst r
 on r.prcsinstance = p.process_instance
where  l.message_set_nbr = 108
and    l.message_nbr = 544
and    p.process_instance = l.process_instance
and    p.message_seq = l.message_seq
and    l.dttm_stamp_sec >= sysdate - 7
group by p.message_parm, r.prcsname
order by 1,2
/

This report tells you which programs failed to allocated instances of which record, how many times that has happened within the last 7 days.

            Processes Unable to Allocate Non-Shared Temporary Record

                                                                  Last
Record          Process                  Last                   Process
Name            Name         Occurrences Occurrence            Instance
--------------- ------------ ----------- ------------------- ----------
TL_ABS_WRK      TL_TIMEADMIN           4 08:24:39 01/01/2009      12345
TL_ATTND_HST1   TL_TIMEADMIN          10 08:23:40 01/01/2009      12345
TL_COMP_BAL     TL_TIMEADMIN          11 08:23:40 01/01/2009      12345
...

NB: Just because an Application Engine could not allocate a non-shared table, does not automatically imply that you need more instances of that record. It could be that other processes had failed, but the temporary tables are still allocated to the process until the process is either restarted and completes successfully, or the process is deleted or cancelled.
You might choose to create some spare instances of records to allow for failed processes, but if you do not clear failed processes you will eventually run out of instances.

UKOUG PeopleSoft Conference 2009 - Call for Papers

Building on the success of the inaugural UKOUG PeopleSoft Conference & Exhibition in 2008, this Conference, presented by UKOUG and GPUG, will provide a forum for the presentation and exchange of ideas and practical experiences within the areas of PeopleSoft Financials, HCM/HR and Technology. The multi-stream agenda will feature keynote presentations, technical and non-technical sessions, roundtables, panel discussions and more.
If you are interested in sharing your experience of using PeopleSoft technology and applications, here is your chance as the call for papers is now open.

Deadlines are tight.  Submit your abstracts now at: www.oug.org/peoplesoft

Closing date for submissions: Friday 27th February.

The review panel, comprised of PeopleSoft community members, will evaluate all abstracts submitted by the closing date. The authors of accepted abstracts will receive confirmation at the beginning of March.

Managing Changes to the Number of Instances of Temporary Tables used in Application Engine Programs

When you run multiple copies of either the same Application Engine program, or different Application Engines that happen to use the same temporary work records, you need to worry about how many instances of the program or programs are likely to run concurrently, and hence how many instances of the temporary table to build.

But how do you manage these tables when you change the number of instances in the Application Engine properties?

How many tables are built for each Temporary Record?

The number of instances of each Application Engine program is set in the properties for that program (and stored on the PeopleTools table PSAEAPPLDEFN in the column TEMPTBLINSTANCES).

This screenshot is the Application Engine Program Properties for TL_TIMEADMIN. It is delivered configured for up to 10 concurrent instances.


The number of tables that are built for each Temporary Record is the sum of the instances in all the Application Engine programs in which the record is used, plus the number of Global Instances (in set up on the PeopleTools Options page.

Let’s take the record TL_PROF_LIST as an example. It is defined as a Temporary Record in 8 different Application Engine programs (in my HCM8.9 demo system).

SELECT a.recname, a.ae_applid, b.temptblinstances
FROM   psaeappltemptbl a, psaeappldefn b
WHERE  a.ae_applid = b.ae_applid
AND    a.recname = 'TL_PROF_LIST'
ORDER BY a.recname
/

RECNAME         AE_APPLID    TEMPTBLINSTANCES
--------------- ------------ ----------------
TL_PROF_LIST    TL_AGG_SECTN                1
TL_PROF_LIST    TL_OUTTCD                   5
TL_PROF_LIST    TL_PUB_TM1                  5
TL_PROF_LIST    TL_SCHHRSRPT                5
TL_PROF_LIST    TL_SCHRES_AE                9
TL_PROF_LIST    TL_ST_LIB                   5
TL_PROF_LIST    TL_TIMEADMIN               10
TL_PROF_LIST    TL_TRPROFILE               10

So, across all the programs 50 temporary tables are required.

SELECT * FROM pstemptblcntvw 
WHERE  recname = 'TL_PROF_LIST'
/

RECNAME         TEMPTBLINSTANCES
--------------- ----------------
TL_PROF_LIST                  50

The system has three global instances.

SELECT temptblinstances
FROM   psoptions
/

TEMPTBLINSTANCES
----------------
               3

So, Application Designer will build 54 tables based on this record.

• 3 ‘global’ instances for Application Engines without a Process Instance number. The table names will be suffixed with numbers 1 to 3.
• 50 ‘private’ instances for the number of requested instances of the Application Engines. The table names will be suffixed with numbers 4 to 53
• 1 ‘shared’ version without a suffix (in other words the usual name) which is used if there is no instance no already allocated to a process instance.

And this is the top and bottom of the script to build the tables only generated by Application Designer.

CREATE TABLE PS_TL_PROF_LIST (PROCESS_INSTANCE DECIMAL(10) NOT NULL,
EMPLID VARCHAR2(11) NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
START_DT DATE,
END_DT DATE) TABLESPACE TLWORK STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/
…
CREATE TABLE PS_TL_PROF_LIST53 (PROCESS_INSTANCE DECIMAL(10) NOT NULL,
EMPLID VARCHAR2(11) NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
START_DT DATE,
END_DT DATE) TABLESPACE TLWORK STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/

Are there any temporary tables that have not been built but that should be built?

If you increase the number of instances of temporary tables on an Application Engine program then you may need to build the extra temporary tables. This query reports the missing tables.

SELECT r.recname
,      n.n instance
,      c.temptblinstances
     + o.temptblinstances temptblinstances
FROM   pstemptblcntvw c
,      psrecdefn r
,      (SELECT rownum-1 n FROM psrecdefn
        WHERE rownum <= 100) n
,      psoptions o
WHERE  r.recname = c.recname
AND    n.n <= c.temptblinstances+o.temptblinstances
AND NOT EXISTS(
       SELECT 'x'
       FROM   user_tables t
       WHERE  t.table_name = 
            DECODE(r.sqltablename, ' ', 'PS_'||r.recname,
                   r.sqltablename) ||DECODE(n.n,0,'',n.n)
        )
ORDER BY 1,2
/

I dropped PS_TL_PROF_LIST42, but I also increased the number of instance of TL_TIMEADMIN from 10 to 11. So the query reports that instances 42 and 54 of this table are missing, and all the other temporary tables for TL_TIMEADMIN also report a missing instance.

RECNAME           INSTANCE TEMPTBLINSTANCES
--------------- ---------- ----------------
…
TL_PMTCH_TMP1           19               19
TL_PMTCH_TMP2           19               19
TL_PROF_LIST            42               54
TL_PROF_LIST            54               54
TL_PROF_WRK             38               38
TL_PT_FINAL             29               29
…

The remedy is very simple. Application Designer will build a script for just the missing tables.

CREATE TABLE PS_TL_PROF_LIST42 (PROCESS_INSTANCE DECIMAL(10) NOT NULL,
EMPLID VARCHAR2(11) NOT NULL,
EMPL_RCD SMALLINT NOT NULL,
START_DT DATE,
END_DT DATE) TABLESPACE TLWORK STORAGE (INITIAL 40000 NEXT 100000
MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80
/

Are there any temporary tables built that should not be built?

If you have reduced the number of temporary tables, then you may need to drop the excess tables.

This query reports tables that are beyond the number required.

SELECT r.recname
,      n.n instance
,      c.temptblinstances
     + o.temptblinstances temptblinstances
,      t.table_name
FROM   pstemptblcntvw c
,      psrecdefn r
,      (SELECT rownum-1 n FROM psrecfield
        WHERE rownum <= 100
       ) n
,      user_tables t
,      psoptions o
WHERE  r.recname = c.recname
AND    t.table_name = 
          DECODE(r.sqltablename, ' ', 'PS_'||r.recname,
                 r.sqltablename) ||DECODE(n.n,0,'',n.n)
AND    n.n > c.temptblinstances+o.temptblinstances
ORDER BY r.recname, n.n
/

I built the missing tables for the previous example, but then I reduced the number of instances on TL_TIMEADMIN back to 10. Now, the query reports that there is an extra table for each record beyond the number defined.

RECNAME         INSTANCE TEMPTBLINSTANCES TABLE_NAME
------------- -------- ---------------- ------------------
…
TL_PMTCH_TMP1       19               18 PS_TL_PMTCH_TMP119
TL_PMTCH_TMP2       19               18 PS_TL_PMTCH_TMP219
TL_PROF_LIST        54               53 PS_TL_PROF_LIST54
TL_PROF_WRK         38               37 PS_TL_PROF_WRK38
TL_PT_FINAL         29               28 PS_TL_PT_FINAL29
…

The problem is that Application Designer will not generate the DDL to drop any of these tables. If you reduce the number of temporary table instances, you will have to drop these tables yourself. However, now, it is easy to adjust the above query to produce the DDL to drop the tables.

SELECT 'DROP TABLE '||t.table_name||' PURGE;' cmd
FROM   …

The query then produces these commands.

…
DROP TABLE PS_TL_PMTCH_TMP119 PURGE;
DROP TABLE PS_TL_PMTCH_TMP219 PURGE;
DROP TABLE PS_TL_PROF_LIST54 PURGE;
DROP TABLE PS_TL_PROF_WRK38 PURGE;
DROP TABLE PS_TL_PT_FINAL29 PURGE;
…

The scripts in this posting can be downloaded from my website.

Factors Affecting Performance of Concurrent Truncate of Working Storage Tables

A longer version of this posting, with detailed experimental results, is available on my website.

Over the past year, I have seen problems with Local Write Wait in the Oracle database on two different Oracle systems. One occasion was in a PeopleSoft Time and Labour batch process, the other was in a custom PL/SQL process in non-PeopleSoft system.

In both cases, normal tables in the databases were being used for temporary working storage before that data was then written to another table. The content of the working storage tables was then cleared out by periodically truncating them. In order to increase overall batch throughput, several instances of the program were run in parallel. The resulting concurrent truncate operations contended with each other, and the processes did not scale well.

I have written about this subject previously. These problems have prompted me to do some research and testing, I am now able to make definite recommendations.

Oracle Note 334822.1 (which I have also quoted before) provides a good technical description of the database’s internal behaviour. Put simply; truncate (and drop) operations serialise. Only one process can truncate at any one time. If you have multiple concurrent processes all trying to truncate their own working storage tables, you could experience performance problems. Such processes not scale well as the number of concurrent processes increases.

Real Problems

In the case of the non-PeopleSoft PL/SQL process, I was able to recreate the working storage tables as Global Temporary Tables (GTTs) that deleted the rows on commit because the process committed only when each transaction was complete. Local write wait totally disappeared in this case. Temporary objects do not need to be recovered, so this mechanism does not apply to them.

The PeopleSoft scenario involved one of the ‘Time & Labor’ batch processes, TL_TIMEADMIN. However, GTTs cannot easily be introduced into the T&L batches because there are ‘restartable’. Therefore, the contents of temporary working storage tables need to be preserved after the process and its session terminates. This precludes the use of GTTs.

The combination of Local Write Wait and enq: RO - fast object reuse accounted for 31% of the total response time. This is a significant proportion of the total response time.

• local write wait occurs, as the name suggests, when the session is waiting for its own write operations. The RO enqueue is used to protect the buffer cache chain while it is scanned for dirty blocks in an object for the database writer to then write to the data files.

• enq: RO - fast object reuse occurs when a process waits to acquire the RO enqueue, in other words, while somebody else is truncating or dropping an object.

Two factors affect the time for which the RO enqueue is held:

1. The time taken to write the blocks to disk. Processes that are frequently truncating temporary working storage are also doing a lot of DML operations to populate the working storage and other tables. The disks under the data files are going to be busy. If the disk becomes a bottleneck, the duration of the local write wait will certainly increase.
2. The time taken to scan the buffer cache for dirty blocks to be written to disk and flushed from cache. The larger the buffer cache, the longer it will take to find these blocks.

The Metalink note also suggests using a different block size, saying that "a separate buffer pool for temporary tables will also reduce RO enqueue". It is not clear whether it is more important to have a different block size or a separate buffer pool. I wanted to find out which factor was more important.

Tests

I created a simple test to model the behaviour of T&L. I created pairs of simple tables, populated one of each pair, and then repeatedly copied the data back and forth between them, truncating the source after the copy. The test script has evolved into a PL/SQL package procedure, mainly so that the tests could be submitted to and run concurrently by the Oracle job scheduler. There are also procedures to create, populate, and drop the pairs of working storage tables. The scripts can be downloaded from my website.

I was able to run the same controlled test in a variety of scenarios. I have run the tests on Oracle 10.2.0.3 on various platforms with similar results. A detailed set of results are available in the longer version of this document on my website.

General Recommendations

If you have to store temporarily working data in a database table, it is much better to use a Global Temporary Table, although the design of the application may preclude this. It is not possible to do this with data used by restartable Application Engine processes, because the contents of the GTT would be lost when the process terminates.

The Metalink note references unpublished bug 414780 in which a PeopleSoft customer reported this problem, but “they seemed to fix it by changing some PeopleSoft code to implement delete rather than truncate on small temporary tables”. However, my tests show that this probably degraded performance further. The individual delete statements take longer than the truncate operations, and the overall test times increased. Although the truncate operations serialise on the RO enqueue and wait for local writes, this is still better than deleting the data and waiting for the undo information to be written to the redo log. Furthermore, although the truncate operations did not scale well, the delete operations exhibited negative scalability for the same volumes and concurrency. They became bottlenecked on redo log.

• Using a recycle pool of the same block size as the rest of the database was not effective; possibly because these pools use the same LRU latches.
• Using a larger non-default block size improved performance of truncate. The performance with 32Kb blocks was better than with 16Kb.
• Using a larger uniform extent size also improved performance for just the truncate operations and the test as a whole. Fewer, larger extents were involved, and hence less time was spent on CPU and row cache lock. The overall throughput truncate operations degraded as the number of processes increased, although, the throughput of the test as whole did scale.
• The presence or absence of indexes did not have a significant effect on the relative test timings, and does not alter my advice.
• The effect of truncating with the REUSE STORAGE option is less clear cut. There are no waits on row cache lock because the blocks do not have to be cleared out of the buffer cache, but on the other hand more time is spent on local write wait because all the dirty blocks have to be written to disk, hence the RO enqueue is held for longer and more time is spent on enq: RO - fast object reuse. If you are using an AUTOALLOCATE tablespace then you would be better to use REUSE STORAGE option, but generally you would be slightly better to use a larger uniform extent size and not to use the REUSE STORAGE option.

PeopleSoft Recommendations

Over time, PeopleSoft batch processing has moved slightly away from SQR and COBOL. These types of process cannot be restarted, and so tables used for temporary working storage within the process can usually be recreated as Global Temporary Tables. This will produce better performance and scalability that any option that involves retaining the permanent table.

However, we are seeing more processing in PeopleSoft applications done with Application Engine. If restart has been disabled for an Application Engine program, then temporary records can also be rebuilt as Global Temporary Tables, because their contents does not need to be preserved for another session to pick up.

Otherwise, move the temporary records and their indexes to tablespace with a 32Kb block size. The change of assigned tablespace can be managed within Application Designer, and released like any other patch or customisation. A 32Kb buffer cache must be created in the database instance. Sizing this is going to be a trade-off between how much memory can be taken from other activities to cache just working storage tables, and how much physical I/O you are going to have to wait for. Oracle’s Automatic Shared Memory Management is of no assistance here (until Oracle 11g), the KEEP, RECYCLE, and other block size buffer caches must be sized manually (see Oracle Reference Manual for SGA_TARGET).

No change to the application code is required. There is no performance improvement to be obtained by customising the application code, either to add the REUSE STORAGE option to the TRUNCATE TABLE commands, nor to use DELETE commands instead.

Added 4.7.2009:Tests on Oracle 10.2.0.3 have shown that there is an advantage to putting the working storage tables into a Non-ASSM tablespace. ASSM introduces additional blocks to map the storage in a segment. These are also maintained during a truncate. No having to maintain these blocks saved me 25% on the elapsed run time on some T&L batch processes.

Oracle Bug 4224840/4260477

Added 2.4.2009:Unfortunately, nothing is quite as simple as it seems. If you have a transaction that locks more than 4095 rows in a 32Kb block you can encounter block corruption (bug 4224840). The fix/workaround in Oracle 10g (bug 4260477) is that a transaction will fail with this message before the corruption occurs.

There is an excellent explanation of this problem, and a test script to reproduce it, on Hermant's Oracle DBA Blog

ORA-08007: Further changes to this block by this transaction not allowed

This error will not be resolved until Oracle 11g. However, it does not occur with smaller block sizes. The workaround is either to commit more frequently, or to move the table concerned back to a tablespace with a smaller block size. I have run into this with Time & Labor in a particular scenario.