Database engine does not reuse space when creating, deleting or update records

Database engine does not reuse space when creating, deleting or update records
Space not being re-used for RAW fields storing XML documents
DBANALYS shows that Average Record size multiplied by Total records should occupy circa. 2GB of space.
PROSTRCT STATISTICS shows physical storage being used of 6 GB
PROSTRCT STATISTICS shows Storage Area is growing by around 600MB a week
Dump and load does not improve the situation
No difference in loading to a Type I or Type II Storage Area architecture
TABANALYS shows no real increase in overall data for this Area but very high scatter factor 7
Table-history is that records are imported in batches then deleted once processed
Only one Table resides in the Storage Area

 

IDXBUILD for associated index area succeeds without error
RM and Free chain rebuilt without error.
No corrupted blocks on the RM chain
No Empty blocks below the high water mark
CHANALYS not showing 3910 and 6704 errors.

Record sizes range from 30 bytes (empty record) to up to 8 KB. Although we expect fragmentation (due to record sizes) the fact that scatter is continually increasing in an area that only contains one table, is a hint that space from the deletes isn't being re-used.

With such a disparate range in record sizes, the space allocation algorithm needs to balance performance with occupied space. With the default Toss Limit set, this allowed for better packing of the smaller records. However, this has the adverse side effect of making the RM chains longer until further smaller records are added. Longer RM chains means longer searching for space for the big records which in this case exhausts the number of times the database engine searches thereyby taking EMPTY blocks from above the High-Water Mark (HWM)

The following changes improved the expansion rate in this Storage Area dramatically:
(From a growth of 600MB to 4 MB per week)

• Increasing -recspacesearchdepth to 10, stabilized the HWM.
• Decrease the Storage Area Create Limit to 32. Since a characteristics of this table is that records don't grow, this ensures maximum block size availability (marginal effect).
• Increase the Storage Area Toss Limit to a little below the average record size. This in effect reduces the records per block to 1 since the average record size in this case is 5 KB, while still allowing a higher rpb (32) to allow for the smaller records.

The above provides an example of a specific case investigation. It is important to take prostrct statistics and proutil -C dbanalys snapshots prior and post each database structure change for further impact evaluation.

Progress Articles: 

How to optimize the database block toss and create limits ?   
What is RecSpaceSearchDepth?  
 Type II Storage Area - space allocation issue with creates and updates.  
 Type II storage area growing rapidly due to corruption on the RM chain  
 VST reports are not showing any change in number of RM Blocks when blobs in a Type II area are deleted.