SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

Metadata Tables
HP NonStop SQL/MX Reference Manual—523725-004
10-58
Query Plan Caching
Query Plan Caching
These attributes enable NonStop SQL/MX to cache query plans:
Attribute Setting
QUERY_CACHE Set to a value between 0 to 4194303. Indicates the size
in kilobytes to which the cache is allowed to grow.
The default setting is 1024, which activates a query
cache that can grow to 1024 KB in the current session.
To deactivate the query cache in the current session,
set QUERY_CACHE to 0. If a query cache was
allocated, this setting frees it.
For more information about this setting, see the
SQL/MX Query Guide.
QUERY_CACHE_MAX_VICTIMS Set to a value between 0 and 4194303. Indicates the
maximum number of cache entries that can be
displaced to accommodate a new entry and stay within
the size limit of the cache. Setting this attribute to a very
large value means that all the cache entries could be
displaced to accommodate one very large query.
Setting this attribute to 0 means that, when the cache
becomes full, no cache entries (pinned or unpinned)
can be displaced, and no new entries can be entered
into the cache.
For more information about this setting, see the
SQL/MX Query Guide.
The default setting is 10 cache entries.
QUERY_CACHE_REQUIRED_
PREFIX_KEYS
Set to a value between 0 and 255. Specifies how many
and which columns of a composite primary or partition
key are required for an equality predicate to be
considered cacheable. If the attribute is set to a value
greater than the number of columns in a composite key,
all columns of the key are required.
The value 0 means that the presence of any one
column of a composite primary or partition key in an
equality key predicate is sufficient to make that
predicate cacheable. A value n that is greater than 0
(zero) but less than the number of columns in the key
indicates that the first n columns of the key must be
present in a key predicate for that predicate to be
considered cacheable.
For more information about this setting, see the
SQL/MX Query Guide.
The default setting is 255, which means that only
complete primary or partition key equality predicates
are cacheable. To avoid compromising query plan
quality, You should keep the system-defined default
setting of 255.