Result Cache (Part 2)

Insanity: doing the same thing over and over again and expecting different results. – Albert Einstein In part 1 we looked at the result cache for PL/SQL Code We can also use this same technique when our function depends on tables (or views). Let’s create a simple table, add some data and a simple, slow function: CREATE OR REPLACE FUNCTION

Insanity: doing the same thing over and over again and expecting different results.
– Albert Einstein

In part 1 we looked at the result cache for PL/SQL Code

We can also use this same technique when our function depends on tables (or views). Let’s create a simple table, add some data and a simple, slow function:

If we call this function in a query over and over again, the time spent will be pretty much the same every time.

The result of these calls is:

If we add the result_cache hint to the query, the first call is still slow (even slower than before, probably because of all the extra work that has to be done):

But the second call is much faster even though the function is not result cached:

If you don’t have access to the query itself (because it is in a front-end application which you don’t own for instance), but you can change the implementation of the function called, then you can still use the result cache by adding the keyword to the function.

If you run the query again (twice) you will notice the second call is faster.

This way you can easily improve performance of the queries. If you have access to the SQL statements you can start adding the result_cache hint right away. Even if your database does not yet support it or your DBA has turned the result cache off. If you add this hint to your statement and the database doesn’t support it yet, it will be ignored. And when you migrate to a newer version of the database, or when your DBA decides to turn on the result cache, your queries will automagically run faster.
If you would like your PL/SQL code to use this feature as soon as it’s available, you might want to add it to your codebase and use Conditional Compilation to be able to put the code in place and have it automatigally enabled when the database version permits it. Just change the header of your function to include the code when available.

Beware not to add the result cache to every query and every function in your application. As you could see when adding the hint to a simple query, the first time executed takes a bit longer than normal, probably because of all the work the database has to do to store the results. And the cache space is not infinite. Your DBA will assign a small portion of the available space to the cache. If the cache is full, old results will be flushed out using a least-recently used algoritm.

DBA

To check if the cache is enabled and how it’s configured you can run this statement

 

to get a report like this:

 

The DBA can turn the result cache option on or off. The use of the SQL query result cache can be controlled by setting the RESULT_CACHE_MODE initialization parameter. The possible parameter values are MANUAL and FORCE. When set to MANUAL, you control when the results are cached by using the RESULT_CACHE hint. When set to FORCE, everything is cached (if possible) unless you use the NO_RESULT_CACHE hint to bypass the cache.

Ref:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/fundamentals.htm#LNPLS00210
http://docs.oracle.com/cd/B28359_01/server.111/b28274/memory.htm#PFGRF10121