PPICacheThrP DBS Control Parameter
The PPICacheThrP performance parameter of the DBS Control utility (see Utilities) specifies the percentage value the system uses to calculate the cache threshold used in operations dealing with multiple partitions. The value is specified in units of 0.10% and can range between 0 - 500 (0% and 50.0%).
Note: Cache thresholds can also be controlled by the Optimizer cost profile parameter constant named PPICacheThrP. If the parameter is set, it overrides the setting for the PPICacheThrP DBS Control parameter. Only technical support personnel can change the cost profile settings for your system.
PPICacheThrP also specifies the percentage value to use for calculating the number of memory segments that can be allocated to buffer the appending of column partition values to column partitions for column‑partitioned tables.
The sum of the sizes of this memory minus some overhead divided by the size of a column partition context determines the number of available column partition contexts. If there are more column partitions in a target column‑partitioned table than available column partition contexts, multiple passes over the source rows are required to process a set of column partitions where the number of column partitions in each set equals the number of available column partition contexts. In this case, there is only one file context open, but each column partition context allocates buffers in memory.
The Optimizer also uses the DBS Control parameter PPICacheThrP to determine the number of available file contexts that can be used at a time to access a partitioned table.
|
IF PPICacheThrP determines the number of available file contexts to be … |
THEN Teradata Database considers this many file contexts to be available … |
|
< 8 |
8 |
|
> 256 |
256 |
Teradata Database uses the number of file contexts as the number of available column partition contexts for a column-partitioned table.
Note: Teradata Database might associate a file context with each column partition context for some operations, and in other cases it might allocate a buffer with each column partition context.
Ideally, the number of column partition contexts should be at least equal to the number of column partitions that need to be accessed by a request. Otherwise, performance can degrade because not all of the needed column partitions can be read at one time.
Performance and memory usage can be impacted if PPICacheThrP is set too high, which can lead to memory thrashing or a system crash. At the same time, the benefits of partitioning can be lessened if the value for the DBS Control parameter PPICacheThrP is set unnecessarily low, causing performance to degrade significantly.
The default is expected to be applicable to most workloads, but you might need to make adjustments to get the best balance.
Note: The DBS Control setting for PPICacheThrP is overridden by the Optimizer cost profile constant PPICacheThrP if that parameter is set in an applicable cost profile. Only technical support personnel can change the cost profile settings for your system.
The PPI Cache Threshold (PCT) is defined as follows.
For byte‑packed format systems, or byte‑aligned format systems where file system cache per AMP is less than 100 MB.
where:
|
Equation element … |
Specifies the … |
|
(∑ file_system_cache) |
total size of file system cache per AMP. |
|
PPICacheThrP |
cache threshold percentage specified by the PPICacheThrP parameter. |
For 64‑bit systems where the file system cache per AMP is greater than 100 MB.
where:
|
Equation element … |
Specifies the … |
|
PPICacheThrP |
cache threshold percentage specified by the PPICacheThrP parameter. |
The default is 10, which represents 1.0%. Increasing the PCT to a higher percentage is likely to cause memory contention problems. Never change this value without first performing a thorough analysis of the impact of the change on your query workloads.
When performing operations on partitioned tables, Teradata Database processes a subset of populated, non‑eliminated partitions together instead of handling them one at a time. Teradata Database maintains a context that defines the current position within a partition for each partition being processed. In the case of multilevel partitioning, the term partition refers to a combined partition.