{"id":13866,"date":"2020-03-28T00:35:10","date_gmt":"2020-03-27T23:35:10","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/a-change-in-full-table-scan-costs-in-19c\/"},"modified":"2020-03-28T00:35:10","modified_gmt":"2020-03-27T23:35:10","slug":"a-change-in-full-table-scan-costs-in-19c","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/a-change-in-full-table-scan-costs-in-19c\/","title":{"rendered":"A change in full table scan costs in 19c?"},"content":{"rendered":"

During tests in Oracle 19c I recently experienced this:<\/p>\n

\ncbleile@orcl@orcl> select * from demo4 where m=103;\ncbleile@orcl@orcl> select * from table(dbms_xplan.display_cursor);\n...\n---------------------------------------------------------------------------\n| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |\n---------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |       |       |       | 26439 (100)|          |\n|*  1 |  TABLE ACCESS FULL| DEMO4 |     1 |    10 | 26439  (14)| 00:00:02 |\n---------------------------------------------------------------------------\n<\/pre>\n
–> The costs of the full table scan are 26439.<\/p>\n
Setting back the optimizer_features_enable to 18.1.0 showed different full table scan costs:<\/p>\n
\ncbleile@orcl@orcl> alter session set optimizer_features_enable='18.1.0';\ncbleile@orcl@orcl> select * from demo4 where m=103;\ncbleile@orcl@orcl> select * from table(dbms_xplan.display_cursor);\n...\n---------------------------------------------------------------------------\n| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |\n---------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |       |       |       |   109K(100)|          |\n|*  1 |  TABLE ACCESS FULL| DEMO4 |     1 |    10 |   109K  (4)| 00:00:05 |\n---------------------------------------------------------------------------\n<\/pre>\n
–> The costs are 109K versus around 26K in 19c.<\/p>\n
Why do we have such a difference for the costs of a full table scan between 18c and 19c?
\nWith the CPU-cost model full table scans are computed as follows:<\/p>\n
FTS Cost = ((BLOCKS\/MBRC) x MREADTIM)\/ SREADTIM + “CPU-costs”
\nREMARK: This is not 100% correct, but the difference is not important here.<\/p>\n
In my case:<\/p>\n
\ncbleile@orcl@orcl> select blocks from tabs where table_name='DEMO4';\n \n    BLOCKS\n----------\n     84888\n \ncbleile@orcl@orcl> select * from sys.aux_stats$;\n \nSNAME                          PNAME                          PVAL1      PVAL2\n------------------------------ ------------------------------ ---------- ----------\n...\nSYSSTATS_MAIN                  SREADTIM                       1\nSYSSTATS_MAIN                  MREADTIM                       10\nSYSSTATS_MAIN                  CPUSPEED                       2852\nSYSSTATS_MAIN                  MBRC                           8\n...\n<\/pre>\n
I.e.
\nFTS Cost = ((BLOCKS\/MBRC) x MREADTIM)\/ SREADTIM + CPU = ((84888\/8) x 10)\/ 1 + CPU = 106110 + CPU
\nConsidering the additional CPU-cost we are at the costs we see in 18c: 109K
\nWhy do we see only costs of 26439 in 19c (around 25% of 18c)?
\nThe reason is that the optimizer considers “wrong” system statistics here. I.e. let’s check the system statistics again:<\/p>\n
\nSNAME                          PNAME                          PVAL1      PVAL2\n------------------------------ ------------------------------ ---------- ---------\nSYSSTATS_MAIN                  SREADTIM                       1\nSYSSTATS_MAIN                  MREADTIM                       10\nSYSSTATS_MAIN                  MBRC                           8\n<\/pre>\n
In theory it’s not possible that MREADTIM > SREADTIM * MBRC. I.e. reading e.g. 8 contiguous blocks from disk cannot be slower than reading 8 random blocks from disk. Oracle has considered that and treats the available system statistics as wrong and takes different values internally. The change was implemented with bug fix 27643128. See My Oracle Support Note “Optimizer Chooses Expensive Index Full Scan over Index Fast Full Scan or Full Table Scan from 12.1 (Doc ID 2382922.1)” for details.<\/p>\n
I.e. switching the bug fix off results in full table scan costs as in 18c:<\/p>\n
\ncbleile@orcl@orcl> alter session set optimizer_features_enable='19.1.0';\ncbleile@orcl@orcl> alter session set \"_fix_control\"='27643128:OFF';\ncbleile@orcl@orcl> select * from demo4 where m=103;\ncbleile@orcl@orcl> select * from table(dbms_xplan.display_cursor);\n...\n---------------------------------------------------------------------------\n| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |\n---------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |       |       |       |   109K(100)|          |\n|*  1 |  TABLE ACCESS FULL| DEMO4 |\t    1 |    10 |   109K  (4)| 00:00:05 |\n---------------------------------------------------------------------------\n<\/pre>\n
To get the intended behavior in 19c you should make sure that
\nMREADTIM <= SREADTIM * MBRC
\nE.g. in my case<\/p>\n
\ncbleile@orcl@orcl> alter system set db_file_multiblock_read_count=12\ncbleile@orcl@orcl> exec dbms_stats.set_system_stats('MBRC',12);\ncbleile@orcl@orcl> alter session set optimizer_features_enable='19.1.0';\ncbleile@orcl@orcl> select * from demo4 where m=103;\ncbleile@orcl@orcl> select * from table(dbms_xplan.display_cursor);\n...\n---------------------------------------------------------------------------\n| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |\n---------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |       |       |       | 74188 (100)|          |\n|*  1 |  TABLE ACCESS FULL| DEMO4 |\t    1 |    10 | 74188   (5)| 00:00:03 |\n---------------------------------------------------------------------------\n...\n \ncbleile@orcl@orcl> alter session set optimizer_features_enable='18.1.0';\ncbleile@orcl@orcl> select * from demo4 where m=103;\ncbleile@orcl@orcl> select * from table(dbms_xplan.display_cursor);\n...\n---------------------------------------------------------------------------\n| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |\n---------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |\t      |       |       | 74188 (100)|          |\n|*  1 |  TABLE ACCESS FULL| DEMO4 |\t    1 |    10 | 74188   (5)| 00:00:03 |\n---------------------------------------------------------------------------\n...\n<\/pre>\n
I.e. the costs in 19c and 18c are the same again.<\/p>\n
Please consider the following:
\n– If you’ve gathered or set system statistics then always check that they are reasonable.
\n– If you do work with a very low SREADTIM and high MREADTIM to favor Index-access (to not use low values for OPTIMIZER_INDEX_COST_ADJ) then make sure that MREADTIM <= SREADTIM * MBRC. Otherwise you may see plan changes when migrating to 19c.<\/p>\n","protected":false},"excerpt":{"rendered":"
During tests in Oracle 19c I recently experienced this: cbleile@orcl@orcl> select * from demo4 where m=103; cbleile@orcl@orcl> select * from table(dbms_xplan.display_cursor); … ————————————————————————— | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ————————————————————————— | 0 | SELECT STATEMENT | | | | 26439 (100)| | |* 1 | TABLE […]<\/p>\n","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,59],"tags":[62,1890,1891,96],"type_dbi":[],"class_list":["post-13866","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-oracle","tag-19c","tag-costs","tag-full-table-scan","tag-oracle"],"acf":[],"yoast_head":"\nA change in full table scan costs in 19c? - 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\/a-change-in-full-table-scan-costs-in-19c\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"A change in full table scan costs in 19c?\" \/>\n<meta property=\"og:description\" content=\"During tests in Oracle 19c I recently experienced this: cbleile@orcl@orcl> select * from demo4 where m=103; cbleile@orcl@orcl> select * from table(dbms_xplan.display_cursor); ... --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 26439 (100)| | |* 1 | TABLE […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/a-change-in-full-table-scan-costs-in-19c\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-03-27T23:35:10+00:00\" \/>\n<meta name=\"author\" content=\"Clemens Bleile\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@ifgtxD2SrQ8r!YuXj\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Clemens Bleile\" \/>\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\\\/a-change-in-full-table-scan-costs-in-19c\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-change-in-full-table-scan-costs-in-19c\\\/\"},\"author\":{\"name\":\"Clemens Bleile\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\"},\"headline\":\"A change in full table scan costs in 19c?\",\"datePublished\":\"2020-03-27T23:35:10+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-change-in-full-table-scan-costs-in-19c\\\/\"},\"wordCount\":355,\"commentCount\":0,\"keywords\":[\"19c\",\"costs\",\"Full Table Scan\",\"Oracle\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-change-in-full-table-scan-costs-in-19c\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-change-in-full-table-scan-costs-in-19c\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-change-in-full-table-scan-costs-in-19c\\\/\",\"name\":\"A change in full table scan costs in 19c? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2020-03-27T23:35:10+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-change-in-full-table-scan-costs-in-19c\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-change-in-full-table-scan-costs-in-19c\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/a-change-in-full-table-scan-costs-in-19c\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"A change in full table scan costs in 19c?\"}]},{\"@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\\\/0ac04011f60f2e93c115358d0789c2da\",\"name\":\"Clemens Bleile\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g\",\"caption\":\"Clemens Bleile\"},\"description\":\"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.\",\"sameAs\":[\"https:\\\/\\\/www.dbi-services.com\",\"https:\\\/\\\/x.com\\\/ifgtxD2SrQ8r!YuXj\"],\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/clemens-bleile\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"A change in full table scan costs in 19c? - 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\/a-change-in-full-table-scan-costs-in-19c\/","og_locale":"en_US","og_type":"article","og_title":"A change in full table scan costs in 19c?","og_description":"During tests in Oracle 19c I recently experienced this: cbleile@orcl@orcl> select * from demo4 where m=103; cbleile@orcl@orcl> select * from table(dbms_xplan.display_cursor); ... --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 26439 (100)| | |* 1 | TABLE […]","og_url":"https:\/\/www.dbi-services.com\/blog\/a-change-in-full-table-scan-costs-in-19c\/","og_site_name":"dbi Blog","article_published_time":"2020-03-27T23:35:10+00:00","author":"Clemens Bleile","twitter_card":"summary_large_image","twitter_creator":"@ifgtxD2SrQ8r!YuXj","twitter_misc":{"Written by":"Clemens Bleile","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/a-change-in-full-table-scan-costs-in-19c\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-change-in-full-table-scan-costs-in-19c\/"},"author":{"name":"Clemens Bleile","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"headline":"A change in full table scan costs in 19c?","datePublished":"2020-03-27T23:35:10+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-change-in-full-table-scan-costs-in-19c\/"},"wordCount":355,"commentCount":0,"keywords":["19c","costs","Full Table Scan","Oracle"],"articleSection":["Database Administration & Monitoring","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/a-change-in-full-table-scan-costs-in-19c\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/a-change-in-full-table-scan-costs-in-19c\/","url":"https:\/\/www.dbi-services.com\/blog\/a-change-in-full-table-scan-costs-in-19c\/","name":"A change in full table scan costs in 19c? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-03-27T23:35:10+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/a-change-in-full-table-scan-costs-in-19c\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/a-change-in-full-table-scan-costs-in-19c\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/a-change-in-full-table-scan-costs-in-19c\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"A change in full table scan costs in 19c?"}]},{"@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\/0ac04011f60f2e93c115358d0789c2da","name":"Clemens Bleile","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1f596609fc67cb28ed714e7bccc81ed4cd73b8582a8148a490c77daeb2fde21a?s=96&d=mm&r=g","caption":"Clemens Bleile"},"description":"Clemens Bleile has more than 30 years of IT experience, thirteen in Oracle Support and fifteen in Oracle Consulting. He is specialized in Oracle Database Performance Tuning (SQL Tuning, DB Tuning) and developing an Oracle DB IT architecture (highly available, low-maintenance, cost efficient storage of data). He is an expert in problem analysis and resolution. Prior to joining dbi services, Clemens Bleile was Manager of the EMEA Database Performance team at the Oracle Global Customer Support Services. Clemens Bleile is Oracle Certified Professional 11g, 12c and Oracle Certified Expert for Performance Management and Tuning and holds a Master Degree, Business Information Systems from the Fachhochschule Furtwangen, Germany.","sameAs":["https:\/\/www.dbi-services.com","https:\/\/x.com\/ifgtxD2SrQ8r!YuXj"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/clemens-bleile\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13866","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\/35"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=13866"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13866\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=13866"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=13866"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=13866"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=13866"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}