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

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-110
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
NUM_PIN_HITS_PARSING 0
NUM_PIN_HITS_BINDING 0
NUM_CACHEABLE_TOO_LARGE 0
NUM_DISPLACED 0
OPTIMIZATION_LEVEL 3
PINNING_STATEOFF
--- 1 row(s) selected.
NUM_DISPLACED INT Number of entries removed from the cache
to make room for new entries or as a
consequence of a resizing of the cache or a
recompilation.
OPTIMIZATION_LEVEL CHAR(10) Indicates the current level of query
optimization. Can be 0, 1, 2, 3, 4, or 5.
PINNING_STATE CHAR(4) Current state of pinning, ON or OFF.
Column Name Data Type Description