{"id":13221,"date":"2020-01-05T22:56:45","date_gmt":"2020-01-05T21:56:45","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/"},"modified":"2020-01-05T22:56:45","modified_gmt":"2020-01-05T21:56:45","slug":"oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/","title":{"rendered":"Oracle FAST=TRUE in sqlplus? Some thoughts about rowprefetch"},"content":{"rendered":"<p>During my time as a Consultant working on Tuning Tasks I had the feeling that many people think that there is an Oracle-parameter &#8220;FAST=TRUE&#8221; to speed up the performance and throughput of the database calls. Unfortunately such a parameter is not available, but since version 12cR2 Oracle provided the option &#8220;-F&#8221; or &#8220;-FAST&#8221; for sqlplus, which looks like a &#8220;FAST=TRUE&#8221;-setting. Here an excerpt from the documentation:<\/p>\n<pre><code>\nThe FAST option improves general performance. This command line option changes the values of the following default settings:\n&nbsp;\n- ARRAYSIZE = 100\n- LOBPREFETCH = 16384\n- PAGESIZE = 50000\n- ROWPREFETCH = 2\n- STATEMENTCACHE = 20\n<\/code><\/pre>\n<p>I was interested in where the rowprefetch-setting could result in an improvement. <\/p>\n<p>The documentation about rowprefetch is as follows:<\/p>\n<pre><code>\nSET ROWPREFETCH {1 | n}\n&nbsp;\nSets the number of rows that SQL*Plus will prefetch from the database at one time. The default value is 1.\n&nbsp;\nExample\n&nbsp;\nTo set the number of prefetched rows to 200, enter\n&nbsp;\nSET ROWPREFETCH 200\n&nbsp;\nIf you do not specify a value for n, the default is 1 row. This means that rowprefetching is off.\n&nbsp;\nNote: The amount of data contained in the prefetched rows should not exceed the maximum value of 2147483648 bytes (2 Gigabytes). The  setting in the oraaccess.xml file can override the SET ROWPREFETCH setting in SQL*Plus. For more information about oraaccess.xml, see the Oracle Call Interface Programmer's Guide. \n<\/code><\/pre>\n<p>A simple test where rowprefetch can make a difference is the use of hash clusters (see the Buffers column in the execution plan below). E.g.<\/p>\n<pre><code>\nSQL&gt; create cluster DEMO_CLUSTER(CUST_ID number) size 4096 single table hashkeys 1000 ;\n&nbsp;\nCluster created.\n&nbsp;\nSQL&gt; create table DEMO cluster DEMO_CLUSTER(CUST_ID) as select * from CUSTOMERS;\n&nbsp;\nTable created.\n&nbsp;\nSQL&gt; exec dbms_stats.gather_table_stats(user,'DEMO');\n&nbsp;\nPL\/SQL procedure successfully completed.\n&nbsp;\nSQL&gt; select num_rows,blocks from user_tables where table_name='DEMO';\n&nbsp;\n  NUM_ROWS     BLOCKS\n---------- ----------\n     55500\t 1035\n&nbsp;\nSQL&gt; show rowprefetch\nrowprefetch 1\nSQL&gt; select \/*+ gather_plan_statistics *\/ rowid,cust_id from DEMO where cust_id=101;\n&nbsp;\nROWID\t\t      CUST_ID\n------------------ ----------\nAAAR4qAAMAAAAedAAA\t  101\n&nbsp;\nSQL&gt; select * from table(dbms_xplan.display_cursor(format=&gt;'allstats last'));\n&nbsp;\nPLAN_TABLE_OUTPUT\n-----------------------------------------\nSQL_ID\t9g2nyr9h2ytk4, child number 0\n-------------------------------------\nselect \/*+ gather_plan_statistics *\/ rowid,cust_id from DEMO where\ncust_id=101\n&nbsp;\nPlan hash value: 3286081706\n&nbsp;\n------------------------------------------------------------------------------------\n| Id  | Operation\t  | Name | Starts | E-Rows | A-Rows |\tA-Time\t | Buffers |\n------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |\t |\t1 |\t   |\t  1 |00:00:00.01 |\t 2 |\n|*  1 |  TABLE ACCESS HASH| DEMO |\t1 |\t 1 |\t  1 |00:00:00.01 |\t 2 |\n------------------------------------------------------------------------------------\n&nbsp;\nPredicate Information (identified by operation id):\n---------------------------------------------------\n&nbsp;\n   1 - access(\"CUST_ID\"=101)\n&nbsp;\nSQL&gt; set rowprefetch 2\nSQL&gt; select \/*+ gather_plan_statistics *\/ rowid,cust_id from DEMO where cust_id=101;\n&nbsp;\nROWID\t\t      CUST_ID\n------------------ ----------\nAAAR4qAAMAAAAedAAA\t  101\n&nbsp;\nSQL&gt; select * from table(dbms_xplan.display_cursor(format=&gt;'allstats last'));\n&nbsp;\nPLAN_TABLE_OUTPUT\n-----------------------------------------\nSQL_ID\t9g2nyr9h2ytk4, child number 0\n-------------------------------------\nselect \/*+ gather_plan_statistics *\/ rowid,cust_id from DEMO where\ncust_id=101\n&nbsp;\nPlan hash value: 3286081706\n&nbsp;\n------------------------------------------------------------------------------------\n| Id  | Operation\t  | Name | Starts | E-Rows | A-Rows |\tA-Time\t | Buffers |\n------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |\t |\t1 |\t   |\t  1 |00:00:00.01 |\t 1 |\n|*  1 |  TABLE ACCESS HASH| DEMO |\t1 |\t 1 |\t  1 |00:00:00.01 |\t 1 |\n------------------------------------------------------------------------------------\n&nbsp;\nPredicate Information (identified by operation id):\n---------------------------------------------------\n&nbsp;\n   1 - access(\"CUST_ID\"=101)\n<\/code><\/pre>\n<p>Due to the prefetch of 2 rows Oracle detects that there actually is only 1 row and avoids the second logical IO (a second fetch).<br \/>\nIf cust_id is unique then I would have created a unique (or primary) key constraint here, which would avoid a second fetch as well (because Oracle knows from the constraint that there can be max 1 row per cust_id), but in that case I have to maintain the created index.<\/p>\n<p>I made a couple of tests, which compared the behaviour with different settings of rowprefetch and arraysize in sqlplus (what is actually the difference between the 2 settings?). That will be a subject of a future Blog.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>During my time as a Consultant working on Tuning Tasks I had the feeling that many people think that there is an Oracle-parameter &#8220;FAST=TRUE&#8221; to speed up the performance and throughput of the database calls. Unfortunately such a parameter is not available, but since version 12cR2 Oracle provided the option &#8220;-F&#8221; or &#8220;-FAST&#8221; for sqlplus, [&hellip;]<\/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":[96,1791,985],"type_dbi":[],"class_list":["post-13221","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-oracle","tag-oracle","tag-rowprefetch","tag-sqlplus"],"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>Oracle FAST=TRUE in sqlplus? Some thoughts about rowprefetch - 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\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle FAST=TRUE in sqlplus? Some thoughts about rowprefetch\" \/>\n<meta property=\"og:description\" content=\"During my time as a Consultant working on Tuning Tasks I had the feeling that many people think that there is an Oracle-parameter &#8220;FAST=TRUE&#8221; to speed up the performance and throughput of the database calls. Unfortunately such a parameter is not available, but since version 12cR2 Oracle provided the option &#8220;-F&#8221; or &#8220;-FAST&#8221; for sqlplus, [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2020-01-05T21:56:45+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\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/\"},\"author\":{\"name\":\"Clemens Bleile\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\"},\"headline\":\"Oracle FAST=TRUE in sqlplus? Some thoughts about rowprefetch\",\"datePublished\":\"2020-01-05T21:56:45+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/\"},\"wordCount\":233,\"commentCount\":0,\"keywords\":[\"Oracle\",\"rowprefetch\",\"sqlplus\"],\"articleSection\":[\"Database Administration &amp; Monitoring\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/\",\"name\":\"Oracle FAST=TRUE in sqlplus? Some thoughts about rowprefetch - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2020-01-05T21:56:45+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle FAST=TRUE in sqlplus? Some thoughts about rowprefetch\"}]},{\"@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":"Oracle FAST=TRUE in sqlplus? Some thoughts about rowprefetch - 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\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/","og_locale":"en_US","og_type":"article","og_title":"Oracle FAST=TRUE in sqlplus? Some thoughts about rowprefetch","og_description":"During my time as a Consultant working on Tuning Tasks I had the feeling that many people think that there is an Oracle-parameter &#8220;FAST=TRUE&#8221; to speed up the performance and throughput of the database calls. Unfortunately such a parameter is not available, but since version 12cR2 Oracle provided the option &#8220;-F&#8221; or &#8220;-FAST&#8221; for sqlplus, [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/","og_site_name":"dbi Blog","article_published_time":"2020-01-05T21:56:45+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\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/"},"author":{"name":"Clemens Bleile","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"headline":"Oracle FAST=TRUE in sqlplus? Some thoughts about rowprefetch","datePublished":"2020-01-05T21:56:45+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/"},"wordCount":233,"commentCount":0,"keywords":["Oracle","rowprefetch","sqlplus"],"articleSection":["Database Administration &amp; Monitoring","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/","name":"Oracle FAST=TRUE in sqlplus? Some thoughts about rowprefetch - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2020-01-05T21:56:45+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-fasttrue-in-sqlplus-some-thoughts-about-rowprefetch\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle FAST=TRUE in sqlplus? Some thoughts about rowprefetch"}]},{"@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\/13221","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=13221"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/13221\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=13221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=13221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=13221"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=13221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}