Before you read on, please check out my related post on “Function Result Cache”. You may find it valuable in this context.
Conversation sounds familiar?
I am sure you have been privy to a conversation like this between an IT manager and a DBA:
Manager: My query is extremely slow. Can you make the query run faster?
DBA: Sure. I can take a look at it and possibly tune it.
Manager: What do you need to do to tune it?
DBA: I have to look at the explain plan and tune portions that are bad. It may require changes to the query and possibly additional indexes.
Manager: Would that not change the results?
DBA: It can. You have to get your team to test it afterwards to make sure it is still correct for all combinations of input.
Manager: I don’t want you to change the results or the query itself. Can’t you do something as a DBA?
DBA: hmmm…. 😉
Manager: I don’t have the time or the resources to test anything. It has to work as it does today, but just faster.
Read on. In 11g R2 and above, the answer from the DBA could be more like this:
DBA: …Tell me more about your query and I may be able do some magic just for you!
Oracle Server Query Result Cache
We know how caching works. Oracle gives us the ability to cache the results of a query, sub-query or view using a hint – /*+ RESULT_CACHE */. As long as the query remains the same, Oracle runs the query once and subsequently returns results from the cache. If the data in any of the underlying tables used by the query changes, the cache is invalidated. The cache is used even if the query is run in another session by a different user.
When to use Query Result Cache feature?
An ideal candidate query would possess the following characteristics:
- Query is static. i.e., nothing about the query changes over time
- Query runs frequently (and is probably slow)
- Data in the underlying tables are mostly static – due to frequent cache invalidations otherwise
- The amount of results returned is not very much – to minimize the cache usage
- Query may be run by different users across sessions – Cache is in SGA and shared across
I think we have talked enough. The demo is not going to be anything spectacular either (the results are) but let us do it.
How to use Query Result Cache feature?
Using the query Result Cache feature is quite simple. You just add the hint /*+ RESULT_CACHE */ and you are all set. As anyone knows, hints do not change the results of a query. They can only make the query run slower or faster. If we picked the right candidate query, it could only go faster in our case with this hint.
Query that was running slow:
Actual query:
SELECT * FROM my_kpi_status_view 104 rows returned in 30 seconds
Query with RESULT_CACHE hint – first time:
–Run with RESULT_CACHE hint
SELECT /*+ RESULT_CACHE */ * FROM my_kpi_status_view 104 rows returned in 30 seconds
Query with RESULT_CACHE hint – second time:
–Run again with RESULT_CACHE hint
SELECT
/*+ RESULT_CACHE */ *
FROM my_kpi_status_view
104 rows returned in 70 milliseconds
Query with RESULT_CACHE hint – third time:
–Run again with RESULT_CACHE hint
SELECT
/*+ RESULT_CACHE */ *
FROM my_kpi_status_view
104 rows returned in 45 milliseconds
The magic happened with the RESULT_CACHE hint after the result was cached. See the dramatic reduction in time.
The time-taken line will continue to stay at the bottom until the underlying data for the view changes or the query itself changed, even a bit (as below, where we took off the hint).
–Run again with RESULT_CACHE hint REMOVED
SELECT * FROM my_kpi_status_view 104 rows returned in 30 seconds
This query was issued from a batch job once every minute. Even with some basic math, the time savings are tremendous on this one query.
| Time spent on query per day | Time spent on query per month | Time spent on query per year | ||
| Total Seconds Taken – Before Hint | 30 seconds to run
X 60 times an hour X 24 hours |
12 hours | 15 days | 6 months |
| Total Seconds Taken – After Hint
(with 10 invalidations) |
30 seconds to run
+ 30 seconds initial run to cache X 10 invalidations |
6 minutes | ~3 hours | ~36 hours |
| Total Seconds Taken – After Hint
(with 100 invalidations) |
30 seconds to run
+ 30 seconds initial run to cache X 100 invalidations |
About 50 minutes | ~62 hours | ~31 days |
I did not change a thing about the view. All I did was added a hint to the view since this specific query was a perfect nail for the Result Cache hammer.
Smarter Result Cache use – RESULT_CACHE hint in a sub-query:
When I looked at the query the query was quite big with a lot of nested clauses. I will simplify it to get the point through.
SELECT
col1,
col2,
col3...
FROM
(
SELECT col1,
col2,
..
COUNT()
FROM
tableA
GROUP BY
col1,
col2
) sub1,
(
SELECT
col1,
col2,
..
MAX()
SUM()
FROM
tableB,
tableC,
tableD,
tableE
WHERE
joins...
GROUP BY
col1,
col2
..
) sub2,
WITH sub3
AS
(
SELECT
col1,
col2,
..
MAX()
SUM()
FROM
tableF
WHERE
..
GROUP BY
..
)
)
WHERE
..
GROUP BY
..
Now, we ended up caching the results of the whole query but any data change to any of the tables in the query – tableA, tableB, tableC, tableD, tableE and tableF could invalidate the cache. This is not a good situation to be in.
If I know for a fact that only this portion of the above query was slow:
(
SELECT col1,
col2,
..
COUNT()
FROM
tableA
GROUP BY
col1,
col2
) sub1,
I could add a RESULT_CACHE hint just to that sub-query and Oracle will only cache the results of that sub-query .
(
SELECT /*+ RESULT_CACHE */ col1,
col2,
..
COUNT()
FROM
tableA
GROUP BY
col1,
col2
) sub1,
Now, the whole query will still be faster but the number of times it gets invalidated will be far less.
When you have a hammer everything looks like a nail!
When you have a hammer, especially a good one like this, every query will look like a nail. Please use caution and resist the urge to go overboard. Read the section at the top to see if your query is a good candidate before using the hint.
Besides the query option, the Result Cache feature can be used at the instance level by forcing Result Cache or at the table level with the clause RESULT_CACHE (MODE FORCE);
Where else can Result Caching be done?
In summary result caching can be done at
- Instance level
- Table level
- Query/View level
- Sub-query level
- PL/SQL function level
It can also be disabled or overridden at different levels. When you have enclosing levels – for example, it is forced at the table level and there is no hint at the query level, the table level clause kicks in. However, if the same query had a /*+ NO_RESULT_CACHE */ hint, it will take precedence and results will not be cached. Remember the good old rule of computing – when there are enclosing scopes, the closest scope to the activity has the highest precedence.
SQL Jana 