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

Query Plan Caching
HP NonStop SQL/MX Query Guide—523728-003
6-11
QUERYCACHEENTRIES Function
QUERYCACHEENTRIES Function
The query plan cache automatically collects statistics on each entry of the cache.
When invoked, the QUERYCACHEENTRIES table-valued stored function collects and
returns these statistics in a table with one row for each entry of the cache. The
statistics are reinitialized when an mxcmp session is started. Each mxcmp session
maintains an independent set of statistics.
This table describes the statistics of the QUERYCACHEENTRIES table:
Column Name Data Type Description
ROW_ID INT UNSIGNED 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 occupies zero, and all
other cache entries not displaced
are increased by one. Entry number
1 is the most recently used entry
after the most recent. Entries with
the highest row IDs are the ones
replaced; they are the least recently
used entries.
PLAN_ID LARGEINT Primary key. System-generated
timestamp stored within each plan
that uniquely identifies it. This
column appears in the EXPLAIN
table and enables joins between the
two tables.
TEXT CHAR(1024) Text of the original SQL statement.
ENTRY_SIZE INT UNSIGNED Size in bytes of this entry.
NUM_HITS INT UNSIGNED Total number of hits for this entry.
PHASE CHAR(10) Contains the mxcmp phase after
when the plan associated with this
entry was cached (parsing or
binding)
OPTIMIZATION_LEVEL CHAR(10) Indicates the desired level of code
optimization at the time the query
was compiled. Can be 0, 2, 3, or 5.
CATALOG_NAME CHAR(40) Name of default catalog under
which the query was compiled.
SCHEMA_NAME CHAR(40) Name of default schema under
which the query was compiled.
NUM_PARAMS INT UNSIGNED Number of constants in the query
that were changed internally into
parameters during compilation.