| DATABASE ADMINISTRATION |
REMOTE DBA
HOT-STANDBY
DISASTER RECOVERY
DBMS SPAM Thresholds (DBMS only)
TECHNICAL
ARTICLES -> PERFORMANCE
ARTICLES
Q: |
We have a very
heavily used MANMAN database, and would like to set the database
Space Area Management (SPAM) thresholds optimally. Should thresholds
be adjusted monthly or should we just stick to the default threshold
values, and do they really make much difference?
|
A: |
Optimal threshold values should be set when the
database is created, and modified only when there is a change to the
storage schema or the page size of a storage area. Anyone that
recommends setting SPAM thresholds more frequently lacks full
understanding of how DBMS implements SPAMs!
Default SPAM thresholds are definitely not optimal. By understanding how DBMS searches for free space when storing new records, you can prove mathematically that any threshold over 89% does not provide any benefit. Since the default thresholds are 70, 85 and 95% there is definitely room for improvement! Also, any utility or tool that recommends SPAM thresholds above 89% should be looked upon with suspicion. The free space search algorithm used by DBMS takes into consideration only static data structures. Therefore, there is no need to calculate thresholds on a monthly basis. The actual or average record length, as reported in the DBO/ANALYZE report has no bearing on optimal threshold values, and should not be used for setting SPAM thresholds. The only data structures that DBMS considers when looking for free space are:
Space Management (SPAM) technology was designed to improve the storage performance of nearly full storage areas and areas where large ranges of full pages. The overall impact of SPAM thresholds on database performance depends on the rate in which new records are stored into the database, and the fullness of the storage area in which records are being stored. An area which contains a lookup table (where records are seldom added once loaded) will probably not benefit much from thresholds. Likewise, an area with plenty of free space generally will not benefit much from thresholds. If you store a record in an empty storage area, it is bound to find a page with sufficient free space. Before even considering SPAM thresholds, DBMS will store a new record on any page in the target buffer that contains sufficient free space –- as long is it is not at the T3 threshold. Assume that your page size is 2 blocks per page and your buffer length is 20. DBMS will scan 10 pages (all pages in the target buffer) before using the SPAMs! Optimal SPAM thresholds have a dramatic impact on heavily updated storage areas that have large ranges of full or nearly-full pages. |