{"id":73214,"date":"2014-02-04T15:05:11","date_gmt":"2014-02-04T15:05:11","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/result-cache-part-2\/"},"modified":"2021-07-14T13:07:36","modified_gmt":"2021-07-14T13:07:36","slug":"result-cache-part-2","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/result-cache-part-2\/","title":{"rendered":"Result Cache (Part 2)"},"content":{"rendered":"

Insanity: doing the same thing over and over again and expecting different results.
\n– Albert Einstein<\/em><\/p>\n

<\/h1>\n

In part 1<\/a> we looked at the result cache for PL\/SQL Code<\/p>\n

We can also use this same technique when our function depends on tables (or views). Let\u2019s create a simple table, add some data and a simple, slow function:<\/p>\n

\n
CREATE<\/span> OR<\/span> REPLACE<\/span> FUNCTION<\/span> frb_slow_test(val_in IN<\/span> NUMBER<\/span>) RETURN<\/span> NUMBER<\/span>\r\nIS<\/span>\r\n  l_returnvalue NUMBER<\/span>;\r\nBEGIN<\/span>\r\n  dbms_lock.sleep(1<\/span>);\r\n  SELECT<\/span> dummy\r\n    INTO<\/span> l_returnvalue\r\n    FROM<\/span> frb_test t\r\n   WHERE<\/span> 1<\/span>=1<\/span>\r\n     AND<\/span> t.dummy = val_in;\r\n  RETURN<\/span> l_returnvalue;\r\nEND<\/span> frb_slow_test;<\/pre>\n<\/div>\n
If we call this function in a query over and over again, the time spent will be pretty much the same every time.<\/p>\n
SELECT<\/span> frb_slow_test(dummy) FROM<\/span> frb_test;\r\nSELECT<\/span> frb_slow_test(dummy) FROM<\/span> frb_test;<\/pre>\n
The result of these calls is:<\/p>\n
FRB_SLOW_TEST(DUMMY)\r\n--------------------\r\n                   1\r\n                   2\r\n                   3\r\n                   4\r\n                   5\r\nExecuted in 5.039 seconds\r\nFRB_SLOW_TEST(DUMMY)\r\n--------------------\r\n                   1\r\n                   2\r\n                   3\r\n                   4\r\n                   5\r\nExecuted in 5.055 seconds<\/pre>\n
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):<\/p>\n
SELECT<\/span> \/*+ result_cache *\/ frb_slow_test(dummy) FROM<\/span> frb_test;\r\nFRB_SLOW_TEST(DUMMY)\r\n--------------------\r\n                   1\r\n                   2\r\n                   3\r\n                   4\r\n                   5\r\nExecuted in 10.109 seconds<\/pre>\n
But the second call is much faster even though the function is not result cached:<\/p>\n
SELECT<\/span> \/*+ result_cache *\/ frb_slow_test(dummy) FROM<\/span> frb_test;\r\nFRB_SLOW_TEST(DUMMY)\r\n--------------------\r\n                   1\r\n                   2\r\n                   3\r\n                   4\r\n                   5\r\nExecuted in 0.047 seconds<\/pre>\n
If you don\u2019t have access to the query itself (because it is in a front-end application which you don\u2019t 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.<\/p>\n
\n
CREATE<\/span> OR<\/span> REPLACE<\/span> FUNCTION<\/span> frb_slow_test(val_in IN<\/span> NUMBER<\/span>) RETURN<\/span> NUMBER<\/span> RESULT_CACHE<\/span>\r\nIS<\/span>\r\n  l_returnvalue NUMBER<\/span>;\r\nBEGIN<\/span>\r\n  dbms_lock.sleep(1<\/span>);\r\n  SELECT<\/span> dummy\r\n    INTO<\/span> l_returnvalue\r\n    FROM<\/span> frb_test t\r\n   WHERE<\/span> 1<\/span>=1<\/span>\r\n     AND<\/span> t.dummy = val_in;\r\n  RETURN<\/span> l_returnvalue;\r\nEND<\/span> frb_slow_test;<\/pre>\n<\/div>\n
If you run the query again (twice) you will notice the second call is faster.<\/p>\n
SELECT<\/span> frb_slow_test(dummy) FROM<\/span> frb_test;\r\nFRB_SLOW_TEST(DUMMY)\r\n--------------------\r\n                   1\r\n                   2\r\n                   3\r\n                   4\r\n                   5\r\nExecuted in 5.038 seconds\r\nSELECT<\/span> frb_slow_test(dummy) FROM<\/span> frb_test;\r\nFRB_SLOW_TEST(DUMMY)\r\n--------------------\r\n                   1\r\n                   2\r\n                   3\r\n                   4\r\n                   5\r\nExecuted in 0.047 seconds<\/pre>\n
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\u2019t 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.
\nIf you would like your PL\/SQL code to use this feature as soon as it\u2019s available, you might want to add it to your codebase and use Conditional Compilation<\/a> 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.<\/p>\n
\n
CREATE<\/span> OR<\/span> REPLACE<\/span> FUNCTION<\/span> frc_multiply_c(left_in  IN<\/span> NUMBER<\/span>\r\n                                         ,right_in IN<\/span> NUMBER<\/span>) RETURN<\/span> NUMBER<\/span>\r\n$IF DBMS_DB_VERSION.ver_le_11 $THEN\r\n  $ELSE\r\nRESULT_CACHE<\/span>\r\n$END\r\n IS<\/span>\r\n...<\/span><\/pre>\n<\/div>\n
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.<\/p>\n
DBA<\/h1>\n
To check if the cache is enabled and how it\u2019s configured you can run this statement<\/p>\n
EXEC DBMS_RESULT_CACHE.memory_report(detailed => true);<\/pre>\n
 <\/p>\n
to get a report like this:<\/p>\n
R e s u l t   C a c h e   M e m o r y   R e p o r t\r\n[Parameters]\r\nBlock Size          = 1K bytes\r\nMaximum Cache Size  = 2080K bytes (2080 blocks)\r\nMaximum Result Size = 104K bytes (104 blocks)\r\n[Memory]\r\nTotal Memory = 157384 bytes [0.050% of the Shared Pool]\r\n\r\n... Fixed Memory =  bytes [% of the Shared Pool]\r\n\r\n....... State Objs =  bytes\r\n... Dynamic Memory = 157384 bytes [0.050% of the Shared Pool]\r\n....... Overhead = 124616 bytes\r\n........... Hash Table    = 64K bytes (4K buckets)\r\n........... Chunk Ptrs    = 24K bytes (3K slots)\r\n........... Chunk Maps    = 12K bytes\r\n........... Miscellaneous = 124616 bytes\r\n....... Cache Memory = 32K bytes (32 blocks)\r\n........... Unused Memory = 0 blocks\r\n........... Used Memory = 32 blocks\r\n............... Dependencies = 12 blocks (12 count)\r\n............... Results = 20 blocks\r\n................... SQL     = 4 blocks (4 count)\r\n................... Invalid = 16 blocks (16 count)<\/pre>\n
 <\/p>\n
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<\/span><\/span> initialization parameter. The possible parameter values are MANUAL<\/span><\/span> and FORCE<\/span><\/span>. When set to MANUAL<\/span><\/span>, you control when the results are cached by using the RESULT_CACHE<\/span><\/span> hint. When set to FORCE<\/span><\/span>, everything is cached (if possible) unless you use the NO_RESULT_CACHE<\/span><\/span> hint to bypass the cache.<\/p>\n
Ref:
\n http:\/\/docs.oracle.com\/cd\/B19306_01\/server.102\/b14200\/statements_5009.htm<\/a>
\n http:\/\/docs.oracle.com\/cd\/B28359_01\/appdev.111\/b28370\/fundamentals.htm#LNPLS00210<\/a>
\n http:\/\/docs.oracle.com\/cd\/B28359_01\/server.111\/b28274\/memory.htm#PFGRF10121<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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\u2019s create a simple table, add some data and a simple, slow function: CREATE OR REPLACE FUNCTION…<\/p>\n","protected":false},"author":316181,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[6006,48491,5710,5921],"coauthors":[],"class_list":["post-73214","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-caching","tag-result-caching","tag-tables","tag-views"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73214","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316181"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73214"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73214\/revisions"}],"predecessor-version":[{"id":91698,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73214\/revisions\/91698"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73214"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73214"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73214"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73214"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}