| DATABASE ADMINISTRATION |
REMOTE DBA
HOT-STANDBY
DISASTER RECOVERY
Prior Pointers Omitted (DBMS only)
TECHNICAL
ARTICLES -> PERFORMANCE
ARTICLES
Q: |
Tell me about prior
pointers omitted.
|
A: |
We discovered the
down side of omitting prior pointers at a client site running MANMAN.
One of the Data Definition Language (DDL) SET
options of the storage schema design is to OMIT PRIOR pointers. This
reduces the size of the pointer clusters by storing only the next
and owner pointer, and may reduce locking during set inserts
(CONNECTs).
While there are some benefits to omitting prior pointers, extreme caution should be used when choosing this option. When prior pointers are omitted, it is still possible to "FIND PRIOR" within the set. However, to do this, the DBCS must find the owner of the set and walk the set in the next direction until the "prior" record is found. If the "prior" record is the first record in the set, then the search completes quickly. If the "prior" record is the last record in the set, then the length of the search depends on the number of records in the set. FIND/FETCH PRIOR is not the only verb which is negatively impacted when prior pointers are omitted. When removing records from a set (DISCONNECT), the DBCS must search for the prior record in the set to modify its next pointer within the set-type being disconnected. Again, to find the prior record, the DBCS must find the owner record and then sequentially walk the set in the next direction until the prior record is found. An even less obvious problem can occur when prior pointers are omitted from CALC sets. As it turns out, in versions of DBMS before v5.1, CALC sets were searched in the prior direction – with a bad twist – the actual search process worked as follows:
Using this algorithm, if a CALC set contains 500 members, it would take 500 "searches" to find the 500th member, but 130,300 to find the first! In a healthy CALC (hashed) set environment (one with many SYSTEM owners distributed throughout the area and few members per owner), the impact of this would be minimal. However, at one of our client sites running ASK MANMAN, their database creation routine created the database area with an initial allocation of 1 and then extended the area to its full size. This left the calc range for that area at 1, forcing all members of CALC sets within this area to share the same SYSTEM owner. Even though the area was small enough to cache all 5,000 records into database buffers (memory), a job to perform only a few hundred updates ran for over 24 hours – using over 24 hours of CPU time! To solve their immediate problem, we performed a DBO/MODIFY/RECALC on this one area, and the job completed in only a few minutes. To solve their long-term problem, we fixed their creation routine and modified their storage schema so that this set would contain prior pointers and then reorganized their database. Again, this problem has been fixed in version 5.1. |