When the Query Result Cache (QRC) is enabled, then incoming queries are compared to those in the query cache before parsing. If the incoming query exists in the QRC, then the QRC results are sent. Prepared statements are also supported. The hit rate is generally less than 80% for the QRC as compared to other caches because:

Queries must be exactly the same to enable usage of the QRC results.

Consider the following two queries:

sql> select * from table foo; 
sql> SELECT * from table foo;

Both of these queries are considered different. Only the QRC results matching the exact same iteration of that SELECT could be used.

QRC Decision Flow

The following diagram illustrates the QRC decision flow:

Enabling and Monitoring the QRC

To enable the QRC, issue the following command from a command prompt on the target machine:

sql> set global qrc_enabled = true;
qrc_enabledEnables or disables the Query Results Cache.false

Understanding the QRC Statuses

The following QRC-related statuses are available:

Considerations for Enabling the QRC

Choosing to use the QRC depends on your data. If all the queries you are performing are simple (such as selecting a row from a table with one row), but still differ so that the queries cannot be cached, the overhead for having the query cache active is about 10%. This could be regarded as the worst case scenario. In real life, queries tend to be much more complicated, so the overhead normally is significantly lower.

Searches for a single row in a single-row table are two times faster with the query cache enabled than without it. This is typical for a query that is cached.

What is NOT Cached in QRC?

The following items are not cached:

Queries will be cached if they provide exactly the same results from repeat executions and if the underlying data remains unaltered. Queries that include any of the following keywords will also not be cached: