.
\nDuring the execution of a SQL statement, you cannot guess how many times an operation, a predicate, or a function will be executed. This depends on the execution plan, on some caching at execution, and some other execution time decisions. Here is an example where result cache may bring some overhead by calling a function multiple times.
\n
\nHere is my function:<\/p>\n
SQL> create or replace function F return number is\n 2 begin\n 3 dbms_lock.sleep(5);\n 4 dbms_output.put_line('Hello World');\n 5 return 255;\n 6 end;\n 7 \/\nFunction created.<\/code><\/pre>\nThe function displays ‘Hello World’ so that I can check how many times it is executed (I’ve set serveroutput on).<\/p>\n
Obviously, on a one row table, it is called only once:<\/p>\n
SQL> select f from dual;\n \n F\n----------\n 255\n \nHello World<\/code><\/pre>\nQuery result cache miss<\/h3>\n
I’ll run now the same query but with the result cache hint. The first execution will have to execute the query because the cache is empty at that point:<\/p>\n
SQL> exec dbms_result_cache.flush;\nPL\/SQL procedure successfully completed.\n \nSQL> select \/*+ result_cache *\/ f from dual;\n \n F\n----------\n 255\n \nHello World\nHello World<\/code><\/pre>\nHere is what I wanted to show: ‘Hello World’ is displayed two times instead of one. If your function is an expensive one, then the first execution, or every cache miss, will have a performance overhead.<\/p>\n
Query result cache hit<\/h3>\n
Now that the result is in the cache:<\/p>\n
SQL> select id, type, status, name from v$result_cache_objects;\n \n ID TYPE STATUS NAME\n---------- ---------- --------- ------------------------------------------------------------\n 33 Dependency Published DEMO.F\n 34 Result Published select \/*+ result_cache *\/ f from dual<\/code><\/pre>\nand the table has not changed (it’s DUAL here :; ) further executions do not call the function anymore, which is the expected result.<\/p>\n
SQL> select \/*+ result_cache *\/ f from dual ;\n \n F\n----------\n 255<\/code><\/pre>\nBug or not?<\/h3>\n
Bug 21484570 has been opened for that and closed as ‘Not a bug’. There is no guarantee that the function is evaluated once, twice, more or never.
\nOk, why not. That’s an implementation decision. Just think about it if you want to workaround an expensive function called for each row, then query result cache may not be the right solution (except if all tables are static and you always have cache hits).<\/p>\n
Note that if the function is declared as deterministic, it is executed only once.<\/p>\n
You can workaround the issue by using result cache at function level (in place, or in addition to query result cache if you need it).<\/p>\n
SQL> create or replace function F return number RESULT_CACHE is\n 2 begin\n 3 dbms_lock.sleep(5);\n 4 dbms_output.put_line('Hello World');\n 5 return 255;\n 6 end;\n 7 \/\nFunction created.\n \nSQL> select \/*+ result_cache *\/ f from dual;\n \n F\n----------\n 255\n \nHello World\n \nSQL> select id, type, status, name from v$result_cache_objects;\n \n ID TYPE STATUS NAME\n---------- ---------- --------- ------------------------------------------------------------\n 64 Dependency Published DEMO.F\n 66 Result Published \"DEMO\".\"F\"::8.\"F\"#e17d780a3c3eae3d #1\n 65 Result Published select \/*+ result_cache *\/ f from dual<\/code><\/pre>\nSo, not a big problem. Just something to know. And anyway, the right design is NOT to call a function for each row because it’s not scalable. Pipeline functions should be used for that.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . During the execution of a SQL statement, you cannot guess how many times an operation, a predicate, or a function will be executed. This depends on the execution plan, on some caching at execution, and some other execution time decisions. Here is an example where result cache may bring some overhead […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[96,303],"type_dbi":[],"class_list":["post-8840","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-oracle","tag-result-cache"],"acf":[],"yoast_head":"\n
Result cache side effects on number of calls - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Result cache side effects on number of calls\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . During the execution of a SQL statement, you cannot guess how many times an operation, a predicate, or a function will be executed. This depends on the execution plan, on some caching at execution, and some other execution time decisions. Here is an example where result cache may bring some overhead […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-09-13T07:38:25+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Result cache side effects on number of calls\",\"datePublished\":\"2016-09-13T07:38:25+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/\"},\"wordCount\":358,\"commentCount\":0,\"keywords\":[\"Oracle\",\"Result cache\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/\",\"name\":\"Result cache side effects on number of calls - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2016-09-13T07:38:25+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Result cache side effects on number of calls\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Result cache side effects on number of calls - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/","og_locale":"en_US","og_type":"article","og_title":"Result cache side effects on number of calls","og_description":"By Franck Pachot . During the execution of a SQL statement, you cannot guess how many times an operation, a predicate, or a function will be executed. This depends on the execution plan, on some caching at execution, and some other execution time decisions. Here is an example where result cache may bring some overhead […]","og_url":"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/","og_site_name":"dbi Blog","article_published_time":"2016-09-13T07:38:25+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Result cache side effects on number of calls","datePublished":"2016-09-13T07:38:25+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/"},"wordCount":358,"commentCount":0,"keywords":["Oracle","Result cache"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/","url":"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/","name":"Result cache side effects on number of calls - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2016-09-13T07:38:25+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/result-cache-side-effects-on-number-of-calls\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Result cache side effects on number of calls"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8840","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=8840"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8840\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=8840"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=8840"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=8840"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=8840"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}