SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-113
Considerations for QUERYCACHEENTRIES
Result of the QUERYCACHEENTRIES Function
The result table of the QUERYCACHEENTRIES function describes the query plan
caching information for each entry in the query plan cache. For more information about
the types of statements that are appropriate candidates for query plan caching, see the
SQL/MX Query Guide.
Column Name Data Type Description
ROW_ID INT A zero-based sequential number. Entry number 0
is the most recently used entry. When a new entry
is cached or matches the query issued, it will
occupy zero and all other cache entries not
displaced will be increased by one. Entry number
1 is the most recently used entry after the most
recent. Entries with the highest rowids are the
ones that will be replaced as they are the least
recently used entries.
PLAN_ID LARGEINT System-generated timestamp stored within each
plan. A unique identifier, the PLAN_ID also
appears in the EXPLAIN function and allows joins
to be written between EXPLAIN and
QUERYCACHEENTRIES.
TEXT CHAR(1024) Text of the original SQL statement.
ENTRY_SIZE INT Entry size in bytes.
NUM_HITS INT Total number of hits for this entry.
PHASE CHAR(10) The mxcmp phase after which the plan associated
with this entry was cached (parsing or binding).
OPTIMIZATION_LEVEL CHAR(10) Indicates the current level of query optimization.
Can be 0, 1, 2, 3, 4, or 5.
CATALOG_NAME CHAR(40) Default catalog name under which the query was
compiled.
SCHEMA_NAME CHAR(40) Default schema name under which the query was
compiled.
NUM_PARAMS INT Number of constants in the query that were
changed internally into parameters during
compilation.
PARAM_TYPES CHAR(1024) Comma separated list of the types of constants
that were changed into parameters.
PLAN_LENGTH INT Size in bytes of the compiled plan associated with
this entry.
IS_PINNED CHAR(6) Current state of pinning, ON or OFF.
COMPILATION_TIME INT Time in milliseconds it took to compile the query
associated with this entry.
AVERAGE_HIT_TIME INT Time in milliseconds it took on the average to
process a query as a cache hit against this entry.