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

Query Plan Caching

HP NonStop SQL/MX Query Guide—523728-003

6-14

Querying the Query Plan Caching Virtual Tables

This query selects all columns of the QUERYCACHEENTRIES virtual table (formatted

for readability):

SELECT * FROM TABLE(QUERYCACHEENTRIES());

ROW_ID PLAN_ID TEXT ENTRY_SIZE

------ ------------------ -------------------------- ----------

0 211894097543468116 select * from employee; 32410

1 211894097552547493 select * from job; 24968

2 211894097548497817 select * from dept; 29730

NUM_HITS PHASE OPTIMIZATION_LEVEL CATALOG_NAME SCHEMA_NAME

-------- ---------- ---------------------- ------------ -----------

1 BINDING 2 SAMDBCAT PERSNL

0 BINDING 2 SAMDBCAT PERSNL

NUM_PARAMS PARAM_TYPES PLAN_LENGTH IS_PINNED COMPILATION_TIME

---------- ----------- ----------- --------- -----------------

0 31752 OFF 334

0 24504 OFF 54

0 29144 OFF 96

AVERAGE_HIT_TIME SHAPE ISOLATION_LEVEL ACCESS_MODE AUTO_COMMIT

---------------- ------- --------------- ------------ -----------

41 Cut (0) READ COMMITTED READ/WRITE ON

0 Cut (0) READ COMMITTED READ/WRITE ON

ROLLBACK_MODE

--------------

NOT SPECIFIED

--- 3 row(s) selected.