17.10 - SyncScanCacheThr - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Database Utilities

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Configuration
Publication ID
B035-1102-171K
Language
English (United States)

Determines the amount of FSG cache that can be used for synchronized table scans.

SyncScanCacheThr is effective only when DisableSyncScan is set to FALSE.

This setting should be changed only under the direction of Teradata Services personnel.

Field Group

Performance

Valid Range

0 through 100%

When this field is set to zero, Vantage 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 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 access 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. Vantage 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 Services personnel. Make only small changes to the field, and carefully observe the effects on system performance before committing the change on a production system.

Related Information