When we examine an execution plan, we’re usually trying to work out the mechanical steps that Oracle took to produce a result set. When we do this, it’s important to remember that the text that Oracle optimized wasn’t necessarily the same as the statement we originally wrote. Oracle may have transformed our statement before passing it through the optimization engine. Sometimes it’s obvious that some significant transformations have taken place because we see some unexpected “VIEW” operations appearing in the plan, but sometimes we need to take note of the “Query Block” details to notice that the statement we started with isn’t the statement that the optimizer ended with.

Query Blocks.

The “unit of optimization” is the Query Block, and Oracle made it very easy from 10g onward to identify query blocks in execution plans. Every time you see a select, insert, update, delete, or merge keyword in a statement you’re looking at the start of a query block where you can introduce the qb_name (query block name) hint, for example:

select  /*+ qb_name(main) */
        outer.*
from
        emp outer
where
        outer.sal > (
                select  /*+ qb_name(avg_subq) */
                        avg(inner.sal)
                from
                        emp inner
                where
                        inner.dept_no = outer.dept_no
        )
;

As you can see I have named the main body of my query “main“, and the correlated subquery “avg_subq“. In the absence of explicitly stated names, Oracle would have generated the names sel$1 and sel$2 (with del$1, ins$1, mrg$1 etc. for the other types of SQL commands). Here’s the execution plan I get for this query, with no other hints from a call to dbms_xplan.display() with the format options of ‘+alias +outline’.  (I should point out that the EMP table in this example is one I generated with 6 departments and 20,000 rows):

----------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost  |
----------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |  1000 | 98000 |   120 |
|*  1 |  HASH JOIN           |         |  1000 | 98000 |   120 |
|   2 |   VIEW               | VW_SQ_1 |     6 |   156 |    84 |
|   3 |    HASH GROUP BY     |         |     6 |    48 |    84 |
|   4 |     TABLE ACCESS FULL| EMP     | 20000 |   156K|    35 |
|   5 |   TABLE ACCESS FULL  | EMP     | 20000 |  1406K|    35 |
----------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$C7CDAD1E
   2 - SEL$11FCF3E2 / VW_SQ_1@SEL$EF633D71
   3 - SEL$11FCF3E2
   4 - SEL$11FCF3E2 / INNER@AVG_SUBQ
   5 - SEL$C7CDAD1E / OUTER@MAIN

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$11FCF3E2")
      FULL(@"SEL$11FCF3E2" "INNER"@"AVG_SUBQ")
      USE_HASH(@"SEL$C7CDAD1E" "OUTER"@"MAIN")
      LEADING(@"SEL$C7CDAD1E" "VW_SQ_1"@"SEL$EF633D71" "OUTER"@"MAIN")
      FULL(@"SEL$C7CDAD1E" "OUTER"@"MAIN")
      NO_ACCESS(@"SEL$C7CDAD1E" "VW_SQ_1"@"SEL$EF633D71")
      OUTLINE(@"MAIN")
      OUTLINE(@"SEL$EF633D71")
      OUTLINE(@"AVG_SUBQ")
      UNNEST(@"AVG_SUBQ")
      OUTLINE_LEAF(@"SEL$C7CDAD1E")
      OUTLINE_LEAF(@"SEL$11FCF3E2")
      ALL_ROWS
      OPT_PARAM('_optimizer_cost_model' 'io')
      DB_VERSION('11.2.0.4')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

There are several points we can pick out from this simple example. First, of course, line 1 shows us a hash join between lines 2 and 5; but line 2 is a VIEW operator and the view name isn’t one that I referenced (or aliased) and, in fact, it doesn’t even exist in my database. This is an example of Oracle generating an internal view during query transformation and then optimizing with a non-mergeable view. In effect the optimizer has rewritten the query into the following form:

select  /*+ qb_name(main) */
        outer.*
from
        (
          select  /*+ qb_name(avg_subq) */
                  inner.dept_no, avg(inner.sal) avg_sal
          from
                emp inner
          group by
                inner.dept_no
        )       vw_sq_1,
        emp     outer
where
        outer.sal     > vw_sq_1.avg_sal
and     outer.dept_no = vw_sq_1.dept_no
;

This rewrite produced exactly the same plan as the original with the slight difference that the name that I’ve explicitly given the inline view didn’t appear in the execution plan (and, if you try this in earlier versions of Oracle, you may find that the costs don’t quite match the original).

The optimizer has decided that it’s not going to merge the inline view (whether generated or explicitly stated) into the main query to produce a single join, so it has optimized it separately. Apart from the clue we get from seeing the VIEW operator we get clues from two other places – first the “Query Block Name / Object Alias section of the report shows us that  line 2 (and its descendants, lines 3 and 4) are part of a query block called SEL$11FCF3E2; then we see that the “Outline Data section of the report tells us that query block SEL$11FCF3E2 is an “outline_leaf”, in other words it is a “final” query block that has actually been subject to independent optimization.

Another detail we can pick from the plan – with the optional extra sections – is the identity of the two different appearance of the EMP table. Lines 4 and 5 both identify EMP as the table scanned; but which EMP comes from which part of the query?  The “Query Block Name / Object Alias section tells us that EMP from line 4 was aliased “inner“ in query block “avg_subq“, while the EMP from line 5 was aliased “outer“ in query block “main“. We might have guessed that very quickly in this example, but it gets harder when you’re looking at Oracle Financials and statements with multiple references to the FND_CODE_COMBINATIONS table!

A question that we could ask about the incomprehensible query block names that Oracle generates is: “are they deterministic?” – is it possible for the same query to give you the same plan while generating different query block names on different versions of Oracle (or different days of the week). The answer is (or should be) no; when Oracle generates a query block name (after supplying the initial defaults of sel$1, sel$2 etc.) it applies a hashing function to the query block names that have gone INTO a transformation to generate the name that it will use for the block that comes OUT of the transformation.

Multiple Transformations

I’m going to push this query just a little further by hinting it into a path that might have appeared if the numbers had been a little different. Having unnested the subquery into the inline aggregate view the optimizer might have decided that it was sensible to use “complex view merging” to join the two copies of EMP before aggregating. I can emulate this by adding the /*+ merge */ hint to the code.  I could do this by adding the hint into the subquery itself to get:

select /*+ qb_name(avg_subq) merge */

or I could add it to the hints in the main query itself but targeting the right query block to get:

select /*+ qb_name(main) merge(@avg_subq)*/

Note, especially, the “@” symbol that I’ve used to direct the hint to a particular query block. Here’s the plan:

---------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  3333K|   254M|       |   953K|
|*  1 |  FILTER              |      |       |       |       |       |
|   2 |   HASH GROUP BY      |      |  3333K|   254M|  6127M|   953K|
|*  3 |    HASH JOIN         |      |    66M|  5086M|       |  6749 |
|   4 |     TABLE ACCESS FULL| EMP  | 20000 |   156K|       |    35 |
|   5 |     TABLE ACCESS FULL| EMP  | 20000 |  1406K|       |    35 |
---------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$A36D7A23
   4 - SEL$A36D7A23 / INNER@AVG_SUBQ
   5 - SEL$A36D7A23 / OUTER@MAIN

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OUTER"."SAL">AVG("INNER"."SAL"))
   3 - access("INNER"."DEPT_NO"="OUTER"."DEPT_NO")

It’s interesting to note that the expected cardinality of the result set has changed because of the change in plan. This shouldn’t happen and it’s one of the weaknesses of the optimizer that it can happen – in principle, different paths through the optimizer ought to produce the same final cardinality estimates (even though the cost may change).

The little points I wanted to pick out here are that (a) the final query block name has changed, (b) there is no VIEW operator, the optimizer has collapsed the entire query down to a single query block and optimized just that and (c) thanks to the use of query block names we can still see where our two EMP tables originally came from.

ANSI headache

I can’t end without mentioning an annoying feature of ANSI SQL in Oracle. Putting it crudely, the optimizer doesn’t like ANSI and (apart from a couple of special cases) transforms ANSI into an equivalent Oracle format before optimizing it. This makes it much harder to use query block names. Consider, for example, the following very simple query and execution plan (excluding predicates):

select
     /*+ qb_name(main) */
     *
from
     t1
join
     t2
on   t2.t2_n1 = t1.t1_n2
join
     t3
on   t3.t3_n1 = t2.t2_n2
join
     t4
on   t4.t4_n1 = t3.t3_n2
;

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   192K|   140M|    56  (15)| 00:00:01 |
|*  1 |  HASH JOIN           |      |   192K|   140M|    56  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | T4   |  3000 |   562K|    12   (0)| 00:00:01 |
|*  3 |   HASH JOIN          |      | 48000 |    26M|    39   (8)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | T3   |  3000 |   562K|    12   (0)| 00:00:01 |
|*  5 |    HASH JOIN         |      | 12000 |  4488K|    25   (4)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T1   |  3000 |   562K|    12   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| T2   |  3000 |   559K|    12   (0)| 00:00:01 |
-----------------------------------------------------------------------------

There’s nothing unexpected about the plan – it seems a perfectly reasonable way to join the 4 tables; but an anomaly appears when you look at the “Query block / Object Alias section of the plan. We clearly have a single query block in the original text, and all 4 tables are in that query block; and a quick check of syntax confirms that the qb_name() hint is properly specified – but here’s how Oracle sees it:

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$43767242
   2 - SEL$43767242 / T4@SEL$3
   4 - SEL$43767242 / T3@SEL$2
   6 - SEL$43767242 / T1@SEL$1
   7 - SEL$43767242 / T2@SEL$1

We don’t have a query block called “main”, we have 3 separate query blocks given the default names of sel$1, sel$2 and sel$3, and we don’t have a four-table join, we have three separate two-table joins. In fact we would be able to see in the outline section that the query block name “main“ has been used but has been merged. In effect, Oracle read the ANSI and transformed it into the following query:

select     /*+ qb_name(main) */
     *
from (
     select    /*+ qb_name(sel$3) */
           *
     from
           (
           select    /*+ qb_name(sel$2) */
                *
           from (
                select
                     /*+ qb_name(sel$1) */
                     *
                from
                     t1,
                     t2
                where t2.t2_n1 = t1.t1_n2
                ) v1,
                t3
           where t3.t3_n1 = v1.t2_n2
           )    v2,
          t4
     where t4.t4_n1 = v2.t3_n2
     )
;

In complex cases, this type of preliminary rewrite can make it much harder to work out how to inject the hints you need when you’re trying to force a particular execution plan for a query.

Summary notes

It’s a good idea to use the qb_name hint throughout your queries to give explicit names to the query blocks that make up your queries. When a query is transformed internally by the optimizer this naming makes it much easier to associate the positions of tables in the transformed plan with their positions in the original text – this can be particularly helpful if the same table is used several times in a single query. Unfortunately, Oracle’s treatment of even the simplest ANSI SQL statements confuses the issue by generating a lot of query block names that you can’t affect.

If you see the VIEW operator in an execution plan, this identifies a separately optimized query block in your (possibly transformed) SQL – sometimes this will also mean that the result set of that piece of SQL will be completely built in memory before the next steps of the query plan take place but this is not necessarily the case. You will probably be able to associate a VIEW operator in the plan with an OUTLINE_LEAF() hint in the outline section of the output.

The outline section of the dbms_xplan output will show some OUTLINE() hints – which correspond to initial or intermediate query blocks – and some (or possibly only one) OUTLINE_LEAF() hints – which correspond to the final query block(s) that were individually optimized.

Tags: , , , ,