Search

OakieTags

Who's online

There are currently 0 users and 42 guests online.

Recent comments

Affiliations

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.

create table t1
as
select
	rownum 			id,
	lpad(rownum,200)	padding
from	all_objects
where	rownum <= 2500 ; 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.