High orphan counts in indexes causing performance degradation
Article ID: 27412
Updated On:
Products
Issue/Introduction
The high orphan counts which are the result of SR8 splits in rapidly growing system-owned linked indexes cause increasing performance degradation during erase, disconnect, and purge. What techniques can be used to minimize this problem?
Environment
Release: All supported releases.
Resolution
It is common for a database to be updated with a large number of new index members on a daily basis, but only purged of unneeded members on a weekly or monthly basis. It is typically the erasing or disconnecting of members, either on an individual basis by online applications or by a periodic batch "purge" job where the performance degradation is observed.
Rebuilding the index provides relief but the problem usually returns very quickly.
This occurs in system-owned linked indexes due to repeated splitting of index SR8 records which causes high "orphan counts". When an SR8 contains the maximum number of entries and a new member targets to that SR8, it must split.
The "Rebuilding for growth" technique may significantly delay splitting for many SR8s in the index thus reducing the performance degradation when disconnecting or erasing members from the index.
NOTE: This technique only works for indexes that grow in a random manner, as in indexes whose new members' sort-key values target to many SR8s across the entire range of the index, rather than to one end of the index or to a few "hot spots" due to very similar sort-key values.
For example, for an index where the sort-key is the current date, all new members will target to one end of the index. Such an index will not benefit from this technique.
The technique works as follows:
Best practices for sizing a system-owned index are to select page size and block contains values such that you get four or more SR8s per page and use as much of the space on the page as possible.
Rebuilding for growth uses a combination of page reserve and a smaller block contains value than the one used for normal processing, (i.e. the one chosen during sizing). This results in SR8s that are able to handle a certain number of new members without splitting.
The page reserve is space that can be used for the SR8s to expand but will prevent the storing of new SR8s.
Example
An index has a 15-byte sort-key, it is stored in its own area of page size 13680 and a block contains value of 63 has been chosen (each SR8 can contain a maximum of 63 entries).
These values will require 1512 bytes to store one full sized SR8.
The pages can each contain 9 full sized SR8s with very little wasted space.
To rebuild for growth, run the MAINTAIN INDEX utility with a DMCL and subschema that specify a block contains value of 50 and a page reserve value of 2700 bytes.
With a block contains value of 50, a full sized SR8 will require 1216 bytes. By reserving 2700 bytes on each page, it should accommodate 9 SR8s per page, each of which will be able to handle 13 new entries before splitting.
The DMCL and subschema used for normal processing will have the page reserve 2700 defined, but block contains will be 63.
NOTE: Be very careful when defining page reserve. It must be sufficient for this purpose but small enough so that the pages can get to be over 70% full.
Also, the TUNE INDEX utility can be run batch local or under CV and will adopt all orphans. Running this utility on a regular basis is good for performance.
Information to analyze an index's structure can come from a number of different utilities:
Additional Information
Feedback
