Losing it

The example I gave last week showing how a SORT operation in an execution plan might include the work of resolving function calls in your SQL and might, therefore, be reporting much higher resource utilisation than expected reminded me of some problems I’ve had with gaps in execution plans in the past. So I thought I’d give a little demonstration of the way in which the completeness of execution plans can develop over time.

We’ll start with the same two tables I had in last week’s demo.

rem
rem     Script:         plan_gaps.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jan 2013
rem

create table t1
as
select
        rownum                  id,
        lpad(rownum,200)        padding
from    all_objects
where   rownum <= 2500  -- > comment to avoid wordpress format issue
;

create table t2
as
select
        *
from t1
;

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                method_opt       => 'for all columns size 1'
        );

        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T2',
                method_opt       => 'for all columns size 1'
        );

end;
/

Once we have the tables and stats, we can start running a few very simple queries – I have a sequence of three queries to demonstrate, showing the lovely progression of history:

select
        (select max(t1.id) from t1 where t1.id <= t2.id) id
from    t2
;

select  id
from    t1
minus
select
        (select max(t1.id) from t1 where t1.id <= t2.id) id
from    t2
;

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
;

The first, and simplest query, demonstrates 8i failing to produce the right plan – but 9i handles it correctly:

Plan from 8.1.7.4
  Id  Par  Pos  Ins Plan
---- ---- ---- ---- ----------------------------------------------------------------------
   0         3        SELECT STATEMENT (all_rows)     Cost (3,2500,10000)
   1    0    1    2     TABLE ACCESS (analyzed)  TEST_USER T2 (full)  Cost (3,2500,10000)

Plan from 9.2.0.8
-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  2500 | 10000 |    12   (9)|
|   1 |  SORT AGGREGATE      |             |     1 |     4 |            |
|*  2 |   TABLE ACCESS FULL  | T1          |   125 |   500 |    12   (9)|
|   3 |  TABLE ACCESS FULL   | T2          |  2500 | 10000 |    12   (9)|
-------------------------------------------------------------------------

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

The second query, including a section of SQL that 9i handled properly, shows an incomplete plan in 9i and 10g, but gets a complete plan in 11g. (The row estimate of 125 in line 5 of the second plan is the usual 5% estimate for a range-based predicate against an unknown value: 5% of 2,500 is 125).

Plan from 9.2.0.8 / 10.2.0.5
-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  2500 | 20000 |    26  (58)|
|   1 |  MINUS               |             |       |       |            |
|   2 |   SORT UNIQUE        |             |  2500 | 10000 |    13  (16)|
|   3 |    TABLE ACCESS FULL | T1          |  2500 | 10000 |    12   (9)|
|   4 |   SORT UNIQUE        |             |  2500 | 10000 |    13  (16)|
|   5 |    TABLE ACCESS FULL | T2          |  2500 | 10000 |    12   (9)|
-------------------------------------------------------------------------

Plan from 11.1.0.7
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  2500 | 20000 |    25  (56)| 00:00:01 |
|   1 |  MINUS              |      |       |       |            |          |
|   2 |   SORT UNIQUE       |      |  2500 | 10000 |    12   (9)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T1   |  2500 | 10000 |    11   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  5 |    TABLE ACCESS FULL| T1   |   125 |   500 |    11   (0)| 00:00:01 |
|   6 |   SORT UNIQUE       |      |  2500 | 10000 |    12   (9)| 00:00:01 |
|   7 |    TABLE ACCESS FULL| T2   |  2500 | 10000 |    11   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

But if we start to hide subqueries inside CASE operators (decodes would do the same), 11g starts to get it a little wrong, as shown by the third example. The depth column of the plan_table can be calculated incorrectly, in this case giving the impression that line 6 is a descendent of line 5. (Funnily enough, 9i gets this example right because the code to display the plan from the plan_table uses the old “connect by” query on id and parent_id rather than the calculated depth column.

Plan from 11.1.0.7 / 11.2.0.3
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |  2500 | 20000 |    25  (56)| 00:00:01 |
|   1 |  MINUS                |      |       |       |            |          |
|   2 |   SORT UNIQUE         |      |  2500 | 10000 |    12   (9)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T1   |  2500 | 10000 |    11   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE      |      |     1 |     4 |            |          |
|*  5 |    TABLE ACCESS FULL  | T1   |   125 |   500 |    11   (0)| 00:00:01 |
|   6 |     SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  7 |      TABLE ACCESS FULL| T1   |   125 |   500 |    11   (0)| 00:00:01 |
|   8 |   SORT UNIQUE         |      |  2500 | 10000 |    12   (9)| 00:00:01 |
|   9 |    TABLE ACCESS FULL  | T2   |  2500 | 10000 |    11   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

You might note, by the way, that in all these examples the estimated COST of the plans is very misleading. The optimizer has made no attempt to allow for the cost of the repeated execution of the scalar subqueries. This doesn’t really matter, of course, for very simple queries like this, but it could make a big difference if something of this sort were embedded in the middle of a more complex statement.

Next time you have to unravel the execution plan for a complex query with scalar subqueries floating around the place – there may be bits of the plan that you can’t see, or that aren’t doing quite what you think you’re being told. When interpretation gets tough make sure you track through the query and the plan to see if the plan is likely to be a complete and truthful representation of what the statement has to do.