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 frb_slow_test(val_in IN NUMBER) RETURN NUMBER
IS
  l_returnvalue NUMBER;
BEGIN
  dbms_lock.sleep(1);
  SELECT dummy
    INTO l_returnvalue
    FROM frb_test t
   WHERE 1=1
     AND t.dummy = val_in;
  RETURN l_returnvalue;
END frb_slow_test;

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

SELECT frb_slow_test(dummy) FROM frb_test;
SELECT frb_slow_test(dummy) FROM frb_test;

The result of these calls is:

FRB_SLOW_TEST(DUMMY)
--------------------
                   1
                   2
                   3
                   4
                   5
Executed in 5.039 seconds
FRB_SLOW_TEST(DUMMY)
--------------------
                   1
                   2
                   3
                   4
                   5
Executed in 5.055 seconds

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):

SELECT /*+ result_cache */ frb_slow_test(dummy) FROM frb_test;
FRB_SLOW_TEST(DUMMY)
--------------------
                   1
                   2
                   3
                   4
                   5
Executed in 10.109 seconds

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

SELECT /*+ result_cache */ frb_slow_test(dummy) FROM frb_test;
FRB_SLOW_TEST(DUMMY)
--------------------
                   1
                   2
                   3
                   4
                   5
Executed in 0.047 seconds

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.

CREATE OR REPLACE FUNCTION frb_slow_test(val_in IN NUMBER) RETURN NUMBER RESULT_CACHE
IS
  l_returnvalue NUMBER;
BEGIN
  dbms_lock.sleep(1);
  SELECT dummy
    INTO l_returnvalue
    FROM frb_test t
   WHERE 1=1
     AND t.dummy = val_in;
  RETURN l_returnvalue;
END frb_slow_test;

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

SELECT frb_slow_test(dummy) FROM frb_test;
FRB_SLOW_TEST(DUMMY)
--------------------
                   1
                   2
                   3
                   4
                   5
Executed in 5.038 seconds
SELECT frb_slow_test(dummy) FROM frb_test;
FRB_SLOW_TEST(DUMMY)
--------------------
                   1
                   2
                   3
                   4
                   5
Executed in 0.047 seconds

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.

CREATE OR REPLACE FUNCTION frc_multiply_c(left_in  IN NUMBER
                                         ,right_in IN NUMBER) RETURN NUMBER
$IF DBMS_DB_VERSION.ver_le_11 $THEN
  $ELSE
RESULT_CACHE
$END
 IS
...

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

EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);

 

to get a report like this:

R e s u l t   C a c h e   M e m o r y   R e p o r t
[Parameters]
Block Size          = 1K bytes
Maximum Cache Size  = 2080K bytes (2080 blocks)
Maximum Result Size = 104K bytes (104 blocks)
[Memory]
Total Memory = 157384 bytes [0.050% of the Shared Pool]

... Fixed Memory =  bytes [% of the Shared Pool]

....... State Objs =  bytes
... Dynamic Memory = 157384 bytes [0.050% of the Shared Pool]
....... Overhead = 124616 bytes
........... Hash Table    = 64K bytes (4K buckets)
........... Chunk Ptrs    = 24K bytes (3K slots)
........... Chunk Maps    = 12K bytes
........... Miscellaneous = 124616 bytes
....... Cache Memory = 32K bytes (32 blocks)
........... Unused Memory = 0 blocks
........... Used Memory = 32 blocks
............... Dependencies = 12 blocks (12 count)
............... Results = 20 blocks
................... SQL     = 4 blocks (4 count)
................... Invalid = 16 blocks (16 count)

 

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

Tags: , , ,