Filter plan error

In 10g, the code to generate execution plans changed dramatically, as did the SQL used by the dbms_xplan package to report execution plans from the plan table. In 9i, the indentation for the lines of a plan was calculated by the reporting query as the level from a ‘connect by’  query; in 10g the explain plan utility itself calculates the level and populates a column called depth in the plan table.

Unfortunately, the depth isn’t always right. I don’t know all the cases where the bug appears but when you have multiple scalar subqueries appearing at the same level in the query, things go wrong. Here’s an example, first the query, then the (correct) 9i execution plan, then the (wrong) 10g plan.

select
	id, n1, small_vc
from
	test	t
where
	(
		select
			/*+ no_unnest */
			count(*)
		from	check_1	c1
		where	c1.id1 = t.n1
	) !=
	(
		select
			/*+ no_unnest */
			count(*)
		from	check_2	c2
		where	c2.id1 = t.n1
	)
;

The /*+ no_unnest */ hints are there to stop Oracle trying to convert the subqueries into inline views – this is necessary in the example because I wanted to build a clean, simple example. The optimizer can’t always unnest subqueries – even in 11g – but in this simple case it would have done so if I hadn’t blocked the transformation.

This is the 9i plan

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |    50 |   950 |    74 |
|*  1 |  FILTER              |             |       |       |       |
|   2 |   TABLE ACCESS FULL  | TEST        |    50 |   950 |    24 |
|   3 |   SORT AGGREGATE     |             |     1 |     3 |       |
|*  4 |    INDEX RANGE SCAN  | C1_PK       |     4 |    12 |     1 |
|   5 |   SORT AGGREGATE     |             |     1 |     3 |       |
|*  6 |    INDEX RANGE SCAN  | C2_PK       |     4 |    12 |     1 |
--------------------------------------------------------------------       

Predicate Information (identified by operation id):
---------------------------------------------------       

   1 - filter( (SELECT /*+ NO_UNNEST */ COUNT(*) FROM "CHECK_1"
              "C1" WHERE "C1"."ID1"=:B1) != (SELECT /*+ NO_UNNEST */ COUNT(*) FROM
              "CHECK_2" "C2" WHERE "C2"."ID1"=:B2))
   4 - access("C1"."ID1"=:B1)
   6 - access("C2"."ID1"=:B1)       

Notice how the indentation on lines three and five are the same – both lines are child operations to the filter operation on line one, and this fact is echoed in the filter predicate for line one. (I had to edit in the “!=” that appears in the filter predicate to get around an oddity with the way WordPress handles < and >)

And this is the plan in 10g

--------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |  1000 | 19000 |    25 |
|*  1 |  FILTER              |       |       |       |       |
|   2 |   TABLE ACCESS FULL  | TEST  |  1000 | 19000 |    24 |
|   3 |   SORT AGGREGATE     |       |     1 |     3 |       |
|*  4 |    INDEX RANGE SCAN  | C1_PK |     4 |    12 |     1 |
|   5 |     SORT AGGREGATE   |       |     1 |     3 |       |
|*  6 |      INDEX RANGE SCAN| C2_PK |     4 |    12 |     1 |
--------------------------------------------------------------    

Predicate Information (identified by operation id):
---------------------------------------------------    

   1 - filter( (SELECT /*+ NO_UNNEST */ COUNT(*) FROM "CHECK_1" "C1"
              WHERE "C1"."ID1"=:B1) != (SELECT /*+ NO_UNNEST */ COUNT(*) FROM
              "CHECK_2" "C2" WHERE "C2"."ID1"=:B2))
   4 - access("C1"."ID1"=:B1)
   6 - access("C2"."ID1"=:B1)

Notice how line five now appears to be a child operation to line four. If you didn’t check the filter predicate section (which is unchanged) you might find this type of thing baffling.

If you switch back to the old-fashioned ‘connect by’ query against the plan_table, you’ll find that you get the right shaped plan, the (id,parent_id) links are correct, it’s just the depth that’s wrong.

This bug is (partly) fixed in 11g – this specific query displays the plan correctly, but I have found some plans with multiple scalar subqueries inside case statements in the select lists where the depth is again reported incorrectly.