Oracle Scratchpad

October 19, 2014

Plan depth

Filed under: 12c,Bugs,Execution plans,Oracle,subqueries — Jonathan Lewis @ 6:20 pm GMT Oct 19,2014

A recent posting on OTN reminded me that I haven’t been poking Oracle 12c very hard to see which defects in reporting execution plans have been fixed. The last time I wrote something about the problem was about 20 months ago referencing 11.2.0.3; but there are still oddities and irritations that make the nice easy “first child first” algorithm fail because the depth calculated by Oracle doesn’t match the level that you would get from a connect-by query on the underlying plan table. Here’s a simple fail in 12c:


create table t1
as
select
	rownum 			id,
	lpad(rownum,200)	padding
from	all_objects
where	rownum <= 2500
;

create table t2
as
select	* from t1
;

-- call dbms_stats to gather stats

explain plan for
select
	case mod(id,2)
		when 1 then (select max(t1.id) from t1 where t1.id <= t2.id)
		when 0 then (select max(t1.id) from t1 where t1.id >= t2.id)
	end id
from	t2
;

select * from table(dbms_xplan.display);

It ought to be fairly clear that the two inline scalar subqueries against t1 should be presented at the same level in the execution hierarchy; but here’s the execution plan you get from Oracle:

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |  2500 | 10000 | 28039   (2)| 00:00:02 |
|   1 |  SORT AGGREGATE      |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL  | T1   |   125 |   500 |    11   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  4 |     TABLE ACCESS FULL| T1   |   125 |   500 |    11   (0)| 00:00:01 |
|   5 |  TABLE ACCESS FULL   | T2   |  2500 | 10000 |    11   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."ID"<=:B1)
   4 - filter("T1"."ID">=:B1)

As you can see, the immediate (default?) visual impression you get from the plan is that one of the subqueries is subordinate to the other. On the other hand if you check the id and parent_id columns from the plan_table you’ll find that lines 1 and 3 are both direct descendents of line 0 – so they ought to have the same depth. The plan below is what you get if you run the 8i query from utlxpls.sql against the plan_table.


SQL> select id, parent_id from plan_table;

        ID  PARENT_ID
---------- ----------
         0
         1          0
         2          1
         3          0
         4          3
         5          0

--------------------------------------------------------------------------------
| Operation                 |  Name    |  Rows | Bytes|  Cost  | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT          |          |     2K|    9K|  28039 |       |       |
|  SORT AGGREGATE           |          |     1 |    4 |        |       |       |
|   TABLE ACCESS FULL       |T1        |   125 |  500 |     11 |       |       |
|  SORT AGGREGATE           |          |     1 |    4 |        |       |       |
|   TABLE ACCESS FULL       |T1        |   125 |  500 |     11 |       |       |
|  TABLE ACCESS FULL        |T2        |     2K|    9K|     11 |       |       |
--------------------------------------------------------------------------------

So next time you see a plan and the indentation doesn’t quite seem to make sense, perhaps a quick query to select the id and parent_id will let you check whether you’ve found an example where the depth calculation produces a misleading result.

 

Update 20th Oct 2014

A question via twitter – does the error also show up with dbms_xplan.display_cursor(), SQL tuning sets, AWR, etc. or is it just a defect of explain plan. Since the depth is (probably) a derived value for display purposes that Oracle doesn’t use internally for executing the plan I would be inclined to assume that the defect is universal, but I’ve only checked it through explain plan/display, and through execution/display_cursor().

 

 

 

3 Comments »

  1. Thanks for excellent case, Jonathan

    from the other hand the two inline scalar subqueries against t1 are executing sequentially, one after the another, isn’t it?:

    12.1.0.1.SCOTT@orcl1201pdb SQL> select
      2      case mod(id,2)
      3          when 1 then (select max(t1.id) from t1 where t1.id <= t2.id)
      4          when 0 then (select max(t1.id) from t1 where t1.id >= t2.id)
      5      end id
      6  from    t2
      7  where rownum <= 1
      8  /
    
            ID
    ----------
             1
    
    SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('','',format => 'iostats last'));
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      |      1 |        |      1 |00:00:00.01 |       3 |
    |   1 |  SORT AGGREGATE      |      |      1 |      1 |      1 |00:00:00.01 |      78 | -- 1st
    |*  2 |   TABLE ACCESS FULL  | T1   |      1 |    125 |      1 |00:00:00.01 |      78 |
    |   3 |    SORT AGGREGATE    |      |      0 |      1 |      0 |00:00:00.01 |       0 | -- next
    |*  4 |     TABLE ACCESS FULL| T1   |      0 |    125 |      0 |00:00:00.01 |       0 |
    |*  5 |  COUNT STOPKEY       |      |      1 |        |      1 |00:00:00.01 |       3 |
    |   6 |   TABLE ACCESS FULL  | T2   |      1 |      1 |      1 |00:00:00.01 |       3 |
    ---------------------------------------------------------------------------------------

    and plan visual impression (based on depth) simply reflects the execution order?

    Igor

    Comment by Игорь Усольцев — October 20, 2014 @ 9:26 pm GMT Oct 20,2014 | Reply

  2. Igor,

    Bear in mind that only one of the statements will execute for any one row of the table and, in principle, the second subquery in the case expression might be the ONLY subquery to run – so there ought to be no indication of sequential execution in the plan. After all, if we switch the order of the case conditions this is what we get:

    select  /*+ gather_plan_statistics */
         case mod(id,2)
            when 0 then (select max(t1.id) from t1 where t1.id <= t2.id)
            when 1 then (select max(t1.id) from t1 where t1.id <= t2.id)
        end id
    from    t2
    where rownum <= 1
    ;
    
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |      |      1 |        |      1 |00:00:00.01 |       2 |
    |   1 |  SORT AGGREGATE      |      |      0 |      1 |      0 |00:00:00.01 |       0 |
    |*  2 |   TABLE ACCESS FULL  | T1   |      0 |    125 |      0 |00:00:00.01 |       0 |
    |   3 |    SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      76 |
    |*  4 |     TABLE ACCESS FULL| T1   |      1 |    125 |      1 |00:00:00.01 |      76 |
    |*  5 |  COUNT STOPKEY       |      |      1 |        |      1 |00:00:00.01 |       2 |
    |   6 |   TABLE ACCESS FULL  | T2   |      1 |      1 |      1 |00:00:00.01 |       2 |
    ---------------------------------------------------------------------------------------
    
    
    

    Comment by Jonathan Lewis — October 21, 2014 @ 2:40 pm GMT Oct 21,2014 | Reply

  3. Not on your main point of your post (and probably why you have commented it out in your script), but in 12c you don’t need to call DBMS_STATS after doing a “CREATE TABLE AS”, as a 12c CTAS performs “OPTIMIZER STATISTICS GATHERING” as part of the table creation/loading.

    Comment by Richard Smith — October 22, 2014 @ 4:13 pm GMT Oct 22,2014 | 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

Blog at WordPress.com.