Oracle Scratchpad

April 20, 2020

Execution Plans

Filed under: Execution plans,Oracle — Jonathan Lewis @ 3:37 pm BST Apr 20,2020

One of the most important skills needed when investigating badly performing SQL is the ability to read Execution Plans. It’s a topic I’ve written and spoken about frequently – even to the extent of doing full-day seminars – but there’s always scope for finding another way of presenting the method.

This is a note based on a few introductory Powerpoint slides I created for the (sadly cancelled) Polish OUG Workshop and Tanel Poder’s Virtual Conference taking a slightly different approach from one I normally use to get people stated on interpreting (serial) execution plans.

I want to begin with a query, rather than a plan:

rem
rem     Script:         poug_plan_01a.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2020
rem

explain plan for
select
        /*+
                qb_name(main)
        */
        ord.id,
        ord.valuation,
        ord.status,
        (select /*+ qb_name(company) */ max(com.name)  from companies  com  where com.id  = ord.id_company) company,
        (select /*+ qb_name(product) */ max(prd1.name) from products   prd1 where prd1.id = orl.id_product) product,
        orl.quantity
from
        orders          ord,
        order_lines     orl
where
        ord.date_placed > trunc(sysdate) - 7
and     orl.id_ord = ord.id
and     orl.id_product in (
                select  /*+ qb_name(class) */
                        prd2.id
                from    products prd2
                where   prd2.class = 'Group25'
        )
/

I’ve got a query with 4 “query blocks” – every time you see the key words “select”, “insert”, “merge”, etc. that’s a query block, and it’s very helpful to adopt a habit of naming the query blocks in your SQL statements, as I have done here, using the the qb_name() hint.

What’s the optimizer going to do with this query, and how is Oracle going to run the query?

There are a number of possibilities – some of them version dependent, some dictated by the statistics Oracle has about the data, some dictated by the available indexes and constraints, but there’s one key feature that will come into play: the optimizer “likes” statements that consist of a single query block because it’s easy to optimize statements of that shape, so the optimizer will attempt to transform this query in ways that will reduce the number of query blocks it has to handle.

In newer versions of Oracle the optimizer uses “cost based query transformation” almost everywhere, but in older versions of Oracle a number of the transformation were “heuristic” (i.e.: “if I see an X I’m going to transform it into a Y”). Cost based query transformation means the optimizer will try to  work out the efficiency of applying a transformation and may then decide not to do it.

Heuristic Transformations: Sometimes the optimizer trace file (10053 trace) will tell you that it has performed a heuristic transformation by printing  a message like (e.g.):  SU: Performing unnesting that does not require costing.

I’m going to do two things with this query – first I’ll tell the optimizer that it should not do any cost-based transformations, then I’ll give it free rein to do whatever it fancies. All I have to do for the first case is add the hint /*+ no_query_transformation */ to the query, which gets me following execution plan under 12.2.0.1:


select * from table(dbms_xplan.display(null,null,'alias'));

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    34 |  3567   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE              |             |     1 |    35 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| COMPANIES   |     1 |    35 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | COM_PK      |     1 |       |     1   (0)| 00:00:01 |
|   4 |  SORT AGGREGATE              |             |     1 |    35 |            |          |
|   5 |   TABLE ACCESS BY INDEX ROWID| PRODUCTS    |     1 |    35 |     2   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | PRD_PK      |     1 |       |     1   (0)| 00:00:01 |
|*  7 |  FILTER                      |             |       |       |            |          |
|*  8 |   HASH JOIN                  |             |  3500 |   116K|    52  (24)| 00:00:01 |
|*  9 |    TABLE ACCESS FULL         | ORDERS      |   700 | 16100 |    12  (34)| 00:00:01 |
|  10 |    TABLE ACCESS FULL         | ORDER_LINES | 50000 |   537K|    37  (14)| 00:00:01 |
|* 11 |   TABLE ACCESS BY INDEX ROWID| PRODUCTS    |     1 |    12 |     2   (0)| 00:00:01 |
|* 12 |    INDEX UNIQUE SCAN         | PRD_PK      |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - COMPANY
   2 - COMPANY / COM@COMPANY
   3 - COMPANY / COM@COMPANY
   4 - PRODUCT
   5 - PRODUCT / PRD1@PRODUCT
   6 - PRODUCT / PRD1@PRODUCT
   7 - MAIN
   9 - MAIN    / ORD@MAIN
  10 - MAIN    / ORL@MAIN
  11 - CLASS   / PRD2@CLASS
  12 - CLASS   / PRD2@CLASS

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("COM"."ID"=:B1)
   6 - access("PRD1"."ID"=:B1)
   7 - filter( EXISTS (SELECT /*+ QB_NAME ("CLASS") */ 0 FROM "PRODUCTS" "PRD2"
              WHERE "PRD2"."ID"=:B1 AND "PRD2"."CLASS"='Group25'))
   8 - access("ORL"."ID_ORD"="ORD"."ID")
   9 - filter("ORD"."DATE_PLACED">TRUNC(SYSDATE@!)-7)
  11 - filter("PRD2"."CLASS"='Group25')
  12 - access("PRD2"."ID"=:B1)

I’ll say more about this plan later, but for the moment I want to pick up two key points.

  • First, you can almost see the 4 query blocks in the body of execution plan. The join between orders and order_lines appears in operations 8 – 10; the query for max(companies.name) is highly visible in operations 1 – 3, as is the query for max(products.name) in operations 4 – 6; but the “IN” subquery is slightly less obvious in the combination of the FILTER operation at operation 7 and the indexed access in operations 11 and 12. Looking at operation 7 in the Predicate Information section of the plan you will note that the “IN” subquery has changed to an “EXISTS” subquery despite the hint that the optimizer shouldn’t do any cost based query transformation – and that’s because this was a heuristic transformation, not a costed transformation, the optimizer did it because it could!
  • The second thing I particularly want to draw your attention to is the section labelled Query Block Name / Object Alias – which appears as the result of including the ‘alias’ format option in my call to dbms_xplan.display(). This gives us two bits of information: for each operation in the plan (except for a few gaps) it has told us which “final” (also referred to as an “outline_leaf”) query block owns that operation; similarly, for any operation involving an object name in the plan it has given us the “fully qualified” alias, i.e. the underlying object alias combined with the query block where the alias was introduced.

For complex plans this alias information is so useful that it’s a bit of a shame that it doesn’t appear in the body of the plan when you ask for it – so before I go on I’m going to edit the output to show it the way I’d like to see it:


-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Query Block | Alias        |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    34 |  3567   (1)| 00:00:01 |             |              |
|   1 |  SORT AGGREGATE              |             |     1 |    35 |            |          | COMPANY     |              |
|   2 |   TABLE ACCESS BY INDEX ROWID| COMPANIES   |     1 |    35 |     2   (0)| 00:00:01 | COMPANY     | COM@COMPANY  |
|*  3 |    INDEX UNIQUE SCAN         | COM_PK      |     1 |       |     1   (0)| 00:00:01 | COMPANY     | COM@COMPANY  |
|   4 |  SORT AGGREGATE              |             |     1 |    35 |            |          | PRODUCT     |              |
|   5 |   TABLE ACCESS BY INDEX ROWID| PRODUCTS    |     1 |    35 |     2   (0)| 00:00:01 | PRODUCT     | PRD1@PRODUCT |
|*  6 |    INDEX UNIQUE SCAN         | PRD_PK      |     1 |       |     1   (0)| 00:00:01 | PRODUCT     | PRD1@PRODUCT |
|*  7 |  FILTER                      |             |       |       |            |          |             |              |
|*  8 |   HASH JOIN                  |             |  3500 |   116K|    52  (24)| 00:00:01 | MAIN        |              |
|*  9 |    TABLE ACCESS FULL         | ORDERS      |   700 | 16100 |    12  (34)| 00:00:01 | MAIN        | ORD@MAIN     |
|  10 |    TABLE ACCESS FULL         | ORDER_LINES | 50000 |   537K|    37  (14)| 00:00:01 | MAIN        | ORL@MAIN     |
|* 11 |   TABLE ACCESS BY INDEX ROWID| PRODUCTS    |     1 |    12 |     2   (0)| 00:00:01 | CLASS       | PRD2@CLASS   |
|* 12 |    INDEX UNIQUE SCAN         | PRD_PK      |     1 |       |     1   (0)| 00:00:01 | CLASS       | PRD2@CLASS   |
-------------------------------------------------------------------------------------------------------------------------

In this initial example the extra columns don’t really add much value – although my alias column does show us very clearly that PRODUCTS (the Name) at operation 11 comes from the CLASS subquery while PRODUCTS at operation 5 comes from the scalar subquery named PRODUCT. Such things are not always so obvious (some readers may immediately think of problems seeing multiple copies of table gl_code_combinations in very long execution plans).

Let’s move on to the second call to the query where we let the 12.2 optimizer do whatever it thinks best with my query – this time I’ll go straight to the edited version of the plan (and also leave the Query Block/Alias section in place):


-------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Query Block  | Alias        |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |    71 |  8236 |    89  (18)| 00:00:01 |              |              |
|*  1 |  HASH JOIN OUTER      |             |    71 |  8236 |    89  (18)| 00:00:01 | SEL$040EE093 |              |
|*  2 |   HASH JOIN OUTER     |             |    71 |  5751 |    75  (19)| 00:00:01 |              |              |
|*  3 |    HASH JOIN          |             |    71 |  3266 |    65  (20)| 00:00:01 |              |              |
|*  4 |     TABLE ACCESS FULL | ORDERS      |   700 | 16100 |    12  (34)| 00:00:01 | SEL$040EE093 | ORD@MAIN     |
|*  5 |     HASH JOIN         |             |  1008 | 23184 |    53  (17)| 00:00:01 |              |              |
|*  6 |      TABLE ACCESS FULL| PRODUCTS    |   200 |  2400 |    13   (8)| 00:00:01 | SEL$040EE093 | PRD2@CLASS   |
|   7 |      TABLE ACCESS FULL| ORDER_LINES | 50000 |   537K|    37  (14)| 00:00:01 | SEL$040EE093 | ORL@MAIN     |
|   8 |    TABLE ACCESS FULL  | COMPANIES   | 10000 |   341K|    10  (10)| 00:00:01 | SEL$040EE093 | COM@COMPANY  |
|   9 |   TABLE ACCESS FULL   | PRODUCTS    | 10000 |   341K|    13   (8)| 00:00:01 | SEL$040EE093 | PRD1@PRODUCT |
-------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$040EE093
   4 - SEL$040EE093 / ORD@MAIN
   6 - SEL$040EE093 / PRD2@CLASS
   7 - SEL$040EE093 / ORL@MAIN
   8 - SEL$040EE093 / COM@COMPANY
   9 - SEL$040EE093 / PRD1@PRODUCT

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("PRD1"."ID"(+)="ORL"."ID_PRODUCT")
   2 - access("COM"."ID"(+)="ORD"."ID_COMPANY")
   3 - access("ORL"."ID_ORD"="ORD"."ID")
   4 - filter("ORD"."DATE_PLACED">TRUNC(SYSDATE@!)-7)
   5 - access("ORL"."ID_PRODUCT"="PRD2"."ID")
   6 - filter("PRD2"."CLASS"='Group25')

Note
-----
   - this is an adaptive plan

It’s a little unsatisfactory that some of the lines in the plan don’t record a query block name at all – however it is still fairly obvious that the entire query has been collapsed into a single query block that is nothing more than a five-table hash join. To get this single query block the optimizer has “unnested” three subqueries and eliminated two ‘group by’s. It’s when the optimzer transforms the query this much that the alias information can be so useful – how easy would it be to decide which occurrence of PRODUCTS at operations 6 and 9 corresponded to which part of the original query?

First Child First – Recursive Descent (FCF)

This is where we finally get to the key rule for reading a (serial) execution plan. A (section of a) plan involving a single query block obeys the sound-bite: “first child first – recursive descent”. (or FCF for short)

For a plan involving multiple query blocks – each query block individually follows the rule, and then you have to learn specific rules for stitching together multiple query blocks. I’ll supply examples of possible stitch-ups over the next few weeks. For now I’ll just show “FCF” applied to the second plan, then take a slightly closer look at the first plan.

Each operation in a query block produces (or “is”, or “represents”) a rowsource. When we talk about “the order of execution” of a plan there’s an implicit interpretation of the phrase to mean “the order in which the operations produce their rowsource”.

FCF encapsulates the idea that to produce a rowsource an operation calls each of its child operations in turn to produce their rowsources then takes some action to combine the child rowsources. The order in which the child operations are called is, in the first case, exactly the order in which the child operations are printed in the execution plan.

There are two important details to add to this.

  • First, although a parent operation will call its child operations in turn, the parent may cycle through the child operations multiple times. The obvious example of this is the nested loop join where the parent will fetch a row from its first child then fetch (zero or more) rows from its second child, then fetch the next row from its first child then call its second child again, and repeat until is has consumed the entire rowsource from the first child.
  • Secondly, an operation may be blocking or non-blocking, and it’s worth remembering this when thinking about whether you want to tune a query for latency (time to first row(s) returned) or throughput (time to last row returned). Some operations will have to create their entire rowsource before they pass anything up to their parent (blocking); some operations will create and pass their rowsource piece by piece on demand.

Let’s apply FCF to the single block plan – which I’ll repeat here without the predicate and alias information:

-------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |    71 |  8236 |    89  (18)| 00:00:01 |
|*  1 |  HASH JOIN OUTER      |             |    71 |  8236 |    89  (18)| 00:00:01 |
|*  2 |   HASH JOIN OUTER     |             |    71 |  5751 |    75  (19)| 00:00:01 |
|*  3 |    HASH JOIN          |             |    71 |  3266 |    65  (20)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL | ORDERS      |   700 | 16100 |    12  (34)| 00:00:01 |
|*  5 |     HASH JOIN         |             |  1008 | 23184 |    53  (17)| 00:00:01 |
|*  6 |      TABLE ACCESS FULL| PRODUCTS    |   200 |  2400 |    13   (8)| 00:00:01 |
|   7 |      TABLE ACCESS FULL| ORDER_LINES | 50000 |   537K|    37  (14)| 00:00:01 |
|   8 |    TABLE ACCESS FULL  | COMPANIES   | 10000 |   341K|    10  (10)| 00:00:01 |
|   9 |   TABLE ACCESS FULL   | PRODUCTS    | 10000 |   341K|    13   (8)| 00:00:01 |
-------------------------------------------------------------------------------------

We start from the top – ignoring the “operation 0 SELECT” which isn’t really a plan operation.

  • Operation 1 is a “hash join outer” – we expect exactly two child operations, the first will supply the “build” table to the hash join, the second will supply the “probe” table. We will have to get the entire rowsource from the first child before calling the second child to start supplying its rowsource. Looking at the text indents, the first child is operation 2, the second is operation 9 so we have to start by calling operation 2.
  • Operation 2 is a “hash join outer” – so, again, we expect it to have two child operations that it will use to construct a rowsource to pass up to its parent. Its first child is operation 3, its second child is operation 8 so we have to start by calling operation 3.
  • Operation 3 is a “hash join” – so, again, we expect it to have two child operations that it will use to construct a rowsource to pass up to its parent. Its first child is operation 4, its second child is operation 5 so we have to start by calling operation 4.
  • Operation 4 is a “table access full” of orders – which is an operation that has no child operations and can immediately start to return a rowsource to its parent. Operation 4 is the first thing that “happens” in this plan.
  • After acquiring the entire rowsource from operation 4 and building an in-memory hash table from it, operation 3 can now call its second child (operation 5) to supply a rowsource to probe the hash table.
  • But operation 5 is a “hash join” – so, yet again, we expect to see two child operations that it will use to construct the rowsource it has to pass up to its parent. Its first child is operation 6, its second child is operation 7 so we have to start by calling operation 6
  • Operation 6 is a “table access full” of products – which is an operation that has no child operations and can immediately start to return a rowsource to its parent. Operation 6 is the second thing that “happens” in this plan.
  • After acquiring the entire rowsource from operation 6 and creating a build table from it, operation 5 can now call its second child (operation 7) to supply a rowsource to use as the probe table.
  • Operation 7 is a “table access full” of order_lines – which is an operation that has no child operations and can immediately start to return a rowsource to its parent. Operation 7 is the third thing that “happens” in this plan
  • Operation 5 can now use the rowsource from operation 7 to probe its build table and start passing the results up to its parent (operation 3) So operation 5 is the fourth thing that “happens” in this plan.
  • Operation 3 can now use the rowsource from operation 5 (its second child) to probe the build table it created from operation 4 and start passing the results up to its parent (operation 2). So operation 3 is the fifth thing that “happens” in this plan..
  • Operation 2 can now absorb the entire row source from operation 3 (its first child) to construct its build table, after which it has to call its second child (operation 8) to acquire a rowsource to use as its probe table.
  • Operation 8 is a “table access full” of companies – which is an operation that has no child operations and can immediately start to return a rowsource to its parent (operation 2). Operation 8 is the sixth thing that “happens” in this plan.
  • Operation 2 can now use the rowsource from operation 8 to probe its build table and start passing the results up to its parent (operation 1) So operation 2 is the seventh thing that “happens” in this plan.
  • Operation 1 can now absorb the entire row source from operation 2 (its first child) to construct its build table, after which it has to call its second child (operation 9) to acquire a rowsource to use as its probe table.
  • Operation 9 is a “table access full” of products – which is an operation that has no child operations and can immediately start to return a rowsource to its parent (operation 1). Operation 9 is the eighth thing that “happens” in this plan.
  • Operation 1 can now use the rowsource from operation 9 to probe its build table and start passing the results up to its parent (“operation 0″/ end-user) So operation 1 is the ninth thing that “happens” in this plan.

Extracting the ordering information the order of operation is:

  • Operation 4 is the first thing that happens
  • Operation 6 is the second thing that happens
  • Operation 7 is the third thing that happens
  • Operation 5 is the fourth thing that happens
  • Operation 3 is the fifth thing that happens
  • Operation 8 is the sixth thing that happens
  • Operation 2 is the seventh thing that happens
  • Operation 9 is the eighth thing that happens
  • Operation 1 is the ninth thing that happens

The rule is very simple but the process seems very long-winded when you have to write down every step completely (fortunately it doesn’t take much practice for this to become a rapid mental exercise in many cases). Start at the top and keep repeating (recursive descent) “what’s the first child” until you get to the operation that doesn’t have any child operations – that’s the first operation in the order of execution. Then back up one step to see if there’s a second (then third, then …) child to consider. and repeat the process of descending and ascending.

There’s one further important observation to make about this particular case.  The order of operation started with the orders table. The first two tables to be joined were the products and order_lines tables. The optimizer has produced a plan where the order of operation does not match the join order. This is an artefact of the optimizer’s ability to “swap join inputs” for hash joins when evaluating a join order.

Multiple Query Blocks

I’ll finish with a brief look at the plan I got when I blocked cost-based query transformation.  This time I’ll show the plan with the edited query block names still in place:


-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Query Block | Alias        |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    34 |  4017   (1)| 00:00:01 |             |              |
|   1 |  SORT AGGREGATE              |             |     1 |    35 |            |          | COMPANY     |              |
|   2 |   TABLE ACCESS BY INDEX ROWID| COMPANIES   |     1 |    35 |     2   (0)| 00:00:01 | COMPANY     | COM@COMPANY  |
|*  3 |    INDEX UNIQUE SCAN         | COM_PK      |     1 |       |     1   (0)| 00:00:01 | COMPANY     | COM@COMPANY  |
|   4 |  SORT AGGREGATE              |             |     1 |    35 |            |          | PRODUCT     |              |
|   5 |   TABLE ACCESS BY INDEX ROWID| PRODUCTS    |     1 |    35 |     2   (0)| 00:00:01 | PRODUCT     | PRD1@PRODUCT |
|*  6 |    INDEX UNIQUE SCAN         | PRD_PK      |     1 |       |     1   (0)| 00:00:01 | PRODUCT     | PRD1@PRODUCT |
|*  7 |  FILTER                      |             |       |       |            |          |             |              |
|*  8 |   HASH JOIN                  |             |  3950 |   131K|    52  (24)| 00:00:01 | MAIN        |              |
|*  9 |    TABLE ACCESS FULL         | ORDERS      |   790 | 18170 |    12  (34)| 00:00:01 | MAIN        | ORD@MAIN     |
|  10 |    TABLE ACCESS FULL         | ORDER_LINES | 50000 |   537K|    37  (14)| 00:00:01 | MAIN        | ORL@MAIN     |
|* 11 |   TABLE ACCESS BY INDEX ROWID| PRODUCTS    |     1 |    12 |     2   (0)| 00:00:01 | CLASS       | PRD2@CLASS   |
|* 12 |    INDEX UNIQUE SCAN         | PRD_PK      |     1 |       |     1   (0)| 00:00:01 | CLASS       | PRD2@CLASS   |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("COM"."ID"=:B1)
   6 - access("PRD1"."ID"=:B1)
   7 - filter( EXISTS (SELECT /*+ QB_NAME ("CLASS") */ 0 FROM "PRODUCTS" "PRD2"
              WHERE "PRD2"."ID"=:B1 AND "PRD2"."CLASS"='Group25'))
   8 - access("ORL"."ID_ORD"="ORD"."ID")
   9 - filter("ORD"."DATE_PLACED">TRUNC(SYSDATE@!)-7)
  11 - filter("PRD2"."CLASS"='Group25')
  12 - access("PRD2"."ID"=:B1)

Under the Query Block column you can see the four separate blocks clearly labelled, and if you use the column to split the plan into 4 separate pieces (which will lose you operation 0 and operation 7) then you should be able to work out what’s happening in the 4 separare sub-plans quite easily.

After that it’s necessary to work out how these 4 subplans should be connected, and in almost all cases you’ll find that you need to look at the query itself to get some clues about the connections. There are a couple of rules that I’ll mention for this example, though.

a) Scalar subqueries in the select list appear as separate sub-plans ABOVE the driving plan. So we know that the COMPANY and PRODUCT sub-plans represent the two scalar subqueries in our select list which will (notionally) run once for every row returned by the main query. What we don’t know is how many times those subqueries will run (partly because we don’t know how many distinct products and companies will be reported by the main query, and partly because we don’t know how effective the “scalar subquery caching” is going to be.

b) When we see a FILTER operation there’s a good chance that there’s going to be a subquery in the “where” clause of the main query – though there are other interpretations.  In our case we know we have an IN subquery in the original text, and can see that this has turned to an EXISTS in the predicate information section so we can connect together the MAIN and CLASS sub-plans using the “filter-subquery” mechanism which behaves a little like a nested loop join, viz: for each row supplied by the first child call the second child (and subquent children in turn) to see if the row should be passed up to the parent operation. We have only two child operations in this case, but if there were several child operations Oracle would call them in order, stopping at the earliest possible moment.

The FILTER operation covers a multitude of sins, so I’ll be working through more examples in greater detail in future notes.

Costing

A final comment to make about this example, and other plans involving multiple query blocks. The optimizer really has very little idea of how many times any sub-plan / subquery will operate and for a very long time the final Cost (operation 0) of the query would cater only for the cost of the main query block – which meant that sometime you could get a plan where the total cost was lower than the cost of one of the sub-plans. As time passed various algorithms were introduced that resulted in costs that appearing that attempted to estimate the number of times that some of the sub-plan / subquery components were likely to run.

In this example the cost of the main query (operation 8) is 52 with a predicted rowsource of 3,500 rows. The total cost of the plan is shown as 3,567 – which means the increment cost of running the existence subquery 3,500 times (at a cost of something between 1 and 2 each time) has been added, and (possibly) some multiple (the number of rows surviving the FILTER which, unfortunately, is not reported) of the cost of running the other two subqueries has been added to that. A quick check of the plan with the inline scalar subqueries removed indicates that their cost may have been added just once each, and the cost of the filter subquery (slightly over 1) was added 3,500 times.

Clearly there’s plenty of scope for the optimizer to produce poor estimates of run-time costs as it compares different cost-based transformations of a query, so it’s important to be able to recognise the patterns caused by different transformations and taking advantage of hinting to block particular transformations if the optimizer has fooled by its arithmetic into making a bad choice.

 

 

 

 

15 Comments »

  1. >>Operation 4 is the first thing that happens
    Operation 6 is the second thing that happens
    Operation 7 is the third thing that happens
    <<

    Jonathan, Does it holds the result set of operation 4 in the memory(pga) while it's operating on op6/7/5, does it have two build tables(4&6) in the memory until it finishes OP 6/7? Can you pls explain?

    Comment by Mohan. — April 21, 2020 @ 2:19 am BST Apr 21,2020 | Reply

    • Mohan,

      Excellent question.

      Both the build tables from operations 4 and 6 will have to exist at the same time until operation 7 has completed so (unless they’re so large that they end up spilling to disc) they will both be in (PGA) memory at the same time.

      There’s more, though. Operation 7 passes a row to its parent (operation 5) which probes the build table from operation 6; if the row survives the join predicate it is passed to operation 3 which uses it to probe the build table from operation 4; if the row survives again it is passed to operation 2 – which starts to create an in-memory has table. So, for a while there will be 3 build tables in memory, one of them under construction.

      As operation 5 receives the last row from operation 7 and probes its build for the last time it can release the memory it’s using for its build table; then the row (which is the last row from operation 5) goes to operation 3 which probes its build table and passes the result up to operation 2 – and that’s the last row for operation 3 so it can release the memory it’s using for its build table; and we’re left with just one build table held by operation 2, which is now ready to start its second child.

      Summary:

      Build tables at operations 3 and 5 (using the rowsources from 4 and 6 respectively) exist at the same time for a “long” time while the build table at operation 2 (using the rowsource from operation 3) is being created.

      As the build table at operation 2 completes the build tables at operations 3 and 5 can be discarded and the (PGA) memory released.

      Comment by Jonathan Lewis — April 21, 2020 @ 9:37 am BST Apr 21,2020 | Reply

  2. I do have different question which I consider more concerning:
    Why is there a completely different cardinality estimate in the final plans, 75 vs 1 row estimate?

    A also, a smaller change is 790 vs 745 rows of the scan on orders table, did statistics change or had sysdate a bigger impact than I would have expected or … ?
    And also, in the text you mention 3,905 rows, however in the plan I see 3950, which is also a difference of 45.

    Comment by Stefan — April 23, 2020 @ 12:26 pm BST Apr 23,2020 | Reply

    • Stefan,

      Thanks for the email.

      I’ll take the easiest one first. the 3,905 looks like a typo – I sometimes swap the order of characters when I’m typing, especially when I have a pair which are a left/right sequence, my right hand sometimes gets ahead of my left. I’ve corrected that in the text.

      The final cardinality is an optimizer bug; I’ve mentioned it before, though haven’t made a big fuss about it. Check the plan labelled “Execution Plan from 11.1.0.6” in this note.

      The annoying one is the 790/745 one. There’s a design flaw in the code I wrote to generate the date: the query uses trunc(sysdate) – 7 but the data is generated from sysdate – 100 in steps of 1/100 for 10,000 steps. As a consequence if I recreate the data at different times of day I get different results from the queries. I suspect I got the first (790) plan after running the test suite in the evening, and the second (745) plan after re-running the whole thing in the morning of the next day. (The whole suite is 5 separate scripts, so the plans are in different output files).

      At some point I’ll have to edit the data generation script and recreate the data set and outputs to be consistent and edit the blog note.

      Regards
      Jonathan Lewis

      Just managed to get it done before the virtual conference starts.

      Comment by Jonathan Lewis — April 23, 2020 @ 2:52 pm BST Apr 23,2020 | Reply

      • Hi Jonathan,
        thanks very much, so on that note, in general is it “safe” to assume, all_rows mode and no other changes, then the final cardinality estimate of a statement should not change using hints (while costs obviously do), except of course inconsistencies in the optimizer?
        Regards,
        Stefan

        Comment by Anonymous — April 23, 2020 @ 3:46 pm BST Apr 23,2020 | Reply

        • Stefan,

          That would be correct – except, as you say, for inconsistencies and bugs in the optimizer.

          Regards
          Jonathan Lewis

          Comment by Jonathan Lewis — April 24, 2020 @ 4:58 pm BST Apr 24,2020

  3. Have you seen the Teradata equivalent of an EXPLAIN PLAN? (Is Teradata even still around?…) It’s much more plain English, along the lines you discuss here.

    I worked briefly with Teradata in the late 90’s, and I did enjoy reading their plan results, but over time I got so adept at reading Oracle’s concise output that I never really wished for anything more explicitly descriptive.

    Maybe this calls for a PL/SQL script (or something in Python or Ruby, etc.) to convert Oracle’s output to something closer to Teradata’s almost-plain English. (If you or Tanel Poder get bored in the next few months…)

    Comment by Jason Bucata — April 25, 2020 @ 1:39 am BST Apr 25,2020 | Reply

    • Jason,

      No, I’ve not seen a Teradata plan, though I do remember reading about the product many years ago.

      I remember one of the GUI trouble-shooting tools from many years ago having a feature that you could hover over a line on the plan and a pop-up window would give you an English language explanation of what the line was telling you – so it has been done. It might have been TOAD, but there were 3 or 4 simlar tools on the market at the time, so maybe it has disappeared by now.

      Adrian Billington or Randolf Geist spring to mind as alternative authors since they’ve already got something on Github that reports and enhances the output of execution plans.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — April 25, 2020 @ 8:50 am BST Apr 25,2020 | Reply

  4. Dear Jonathan,

    Thank you.Its really useful post.

    Similar to rules a) and b)….,Are there any rules for “VIEW” and “VIEW PUSHED PREDICATE” and “SUBQUERIES” in WHERE caluse.Form your experience,can you please share any such rules.I understand,there will be some violations

    Thank You,
    Satish

    Comment by satish — April 27, 2020 @ 9:38 am BST Apr 27,2020 | Reply

    • Satish
      As a simple set of guidelines
      VIEW – treat as if the opetion is a table that can only be accessed by tablescan. It’s descendants generate the content of the “table”
      VIEW PUSHED PREDICATE – treat as a table with one index, which is defined on the column(s) that appear as the pushed predicate

      A subquery in the where clause may be unnested to become a VIEW, which may be a VIEW PUSHED PREDICATE, or the optimizer might go one step further and follow-up the unnnesting with complex view merging and the VIEW operation could simply disappear with the tables in the subquery being scattered across the main query block.

      If the subquery is not unnested then you may see its query block as the 2nd (or subsequent) child of a FILTER operation; although subquery may be “pushed” in which case the FILTER operation will disappear from the body of the plan and you will have to recognise the subquery by its reappearance in the Predicate Information. I’ll be writing a summary note on Filter Subqueries some time in the next couple of weeks.

      Comment by Jonathan Lewis — April 27, 2020 @ 10:03 am BST Apr 27,2020 | Reply

  5. […] a recent blog note I made the point that there is a very simple rule (“first child first”) for reading execution plans if the query (as written or after transformation by the optimizer) […]

    Pingback by Execution Plans | Oracle Scratchpad — April 27, 2020 @ 11:57 am BST Apr 27,2020 | Reply

  6. Hi Jonathan,

    > Start at the top and keep repeating (recursive descent) “what’s the first child” until you get to the operation that doesn’t have any child operations – that’s the first operation in the order of execution.

    There are cases when child operations are not getting called by their parent. For instance, when we have a FILTER operation that can be resolved to FALSE without calling its children like in the example below:

    ```sql
    create table orders(
      order_id int,
      amount int,
      constraint ord_pk primary key(order_id)
    );
    
    insert into orders values (1, 100);
    
    var bind_order_id number=1
    
    select /*+ gather_plan_statistics*/
           * 
      from orders
     where order_id = nvl(:bind_order_id, order_id);
    
    select * from dbms_xplan.display_cursor(format=>; 'rowstats last');
    
    ---------------------------------------------------------------------------
    | Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |        |      1 |        |      1 |
    |   1 |  CONCATENATION                |        |      1 |        |      1 |
    |*  2 |   FILTER                      |        |      1 |        |      0 |
    |*  3 |    TABLE ACCESS FULL          | ORDERS |      0 |      1 |      0 |
    |*  4 |   FILTER                      |        |      1 |        |      1 |
    |   5 |    TABLE ACCESS BY INDEX ROWID| ORDERS |      1 |      1 |      1 |
    |*  6 |     INDEX UNIQUE SCAN         | ORD_PK |      1 |      1 |      1 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter(:BIND_ORDER_ID IS NULL)
       3 - filter("ORDER_ID" IS NOT NULL)
       4 - filter(:BIND_ORDER_ID IS NOT NULL)
       6 - access("ORDER_ID"=:BIND_ORDER_ID)
    ```
    

    In the example above, there is Operation 2 that has a child operation 3 which did not get called (the fact of getting called is judged by the ‘Starts’ column – its ‘Starts’ 0).
    Personally, I think this one just proves the general rule that you stipulated (FCF) – Oracle is smart to enough to eliminate unneeded work by visiting child nodes when their parent has already been evaluated to FALSE.
    Probably you rule should be extended or it might as well be a little bit special case. Either way, the FILTER operation may not call its children and eliminate the plan’s sub-trees altogether.
    Are you going to cover it in the future notes?
    > The FILTER operation covers a multitude of sins, so I’ll be working through more examples in greater detail in future notes.

    Regards,
    Mikhail.

    Comment by Mikhail Velikikh — April 27, 2020 @ 12:02 pm BST Apr 27,2020 | Reply

    • Mikhail,

      Thanks for the comment

      It’s a noteworthy, but not particularly special example. The ordering for the query block is, after all, unchanged by the fact that in specific cases one child may not be called. After all, if a (serial) hash join finds no data for its first child it doesn’t call its second child.

      There are more interesting examples of FILTER operations, though, where the order of activity doesn’t obey FCF – until you realise that (like this example) you’re looking at multiple query blocks and FCF can’t be casually applied. This particular example was going to get a mention in that “chapter” of the series.

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — April 27, 2020 @ 2:31 pm BST Apr 27,2020 | Reply

  7. Dear Jonathan,

    Thanks for the updates.
    Sure,I will be eagerly waiting for our posts on filter subqueries.

    Thank You,
    Satish

    Comment by satish — April 28, 2020 @ 4:08 am BST Apr 28,2020 | Reply

  8. […] previous articles on reading execution plans I’ve made the point that the optimizer is very “keen” to transform complex […]

    Pingback by Execution Plans | Oracle Scratchpad — May 1, 2020 @ 1:59 pm BST May 1,2020 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.