{"id":4851,"date":"2015-06-12T07:28:46","date_gmt":"2015-06-12T05:28:46","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/"},"modified":"2015-06-12T07:28:46","modified_gmt":"2015-06-12T05:28:46","slug":"12c-dynamic-sampling-and-standard-edition","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/","title":{"rendered":"12c Dynamic Sampling and Standard Edition"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\n12c is coming with more dynamic sampling, now called dynamic statistics and using the new Adaptive Dynamic Sampling algorithm. The goal is to have better estimations and better estimations gives better execution plans. However, this new approach will increase parse time because dynamic sampling kicks in more often, reads more blocs, and run more queries.<\/p>\n<p>It&#8217;s probably not a problem for applications that are well designed, using bind variables to avoid to many parses, having good statistics (histograms where it makes sense, extended statistics for correlated columns). The SQL Plan Directives are there to trigger dynamic sampling only where misestimates have been observed. An OLTP application should not parse often, and should not have huge misestimates. A reporting use-case can spend more time on parsing and the few seconds spend to do dynamic sampling will probably benefit to the execution time.<\/p>\n<p>In addition to that, in order to lower the dynamic sampling overhead, Oracle 12c Adaptive Dynamic Sampling run its queries with the \/*+ result_cache(snapshot=3600) *\/ hint. The result is cached in the result cache and is not invalidated by dependencies. So even when the underlying table is updated, the dynamic sampling result is still valid in cache for 3600 seconds. This is why doing more dynamic sampling is not a big overhead according that:<\/p>\n<ul>\n<li>your result cache is sized accordingly. The default (0.25% of MEMORY_TARGET or 0.5% of SGA_TARGET or 1% of SHARED_POOL_SIZE) is probably too low to fit all the dynamic sampling result for frequently parsed statements.<\/li>\n<li>your result cache is enabled, meaning that you are in Enterprise Edition<\/li>\n<\/ul>\n<p>So the question of the day is that I want to know if the RESULT_CACHE hint is just ignored in Standard Edition, or if there is a mechanism that allows it from Adaptive Dynamic Sampling.<\/p>\n<p>If you have a bad application (not using bind variables, parse at each execution) and you are in Standard Edition, then there is a risk that the current parse contention you suffer from (CPU and latches) will be more problematic (more CPU and I\/O). Let&#8217;s try the following:<\/p>\n<pre><code>declare\n c sys_refcursor;\nbegin\n for i in 1..100\n loop\n  open c for 'select count(*) COUNT'||i||' from DEMO_TABLE where a+b=c+d';\n  dbms_sql.return_result(c);\n  null;\n end loop;\nend;\n\/\n<\/code><\/pre>\n<p>which run 100 times the same statement not using bind variables. So I&#8217;m parsing it each time, but it&#8217;s reading the same table with same predicate, so the result of dynamic sampling should not change a lot.<\/p>\n<p>I&#8217;ll run it in Standard and Enterprise editions, with no dynamic sampling, and with the new AUTO level.<\/p>\n<h3>Enterprise Edition with Adaptive Dynamic Sampling<\/h3>\n<pre><code>Connected to:\nOracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production\n\nSQL&gt; show parameter optimizer_dynamic_sampling\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\noptimizer_dynamic_sampling           integer     11\n<\/code><\/pre>\n<p>I&#8217;m in Enterprise Edition and with the new AUTO dynamic sampling level (11). Here are a few statistics from v$mystat:<\/p>\n<pre><code>NAME                                                                        VALUE\n------------------------------------------------------------ --------------------\nparse count (hard)                                                            204\nparse count (total)                                                           654\nparse time cpu                                                                 59\nparse time elapsed                                                             79\nrecursive calls                                                             4,460\nsession logical reads                                                     312,198\n\n<\/code><\/pre>\n<p>when I check result cache statistics, I can see that &#8216;Find Count&#8217; reaches the number of executions:<\/p>\n<pre><code>        ID NAME                           VALUE\n---------- ------------------------------ --------\n         1 Block Size (Bytes)             1024\n         2 Block Count Maximum            5120\n         3 Block Count Current            32\n         4 Result Size Maximum (Blocks)   256\n         5 Create Count Success           17\n         6 Create Count Failure           0\n         7 Find Count                     104\n         8 Invalidation Count             0\n         9 Delete Count Invalid           0\n        10 Delete Count Valid             0\n        11 Hash Chain Length              1\n        12 Find Copy Count                104\n        13 Latch (Share)                  0\n<\/code><\/pre>\n<h3>Enterprise Edition without Dynamic Sampling<\/h3>\n<pre><code>Connected to:\nOracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production\n\nSQL&gt; show parameter optimizer_dynamic_sampling\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\noptimizer_dynamic_sampling           integer     0\n\n<\/code><\/pre>\n<p>I&#8217;m in Enterprise Edition but disabled the dynamic sampling.<\/p>\n<pre><code>NAME                                                                        VALUE\n------------------------------------------------------------ --------------------\nparse count (hard)                                                            196\nparse count (total)                                                           342\nparse time cpu                                                                 29\nparse time elapsed                                                             38\nrecursive calls                                                             3,527\nsession logical reads                                                     310,785\n\n<\/code><\/pre>\n<p>Here the parse time is smaller because we have less recursive calls. But the difference is not that big. Which means that the dynamic sampling was not a big overhead, thanks to result cache.<\/p>\n<h3>Standard Edition without Dynamic Sampling<\/h3>\n<pre><code>Connected to:\nOracle Database 12c Release 12.1.0.1.0 - 64bit Production\n\nSQL&gt; show parameter optimizer_dynamic_sampling\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\noptimizer_dynamic_sampling           integer     0\n\n<\/code><\/pre>\n<p>I&#8217;m now connected to a Standard Edition with the dynamic sampling disabled:<\/p>\n<pre><code>NAME                                                                        VALUE\n------------------------------------------------------------ --------------------\nparse count (hard)                                                            167\nparse count (total)                                                           271\nparse time cpu                                                                 26\nparse time elapsed                                                             22\nrecursive calls                                                             2,131\nsession logical reads                                                     309,449\n\n<\/code><\/pre>\n<p>This is very similar to the previous one. When dynamic sampling is not enabled, then the parsing time is similar in SE and in EE.<\/p>\n<h3>Standard Edition with Adaptive Dynamic Sampling<\/h3>\n<pre><code>Connected to:\nOracle Database 12c Release 12.1.0.1.0 - 64bit Production\n\nSQL&gt; show parameter optimizer_dynamic_sampling\nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\noptimizer_dynamic_sampling           integer     11\n\n<\/code><\/pre>\n<p>And finally here is the level=AUTO dynamic sampling in Standard Edition:<\/p>\n<pre><code>NAME                                                                        VALUE\n------------------------------------------------------------ --------------------\nparse count (hard)                                                            204\nparse count (total)                                                           649\nparse time cpu                                                                690\nparse time elapsed                                                            879\nrecursive calls                                                             4,256\nsession logical reads                                                   1,019,986\n\n<\/code><\/pre>\n<p>This is where we have a problem. Now wit Adaptive Dynamic Sampling, we are doing the same number of recursive calls as in EE but the result cache is not there to optimize them. My parse time is 10x higher. The number of blocks read is huge. there is no result cache here. And I&#8217;ve been running the queries from the same session where an internal cache is supposed to help.<\/p>\n<h3>Conclusion<\/h3>\n<p>The result cache &#8211; which is very important to ensure that the new 12c adaptive dynamic sampling is not a huge overhead at parsing &#8211; is not available in Standard Edition. Which means that if you already have parsing issues, you should solve them before gping to 12c or maybe you will have to disable the adaptive dynamic sampling.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By Franck Pachot . 12c is coming with more dynamic sampling, now called dynamic statistics and using the new Adaptive Dynamic Sampling algorithm. The goal is to have better estimations and better estimations gives better execution plans. However, this new approach will increase parse time because dynamic sampling kicks in more often, reads more blocs, [&hellip;]<\/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":[198,59],"tags":[],"type_dbi":[],"class_list":["post-4851","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>12c Dynamic Sampling and Standard Edition - dbi Blog<\/title>\n<meta name=\"description\" content=\"12c Dynamic Sampling is based on result cache, which is not available in Standard Edition\" \/>\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\/12c-dynamic-sampling-and-standard-edition\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"12c Dynamic Sampling and Standard Edition\" \/>\n<meta property=\"og:description\" content=\"12c Dynamic Sampling is based on result cache, which is not available in Standard Edition\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-06-12T05:28:46+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=\"5 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\/12c-dynamic-sampling-and-standard-edition\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"12c Dynamic Sampling and Standard Edition\",\"datePublished\":\"2015-06-12T05:28:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/\"},\"wordCount\":700,\"commentCount\":0,\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/\",\"name\":\"12c Dynamic Sampling and Standard Edition - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-06-12T05:28:46+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"12c Dynamic Sampling is based on result cache, which is not available in Standard Edition\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"12c Dynamic Sampling and Standard Edition\"}]},{\"@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":"12c Dynamic Sampling and Standard Edition - dbi Blog","description":"12c Dynamic Sampling is based on result cache, which is not available in Standard Edition","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\/12c-dynamic-sampling-and-standard-edition\/","og_locale":"en_US","og_type":"article","og_title":"12c Dynamic Sampling and Standard Edition","og_description":"12c Dynamic Sampling is based on result cache, which is not available in Standard Edition","og_url":"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/","og_site_name":"dbi Blog","article_published_time":"2015-06-12T05:28:46+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"12c Dynamic Sampling and Standard Edition","datePublished":"2015-06-12T05:28:46+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/"},"wordCount":700,"commentCount":0,"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/","url":"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/","name":"12c Dynamic Sampling and Standard Edition - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-06-12T05:28:46+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"12c Dynamic Sampling is based on result cache, which is not available in Standard Edition","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/12c-dynamic-sampling-and-standard-edition\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"12c Dynamic Sampling and Standard Edition"}]},{"@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\/4851","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=4851"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4851\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4851"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4851"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4851"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4851"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}