Hold My Spot Please

The OpenEdge database maintains all the records in the database. It allows fast access to these records by using Indexes. The pointer from the Index Key in the Index to the Record is something called a ROWID. The ROWID holds the Area, Partition, and Physical location of the record on disk. This is a 64bit addressable space that manages the number of records per block as well as the block number and other attributes.

Creating Records

When a record is created, free space is found on either the Record Manager (RM) chain or the Free chain. If the record can fit into an existing block that is on the RM chain, then the record is placed there, the free space in that block is adjusted, and the block may be moved on the RM chain or removed from it altogether. If there is no room on the top end of the RM chain, then a block on the Free chain is converted to a record block, the record is placed in the block and the block will go on the RM chain if there is enough free space remaining on the chain.

Updating Records

When a record is updated, the update happens in place. If the record grows in size, it will consume the remaining space in the existing block if possible. If not, it will find space in a different block, either on the RM or Free chains, then put the remaining part of the record in the new block. This means that a record will be split over multiple blocks. Hence anytime the record is read, it will incur multiple database block reads. The table analysis of the Database Analysis report shows both the record count and the record fragments. If this number is the same, then there are no fragmented records. If the numbers are not the same, then record fragmentation exists. The below snippet from a Database Analysis report shows the fragmentation.

In this example there are 47,118,632 containing 47,121,330 fragments, meaning 2,698 records are fragmented. Not too bad.

When a record is updated and a field that is part of an index changes, then the index key needs to change. There is no mechanism to update an index key, since it’s location in the index B-Tree will likely change. Instead, the original key is deleted, and the new key is created. However, an Index Delete Placeholder may be put in place for the deleted key. More on this in a bit.

Deleting Records

When a record is deleted, the block holding the record and all the blocks that could be holding the record fragments get deleted. However, the record location in the block is not freed up, but instead, the transaction id is stored in that location. The reason for this is to prevent a new record create from taking that exact spot. If for some reason the transaction that deleted the record was UNDONE, then the ROWID of that record needs to be preserved as all the index keys that point to that record need to be intact, as well as some application depend on the ROWID not changing. If the transaction completes successfully, sometime later this record delete placeholder is removed.

For any UNIQUE index for that table, a similar Index Delete Placeholder is put in place. This is required not to keep the ROWID intact, but because the index is UNIQUE, a similar key cannot be created in that index, because a transaction undo would not allow this key to be returned. This only happens for UNIQUE indexes.

The Deleted Record Placeholder is not too big of a deal, mostly because a data block typically holds 30 – 60 records. However, the Index Delete Placeholder can be a much larger issue on performance since they are sorted, meaning if a mass delete took place, there could be hundreds of index blocks that hold nothing but Index Delete Placeholders, and each Index block can hold thousands of keys, so the chances of coming across an Index Delete Placeholder is much higher.

Cleaning Up Placeholders

In both cases, the placeholders are fixed only when that block is going to be updated. Reading the record, which reads the key, will not remove either of these placeholders.

Given that the Index Delete Placeholder can have negative performance consequences, an easy way to remove them is to perform an Index Compact. For more information about Index Compacting see this previous blog here.

Conclusion

The OpenEdge database does a lot behind the scenes that you may not know about or even care, however sometimes maintenance needs to be done to keep the system running at peak performance. Having people like the DBAs at OmegaServe know all these details so you don’t have to.

OpenEdge System Health Check

Is your Progress OpenEdge database running optimally? Could there be an unknown issue that could potentially damage or bring down your production database?