EsgynDB query cache
分布式数据库EsgynDB，每个连接服务mxosrvr进程中都有一块local cache，可以保存在mxosrvr中执行过的SQL执行计划的信息；
query cache查看
query cache主要涉及两张虚拟表：
table(querycache('user','local'))：保存了所有cache的query的汇总信息
table(querycacheentries('user','local'))：保存每条cache的query的详细信息
SQL>select * from table(querycache('user','local'));
AVG_TEMPLATE_SIZE CURRENT_SIZE MAX_CACHE_SIZE MAX_NUM_VICTIMS NUM_ENTRIES NUM_PLANS NUM_COMPILES NUM_RECOMPILES NUM_RETRIES NUM_CACHEABLE_PARSING NUM_CACHEABLE_BINDING NUM_CACHE_HITS_PARSING NUM_CACHE_HITS_BINDING NUM_CACHEABLE_TOO_LARGE NUM_DISPLACED OPTIMIZATION_LEVEL TEXT_CACHE_HITS AVG_TEXT_SIZE TEXT_ENTRIES DISPLACED_TEXTS NUM_LOOKUPS
----------------- ------------ -------------- --------------- ----------- ---------- ------------ -------------- ----------- --------------------- --------------------- ---------------------- ---------------------- ----------------------- ------------- ------------------ --------------- ------------- ------------ --------------- -----------
17701 1628 16384 10 31 31 498 0 0 2 41 0 16 0 0 3 6 1189 26 0 55
--- 1 row(s) selected.
SQL>select * from table(querycacheentries('user','local'));
1 212475029761359931 select * from lt 2418 9496 1 BINDING 3 TRAFODION SEABASE 0 3 0 READ COMMITTED NOT SPECIFIED READ/WRITE ON WAITED
2 212475029758702217 insert into lt values (1),(2),(3) 3497 11728 0 PARSING 3 TRAFODION SEABASE 3 NUMERIC(1) SIGNED NO NULLS,NUMERIC(1) SIGNED NO NULLS,NUMERIC(1) SIGNED NO NULLS 1047 0 READ COMMITTED READ COMMITTED READ/WRITE ON WAITED
3 212475021989353196 select usage_uid, usage_type, flags from TRAFODION."_TENANT_MD_".TENANT_USAGE where tenant_id = 1500000 2614 12552 0 BINDING 3 TRAFODION SEABASE 1 INTEGER SIGNED NO NULLS 18 0 READ COMMITTED NOT SPECIFIED READ/WRITE OFF WAITED
4 212475021989322264 select tenant_id, admin_role_id, default_schema_uid, affinity, cluster_size, tenant_size, flags from TRAFODION."_TENANT_MD_".TENANTS where tenant_id = 1500000 3006 14968 0 BINDING 3 TRAFODION SEABASE 1 INTEGER SIGNED NO NULLS 24 0 READ COMMITTED NOT SPECIFIED READ/WRITE OFF WAITED
5 212475021989299582 select auth_id, auth_db_name, auth_ext_name, auth_type, auth_creator, auth_is_valid, auth_create_time, auth_redef_time, flags from TRAFODION."_MD_".AUTHS where auth_db_name = 'ESGYNDB' 3079 18256 0 BINDING 3 TRAFODION SEABASE 1 VARCHAR(256 BYTES) CHARACTER SET UTF8 NO NULLS 6 0 READ COMMITTED NOT SPECIFIED READ/WRITE OFF WAITED
6 212475021989253676 select auth_id, auth_db_name, auth_ext_name, auth_type, auth_creator, auth_is_valid, auth_create_time, auth_redef_time, flags from TRAFODION."_MD_".AUTHS where auth_ext_name = 'DB__ROOT' 3081 18264 0 BINDING 3 TRAFODION SEABASE 1 VARCHAR(256 BYTES) CHARACTER SET UTF8 NO NULLS
清空Query cache
SQL>cqd query_cache '0';
--- SQL operation complete.
SQL>select * from table(querycache('user','local'));
AVG_TEMPLATE_SIZE CURRENT_SIZE MAX_CACHE_SIZE MAX_NUM_VICTIMS NUM_ENTRIES NUM_PLANS NUM_COMPILES NUM_RECOMPILES NUM_RETRIES NUM_CACHEABLE_PARSING NUM_CACHEABLE_BINDING NUM_CACHE_HITS_PARSING NUM_CACHE_HITS_BINDING NUM_CACHEABLE_TOO_LARGE NUM_DISPLACED OPTIMIZATION_LEVEL TEXT_CACHE_HITS AVG_TEXT_SIZE TEXT_ENTRIES DISPLACED_TEXTS NUM_LOOKUPS
----------------- ------------ -------------- --------------- ----------- ---------- ------------ -------------- ----------- --------------------- --------------------- ---------------------- ---------------------- ----------------------- ------------- ------------------ --------------- ------------- ------------ --------------- -----------
17701 1003 0 10 0 0 1 0 0 0 0 0 0 0 31 3 0 1189 0 26 0
--- 1 row(s) selected.
SQL>select * from table(querycacheentries('user','local'));
--- 0 row(s) selected.
SQL>
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/69985104/viewspace-2743519/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
