{"id":20641,"date":"2022-11-24T17:08:12","date_gmt":"2022-11-24T16:08:12","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=20641"},"modified":"2022-11-24T17:37:53","modified_gmt":"2022-11-24T16:37:53","slug":"latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\/","title":{"rendered":"Latency test from Client to Oracle DB: Minimum number of rows fetched in sqlplus and sqlcl"},"content":{"rendered":"\n<p>Some time ago I wrote a <a href=\"https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/\">Blog<\/a> on how to calculate the network latency fetching a row to a Client from an Oracle database. The tool I used was sqlcl, because it just requires Java and hence no Oracle Client needs to be installed. Some people executed the script provided in the blog with sqlplus (also because current versions of sqlcl require Java 11) and saw only 1669 network round trips with 5000 rows and an arraysize of 1:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>test_netlat@orclcdb1@PDB1&gt; exec dbms_output.put_line(to_char(:roundtrips_end - :roundtrips_begin)||' network round trips.');\n<strong>1669 network round trips.<\/strong>\n\ntest_netlat@orclcdb1@PDB1&gt; exec dbms_output.put_line(to_char((:time_end - :time_begin)*10)||' ms elapsed time.');\n790 ms elapsed time.\n\ntest_netlat@orclcdb1@PDB1&gt; exec dbms_output.put_line(to_char((:db_time_end - :db_time_start)\/1000)||' ms DB time.');\n165.841 ms DB time.\n\ntest_netlat@orclcdb1@PDB1&gt; exec dbms_output.put_line(to_char(round((((:time_end - :time_begin)*10)-((:db_time_end - :db_time_start)\/1000))\/(:roundtrips_end - :roundtrips_begin),3))||' ms latency per round trip.');\n.374 ms latency per round trip.\n\ntest_netlat@orclcdb1@PDB1&gt; exec dbms_output.put_line('--&gt; (Elapsed Time - DB Time) \/ network round trips');\n--&gt; (Elapsed Time - DB Time) \/ network round trips\n<\/code><\/pre>\n\n\n\n<p>REMARK: rowprefetch was set to the default of 1 during the sqlplus tests.<\/p>\n\n\n\n<p>With sqlcl I can see a number of network round trips closer to the expected 5000:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL&gt; exec dbms_output.put_line(to_char(:roundtrips_end - :roundtrips_begin)||' network round trips.');\n<strong>4953 network round trips.<\/strong>\n\nSQL&gt; exec dbms_output.put_line(to_char((:time_end - :time_begin)*10)||' ms elapsed time.');\n2340 ms elapsed time.\n\nSQL&gt; exec dbms_output.put_line(to_char((:db_time_end - :db_time_start)\/1000)||' ms DB time.');\n987.403 ms DB time.\n\nSQL&gt; exec dbms_output.put_line(to_char(round((((:time_end - :time_begin)*10)-((:db_time_end - :db_time_start)\/1000))\/(:roundtrips_end - :roundtrips_begin),3))||' ms latency per round trip.');\n.273 ms latency per round trip.\n\nSQL&gt; exec dbms_output.put_line('--&gt; (Elapsed Time - DB Time) \/ network round trips');\n--&gt; (Elapsed Time - DB Time) \/ network round trips\n<\/code><\/pre>\n\n\n\n<p>So how can we explain the 1669 network round trips with sqlplus and the 4953 network round trips with sqlcl?<\/p>\n\n\n\n<p>The number of rows fetched can be checked with sql_trace:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>set feed only\nalter session set sql_trace=true;\nselect filler from tlat ;\nalter session set sql_trace=false;\nset feed on<\/code><\/pre>\n\n\n\n<p>The trace file shows the following lines when running this with a 19.16. sqlplus against a 19.16-database:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FETCH #139731510199544:c=67,e=66,p=0,cr=3,cu=0,mis=0,<strong>r=2<\/strong>,dep=0,og=1,plh=3090445234,tim=20585748787\nFETCH #139731510199544:c=9,e=9,p=0,cr=1,cu=0,mis=0,<strong>r=3<\/strong>,dep=0,og=1,plh=3090445234,tim=20585749126\nFETCH #139731510199544:c=7,e=7,p=0,cr=1,cu=0,mis=0,<strong>r=3<\/strong>,dep=0,og=1,plh=3090445234,tim=20585749293\nFETCH #139731510199544:c=8,e=8,p=0,cr=1,cu=0,mis=0,<strong>r=3<\/strong>,dep=0,og=1,plh=3090445234,tim=20585749521\nFETCH #139731510199544:c=8,e=8,p=0,cr=1,cu=0,mis=0,<strong>r=3<\/strong>,dep=0,og=1,plh=3090445234,tim=20585749706\nFETCH #139731510199544:c=0,e=8,p=0,cr=1,cu=0,mis=0,<strong>r=3<\/strong>,dep=0,og=1,plh=3090445234,tim=20585749874\n...\n<\/code><\/pre>\n\n\n\n<p>Important is the information &#8220;r=&#8221;. The first fetch fetched 2 rows and all subsequent fetches fetched 3 rows.<\/p>\n\n\n\n<p>With sqlcl (version 22.3.1.0) I can see 50 rows fetched on the first fetch and then the expected 1 row fetched at a time:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FETCH #140181223387392:c=52,e=52,p=0,cr=3,cu=0,mis=0,<strong>r=50<\/strong>,dep=0,og=1,plh=3090445234,tim=5975977699\nFETCH #140181223387392:c=17,e=17,p=0,cr=1,cu=0,mis=0,<strong>r=1<\/strong>,dep=0,og=1,plh=3090445234,tim=5975978365\nFETCH #140181223387392:c=0,e=19,p=0,cr=1,cu=0,mis=0,<strong>r=1<\/strong>,dep=0,og=1,plh=3090445234,tim=5975978679\nFETCH #140181223387392:c=0,e=13,p=0,cr=1,cu=0,mis=0,<strong>r=1<\/strong>,dep=0,og=1,plh=3090445234,tim=5975978968\nFETCH #140181223387392:c=13,e=13,p=0,cr=1,cu=0,mis=0,<strong>r=1<\/strong>,dep=0,og=1,plh=3090445234,tim=5975979473\nFETCH #140181223387392:c=13,e=13,p=0,cr=1,cu=0,mis=0,<strong>r=1<\/strong>,dep=0,og=1,plh=3090445234,tim=5975979909\n...\n<\/code><\/pre>\n\n\n\n<p>So sqlplus in 19c has a minimum fetchsize (arraysize) of 3 except for the first fetch and sqlcl fetches the expected 1 row except for the first fetch, which takes 50 rows.<\/p>\n\n\n\n<p>REMARK: In earlier releases of sqlplus the minimum for arraysize was 2 with a first fetch of 1 row. E.g. in 12.1.0.2 or 12.2.0.1:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FETCH #139833692592448:c=0,e=60,p=0,cr=4,cu=0,mis=0,<strong>r=1<\/strong>,dep=0,og=1,plh=3090445234,tim=422087587\nFETCH #139833692592448:c=0,e=23,p=0,cr=1,cu=0,mis=0,<strong>r=2<\/strong>,dep=0,og=1,plh=3090445234,tim=422088651\nFETCH #139833692592448:c=0,e=16,p=0,cr=1,cu=0,mis=0,<strong>r=2<\/strong>,dep=0,og=1,plh=3090445234,tim=422089371\nFETCH #139833692592448:c=0,e=15,p=0,cr=1,cu=0,mis=0,<strong>r=2<\/strong>,dep=0,og=1,plh=3090445234,tim=422090092\n<\/code><\/pre>\n\n\n\n<p>In 18c it changed:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FETCH #140635627289560:c=62,e=61,p=0,cr=3,cu=0,mis=0,<strong>r=2<\/strong>,dep=0,og=1,plh=3090445234,tim=559611770\nFETCH #140635627289560:c=22,e=22,p=0,cr=1,cu=0,mis=0,<strong>r=3<\/strong>,dep=0,og=1,plh=3090445234,tim=559612282\nFETCH #140635627289560:c=15,e=14,p=0,cr=1,cu=0,mis=0,<strong>r=3<\/strong>,dep=0,og=1,plh=3090445234,tim=559612368\nFETCH #140635627289560:c=34,e=34,p=0,cr=1,cu=0,mis=0,<strong>r=3<\/strong>,dep=0,og=1,plh=3090445234,tim=559612583\n<\/code><\/pre>\n\n\n\n<p>The old behavior up to 12.2. is documented in MOS Note<br>Pipelined Function with Pipe Row() Pipes out 15 Rows at a Time (Doc ID <a href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocumentDisplay?id=1265916.1\">1265916.1<\/a>):<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p>SQL*Plus is written in OCI, and OCI has a default prefetch value of 1 row.<\/p>\n\n\n\n<p>The first fetch is 1 row, as 1 row is prefetched on the execute.<br>Then it either performs a scalar fetch, so one requested row plus one prefetched row,<br>or it performs an array fetch so you see eg :<\/p>\n\n\n\n<p>arraysize = 1, fetches are: 1, 2, 2, \u2026<\/p>\n\n\n\n<p>On the initial execute it fetches one row. So on the first fetch call SQL<em>Plus makes it returns that row. On the second fetch call SQL<\/em>Plus makes, asking for one row, it has to go to<br>the database. This means it fetches the row requested plus it prefetches one additional row,<br>and you see two fetched. The next fetch call SQL*Plus makes uses the prefetched row,<br>then the next goes to the database and fetches two more and so on.<\/p>\n<\/blockquote>\n\n\n\n<p>So up to 12.2. sqlplus just relied on OCI&#8217;s prefetch-mechanism and hence the minimum fetch size was 2. Why do we see a minimum of 3 rows fetched from 18c onwards?<\/p>\n\n\n\n<p>I made a test with an OCI-program I took from MOS Note<br>OCI: Sample Program That Prefetches Rows (Doc ID <a href=\"https:\/\/support.oracle.com\/epmos\/faces\/DocumentDisplay?id=1126015.1\">1126015.1<\/a>)<\/p>\n\n\n\n<p>The provided c-program was adjusted to use<\/p>\n\n\n\n<p>MAX_FETCH_COUNT 1 &#8211;&gt; fetchsize, i.e. arraysize in sqlplus<br>prefetch_cnt = 1 &#8211;&gt; prefetch-size of OCI<\/p>\n\n\n\n<p>The trace file running the program in 19c looks as follows:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FETCH #139759481436136:c=115,e=115,p=0,cr=3,cu=0,mis=0,<strong>r=2<\/strong>,dep=0,og=1,plh=3090445234,tim=17304993374\nFETCH #139759481436136:c=0,e=50,p=0,cr=1,cu=0,mis=0,<strong>r=2<\/strong>,dep=0,og=1,plh=3090445234,tim=17304993696\nFETCH #139759481436136:c=0,e=73,p=0,cr=1,cu=0,mis=0,<strong>r=2<\/strong>,dep=0,og=1,plh=3090445234,tim=17304994078\nFETCH #139759481436136:c=41,e=41,p=0,cr=1,cu=0,mis=0,<strong>r=2<\/strong>,dep=0,og=1,plh=3090445234,tim=17304994447\n<\/code><\/pre>\n\n\n\n<p>After changing prefetch_cnt = 0 I really can see only 1 row per fetch:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>FETCH #140542285248488:c=93,e=93,p=0,cr=3,cu=0,mis=0,<strong>r=1<\/strong>,dep=0,og=1,plh=3090445234,tim=17507201670\nFETCH #140542285248488:c=74,e=74,p=0,cr=1,cu=0,mis=0,<strong>r=1<\/strong>,dep=0,og=1,plh=3090445234,tim=17507202044\nFETCH #140542285248488:c=47,e=46,p=0,cr=1,cu=0,mis=0,<strong>r=1<\/strong>,dep=0,og=1,plh=3090445234,tim=17507202294\nFETCH #140542285248488:c=28,e=28,p=0,cr=1,cu=0,mis=0,<strong>r=1<\/strong>,dep=0,og=1,plh=3090445234,tim=17507202563\n<\/code><\/pre>\n\n\n\n<p>From the tests it&#8217;s not clear what caused the behavior change in 18c. The new features described in the <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/18\/sqpug\/release-changes.html#GUID-C9E008E9-8A32-41CB-A62F-F27C18F3163D\">release notes of sqlplus in 18c<\/a> seem not related.  <\/p>\n\n\n\n<p>Summary: The script provided in Blog <a href=\"https:\/\/www.dbi-services.com\/blog\/script-to-calculate-the-network-latency-between-the-application-and-the-oracle-db-server\/\">Script to calculate the network latency between the application and the Oracle DB-server<\/a> is correct, but you may consider that sqlplus and sqlcl provide a different number of fetches (with arraysize 1), i.e. the amount of data transported may be bit different, but that should not have any impact on the result. To be 100% correct with the latency an OCI-program can be used with a fetchsize of 1 and a prefetch-size of 0. Alternatively you may test with an arraysize of 3 in sqlplus and sqlcl.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Some time ago I wrote a Blog on how to calculate the network latency fetching a row to a Client from an Oracle database. The tool I used was sqlcl, because it just requires Java and hence no Oracle Client needs to be installed. Some people executed the script provided in the blog with sqlplus [&hellip;]<\/p>\n","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59],"tags":[],"type_dbi":[],"class_list":["post-20641","post","type-post","status-publish","format-standard","hentry","category-oracle"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.5) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Latency test from Client to Oracle DB: Minimum number of rows fetched in sqlplus and sqlcl - 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\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Latency test from Client to Oracle DB: Minimum number of rows fetched in sqlplus and sqlcl\" \/>\n<meta property=\"og:description\" content=\"Some time ago I wrote a Blog on how to calculate the network latency fetching a row to a Client from an Oracle database. The tool I used was sqlcl, because it just requires Java and hence no Oracle Client needs to be installed. Some people executed the script provided in the blog with sqlplus [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2022-11-24T16:08:12+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-11-24T16:37:53+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=\"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\\\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\\\/\"},\"author\":{\"name\":\"Clemens Bleile\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\"},\"headline\":\"Latency test from Client to Oracle DB: Minimum number of rows fetched in sqlplus and sqlcl\",\"datePublished\":\"2022-11-24T16:08:12+00:00\",\"dateModified\":\"2022-11-24T16:37:53+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\\\/\"},\"wordCount\":624,\"commentCount\":0,\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\\\/\",\"name\":\"Latency test from Client to Oracle DB: Minimum number of rows fetched in sqlplus and sqlcl - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2022-11-24T16:08:12+00:00\",\"dateModified\":\"2022-11-24T16:37:53+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0ac04011f60f2e93c115358d0789c2da\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Latency test from Client to Oracle DB: Minimum number of rows fetched in sqlplus and sqlcl\"}]},{\"@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":"Latency test from Client to Oracle DB: Minimum number of rows fetched in sqlplus and sqlcl - 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\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\/","og_locale":"en_US","og_type":"article","og_title":"Latency test from Client to Oracle DB: Minimum number of rows fetched in sqlplus and sqlcl","og_description":"Some time ago I wrote a Blog on how to calculate the network latency fetching a row to a Client from an Oracle database. The tool I used was sqlcl, because it just requires Java and hence no Oracle Client needs to be installed. Some people executed the script provided in the blog with sqlplus [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\/","og_site_name":"dbi Blog","article_published_time":"2022-11-24T16:08:12+00:00","article_modified_time":"2022-11-24T16:37:53+00:00","author":"Clemens Bleile","twitter_card":"summary_large_image","twitter_creator":"@ifgtxD2SrQ8r!YuXj","twitter_misc":{"Written by":"Clemens Bleile","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\/"},"author":{"name":"Clemens Bleile","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"headline":"Latency test from Client to Oracle DB: Minimum number of rows fetched in sqlplus and sqlcl","datePublished":"2022-11-24T16:08:12+00:00","dateModified":"2022-11-24T16:37:53+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\/"},"wordCount":624,"commentCount":0,"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\/","url":"https:\/\/www.dbi-services.com\/blog\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\/","name":"Latency test from Client to Oracle DB: Minimum number of rows fetched in sqlplus and sqlcl - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-11-24T16:08:12+00:00","dateModified":"2022-11-24T16:37:53+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0ac04011f60f2e93c115358d0789c2da"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/latency-test-from-client-to-oracle-db-minimum-number-of-rows-fetched-in-sqlplus-and-sqlcl\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Latency test from Client to Oracle DB: Minimum number of rows fetched in sqlplus and sqlcl"}]},{"@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\/20641","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=20641"}],"version-history":[{"count":8,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/20641\/revisions"}],"predecessor-version":[{"id":20650,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/20641\/revisions\/20650"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=20641"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=20641"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=20641"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=20641"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}