Three enhancements related to disk writes give DBQL users better performance and increased flexibility:
- The DBQL cache size is now adjustable from 64 KB to 16 MB, allowing customers to decide how much data the cache holds before writing to the DBQL Data Dictionary tables.
- The default DBQL table cache size increases from 64 KB to 2 MB.
- The default block size increases from 64 KB to 1 MB. This value is not tunable.
To adjust the DBQL cache size, change the value for the DBS Control parameter DBQLDefCacheSize and restart Teradata Database.
Benefits
- Reduces internal DBQL transactions, cutting overhead and improving performance.
- Reduces the resource contention caused by heavy user workloads and detailed query logging.
- Adapts to different workloads; for example, a larger cache reduces the frequency of cache flushes for high-frequency tactical workloads.
- Reduces the frequency of physical writes and the I/O cost associated with those writes.
Considerations
- The default cache size for upgraded systems remains at 64 KB. The default cache size for newly initialized systems is 2 MB.
- DBAs must flush all DBQL caches before changing the default cache size. Otherwise, the cache contents will be lost during the required restart.
- Although 2 MB is the optimal cache size in most cases, consider increasing this if detailed query logging is enabled and queries experience resource contention.
- The DBQLParamTbl has LOB columns, so that cache is controlled by the DBS Control field DBQLLOBCacheSize, which has not changed.
Additional Information
For more information, see Teradata Vantage™ - Database Administration, B035-1093 and Teradata Vantage™ - Database Utilities , B035-1102 .