Oracle Scratchpad

January 10, 2008

Filter plan error

Filed under: dbms_xplan,Execution plans,Oracle — Jonathan Lewis @ 7:24 pm BST Jan 10,2008

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.

6 Comments »

  1. Thanks Jonathan, that’s something that could lead to no little degree of confusion indeed.

    Comment by SeánMacGC — January 11, 2008 @ 12:17 pm BST Jan 11,2008 | Reply

  2. [...] is two simultaneous defects in the execution plan information generated by Oracle. The first is one I’ve mentioned in the past which can appear when you have multiple subqueries – and seems to be particularly prevalent [...]

    Pingback by Odd Filter « Oracle Scratchpad — May 19, 2009 @ 6:40 pm BST May 19,2009 | Reply

  3. [...] haven’t had a need to adjust the scripts to fix the problem what Jonathan Lewis has described earlier, but it should be easily doable (just need to make sure that the connect by recursive loop is done [...]

    Pingback by Scripts for showing execution plans via plain SQL and also in Oracle 9i | Tanel Poder's blog: Core IT for Geeks and Pros — May 26, 2009 @ 8:42 pm BST May 26,2009 | Reply

  4. [...] you’ll notice an error in the last two lines of this 10g plan as well [now described in a note written in Jan 2008]). Comments [...]

    Pingback by Subquery Selectivity « Oracle Scratchpad — June 28, 2009 @ 4:45 pm BST Jun 28,2009 | Reply

  5. It’s a good example.thanks.

    Comment by leo — August 4, 2011 @ 8:19 am BST Aug 4,2011 | Reply

  6. [...] they do. This is an error in the calculated depth column of the plan table – an error which I first mentioned a few years ago with an example from 10g adding a throwaway comment that I had found a similar bug in 11g – [...]

    Pingback by dbms_xplan bug « Oracle Scratchpad — January 17, 2013 @ 6:24 pm BST Jan 17,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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,266 other followers