|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!
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
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:
- MAX_FREE_LEN - based on page size and whether
SNAPSHOTS are allowed.
- PAGE_PAD_LEN - based on page size.
- STATIC_RECORD_LEN-the sum of the lengths of the static
data items as defined in the storage schema.
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.