16.10 - Memory Limitations and Partitioning - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

Error Messages for Memory Limitations for Partitioning

You might encounter several different memory limitations when working with partitioned tables. The following table documents the relevant error messages.

Message Number Problem Possible Cause Remedy
3708 Table header size limit exceeded Too many columns in the table. If possible, reduce the number of columns in the table.
Too many distinct values compressed in the table. If possible, reduce the number of compressed values.
Partitioning expressions are too complex. Simplify the partitioning expressions or reduce their number.

If possible, use RANGE_N instead of CASE_N.

3710 Parser memory size limit exceeded.

The limit is variable and is determined by the value of the MaxParseTreeSegs field in DBS Control.

Current values for DBS Control parameters do not allocate enough parser memory to process the request. Change the values for the following DBS Control fields to the specified settings:
  • If the problem occurs with partitioned tables that do not have either hash or join indexes, change the value for MaxParseTreeSegs to this value.

    2000 for byte-packed format systems

    4000 for byte-aligned format systems

  • If the problem occurs with partitioned tables that have either hash indexes or join indexes or both, change the value for MaxParseTreeSegs to this value.

    2000 for byte-packed format systems

    4000 for byte-aligned format systems

    and contact Teradata Support.

You might find that your query workloads that require these values need to be increased still further.

See Utilities for information about how to change the value for MaxParseTreeSegs in the DBS Control record.

Parser memory size limit exceeded. Partitioning expressions are too complex. Simplify the partitioning expressions or reduce their number.

If possible, use RANGE_N instead of CASE_N.

3891 Check if the partitioning CHECK constraint text size limit is exceeded.

The partitioning CHECK constraint text is derived from the partitioning expressions for the partitioning.

Text for the partitioning expressions in the partitioning definition is too long. Reduce the partitioning constraint text to 16,000 or fewer characters.

30 characters of the 16,000 character limit for table constraints, partitioning constraints, and named constraints apply to the following constraint for a single-level partitioning: CHECK ((partitioning_expression) BETWEEN 1 AND 65535). This defines the limit for the partitioning expression text to be 16,000 - 30 = 15,970 characters.

For multilevel partitioning, with a minimum partitioning constraint of CHECK (/*nn/* partitioning_expression_1 IS NOT NULL AND partitioning_expression_2 IS NOT NULL), the constraint is a minimum of 48 characters in length, so the minimum limit for the partitioning expression text is 16,000 - 48 = 15,952. Each additional partition beyond 2 subtracts another 19 characters from the minimum of 15,952.

3930 The dictionary cache is full. Dictionary cache is too small. Increase size of dictionary cache to 1 MB.

Because the default size of the dictionary cache is 1 MB, the only reason you might need to do this is if the DBA has reduced the size of the dictionary cache to something less than its default.

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%).

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.

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.

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.

How Teradata Database Controls Memory Usage by Partitions

The system associates each data block that contains a non-eliminated partition with each context. Whenever possible, the set of data blocks containing the partitions being processed is kept in memory to improve its processing.

If enough memory is not available to contain all the relevant data blocks, some of those blocks must be swapped to disk. While a minimum amount of swapping is acceptable, excessive swapping degrades system performance. The function of the PPICacheThrP parameter is to control the amount of partitioning data block swapping.

PPICacheThrP does this by controlling the memory usage of partitioned operations. Larger values improve the performance of partitioned operations as long as the following restrictions are observed:

  • Data blocks are kept memory-resident.

    If they must be swapped to disk, performance might begin to degrade.

  • The number of contexts does not exceed the number of populated, non-eliminated partitions being processed.

    In this case, increasing the value of PPICachThrP does not improve performance because each partition has a context, and additional contexts would not be used.

Note that the maximum PPI cache that can be allocated is 100 MB regardless of the setting for PPICacheThrP.

Teradata Database uses the value of PCT for the following operations on a partitioned table.

  • Joins
  • Aggregation
  • Merge spooling