Month: July 2009

Automatic Segment Space -v- Freelist Management for PeopleSoft Temporary Records

Earlier this year, I wrote about some research into the effects of concurrent TRUNCATE TABLE operations in concurrent PeopleSoft Application Engine process. Since then I have been prompted to look at the effect of Automatic Segment Space -v- Freelist (or Manual) Management.

ASSM was introduced in Oracle 9i for Locally Managed Tablespaces, and from Oracle 10g it is default option, It was designed for high concurrency systems, and avoids some of the problems, such as contention on header blocks, that can occur with Freelist Management. It uses tracking blocks to indicate utilisation of data blocks. The ASSM structure is similar to a B-tree index (see Tanel Poder’s presentation: Freelists -v- ASSM in Oracle 9i).

Normally, using ASSM is preferable in multi-user OLTP systems. The reduction in contention on the freelists should outweigh the additional I/O for the tracking blocks.

However, non-shared instances of PeopleSoft Temporary Records are only used by a single Application Engine process at any one time. Therefore, there would be no contention on freelists for these objects, and so DML operations on these objects will not derive much benefit from ASSM. In fact, the contrary could easily be true. The additional tracking blocks are also maintained during DML and are cleared out by local writes during a truncate operation. Truncate operations are serialised in Oracle on the RO enqueue. If you run multiple concurrent Application Engine programs you can get contention between the truncate operations. The additional I/O on the ASSM tracking blocks causes the truncate to take longer and in extreme cases can aggravate truncate contention.

The local write operations that occur during truncate operations cannot be deferred. Significant truncate contention can be a symptom of disk contention. However, switching back to Freelist Management saves the I/O to the tracking blocks, and so improves the performance of truncate.

I ran a test where I truncated a table with 5000 rows. I examined an Oracle trace with waits of the truncate. I tested it in an ASSM tablespace, and Freelist Managed tablespace. I tested truncating just the table on its own, and sometimes with an index.

Number of Wait Events

Table only, without any indexes

With a single primary key index

Tablespace Type db file sequential read enq: RO – fast object reuse local writes db file sequential read enq: RO – fast object reuse local writes
Automatic Segment Space Management

8

1

4

16

2

9

Freelist Management

3

1

1

6

2

3

You can see from this table that the truncates in the ASSM tablespace required more single block reads (db_file_sequential_read) and more local writes.

Recommendation

In addition to my previous recommendation to move the all (non-shared instances) of temporary working storage tables, and their indexes, to tablespaces with a larger block size, I also recommend that those tablespaces should be specified with MANUAL segment space management.

$deleted$ tablespace names bug

This one turned out to be a an interesting bug the other day… I did a simple select from DBA_TAB_PARTITIONS and noticed that some tablespace_names were of the form “_$deleted$n$m” where n and m are numbers. Slightly worrying, but at least the data was all present and correct, when I checked. I knew the DBA team had been doing some reorganisations the previous weekend, to recover some space, so I wondered if that was connected….it […]

my week in venn diagram form …

(from thisisindexed.com)My take on this one has nothing to do with Bernie and everything to do with recent events on the big project. If I could add a small red ‘x’ in the middle of ‘Doom’ with an arrow and a ‘You are Here’ message, it would be perfect …

my week in venn diagram form …

(from thisisindexed.com)My take on this one has nothing to do with Bernie and everything to do with recent events on the big project. If I could add a small red ‘x’ in the middle of ‘Doom’ with an arrow and a ‘You are Here’ message, it would be perfect …

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569