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.

PeopleSoft's PSADMIN Role

Recently, I have had a number of conversations about the privileges that are granted to the PeopleSoft OWNERID account (usually SYSADM) via the PSADMIN role.

A posting on the PeopleSoft DBA Forum, referenced Tanel Poder's blog entry: Oracle Security: All your DBAs are SYSDBAs and can have full OS access. Essentially, there is a security bug in Oracle where users with IMP_FULL_DATABASE and BECOME_USER can gain SYSDBA access. This hole has been closed up a patch delivered in the July 2008 Critical Patch Update.

The PSADMIN role has both of these privileges, as well as another 22 that are not required for normal operation of a PeopleSoft system.

There are two morals to this story:

1. There are good reasons to keep up to date with Oracle's CPUs.
2. PSADMIN has privileges that if given to application accounts could compromise database security.
   

I think that PSADMIN could be stripped down to just the following 8 privileges:

• ANALYZE ANY
• ALTER SESSION
• CREATE SESSION
• CREATE TABLE
• CREATE TRIGGER
• CREATE_VIEW
• SELECT_CATALOG_ROLE
• ALTER USER

You only need CREATE USER when creating the PeopleSoft database with the Data Mover import. SELECT_CATALOG_ROLE is not in the delivered PSADMIN, but is required if you remove IMP_FULL_DATABASE and EXP_FULL_DATABASE.

Each privilege is discussed in Chapter 3 of PeopleSoft for the Oracle DBA.

Poor performance of PSPMSESSIONS_VW view affects Performance Monitor System Monitor Component

The System Performance Monitor component (PSPMSYSHEALTH; navigation: PeopleTools -> Performance Monitor -> System Performance) gives an overview of each system monitored by the PeopleSoft Performance Monitor. However, the poor performance of the view PSPMSESSIONS_VW can severely affect this component, to the extent that as transaction history builds up the component will not respond within the timeout.

Below is an extract from a PeopleTools trace. What happened is that the query ran until the Tuxedo service timed out. Tuxedo terminated application server process 31944, and spawned a new process (ID 3598). The user session received an error.

PSAPPSRV.31944 (70)   1-783    11.38.38    0.000072
Cur#1.31944.PMONITOR RC=0 Dur=0.000040 COM
Stmt=select count(*) from pspmsessions_vw where pm_agentid = :1
PSAPPSRV.31944 (70)   1-784    11.38.38    0.000008
Cur#1.31944.PMONITOR RC=0 Dur=0.000001 
Bind-1 type=19 length=3 value=689
PSAPPSRV.3598 (112)   1-132    11.42.39  314.495972
Cur#1.3598.PMONITOR RC=0 Dur=0.000095 COM
Stmt=SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'SYS'

Even when each query finishes within a reasonable amount of time, the query can be run several times by the component.

This is the definition of PSPMSESSIONS_VW delivered by PeopleSoft.

SELECT T3.PM_CONTEXT_VALUE1
,      T3.PM_AGENTID
,      T3.PM_AGENT_STRT_DTTM
FROM   PSPMTRANSHIST T3
WHERE  T3.PM_TRANS_DEFN_ID = 116
AND    PM_TRANS_STATUS = '1'
AND    PM_TRANS_DURATION <> 0
AND    T3.PM_CONTEXT_VALUE1 IN (
SELECT T.PM_CONTEXT_VALUE1
FROM   PSPMTRANSHIST T
WHERE  T.PM_TRANS_DEFN_SET = 1
AND    T.PM_TRANS_DEFN_ID = 109
AND    T.PM_MON_STRT_DTTM > %TimeAdd(%CurrentDateTimeIn, -720)
AND    T.PM_PARENT_INST_ID <> PM_TOP_INST_ID
AND    T.PM_CONTEXT_VALUE1 NOT IN (
 SELECT T2.PM_CONTEXT_VALUE1
 FROM   PSPMTRANSHIST T2
 WHERE  T2.PM_TRANS_DEFN_SET = 1
 AND    T2.PM_TRANS_DEFN_ID = 108
 AND    T2.PM_CONTEXT_VALUE1 = T.PM_CONTEXT_VALUE1
 AND    T2.PM_MON_STRT_DTTM > %TimeAdd(%CurrentDateTimeIn, -720)))

Firstly, accurate object statistics are required on the transaction history table and its indexes.

An additional index is required on PSPMTRANSHIST:

CREATE INDEX PSPPSPMTRANSHIST ON PSPMTRANSHIST
(PM_TRANS_DEFN_SET
,PM_TRANS_DEFN_ID
,PM_CONTEXT_VALUE1
,PM_MON_STRT_DTTM)
TABLESPACE PSINDEX PCTFREE 0
PARALLEL NOLOGGING COMPRESS 3
/
ALTER INDEX PSPPSPMTRANSHIST NOPARALLEL LOGGING
/

Finally, I have changed the view.

• The IN() operators have been changed to WHERE EXISTS(). The new index supports the efficient execution of these sub-queries.
• The sub-queries are now both correlated back to the main query on T3.
• The ROWNUM criteria have been added to restrict the number of rows the sub-queries can return.

SELECT T3.PM_CONTEXT_VALUE1 /*Session ID*/
,      T3.PM_AGENTID
,      T3.PM_AGENT_STRT_DTTM
FROM   PSPMTRANSHIST T3
WHERE  T3.PM_TRANS_DEFN_ID = 116 /*Redirect after login*/
AND    T3.PM_TRANS_DEFN_SET = 1 /*added*/
AND    T3.PM_TRANS_STATUS = '1'
AND    T3.PM_TRANS_DURATION <> 0
AND EXISTS(
SELECT 'x'
FROM   PSPMTRANSHIST T
WHERE  T.PM_TRANS_DEFN_SET = 1
AND    T.PM_TRANS_DEFN_ID = 109 /*User Session Began*/
AND    T.PM_MON_STRT_DTTM > %TimeAdd(%CurrentDateTimeIn, -720)
AND    T.PM_PARENT_INST_ID <> T.PM_TOP_INST_ID
AND    T.PM_CONTEXT_VALUE1 = T3.PM_CONTEXT_VALUE1
AND NOT EXISTS(
 SELECT 'x'
 FROM   PSPMTRANSHIST T2
 WHERE  T2.PM_TRANS_DEFN_SET = 1
 AND    T2.PM_TRANS_DEFN_ID = 108 /*User Session Ended*/
 AND    T2.PM_MON_STRT_DTTM > %TimeAdd(%CurrentDateTimeIn, -720)
 AND    T2.PM_CONTEXT_VALUE1 = T3.PM_CONTEXT_VALUE1
 AND    ROWNUM <= 1)
 AND    ROWNUM <= 1)

The effect of these changes is a significant improvement in the performance of the query. I ran a test query for a single agent on a system with over 3 million rows on PSPMTRANSHIST:

• Original: 24895 consistent gets
  
• New Index: 22547 consistent gets
  
• and View Changes: 85 consistent gets

Your mileage may vary, but for me this made the difference between the component being usable and not.

How to Clear the Application Server Cache Without Shutting it Down

Updated 4.9.2008: It is often necessary to clear the physical cache files on the PeopleSoft application server. You would think that after all the length of time that PeopleTools has been around, that development would have sorted out the problems in object version numbering. Sometimes, recently migrated changes do not take effect until the cache is cleared. Global Support will nearly always ask you to clear the cache if you are experiencing any kind of problem in the PIA. Normally, you would shut down the application server, delete the physical cache directories and restart the application server. From PeopleTools 8.48, each Application server process can only use one dedicated set of physical cache files that is in a directory whose name includes the name and ID number of the server, (previously the server process could use any unlock set cache directories). This allowed PeopleSoft to add an option to the psadmin utility to trigger each PSAPPSRV process to clear out its own physical cache (my thanks to the anonymous question asked after the original version of this posting that reminded me). This option also causes each server process to be recycled. It is the recommended and supported way to clear the physical cache files. This can also be invoked from the command line

psadmin -c purge -d <domain> [-noarch | -arch <archive_directory>] [-log <"log_comments">]
where 'domain' specifies domain name in PS_HOME and
   'archive_directory' specifies location to which to quarantine the purged cache,
   'log_comments' specifies any comments to be added to the purge cache log entry

However, even in previous versions, there has always been a way to invalidate all physical cache files. Any cached object older than the value of LASTREFRESHDTTM on the table PSSTATUS (it was on a different table prior to PeopleTools 8) is purged from the cache when the process that references that cache starts. Therefore, if that value is updated to the current time, the entire cache will be purged.

UPDATE PSSTATUS
SET    LASTREFRESHDTTM = SYSDATE
/
COMMIT
/

Sometimes, developers also have to clear the physical cache on their clients used by the Application Designer. Updating PSSTATUS also clears two-tier client caches. In fact, this behaviour is left over from the days when PeopleTools was a two-tier application, and it was necessary to clear cache files on users' desktop computers.

• If you have multiple Application Servers on a single database, then you can shut each one down in turn without any loss of server. The users will fail over to the surviving servers. However, this can result in the load being unevenly distributed and could overload one server.
• In small environments, and this includes most development and test systems, there is only a single application server. Shutting down an application server requires downtime and can be disruptive.

If an application server has at least two instances of a server process then each process can be recycled one by one, and there is no loss of service because the other processes can handle incoming requests. You could do this manually by entering the stop and boot commands in tmadmin (I discussed this in Chapter 13 of PeopleSoft for the Oracle DBA, but didn't combine it with the idea of clearing the Application Server cache). I have produced a script, tuxcycle.sh (available from GitHub) that uses the Tuxedo command line interface utility, tmadmin, to find out which PSAPPSRV processes are running, and then sequentially recycles each of them. I don't think I would completely automate these two operations into a single process, nor would I run them unattended, but you can now clear cache files without taking a system away from users or developers. Remember, that users will experience a reduction in performance after clearing the cache because it needs to be rebuilt by querying the PeopleTools table. The Process Scheduler, Master Process Scheduler, Distribution Server, and Application Engine processes also have physical caches. These are also cleared when LASTREFRESHDTTM is updated and the processes restarted. However, you should not shut down the Process Scheduler while processes are executing. The server process will shut down and the Application Engine will be killed if using an Application Engine server process (PSAESRV). Any stand-alone processes, including psae, will be killed by the Process Scheduler when it is restarted.

Serious fault with PeopleSoft Performance Monitor fixed in PeopleTools 8.49.14

Regular readers of my ramblings will know that I spend a lot of my time working with the PeopleSoft Performance Monitor (PPM). It is an extremely valuable source of performance metrics that are mostly not available, or if they are just difficult to obtain and process.

In PeopleTools 8.49, PPM has a serious fault. It is very similar to scenario reported for PeopleTools 8.44 in GSC Solution 200769188: PerfMon: Too many httpd processes spawned/sockets opened when using PerfMon.

"When a 404 is returned from the monitor server the calling agent code only checks the HTTP return code but does not flush the input stream. This causes the connection to remain open until the GC finally closes the input steam which closes the open connection. This is easily detected by the increasing list of sockets in CLOSE_WAIT state [, as reported by netstat]. On NT there is no hard limit on file descriptors and therefore a crash is not noticed. On Unix platforms this can cause the process to run out of file descriptors before the JVM GC has a chance to run and free up the sockets."

Hence, when running the application server on Unix, you will reach a point where JSH processes cannot open new ports and PIA users can receive the 'Application Server is Down' message. So, this can cause the system to become unavailable until the application server is rebooted. To prevent this occurring, the PPM must be deconfigured. The Monitor URL should be set to 'NONE'.

According to PeopleSoft Global Support this problem will be resolved in PeopleTools 8.49 patch 14.