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

Query Plan Caching
HP NonStop SQL/MX Query Guide—523728-003
6-10
QUERYCACHE Function
NUM_RETRIES INT UNSIGNED Number of successful compiles that
initially fail with caching on (caused
by a defect in mxcmp) and that
succeed with caching off.
NUM_CACHEABLE_PARSING INT UNSIGNED Total number of SQL statements
that mxcmp has processed after
parsing and before binding the
query that satisfy the conditions for
caching.
NUM_CACHEABLE_BINDING INT UNSIGNED Total number of SQL statements
that mxcmp has processed after
binding and before transformation
of the query that satisfy the
conditions for caching.
NUM_CACHE_HITS_PARSING INT UNSIGNED Total number of SQL statements
that mxcmp has processed after
parsing and before binding that
have produced hits.
NUM_CACHE_HITS_BINDING INT UNSIGNED Total number of SQL statements
that mxcmp has processed after
binding and before transformation
of the query that have produced
hits.
NUM_PIN_HITS_PARSING INT UNSIGNED Total number of hits on pinned
entries that occurred after parsing
and before binding.
NUM_PIN_HITS_BINDING INT UNSIGNED Total number of hits on pinned
entries that occurred after binding
and before transformation.
NUM_CACHEABLE_TOO_LARGE INT UNSIGNED Number of SQL statements
processed by mxcmp that satisfy the
conditions for cacheability but with
plans too large to fit in the cache.
NUM_DISPLACED INT UNSIGNED Number of entries removed from
the cache to make room for new
entries or as a consequence of a
resizing of the cache or
recompilation.
OPTIMIZATION_LEVEL CHAR(10) Indicates the current desired level
of query optimization. Can be 0, 2,
3, or 5.
PINNING_STATE CHAR(4) Current state of pinning. Can be ON
or OFF.
Column Name Data Type Description