{"id":4514,"date":"2015-04-01T04:00:00","date_gmt":"2015-04-01T02:00:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/"},"modified":"2015-04-01T04:00:00","modified_gmt":"2015-04-01T02:00:00","slug":"oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/","title":{"rendered":"Oracle tuning silver bullet: add an order by to make your query faster"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nYou have read all Cary Millsap work about Oracle database performance tuning. You know that there are no silver bullets. Reducing the response time requires a methodical approach in order to analyze the response time with the goal of eliminating all unnecessary work.
\nBut I’ll show something completly opposite here. A performance tuning silver bullet. Do more work in order to run it faster: just add an ORDER BY to your query and its faster.
\nI’ve made a very simple test case in order to let you test it by yourself.
\nI set my sqlplus environement and connect remotely to ma database:<\/p>\n

SQL> set pagesize 1000 linesize 100 echo on timing on\nSQL> connect xxx\/xxx@\/\/xxx\/xxx\nConnected.\n<\/code><\/pre>\n
I create a DEMO table which is very simple: 10000 rows of small column.<\/p>\n
SQL> create table DEMO pctfree 0 as select mod(rownum,2) x from dual connect by 10000>=level;\nTable created.\nElapsed: 00:00:00.03\n<\/code><\/pre>\n
And a run a very simple query which does a full scan of that small table and pads the output to make it a significant size:<\/p>\n
SQL> set autotrace trace\nSQL> select lpad(x,2000,x) from DEMO;\n10000 rows selected.\n\nElapsed: 00:00:21.52\n<\/code><\/pre>\n
It took 22 seconds to execute that. I’ve run it with ‘autotrace trace’ so that I’ve not the overhead of displaying all rows from sqlplus. Rows are fetched but not displayed. I measure only the time to get the result. Let’s run it again to be sure there is no cache effect:<\/p>\n
SQL> select lpad(x,2000,x) from DEMO;\n10000 rows selected.\n\nElapsed: 00:00:25.61\n<\/code><\/pre>\n
It is still taking more than 20 seconds to retreive the 10000 rows.
\nAs I am with autotrace, I get the execution plan and basic statistics:<\/p>\n
Execution Plan\n----------------------------------------------------------\nPlan hash value: 4000794843\n\n--------------------------------------------------------------------------\n| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |\n--------------------------------------------------------------------------\n|   0 | SELECT STATEMENT  |      | 10000 |   126K|     4   (0)| 00:00:01 |\n|   1 |  TABLE ACCESS FULL| DEMO | 10000 |   126K|     4   (0)| 00:00:01 |\n--------------------------------------------------------------------------\n\nNote\n-----\n   - dynamic statistics used: dynamic sampling (level=2)\n\n\nStatistics\n----------------------------------------------------------\n          0  recursive calls\n          0  db block gets\n        683  consistent gets\n          0  physical reads\n          0  redo size\n   20163693  bytes sent via SQL*Net to client\n       7818  bytes received via SQL*Net from client\n        668  SQL*Net roundtrips to\/from client\n          0  sorts (memory)\n          0  sorts (disk)\n      10000  rows processed\n<\/code><\/pre>\n
You can’t do more simple: a full table scan retreiving all the rows.<\/p>\n
order by<\/h3>\n
And now, let’s apply my silver bullet: add an ORDER BY at the end and run it again:<\/p>\n
<\/code><\/pre>\n
SQL> a  order by x\n  1* select lpad(x,2000,x) from DEMO order by x\nSQL> \/\n10000 rows selected.\n\nElapsed: 00:00:07.06<\/b>\n<\/code><\/pre>\n
Here it is. The response time is now less than 10 seconds. 2x faster. If you don’t believe it, just test it.
\nAnd thanks to autotrace I can check that I’m doing the same full table scan, but with an additional sort:<\/p>\n
Execution Plan\n----------------------------------------------------------\nPlan hash value: 903288357\n\n---------------------------------------------------------------------------\n| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |\n---------------------------------------------------------------------------\n|   0 | SELECT STATEMENT   |      | 10000 |   126K|     5  (20)| 00:00:01 |\n|   1 |  SORT ORDER BY     |      | 10000 |   126K|     5  (20)| 00:00:01 |\n|   2 |   TABLE ACCESS FULL| DEMO | 10000 |   126K|     4   (0)| 00:00:01 |\n---------------------------------------------------------------------------\n\nNote\n-----\n   - dynamic statistics used: dynamic sampling (level=2)\n\n\nStatistics\n----------------------------------------------------------\n          4  recursive calls\n          0  db block gets\n         35  consistent gets\n          0  physical reads\n          0  redo size\n     118324  bytes sent via SQL*Net to client\n       7818  bytes received via SQL*Net from client\n        668  SQL*Net roundtrips to\/from client\n          1  sorts (memory)\n          0  sorts (disk)\n      10000  rows processed\n\n<\/code><\/pre>\n
The plan clearly show that I’m doing additional work: Oracle has to sort the rows before sending them. And the statistics show that I’m still returning 10000 rows and have 1 sort done.
\nAdding an order by made this query faster…<\/p>\n
Ok, at that point you probably realize that this post is clearly the opposite of the tuning method I usually recommend, and you have checked today’s date. Please don’t hesitate to comment if you have an idea about the reasons (plural because there are at least two of them) why the second query is much faster. If you attended my tuning workshop<\/a> you should already know the main reason. If you are a long time reader of Jonathan Lewis blog<\/a> then you should also get the second one.<\/p>\n
Update 2nd April 2015<\/h3>\n
All good answers are in the comments. Of course something was wrong in the introduction. We didn’t introduce additional work with the SORT operation, but this is minimal. And there were unnecessary work before (the roundtrips) that had a bigger overhead. Besides the April Fool joke, the goal of this post was to show that it’s not only important to make a query faster. You must know why you made it faster, or maybe you just hide a problem that will come back again. And that’s not too difficult if you use the right tools. Here, a simple autotrace showing only 11 session statistics had all necessary information.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . You have read all Cary Millsap work about Oracle database performance tuning. You know that there are no silver bullets. Reducing the response time requires a methodical approach in order to analyze the response time with the goal of eliminating all unnecessary work. But I’ll show something completly opposite here. A […]<\/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":[96,67],"type_dbi":[],"class_list":["post-4514","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-oracle","tag-performance"],"acf":[],"yoast_head":"\nOracle tuning silver bullet: add an order by to make your query faster - dbi Blog<\/title>\n<meta name=\"description\" content=\"Oracle database tuning silver bullet...\" \/>\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-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle tuning silver bullet: add an order by to make your query faster\" \/>\n<meta property=\"og:description\" content=\"Oracle database tuning silver bullet...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-04-01T02:00:00+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=\"4 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-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle tuning silver bullet: add an order by to make your query faster\",\"datePublished\":\"2015-04-01T02:00:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/\"},\"wordCount\":559,\"commentCount\":0,\"keywords\":[\"Oracle\",\"Performance\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/\",\"name\":\"Oracle tuning silver bullet: add an order by to make your query faster - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-04-01T02:00:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"Oracle database tuning silver bullet...\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle tuning silver bullet: add an order by to make your query faster\"}]},{\"@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":"Oracle tuning silver bullet: add an order by to make your query faster - dbi Blog","description":"Oracle database tuning silver bullet...","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-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/","og_locale":"en_US","og_type":"article","og_title":"Oracle tuning silver bullet: add an order by to make your query faster","og_description":"Oracle database tuning silver bullet...","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/","og_site_name":"dbi Blog","article_published_time":"2015-04-01T02:00:00+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle tuning silver bullet: add an order by to make your query faster","datePublished":"2015-04-01T02:00:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/"},"wordCount":559,"commentCount":0,"keywords":["Oracle","Performance"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/","name":"Oracle tuning silver bullet: add an order by to make your query faster - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-04-01T02:00:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"Oracle database tuning silver bullet...","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-tuning-silver-bullet-add-an-order-by-to-make-your-query-faster\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle tuning silver bullet: add an order by to make your query faster"}]},{"@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\/4514","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=4514"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4514\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4514"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4514"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4514"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4514"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}