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

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-108
QUERYCACHE Function
QUERYCACHE Function
Considerations for QUERYCACHE
Examples of QUERYCACHE
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.
The QUERYCACHE function is an SQL/MX extension.
The QUERYCACHE function can be specified as a table reference (table) in the
FROM clause of a SELECT statement if it is preceded by the keyword TABLE and
surrounded by parentheses. The syntax for the QUERYCACHE function has no
parameters.
In a dynamic environment (that is, MXCI, MXCS, JDBC, or dynamic SQL), the
QUERYCACHE function returns the statistics of the query plan cache of the mxcmp
associated with the dynamic session. In a static environment (that is, statically
compiled embedded SQL), the QUERYCACHE function returns zero rows because at
runtime there is no associated mxcmp.
Considerations for QUERYCACHE
Using QUERYCACHE and DISPLAY_QC
The result of the QUERYCACHE function can be generated and displayed either by
using the QUERYCACHE function or the DISPLAY_QC command. The DISPLAY_QC
command provides a subset of the information displayed by the QUERYCACHE
function. The output of the QUERYCACHE function and DISPLAY_QC command is
machine-readable format. See the DISPLAY_QC Command on page 4-18.
Result of the QUERYCACHE Function
The result table of the QUERYCACHE function describes the query plan caching
information for certain SELECT, INSERT, DELETE, UPDATE, or join statements. For
more information about the types of statements that are appropriate candidates for
query plan caching, see the SQL/MX Query Guide.
QUERYCACHE ()
Column Name Data Type Description
AVG_PLAN_SIZE INT Total KB size of all cache entries divided by
the number of entries.
CURRENT_SIZE INT Current size of the query cache, in KB.
MAX_CACHE_SIZE INT Maximum cache size, in KB.