Selectivity based hybrid query cache
Hybrid Query Cache (HQC) is a new query caching mechanism in Trafodion starting release 1.1, designed for OLTP and ODS workloads. HQC builds a cache key much earlier during the compilation phase and exploits existing selectivity query cache to guarantee optimal plan quality. An early prototype of HQC reduces the compile time from 1.07ms to 0.33ms for queries in an OLTP/ODS mixed workload.
Hybrid Query Cache (HQC) is a marriage of Selectivity-based Query Cache (SQC) and Post-Parser Cache (PPC). The structural similarity of two queries can be verified very quickly if their signatures can be computed and compared in the parser, usually on the order of 1ms or less. The signature is the sequence of tokens into which the query is parsed. The signature becomes the key k for HQC since it can uniquely identify a class of structurally similar queries. The HQC key is quite different from the cache key for SQC since the latter is constructed in the binder, using the insight gained during the semantic analysis. During parsing, SQL literals in the query are also collected.
This feature can be controlled by control query default HYBRID_QUERY_CACHE. A value of 'ON' turns on the feature and 'OFF' turns it off.
Its caching statistics can be obtained from two new virtual tables: hybridquerycache, and hybridquerycach
To find all cached HQC user queries, use query "select * from table(hybridque
To find all cached meta-data HQC queries, use query "select * from table(hybridque
To find all cached statistics HQC queries, use query "select * from table(hybridque
To find all cached HQC queries, use query "select * from table(hybridque
To find the length of HQC caching keys, the number of hits and parameters for all user queries, use query "select char_length(hkey), num_hits, num_PLiterals, num_NPLiterals from table(hybridque