{"id":93131,"date":"2022-01-14T18:04:03","date_gmt":"2022-01-14T18:04:03","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=93131"},"modified":"2022-03-18T21:23:02","modified_gmt":"2022-03-18T21:23:02","slug":"transformations-by-the-oracle-optimizer","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/transformations-by-the-oracle-optimizer\/","title":{"rendered":"Transformations by the Oracle Optimizer"},"content":{"rendered":"

Jonathan Lewis' continuing series on the Oracle optimizer and how it transforms queries into execution plans:<\/strong><\/p>\n

    \n
  1. Transformations by the Oracle Optimizer<\/a><\/li>\n
  2. The effects of NULL with NOT IN on Oracle transformations<\/a><\/li>\n
  3. Oracle subquery caching and subquery pushing<\/a><\/li>\n
  4. Oracle optimizer removing or coalescing subqueries<\/a><\/li>\n
  5. Oracle optimizer Or Expansion Transformations<\/a><\/li>\n<\/ol>\n\n

    It\u2019s common knowledge that when you write an SQL statement, you\u2019re describing to the database what<\/em><\/strong> you want but not how<\/em><\/strong> to get it. It shouldn\u2019t come as a surprise, then, that in all but the simplest cases, the statement that Oracle optimizes isn\u2019t necessarily the statement that you wrote. Putting it another way, Oracle will probably transform your statement into a logically equivalent statement before applying the arithmetic it uses to pick an execution path.<\/p>\n

    If you want to become skilled in troubleshooting badly performing SQL, it\u2019s important to be able to recognize what transformations have been applied, what transformations haven\u2019t been applied when they could have been, and how to force (or block) the transformations you think will have the greatest impact.<\/p>\n

    In this article, we\u2019re going to examine one of the oldest and most frequently occurring transformations that the Optimizer considers: unnesting subqueries.<\/p>\n

    The Optimizer\u2019s wish-list<\/h2>\n

    The “query block” is the starting point for understanding the optimizer\u2019s methods and reading execution plans. Whatever you\u2019ve done in your query, no matter how complex and messy it is, the optimizer would like to transform it into an equivalent query of the form:<\/p>\n

    select  {list of columns}\r\nfrom    {list of \u201ctables\u201d}\r\nwhere   list of simple join and filter predicates}<\/pre>\n
    Optionally your query may have group by<\/code>, having<\/code>, and order by<\/code> clauses as well as any of the various \u201cpost-processing\u201d features of newer versions of Oracle, but the core task for the optimizer is to find a path that acquires the raw data in the shortest possible time. The bulk of the optimizer\u2019s processing is focused on \u201cwhat do you want\u201d (select<\/code>), \u201cwhere is it\u201d (from<\/code>), \u201chow do I connect the pieces\u201d (where<\/code>). This simple structure is the \u201cquery block\u201d<\/em><\/strong>, and most of the arithmetic the optimizer does is about calculating the cost of executing each individual query block once.<\/p>\n
    Some queries, of course, cannot be reduced to such a simple structure \u2013 which is why I\u2019ve put \u201ctables\u201d<\/em> in quote marks and used the word \u201csimple\u201d<\/em> in the description of predicates. In this context, a \u201ctable\u201d<\/em> could be a \u201cnon-mergeable view\u201d<\/em><\/strong>, which would be isolated and handled as the result set from a separately optimized query block. If the best that Oracle can do to transform your query still leaves some subqueries in the where<\/code> clause or select<\/code> list, then those subqueries would also be isolated and handled as separately optimized query blocks.<\/p>\n
    When you\u2019re trying to solve a problem with badly performing SQL, it’s extremely useful to identify the individual query blocks from your original query in the final execution plan. Your performance problem may have come from how Oracle has decided to \u201cstitch together\u201d the separate query blocks that it has generated while constructing the final plan.<\/p>\n
    Case study<\/h2>\n
    Here\u2019s an example of a query that starts life with two query blocks. Note the qb_name()<\/code> hint that I\u2019ve used to give explicit names to the query blocks; if I hadn\u2019t done this, Oracle would have used the generated names sel$1<\/code> and sel$2<\/code> for the main<\/code> and subq<\/code> query blocks respectively.<\/p>\n
    select  \/*+ qb_name(main) *\/\r\n        *\r\nfrom    t1\r\nwhere   owner = 'OUTLN'\r\nand     object_name in (\r\n                select  \/*+ qb_name(subq) *\/\r\n                       object_name\r\n                from   t2\r\n                where  object_type = 'TABLE'\r\n        )\r\n;<\/pre>\n
    I\u2019ve created both t1<\/code> and t2<\/code> using selects on the view all_objects<\/code> so that the column names (and possible data patterns) look familiar and meaningful, and I\u2019ll be using Oracle 12.2.0.1 to produce execution plans as that\u2019s still very commonly in use and things haven\u2019t changed significantly in versions up to the latest release of 19c.<\/p>\n
    There are several possible plans for this query, depending on how my CTAS<\/code> filtered or scaled up the original content of all_objects<\/code>, what indexes I\u2019ve added, and whether I\u2019ve added or removed any not null<\/code> declarations. I\u2019ll start with the execution plan I get when I add the hint \/*+ no_query_transformation *\/<\/code> to the text:<\/p>\n
    explain plan for \r\nselect  \/*+ qb_name(main) no_query_transformation *\/\r\n        *\r\nfrom    t1\r\nwhere   owner = 'sys'\r\nand     object_name in (\r\n                select  \/*+ qb_name(subq) *\/\r\n                       object_name\r\n                from   t2\r\n                where  object_type = 'TABLE'\r\n        )\r\n;\r\nselect * from table(dbms_xplan.display(format=>'alias -bytes'));<\/pre>\n
    The plan returned looks like this:<\/p>\n
    <\/p>\n
    I\u2019ve used explain plan<\/code> here so that you can see the complete Predicate Information<\/em>. If I had executed the query and pulled the plan from memory with a call to dbms_xplan.display_cursor()<\/code>,<\/em><\/strong> the predicate information for operation 1 would have read: \u201c1 – filter( IS NOT NULL)\u201d<\/em>.Since there are no bind variables (and no risk of side-effects due to mismatched character sets), it\u2019s safe to assume that explain plan<\/code> will produce the correct plan.<\/p>\n
    There are a few points to pick up on this query and plan:<\/p>\n
    Even though I have instructed the optimizer to do \u201cno query transformations,\u201d you can see from the Predicate Information<\/em> for operation 1 that my non-correlated IN<\/code> subquery has turned into a correlated EXISTS<\/code> subquery where the correlating object_name<\/code> has been supplied as a bind variable (:B1<\/code>). There\u2019s no conflict here between my hint and the optimizer\u2019s transformation \u2013 this is an example of a \u201cheuristic\u201d<\/em> transformation (i.e., the optimizer\u2019s going to do it because there\u2019s a rule to say it can), and the hint relates only to cost-based transformations.<\/p>\n
    The \u201calias\u201d information that I requested in my call to dbms_xplan.display()<\/code> results in the Query Block Name \/ Object Alias<\/em> section of the plan being reported. This section allows you to see that the plan is made up of the two query blocks (main<\/em> and subq<\/em>) that I had named in the original text. You can also see that t1<\/em> and t1_i1<\/em> appearing in the body of the plan correspond to the t1<\/em> that appeared in the main<\/em> query block (t1@main<\/em>), and the t2\/t2_i1<\/em> corresponds to t2<\/em> in the subq<\/em> query block (t2@subq<\/em>). This is a trivial observation in this case, but if, for example, you are troubleshooting a long, messy query against Oracle\u2019s General Ledger schema, it\u2019s very helpful to be able to identify where in the query each of the many references to a frequently used GLCC<\/em> originated.<\/p>\n
    If you examine the basic shape of this plan, you will see that the main<\/em> query block has been executed as an index range scan based on the predicate owner = \u2018OUTLN\u2019<\/code>.<\/em><\/strong> The subq<\/em> query block has been executed as an index range scan based on the predicate object_name = {bind variable}<\/code>. Oracle has then stitched these two query blocks together through a filter<\/em> operation. For each row returned by the main<\/em> query block, the filter<\/em> operation executes the subq<\/em> query block to see if it can find a matching object_name<\/em> that is also of type TABLE<\/em>.<\/p>\n
    Finally, a comment on Cost<\/em>: if all you can see is the execution plan, it\u2019s easy to be misled by rounding effects and hidden details of the optimizer allowing for \u201cself-induced caching,\u201d etc. In this case, the base cost of 2 for each execution of the subquery is reduced to 1 in the assumption that the index root block will be cached, and a quick check of the CBO\u2019s trace file (10053 trace) shows that the 1 is actually closer to 1.003 (and the 84 for the t1<\/em> access is actually 84.32). Since the optimizer has an estimate of 3,125 rows for the number of rows it will initially fetch from t1 before filtering the total predicted cost of 3219 is approximately: 84.32+ 3125 * 1.003. Realistically, of course, you might expect there to be much more data caching with a hugely reduced I\/O load; especially in this case where the total size of the t2<\/em> table was only 13 blocks, so a calculation that produced a cost estimate equivalent to more than 3,000 physical read requests for repeated execution of the subquery is clearly inappropriate. Furthermore, the optimizer doesn\u2019t attempt to allow for a feature known as scalar subquery caching<\/em> so the arithmetic is based on the assumption that the t2 subquery will be executed for each row found in t1.<\/p>\n
    Here is the section from the CBO\u2019s trace file:<\/p>\n
    <\/p>\n
    Variations on a theme<\/h2>\n
    The optimizer transformed an IN subquery to an EXISTS subquery. If the value of the object_name<\/code> in a row from t1<\/em> is of interest if it appears in a list of values, then it\u2019s sensible to check by finding that value in the list. If there are duplicates in the list, stop looking after finding the first match. That\u2019s effectively the method of the FILTER operation in the initial plan, but there is an alternative; if the data sets happen to have the right pattern and volume, Oracle could go through virtually the same motions but use a semi_join<\/em>, which means \u201cfor each row in <\/em>t1<\/em><\/strong> find the first matching row in <\/em>t2<\/em><\/strong>\u201d<\/em>. The plan would be like the following:<\/p>\n
    <\/p>\n
    The semi-join was actually the strategy that the optimizer chose for my original data set when I allowed cost-based transformations to take place, but it used a hash join rather than a nested loop join. Since t2<\/em><\/strong> was the smaller row source, Oracle also \u201cswapped sides<\/em>\u201d to get to a hash join that was both \u201csemi\u201d<\/em> and \u201cright\u201d<\/em>:<\/p>\n
    <\/p>\n
    But there\u2019s more<\/h2>\n
    The volumes of data involved might mean it\u2019s a good idea to adopt a completely different strategy \u2013 and this is where unnesting<\/em><\/strong> becomes apparent. If the volume of data from t1<\/em> is so small that you only have to run the check a few times, then an existence test could be a very good idea (especially if there\u2019s an index that supports a very efficient check). If the volume of data from t1<\/em> is large or there\u2019s no efficient path into the t2<\/em> table to do the check, then it might be better to create a list of the distinct values (of object_name<\/code>) in t2<\/em> and use that list to drive a join into t1<\/em>. Here\u2019s one example of how the plan could change:<\/p>\n
    <\/p>\n
    The plan now has a new object at operation 2, a (non-mergeable) view called vw_nso_1<\/em> that is recognizably something holding the distinct (hash unique<\/em>) values of t2.object_name<\/code>. The number of such values is quite small (only 36 estimated), so the optimizer might have used a nested loop join to collect related rows from t1<\/em>, but the arithmetic has pushed it into using the \u201cbrute force\u201d method of finding all the t1<\/em> rows of type TABLE<\/em> and then doing a hash join.<\/p>\n
    It\u2019s important to get into the habit of using the alias<\/code> format option when the shape of the plan doesn\u2019t match the pattern you were expecting to see. In this case, you can see from the Query Block Name<\/em> information that the final plan consists of two new query blocks: sel$aa0d0e02<\/em> and sel$a93afaed<\/em>. The first query block describes how Oracle will produce a \u201ctable\u201d representing the list of distinct object names, and the second query block is a simple join of two \u201ctables\u201d with a hash join. Note how the Object Alias<\/em> information tells you that the view vw_nso_1<\/em> originated in the query block sel$a93afaed<\/em>.<\/em><\/strong><\/p>\n
    \n
    Side Note: A generated query block name is a hash value generated from the names of the query blocks that were used to produce the new query block, and the hashing function is deterministic. <\/em><\/p>\n<\/div>\n
    In effect, the original query has been transformed into the equivalent:<\/p>\n
    select\r\n        t1.*\r\nfrom    (\r\n        select\r\n                distinct object_name \r\n        from    t2 \r\n        where   object_type=\u2019TABLE\u2019\r\n        ) vw_nso_1,\r\n        t1 \r\nwhere   t1.owner=\u2019OUTLN\u2019 \r\nand     t1.object_name = vw_nso_1.object_name\r\n\/<\/pre>\n
    Whenever you see a view in the plan with a name like vw_nso_{number}<\/em>,<\/em><\/strong> it\u2019s an indication that the optimizer has transformed an IN subquery into an inline view in this way. Interestingly, if you manually rewrite a query to change an IN subquery to an EXISTS<\/code> subquery, you may find that you get precisely the same plan but with a view named vw_sq_{number}<\/em>.<\/p>\n
    And even this strategy could be subject to further transformation if the data pattern warranted it. In my test case, the optimizer does a \u201cdistinct aggregation\u201d<\/em> on a number of small rows before joining to another table to produce much longer rows, which means the aggregation step can be quite efficient. But what if there were a large number of rows to aggregate in t2<\/em>, and what if the rows were close to being unique and were quite long rows \u2013 the aggregation step could require a lot of work for very little benefit. If the join to t1<\/em> then eliminated a lot of (aggregated) t2<\/em> data and the t1<\/em> rows in the select list were quite short, it might be more efficient to join t1<\/em> and t2<\/em> to eliminate lots of long rows before<\/strong> aggregating to eliminate the duplicates, producing a plan like the following:<\/p>\n
    <\/p>\n
    As you can see, the optimizer has produced a hash join between t1<\/em> and t2<\/em> before eliminating duplicates with a \u201chash unique\u201d<\/em> operation. This is a little more subtle than it looks because if you tried to write a statement to do this, you could easily make an error that eliminated some rows that should have been kept \u2013 an error best explained by examining the approximate equivalent of the statement that Oracle has actually optimized:<\/p>\n
    select  \r\n        {list of t1 columns}\r\nfrom    (\r\n        select \r\n                distinct\r\n                t2.object_name  t2_object_name,\r\n                t1.rowid _owed,\r\n                t1.*\r\n        from\r\n                t2,\r\n                t1\r\n        where\r\n                t1.owner = \u2018OUTLN\u2019\r\n        and     t2.object_name = t1.object_name\r\n        and     t2.object_type = \u2018TABLE\u2019\r\n        ) vm_nwvw_2\r\n;<\/pre>\n
    Notice the appearance of t1.rowid<\/code> in the inline view; that\u2019s the critical bit that protects Oracle from producing the wrong results. Since the join is on object_name<\/code> (which is not unique in the view all_objects<\/em> that I created my tables from), there could be two rows in t1<\/em> and two in t2<\/em> all holding the same object_name<\/code>, with the effect that a simple join would produce four rows.<\/p>\n
    The final result set should hold two rows (corresponding to the initial two rows in t1<\/em>), but if the optimizer didn\u2019t include the t1<\/em> rowid in the select list before using the distinct operator, those four rows would aggregate down to a single row. The optimizer sometimes has to do some very sophisticated (or possibly incomprehensible) work to produce a correctly transformed statement \u2013 which is why the documentation sometimes lists restrictions to new transformations: the more subtle bits of code that would guarantee correct results for particular cases don\u2019t always appear in the first release of a new transformation.<\/p>\n
    You\u2019ll notice that the generated view name, in this case, takes the form vm_nwvw_{number}<\/em> Most of the generated view names start with vw_<\/em> this one may be the one special case. The view shows one of the effects of \u201cComplex <\/em>V<\/em><\/strong>iew <\/em>M<\/em><\/strong>erging\u201d<\/em> \u2013 hence the vm<\/em>, perhaps \u2013 where Oracle changes \u201caggregate then join\u201d into \u201cjoin then aggregate\u201d, which happens quite often as a step following subquery unnesting.<\/p>\n
    Summary<\/h2>\n
    If you write a query containing subqueries, whether in the select<\/code> list or (more commonly) in the where<\/code> clause, the optimizer will often consider unnesting<\/code> as many subqueries as possible. This generally means restructuring your subquery into an inline view that can go into the from<\/code> clause and be joined to other tables in the from clause.<\/p>\n
    The names of such inline views are typically of the form vw_nso_{number}<\/em> or vw_sq_{number}<\/em>.<\/p>\n
    After unnesting a subquery, Oracle may apply further transformations that make the inline view \u201cdisappear\u201d \u2013 turning into a join, or semi-join, or (an example we have not examined) an anti-join. Alternatively, the inline view may also be subject to complex view merging, which may introduce a new view with a name of the form vm_nwvw_{number}<\/em> but may result in the complete disappearance of any view operation.<\/p>\n
    I haven\u2019t covered the topic of how Oracle can handle joins to or from an unnested view if it turns out to be non-mergeable, and I haven\u2019t said anything about the difference between IN<\/code> and NOT IN<\/code> subqueries. These are topics for another article.<\/p>\n
    Finally, I opened the article with a discussion of the \u201cquery block\u201d and its importance to the optimizer. Whenever you write a query, it is a good idea to give every query block its own query block name using the qb_name()<\/code> hint. When you need to examine an execution plan, it is then a good idea to add the alias<\/code> format option to the call to dbms_xplan<\/code> so that you can find your original query blocks in the final plan, see which parts of the plan are query blocks create by optimizer transformation, and see the boundaries where the optimizer has \u201cstitched together\u201d separate query blocks.<\/p>\n
    Footnote<\/h2>\n
    If you\u2019ve been examining these execution plans in detail, you will have noticed that all five plans look as if they\u2019ve been produced from exactly the same tables \u2013 even though my notes were about how different plans for the same query could appear thanks to different patterns in the data. To demonstrate the different plans, I used a minimum number of hints to override the optimizer\u2019s choices. Ultimately, when the optimizer has made a mistake in its choice of transformation, you do need to know how to recognize and control transformations, and you may have to attach hints to your production code.<\/p>\n
    I\u2019ve often warned people against hinting because of the difficulty of doing it correctly, but hints aimed at query blocks (the level where the transformation choices come into play) are significantly safer than using \u201cmicro-management\u201d hints at the object level.<\/p>\n
    Although I had to use \u201cmicro-management\u201d (object-level) hints to switch between the nested loop join and the hash join in the section \u201cVariations on a Theme,\u201d all the other examples made use of query block hints, namely: merge\/no_merge<\/code>, unnest\/no_unnest<\/code>, or semijoin\/no_semijoin<\/code>.<\/p>\n
     <\/p>\n
    If you liked this article, you might also like Index Costing Threat<\/a><\/em><\/p>\n","protected":false},"excerpt":{"rendered":"
    To troubleshoot poorly performing SQL in Oracle, you must understand which transformations the optimizer has made. Jonathan Lewis demonstrates several possible optimizations for one query.…<\/p>\n","protected":false},"author":101205,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143533],"tags":[124952],"coauthors":[39048],"class_list":["post-93131","post","type-post","status-publish","format-standard","hentry","category-featured","category-oracle-databases","tag-redgate-deploy"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93131","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/101205"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=93131"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93131\/revisions"}],"predecessor-version":[{"id":93573,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93131\/revisions\/93573"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=93131"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=93131"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=93131"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=93131"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}