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-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 COMPILATION_TIME

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

0 31752 334

0 24504 54

0 29144 96

AVERAGE_HIT_TIME SHAPE ISOLATION_LEVEL_ ACCESS_MODE AUTO_COMMIT

FOR_UPDATES

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

41 Cut (0) READ COMMITTED READ/WRITE ON

0 Cut (0) READ COMMITTED READ/WRITE ON

ROLLBACK_MODE

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

NOT SPECIFIED

--- 3 row(s) selected.

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