Selectivity based hybrid query cache

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 hybridquerycacheentries. The keyword 'local' indicates the statistics existing in the embedded compile instances are reported.

To find all cached HQC user queries, use query "select * from table(hybridquerycache('user', 'local'))".

To find all cached meta-data HQC queries, use query "select * from table(hybridquerycache('meta', 'local'));"

To find all cached statistics HQC queries, use query "select * from table(hybridquerycache('ustats', 'local'));"

To find all cached HQC queries, use query "select * from table(hybridquerycache('all', 'local'));"

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(hybridquerycacheentries('user', 'local'))".