SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
9-134
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 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 (0). Entries with the highest
row IDs are 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. Plan
sharing can be recognized when the same
PLAN_ID appears on multiple cache entries
whose PHASE column is BINDING.
TEXT CHAR(1024) Text of the original SQL statement.
ENTRY_SIZE INT Size in bytes of this entry, excluding the size of the
compiled plan with which this entry is associated.
NUM_HITS INT The total number of queries that have an identical
query template with this entry and have reused the
compiled plan.
PHASE CHAR(10) The mxcmp phase after when the plan associated
with this entry was cached (parsing or binding).
For template cache entries, the value is always
binding.
OPTIMIZATION_LEVEL CHAR(10) 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 the default catalog under which the query
was compiled.
SCHEMA_NAME CHAR(40) Name of the default schema 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. Blank, if none.