SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Query Plan Caching
HP NonStop SQL/MX Query Guide—523728-003
6-9
QUERYCACHE Function
•
QUERY_CACHE_STATEMENT_PINNING
System-defined default setting: OFF
Allowable values: ON, OFF, CLEAR
This attribute controls whether queries are entered into the cache as pinned or
unpinned. You might have important, compile-time critical queries that you want to
ensure are in the cache when needed. When a query is pinned in the cache, it
usually cannot be displaced from the cache unless the cache becomes full of
pinned queries. In this case, the least recently used pinned entries also become
displaceable.
The system-defined default setting, OFF, means that all subsequent query cache
entries are unpinned.
The value CLEAR means that all subsequent query cache entries are unpinned,
and all pinned entries in the cache are also unpinned.
The value ON means that all subsequent query cache entries are pinned.
QUERYCACHE Function
The query plan cache automatically collects statistics regarding its use. When invoked,
the QUERYCACHE table-valued stored function collects and returns the current state
of these statistics in a single row table. The statistics are reinitialized when an mxcmp
session is started, and each mxcmp session maintains an independent set of statistics.
This table describes the various statistics of the QUERYCACHE table:
Column Name Data Type Description
AVG_PLAN_SIZE INT UNSIGNED Total KB size of all cache entries
divided by the number of entries.
CURRENT_SIZE INT UNSIGNED Current KB size of the query cache.
MAX_CACHE_SIZE INT UNSIGNED Maximum cache size in KB.
MAX_NUM_VICTIMS INT UNSIGNED Maximum number of plans that can
be removed from the cache to
make room for a new entry.
NUM_ENTRIES INT UNSIGNED Total number of query entries in the
cache.
NUM_PINNED INT UNSIGNED Total number of pinned entries.
NUM_COMPILES INT UNSIGNED Total number of complete compile
requests (excludes DESCRIBE and
SHOWSHAPE).
NUM_RECOMPILES INT UNSIGNED Total number of recompilations.
Recompilation of a cached plan
occurs when a referenced table has
been re-created or altered.