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 ensure 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 to discover that lines 2-4 are a subquery, and then you have to check whether they form a filter subquery or an access subquery.

Footnote: 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 this method for displaying the order of operation.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,524 other followers