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:
1 2 3 4 5 6 7 8 9 10 11 12 |
<span class="kwrd">CREATE</span> <span class="kwrd">OR</span> <span class="kwrd">REPLACE</span> <span class="kwrd">FUNCTION</span> frb_slow_test(val_in <span class="kwrd">IN</span> <span class="kwrd">NUMBER</span>) <span class="kwrd">RETURN</span> <span class="kwrd">NUMBER</span> <span class="kwrd">IS</span> l_returnvalue <span class="kwrd">NUMBER</span>; <span class="kwrd">BEGIN</span> dbms_lock.sleep(<span class="str">1</span>); <span class="kwrd">SELECT</span> dummy <span class="kwrd">INTO</span> l_returnvalue <span class="kwrd">FROM</span> frb_test t <span class="kwrd">WHERE</span> <span class="str">1</span>=<span class="str">1</span> <span class="kwrd">AND</span> t.dummy = val_in; <span class="kwrd">RETURN</span> l_returnvalue; <span class="kwrd">END</span> 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.
1 2 |
<span class="kwrd">SELECT</span> frb_slow_test(dummy) <span class="kwrd">FROM</span> frb_test; <span class="kwrd">SELECT</span> frb_slow_test(dummy) <span class="kwrd">FROM</span> frb_test; |
The result of these calls is:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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):
1 2 3 4 5 6 7 8 9 |
<span class="kwrd">SELECT</span> /*+ result_cache */ frb_slow_test(dummy) <span class="kwrd">FROM</span> 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:
1 2 3 4 5 6 7 8 9 |
<span class="kwrd">SELECT</span> /*+ result_cache */ frb_slow_test(dummy) <span class="kwrd">FROM</span> 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.
1 2 3 4 5 6 7 8 9 10 11 12 |
<span class="kwrd">CREATE</span> <span class="kwrd">OR</span> <span class="kwrd">REPLACE</span> <span class="kwrd">FUNCTION</span> frb_slow_test(val_in <span class="kwrd">IN</span> <span class="kwrd">NUMBER</span>) <span class="kwrd">RETURN</span> <span class="kwrd">NUMBER</span> <span class="kwrd">RESULT_CACHE</span> <span class="kwrd">IS</span> l_returnvalue <span class="kwrd">NUMBER</span>; <span class="kwrd">BEGIN</span> dbms_lock.sleep(<span class="str">1</span>); <span class="kwrd">SELECT</span> dummy <span class="kwrd">INTO</span> l_returnvalue <span class="kwrd">FROM</span> frb_test t <span class="kwrd">WHERE</span> <span class="str">1</span>=<span class="str">1</span> <span class="kwrd">AND</span> t.dummy = val_in; <span class="kwrd">RETURN</span> l_returnvalue; <span class="kwrd">END</span> frb_slow_test; |
If you run the query again (twice) you will notice the second call is faster.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<span class="kwrd">SELECT</span> frb_slow_test(dummy) <span class="kwrd">FROM</span> frb_test; FRB_SLOW_TEST(DUMMY) -------------------- 1 2 3 4 5 Executed in 5.038 seconds <span class="kwrd">SELECT</span> frb_slow_test(dummy) <span class="kwrd">FROM</span> 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.
1 2 3 4 5 6 7 8 |
<span class="kwrd">CREATE</span> <span class="kwrd">OR</span> <span class="kwrd">REPLACE</span> <span class="kwrd">FUNCTION</span> frc_multiply_c(left_in <span class="kwrd">IN</span> <span class="kwrd">NUMBER</span> ,right_in <span class="kwrd">IN</span> <span class="kwrd">NUMBER</span>) <span class="kwrd">RETURN</span> <span class="kwrd">NUMBER</span> $IF DBMS_DB_VERSION.ver_le_11 $THEN $ELSE <span class="kwrd">RESULT_CACHE</span> $END <span class="kwrd">IS</span> <span class="kwrd">...</span> |
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
1 |
EXEC DBMS_RESULT_CACHE.memory_report(detailed => true); |
to get a report like this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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
Load comments