HomeSearchSite MapContact Us RemoteDBA Services for CODASYL DBMS and RdbPreserving Mission Critical IT Applications Through Virtualization

Remote Management of OpenVMS SystemsExtreme Performance Consulting


DATABASE
ADMINISTRATION

DBMS SPAM Thresholds (DBMS only)
TECHNICAL ARTICLES -> PERFORMANCE ARTICLES
Back ] Next ]

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:

  • 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.

Top


If you have a technical question about this article or about any other 
CODASYL DBMS or Rdb topic, then ask our experts.

How would you rate this article?

5 (Highest)
4
3
2

0 (Did not apply)
Comments about this article or topic suggestions for new articles

Please type the word above:

Copyright 2016 Software Concepts International
All Rights Reserved