SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-004
8-136
Examples of QUERYCACHE
Examples of QUERYCACHE
•
Display all query plan caching statistics for an mxcmp session. Note that the output
has been formatted for readability:
>>SET SCHEMA SAMDBCAT.PERSNL;
--- SQL operation complete.
>SELECT * FROM EMPLOYEE;
Employee/Number First Name Last Name Dept/Num Job/Code Salary
--------------- ------------ ----------------- -------- -------- ------
1 ROGER GREEN 9000 100 175500.00
23 JERRY HOWARD 1000 100 137000.10
29 JANE RAYMOND 3000 100 136000.00
32 THOMAS RUDLOFF 2000 100 138000.40
.
.
.
--- 62 row(s) selected.
>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
OPTIMIZATION_LEVEL CHAR(10) The current desired level of query
optimization. Can be 0, 2, 3, or 5.
AVG_TEMPLATE_SIZE INT
UNSIGNED
Average template cache entry size in bytes.
The size of a shared plan object is counted
once.
TEXT_CACHE_HITS INT
UNSIGNED
Reserved for future use.
AVG_TEXT_SIZE INT
UNSIGNED
Reserved for future use.
TEXT_ENTRIES INT
UNSIGNED
Reserved for future use.
DISPLACED_TEXTS INT
UNSIGNED
Reserved for future use.
NUM_LOOKUPS INT
UNSIGNED
Reserved for future use.
Column Name Data Type Description