Oracle Scratchpad

November 29, 2013

Interesting Plan

Filed under: Execution plans,Oracle,subqueries — Jonathan Lewis @ 8:56 am GMT Nov 29,2013

A recent question on the OTN database forum included an execution plan that prompted one reader to ask: “but where has the existence subquery gone?” Here’s the original question showing the query, and here’s the later response showing the plan that prompted the question.

There were three possible reasons why that question may have been posed:

  1. first, of course, is that Oracle may have transformed the subquery into a join (“existence” often turns into a “semi-join”, and sometimes – depending on uniqueness constraints – a simple join). In this case the OP had ensured that this didn’t happen by adding a hint to the code.
  2. second is that the execution plan was extracted from memory (v$sql_plan), and when there is a defect in the code to populate v$sql_plan that results in the actual text of subqueries getting lost, so a line in the predicate section of the plan could read something like:  “AND     IS NOT NULL”, with a small gap where you’re expecting to see the text of a subquery.
  3. third is that we often expect to see a FILTER operation (i.e. separate line) in the execution plan with two children, the first being the data access operation and the second being the start of the subquery plan (see below). However, in this case, Oracle had “pushed” the subquery (thanks to a hint), and in these circumstances the FILTER operation often disappears introducing a small distortion in the shape of the execution plan that makes it easy to mis-interpret the plan. In this case line 11 of the plan visits a table to collect some rows, and line 12 (indented one step from line 11) is the start of the existence subquery.

I came close to writing a complete blog post about this problem with reading execution plans – then decided to check if I had mentioned it before. I have: twice: originally in 2010, and then again in 2012.

“Classic” filter subquery shape:

select from child where exists (select from parent join grandparent)

| Id  | Operation           | Name        | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT    |             |     1 |  1627 |   595K|
|*  1 |  FILTER             |             |       |       |       |
|   2 |   TABLE ACCESS FULL | CHILD       | 10000 |    15M|   381 |
|*  3 |   HASH JOIN         |             |     1 |    22 |   119 |
|*  4 |    TABLE ACCESS FULL| PARENT      |     1 |    11 |    78 |
|   5 |    TABLE ACCESS FULL| GRANDPARENT |  1000 | 11000 |    40 |

Same query with “pushed” subquery.

| Id  | Operation           | Name        | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT    |             |   500 |   794K|   500 |
|*  1 |  TABLE ACCESS FULL  | CHILD       |   500 |   794K|   381 |
|*  2 |   HASH JOIN         |             |     1 |    22 |   119 |
|*  3 |    TABLE ACCESS FULL| PARENT      |     1 |    11 |    78 |
|   4 |    TABLE ACCESS FULL| GRANDPARENT |  1000 | 11000 |    40 |

The first version of the plan can be interpreted by what I have taken to calling the “first child first, recursive descent” method.

To interpret the second plan you actually need to look at the predicate section (which I deliberated didn’t include) to discover that lines 2-4 are the subquery, and then you have to check whether the subquery appears as a FILTER() subquery or as an ACCESS() subquery.


there are a few special cases for serial execution plans where the recursive descent method doesn’t give you the right answer – and it’s almost certain that if you use it on parallel execution plans you’ll come to the wrong conclusion about the order of operation; however OEM (and Toad etc., etc….) still use the recursive descent method to derive the order of operation, which means they can report the wrong order.

[Update 3/Jan/2015: Here’s a link to a catalogue of articles that Randolf Geist and I have written about parallel execution plans showing (a.o.) the failure of the recursive descent mechanism.]


Leave a Comment »

No comments yet.

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: Logo

You are commenting using your 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.

Website Powered by

%d bloggers like this: