{"id":35,"date":"2009-03-02T22:20:41","date_gmt":"2009-03-02T22:20:41","guid":{"rendered":"http:\/\/oracle-internals.com\/blog\/?p=35"},"modified":"2014-01-26T22:21:09","modified_gmt":"2014-01-26T22:21:09","slug":"advanced-query-optimization-question","status":"publish","type":"post","link":"http:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/","title":{"rendered":"Advanced Query Optimization Question"},"content":{"rendered":"<p>Having now had a couple days to rest and reflect, I can definitively say that SEOUC was great.\u00a0 I\u2019m really glad I attended, was able to meet a lot of new people, and have some good discussions about problems people faced both technically and given the current economy.\u00a0 I was also able to get some good insight into other types of Oracle environments and felt the conference was an overall success.<\/p>\n<p>The conference ended with a closing session on the topic of Oracle Exadata Storage Server presented by Oracle Director of Product Management,\u00a0<a href=\"https:\/\/web.archive.org\/web\/20110919011359\/http:\/\/www.linkedin.com\/ppl\/webprofile?action=vmi&amp;id=1754967&amp;authToken=_WVm&amp;authType=name&amp;trk=ppro_viewmore&amp;lnk=vw_pprofile\">Charles Garry<\/a>.\u00a0 While the\u00a0<a href=\"https:\/\/web.archive.org\/web\/20110919011359\/http:\/\/www.unyoug.com\/uploads\/files\/20081114\/OracleExadata.pdf\">slide deck<\/a>\u00a0was quite marketing-heavy, Charles did a good job entertaining everyone and answering questions.<\/p>\n<p>After the session, I had a few discussions with people regarding the Exadata architecture and the method by which Oracle Database is able to distribute nodes of a query execution plan directly to the storage server for local processing.\u00a0 For people who haven\u2019t worked on query optimizers and executors, it seems that this is an area of technical confusion.\u00a0 Regardless, Charles and I both rode the shuttle back to the airport, which gave me a chance to talk with him about Oracle from both a company and technology perspective.<\/p>\n<p>Also, now that I\u2019m home and have completed my second SEOUC presentation, The Life of a Query, I wanted to follow-up on my experience and ask you a question.<\/p>\n<p>First, the description of The Life of a Query is as follows:<\/p>\n<blockquote><p>Have you ever wondered what happens when you execute a query?\u00a0 In this session, we\u2019ll take a walk through the Life of an Oracle Query from beginning to end.\u00a0 Not only does this help you better understand the various steps in the execution of your query, but it will also give you a good appreciation for the architecture of the Oracle Database server and, in particular, of the query optimizer.\u00a0 You will gain valuable information that will allow you to solve performance issues and write more efficient queries going forward.\u00a0 Additionally, I will share some of my insights into the Oracle optimizer as we move through this discussion.\u00a0 This discussion is also a great refresher for those of you familiar with Oracle concepts and architecture.<\/p><\/blockquote>\n<p>In short, this session takes a query and walks through all major components of the Oracle database starting from the client and going through connection, the entire Oracle Kernel stack, and back to the client.\u00a0 After the session, I received quite a few positive comments from attendees.\u00a0 Moreover, I found that people are extremely interested in how query optimization works.<\/p>\n<p>While I covered the basics of parsing, query rewrite, and optimization, it seemed that including view\/subquery merging, basic algebraic optimization, join permutation, join elimination, and partition elimination wasn\u2019t enough.\u00a0 Surprisingly, someone even asked to go into more depth on the mathematics behind query optimization; specifically the application of graph theory to plans and the way costing is performed using a graph.\u00a0 This brings me to my question:<\/p>\n<p><strong>Would anyone be interested in a fairly advanced presentation\/article on query optimization?<\/strong><\/p>\n<p>For comparison purposes, my meaning of fairly advanced is between the level of Jonathan Lewis\u2019\u00a0<a href=\"https:\/\/web.archive.org\/web\/20110919011359\/http:\/\/www.amazon.com\/Cost-Based-Oracle-Fundamentals-Experts-Voice\/dp\/1590596366\/ref=sr_1_1?ie=UTF8&amp;s=books&amp;qid=1235976782&amp;sr=1-1\">Cost-Based Oracle Fundamentals<\/a>\u00a0and not-quite pure math.\u00a0 If I decide to do it, I\u2019m thinking of presenting it similar to Craig Shallahamer\u2019s based-on-math-yet-practically-applied\u00a0<a href=\"https:\/\/web.archive.org\/web\/20110919011359\/http:\/\/www.amazon.com\/Forecasting-Oracle-Performance-Craig-Shallahamer\/dp\/1590598024\/ref=sr_1_3?ie=UTF8&amp;s=books&amp;qid=1235976782&amp;sr=1-3\">Forecasting Oracle Performance<\/a>, describing the math and theory but visually demonstrating how it applies to actual queries.\u00a0 Thoughts?<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Having now had a couple days to rest and reflect, I can definitively say that SEOUC was great.\u00a0 I\u2019m really glad I attended, was able to meet a lot of new people, and have some good discussions about problems people faced both technically and given the current economy.\u00a0 I was also able to get some [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Advanced Query Optimization Question - Oracle Internals<\/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:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Advanced Query Optimization Question - Oracle Internals\" \/>\n<meta property=\"og:description\" content=\"Having now had a couple days to rest and reflect, I can definitively say that SEOUC was great.\u00a0 I\u2019m really glad I attended, was able to meet a lot of new people, and have some good discussions about problems people faced both technically and given the current economy.\u00a0 I was also able to get some [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/\" \/>\n<meta property=\"og:site_name\" content=\"Oracle Internals\" \/>\n<meta property=\"article:published_time\" content=\"2009-03-02T22:20:41+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-01-26T22:21:09+00:00\" \/>\n<meta name=\"author\" content=\"Jonah Harris\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@jonahharris\" \/>\n<meta name=\"twitter:site\" content=\"@jonahharris\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonah Harris\" \/>\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:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/\"},\"author\":{\"name\":\"Jonah Harris\",\"@id\":\"http:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4\"},\"headline\":\"Advanced Query Optimization Question\",\"datePublished\":\"2009-03-02T22:20:41+00:00\",\"dateModified\":\"2014-01-26T22:21:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/\"},\"wordCount\":575,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/\",\"url\":\"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/\",\"name\":\"Advanced Query Optimization Question - Oracle Internals\",\"isPartOf\":{\"@id\":\"http:\/\/oracle-internals.com\/blog\/#website\"},\"datePublished\":\"2009-03-02T22:20:41+00:00\",\"dateModified\":\"2014-01-26T22:21:09+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/oracle-internals.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Advanced Query Optimization Question\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/oracle-internals.com\/blog\/#website\",\"url\":\"http:\/\/oracle-internals.com\/blog\/\",\"name\":\"Oracle Internals\",\"description\":\"Researching the Inner Workings of the World&#039;s Most Powerful Database\",\"publisher\":{\"@id\":\"http:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/oracle-internals.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4\",\"name\":\"Jonah Harris\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/oracle-internals.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/1.gravatar.com\/avatar\/a6d16ed0f510e8de0929f129471dc1e5?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/1.gravatar.com\/avatar\/a6d16ed0f510e8de0929f129471dc1e5?s=96&d=mm&r=g\",\"caption\":\"Jonah Harris\"},\"logo\":{\"@id\":\"http:\/\/oracle-internals.com\/blog\/#\/schema\/person\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/in\/jonahharris\/\",\"https:\/\/x.com\/jonahharris\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Advanced Query Optimization Question - Oracle Internals","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:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/","og_locale":"en_US","og_type":"article","og_title":"Advanced Query Optimization Question - Oracle Internals","og_description":"Having now had a couple days to rest and reflect, I can definitively say that SEOUC was great.\u00a0 I\u2019m really glad I attended, was able to meet a lot of new people, and have some good discussions about problems people faced both technically and given the current economy.\u00a0 I was also able to get some [&hellip;]","og_url":"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/","og_site_name":"Oracle Internals","article_published_time":"2009-03-02T22:20:41+00:00","article_modified_time":"2014-01-26T22:21:09+00:00","author":"Jonah Harris","twitter_card":"summary_large_image","twitter_creator":"@jonahharris","twitter_site":"@jonahharris","twitter_misc":{"Written by":"Jonah Harris","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/#article","isPartOf":{"@id":"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/"},"author":{"name":"Jonah Harris","@id":"http:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4"},"headline":"Advanced Query Optimization Question","datePublished":"2009-03-02T22:20:41+00:00","dateModified":"2014-01-26T22:21:09+00:00","mainEntityOfPage":{"@id":"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/"},"wordCount":575,"commentCount":0,"publisher":{"@id":"http:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/","url":"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/","name":"Advanced Query Optimization Question - Oracle Internals","isPartOf":{"@id":"http:\/\/oracle-internals.com\/blog\/#website"},"datePublished":"2009-03-02T22:20:41+00:00","dateModified":"2014-01-26T22:21:09+00:00","breadcrumb":{"@id":"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/oracle-internals.com\/blog\/2009\/03\/02\/advanced-query-optimization-question\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/oracle-internals.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Advanced Query Optimization Question"}]},{"@type":"WebSite","@id":"http:\/\/oracle-internals.com\/blog\/#website","url":"http:\/\/oracle-internals.com\/blog\/","name":"Oracle Internals","description":"Researching the Inner Workings of the World&#039;s Most Powerful Database","publisher":{"@id":"http:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/oracle-internals.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"http:\/\/oracle-internals.com\/blog\/#\/schema\/person\/549d9c522c3960b062618b600bb762a4","name":"Jonah Harris","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/oracle-internals.com\/blog\/#\/schema\/person\/image\/","url":"http:\/\/1.gravatar.com\/avatar\/a6d16ed0f510e8de0929f129471dc1e5?s=96&d=mm&r=g","contentUrl":"http:\/\/1.gravatar.com\/avatar\/a6d16ed0f510e8de0929f129471dc1e5?s=96&d=mm&r=g","caption":"Jonah Harris"},"logo":{"@id":"http:\/\/oracle-internals.com\/blog\/#\/schema\/person\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/in\/jonahharris\/","https:\/\/x.com\/jonahharris"]}]}},"_links":{"self":[{"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/posts\/35"}],"collection":[{"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/comments?post=35"}],"version-history":[{"count":1,"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/posts\/35\/revisions"}],"predecessor-version":[{"id":36,"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/posts\/35\/revisions\/36"}],"wp:attachment":[{"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/media?parent=35"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/categories?post=35"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/oracle-internals.com\/blog\/wp-json\/wp\/v2\/tags?post=35"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}