Purpose
Determines the amount of FSG cache that can be used for synchronized table scans.
SyncScanCacheThr is effective only when DisableSyncScan is set to FALSE.
Field Group
Performance
Valid Range
0 through 100%
When this field is set to zero, Teradata Database uses the default value for the field.
Default
10%.
Changes Take Effect
After the DBS Control Record has been written.
Usage Notes
The synchronized full table scan feature of Teradata Database allows several table scanning tasks to simultaneously access the portion of a large table that is currently in the cache. Synchronized table scans happen only when full table scans are accessing similar areas of a large table. Synchronized table scans can improve database performance by reducing the required disk I/O. Synchronized table scans are available only to large tables undergoing full table scans.
Full table scans of large tables can fill up the cache quickly, flushing data from smaller reference tables out of the cache too soon, or preventing such data from being cached at all. Because it is unlikely that the cached data from these large tables will be accessed again before the data has been replaced in the cache, the benefits of caching are normally not realized for full table scans of large tables. Therefore, large tables are normally excluded from the cache. The DBSCacheThr setting demarcates "small" from "large" tables for purposes of most system caching decisions.
However, if several tasks are scanning the same large table, efficiencies can be realized by caching a portions of the table, and allowing several scans to access the cached data simultaneously. When synchronized scanning is enabled, a portion of the cache can be used for synchronized scanning of large tables that might otherwise be excluded from the cache. Teradata Database determines which tables qualify for potential synchronized scanning.
The relative benefits of synchronized large table scans versus allowing more of the cache to be used for small reference tables depends on the specific mix of work types on the system, and may change with time. Cache in use for synchronized scans is not available for caching frequently accessed data from small reference tables. Therefore, Teradata recommends changing SyncScanCacheThr only if you are directed to do so by Teradata Support personnel. Make only small changes to the field, and carefully observe the effects on system performance before committing the change on a production system.