SQL/MX 3.1 Query Guide (H06.23+, J06.12+)

Query Plan Caching

HP NonStop SQL/MX Release 3.1 Query Guide—663851-001

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_TEMPLATE_SIZE 31

CURRENT_SIZE 35

MAX_CACHE_SIZE 1024

MAX_NUM_VICTIMS 10

NUM_ENTRIES 1

NUM_PLANS 2

NUM_COMPILES 2

NUM_RECOMPILES 53

NUM_RETRIES 0

NUM_CACHEABLE_PARSING 0

NUM_CACHEABLE_BINDING 0

NUM_CACHE_HITS_PARSING 2

NUM_CACHE_HITS_BINDING 0

NUM_CACHEABLE_TOO_LARGE 0

NUM_DISPLACED 0

OPTIMIZATION_LEVEL 3

TEXT_CACHE_HITS 0

AVG_TEXT_SIZE 500

TEXT_ENTRIES 0

DISPLACED_TEXTS 0

NUM_LOOKUPS 2

--- 1 row(s) selected.

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

been formatted for readability.

TP663851.fm Page 13 Monday, October 17, 2011 11:48 AM