Oracle Scratchpad

November 21, 2012

Plan Order

Filed under: Execution plans,Oracle,subqueries — Jonathan Lewis @ 6:53 pm BST Nov 21,2012

The previous post reminded me of another (fairly special) case where the order of operations in an execution plan seems to be wrong according to the “traditional” strategy for reading execution plans. Here’s a simple select statement with its execution plan to demonstrate the point:

select
	small_vc
from
	t1
where
	exists (
		select	null
		from	f1
		where	f1.id       = t1.id
		and	f1.small_vc = t1.small_vc
	)
and
	exists (
		select	null
		from	f2
		where	f2.id = 21
	)
;

------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    29 |    51   (2)| 00:00:01 |
|*  1 |  FILTER               |       |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|       |     1 |    29 |    51   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | F1    |    20 |   280 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | T1    | 10000 |   146K|    48   (0)| 00:00:01 |
|*  5 |   INDEX UNIQUE SCAN   | F2_PK |     1 |    13 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "F2" "F2" WHERE "F2"."ID"=21))
   2 - access("F1"."ID"="T1"."ID" AND "F1"."SMALL_VC"="T1"."SMALL_VC")
   5 - access("F2"."ID"=21)

The traditional strategy for reading this plan (recursively operate the child rows of each parent row in the order that they appear) would say: we scan table F1 and buildilng a hash table in memory, then scan table T1 probing the hash table to perform the hash semi join of line 2. For each row that survives the hash join, the filter operation at line 1 tells us to run the subquery against F2 to see if the row should be passed forward as an output of the select statement.

You might like to pause briefly at this point to convince yourself that this is the way we usually interpret the indentation of an execution plan.

It’s not what happens in this special case. Notice that the second subquery isn’t correlated – it need only run once for Oracle to decide whether or not it will return any data. As a side effect of this special case, the plan operates “upside down”. Here’s the same execution plan pulled from memory after enabling rowsource execution statistics. It’s implrtant to be aware that in my test case the data in F2 doesn’t have a row where id = 21.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  5bd1m72cz4awy, child number 0
-------------------------------------
select  small_vc from  t1 where  exists (   select null   from f1   where f1.id       = t1.id   and
f1.small_vc = t1.small_vc  ) and  exists (   select null   from f2   where f2.id = 21  )

Plan hash value: 1423735592

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|*  1 |  FILTER               |       |      1 |        |      0 |00:00:00.01 |       1 |       |       |          |
|*  2 |   HASH JOIN RIGHT SEMI|       |      0 |      1 |      0 |00:00:00.01 |       0 |   825K|   825K|          |
|   3 |    TABLE ACCESS FULL  | F1    |      0 |     20 |      0 |00:00:00.01 |       0 |       |       |          |
|   4 |    TABLE ACCESS FULL  | T1    |      0 |  10000 |      0 |00:00:00.01 |       0 |       |       |          |
|*  5 |   INDEX UNIQUE SCAN   | F2_PK |      1 |      1 |      0 |00:00:00.01 |       1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   2 - access("F1"."ID"="T1"."ID" AND "F1"."SMALL_VC"="T1"."SMALL_VC")
   5 - access("F2"."ID"=21)

Look carefully at the starts column. Line 1 (filter) started once; line 5 (the second child to the filter) started once and returned no rows; line 2 (the first child to the filter) never started – it didn’t have to because by this point Oracle had already determined that any data it generated would be eliminated by the non-existence of a match from line 5.

Summary

There are a few special case plans where the normal “first child first” rule (“… more what you’d call guidelines than actual rules.” — Captain Barbossa) for reading execution plans doesn’t apply. The “constant subquery” introduces one of them.

Footnote:

If you want to repeat the experiment on different versions of Oracle, here’s the code to generate my test data:

create table t1
as
with generator as (
	select	--+ materialize
		rownum 	id
	from	all_objects
	where	rownum <= 3000
)
select
	rownum			id,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 10000
;

create table f1 as select * from t1 where id <= 20;
create table f2 as select * from t1 where id <= 20;

alter table f1 add constraint f1_pk primary key(id);
alter table f2 add constraint f2_pk primary key(id);

-- collect stats (compute, no histograms)

7 Comments »

  1. Thanks for explanation, just today, spoke with colleague about the order from the previous your post.

    Comment by Sayan Malakshinov — November 21, 2012 @ 7:29 pm BST Nov 21,2012 | Reply

  2. Jonathan,
    Since you are posting about plan order oddities, i wanted to point out one frequent oddity thats encountered (especially in DW environments), the right deep queries with hash joins. (I even remember reading a note from you about that – long time back).

    Comment by srivenu kadiyala — November 26, 2012 @ 5:17 pm BST Nov 26,2012 | Reply

    • Srivenu,

      The oddity in those plans (where the hash join swaps the join input) is that the order in which the tables are joined is not the order in which they appear in the plan. The order in which the operations run is still (except for certain parallel plans) the order you would expect from the “traditional” descent through the plan.

      Comment by Jonathan Lewis — December 13, 2012 @ 6:35 pm BST Dec 13,2012 | Reply

  3. Oracle does not starts the execution from the driving table right out the bat.
    When you have a query that has contradictory predicates (a=1 and a=2) then Oracle realizes it is contradictory and it is pulled up as far as possible so the steps below are not executed.
    it initializes each step from bottom to top starting from the first line so if there is a predicate that can stop the execution of the remaining dependent steps.
    All steps are probed to see if they can be resolved completely or have a dependency. Resolve that dependency and then come back to execute.

    so really the execution is :
    Initialize FILTER in step 1. if filter can be resolved completely then it is done. if it has a dependency then resolve it and then proceed with next step.
    The dependency in this case is the subquery in step 5.
    The join in step 3 is not initialized until the step 5 is resolved at least once.
    Step 5 is initialized and resolved and returns execution to step 1.
    Since all dependencies of 1 are resolved then it can proceed but since the results of step 5 does not match the criteria then it stops.

    Comment by abel — November 28, 2012 @ 2:13 pm BST Nov 28,2012 | Reply

    • Abel,

      Thanks for the description – its a very useful insight into the way we should think about interpreting execution plans. It raises a couple of question, though, most significantly: Is there anything in, or that could be added to, the plan table that could allow an automatic analysis of the plan table to show that the subquery at line 5 was going to run first without having to do a semantic analysis of the filter predicate on line 1 ? As it stands I can infer that line 5 will execute before line 2 – but only because I can work out that line 5 represents what I called a “constant subquery” – but I can’t think of any way to analyze the plan table to make that inference within an SQL or PL/SQL script.

      I’d be prepared to bet that if you ran this plan through OEM and displayed the pages that shows the execution plan for a query with the column showing order of operation, the ordering would be wrong because the code to calculate the order uses (or seems to use) the traditional “first child first” model, and it would be nice if OEM could get these things right.

      Comment by Jonathan Lewis — December 13, 2012 @ 9:41 pm BST Dec 13,2012 | Reply

  4. Very interessting indeed, thanks for sharing !

    Comment by Guillaume Goulet-Vallières — November 30, 2012 @ 9:11 pm BST Nov 30,2012 | Reply

  5. […] plan reading is wrong. For that I used the example given by Jonathan Lewis in his “constant subquery” example and that you can easily […]

    Pingback by SQLTXPLAIN: Execution plan and operation order : Exec Ord column | Mohamed Houri’s Oracle Notes — June 13, 2013 @ 10:01 am BST Jun 13,2013 | Reply


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 3,528 other followers