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

Query Plan Caching

HP NonStop SQL/MX Query Guide—523728-003

6-13

Querying the Query Plan Caching Virtual Tables

You can query and display certain columns or all columns of the query plan caching

virtual tables. You specify the virtual table in a SELECT statement preceded by the

keyword table and surrounded by parenthesis. In addition, a pair of parentheses must

follow the table name. Information is returned in machine-readable format.

If the query plan cache does not contain any stored plans, no rows are returned.

For example, this query selects all columns from the QUERYCACHE virtual table. Only

one plan was stored in the cache as reflected by the NUM_ENTRIES value of 1.

SELECT * FROM TABLE(QUERYCACHE());

AVG_PLAN_SIZE 31

CURRENT_SIZE 35

MAX_CACHE_SIZE 1024

MAX_NUM_VICTIMS 10

NUM_ENTRIES 1

NUM_PINNED 0

NUM_COMPILES 21

NUM_RECOMPILES 0

NUM_RETRIES 0

NUM_CACHEABLE_PARSING 0

NUM_CACHEABLE_BINDING 1

NUM_CACHE_HITS_PARSING 0

NUM_CACHE_HITS_BINDING 0

NUM_PIN_HITS_PARSING 0

NUM_PIN_HITS_BINDING 0

NUM_CACHEABLE_TOO_LARGE 0

NUM_DISPLACED 0

OPTIMIZATION_LEVEL 2

PINNING_STATE OFF

--- 1 row(s) selected.

Although the QUERYCACHE function is a one-row multi-column table, this output has

been formatted for readability.