Selectivity based hybrid query cache

Registered by QF Chen on 2015-03-25

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.

Blueprint information

Status:
Complete
Approver:
None
Priority:
High
Drafter:
QF Chen
Direction:
Needs approval
Assignee:
QF Chen
Definition:
Approved
Series goal:
None
Implementation:
Implemented
Milestone target:
None
Started by
QF Chen on 2015-04-24
Completed by
QF Chen on 2015-04-24

Related branches

Sprints

Whiteboard

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'))".

(?)

Work Items

This blueprint contains Public information 
Everyone can see this information.

Subscribers

No subscribers.