Oracle Scratchpad

December 3, 2008

Predicate Problems

Filed under: CBO,dbms_xplan,Execution plans,Troubleshooting — Jonathan Lewis @ 8:47 pm BST Dec 3,2008

Whenever you look at an execution plan, you must look at the predicate section (introduced in 9i) before you start trying to work out what’s gone wrong. Here’s an example (stripped down to the basic issue from a more complex query) that shows the importance of this check.

Oracle always tries to transform your query before optimizing it. One of the transformation steps involves rewriting your predicates. This step is allowed to introduce new predicates from your table constraints, and may also rearrange your predicates through transitive closure.

So here’s a little bug from 10.2.0.1 and 9.2.0.6, fixed in 10.2.0.3 and 9.2.0.8, where Oracle turns constraints into predicates then invokes transitive closure – and introduces redundant work into the execution path as a consequence.

create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from	all_objects
		where rownum <= 3000
)
select
	rownum			id,
	mod(rownum,10) 		n1,
	mod(rownum,10)		n2,
	2 * mod(rownum,10)	n3,
	lpad(rownum,10,'0')	vc1,
	rpad('x',100) 		padding
from
	generator v1,
	generator v2
where
	rownum <= 10000
;

alter table t1 add constraint t1_pk primary key(id);

alter table t1 modify n1 not null;
alter table t1 modify n2 not null;
alter table t1 modify n3 not null;

alter table t1 add constraint t1_ck_n3 check (n3 >= 0);
alter table t1 add constraint t1_sum check (n1 + n2 = n3);

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		estimate_percent => 100,
		block_sample	 => true,
		method_opt	 => 'for all columns size 1'
	);
end;
/

The table, stripped to a minimum, represents a product table with the requirement embedded in the database that: “quantity_on_hand + quantity _reserved = quantity _total”.

explain plan for
select
	count(*)
from
	t1
where
	id < 100
;

select * from table(dbms_xplan.display);

The query isn’t a realistic one, but it demonstrates an important point.
If you look at the query, and check the code that created the table you will see that the optimizer should be able to execute this query without visiting the table. You might also expect the optimizer to work out correctly that the number of rows returned would be about 100. So why does the plan look like this (in 9.2.0.6 and 10.2.0.1) ?

------------------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Bytes | Cost  |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |     1 |    10 |     4 |
|   1 |  SORT AGGREGATE                |       |     1 |    10 |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID  | T1    |     5 |    50 |     4 |
|*  3 |    INDEX RANGE SCAN            | T1_PK |    99 |       |     2 |
------------------------------------------------------------------------

Line 2 says we’re going to visit the table, and the number of rows returned is going to be five !
When you check the predicates, you can see what’s gone wrong. (Notice that lines 2 and 3 both have asterisks to show that there are related predicates in the predicate section of the output).

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("N1"+"N2">=0)
   3 - access("ID"<100)

We have to go to the table to resolve the predicate “n1 + n2 >= 0”, and the predicted cardinality has dropped from 99 to 5 because that predicate (since it involves a “non-trivial function” applied to the columns) has been given the fixed “unknown range” selectivity of 5%. (In this case, the virtual column technology introduced in Oracle 11g would have helped correct the cardinality error.)
But where did the spurious predicate come from ? The optimizer has pulled the two constraints from the table – and then used transitive closure to generate an equivalent, but irrelevant, predicate.

if   n1 + n2 = n3
and  n3 >= 0
then n1 + n2 >= 0

Sometimes the optimizer can be too clever for its own good.
I don’t know if this ever appeared as a published bug – but the execution plans for 9.2.0.8 and 10.2.0.3 show the expected index-only access.

Always check the predicate section of the execution plan.

14 Comments »

  1. Jonathan,

    what is the platform in which you tested the example? I tested it in 10.2.0.1 on Windows XP and I get the right result:

    SQL> explain plan for
     2  select
     3     count(*)
     4  from
     5     t1
     6  where
     7     id < 100
     8  ;

    Explained

    SQL> select * from table(dbms_xplan.display);

    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------

    Plan hash value: 3557810422

    ---------------------------------------------------------------------------
    | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |     1 |     4 |     2   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE   |       |     1 |     4 |            |          |
    |*  2 |   INDEX RANGE SCAN| T1_PK |    99 |   396 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------
      2 - access("ID"<100)

    14 filas seleccionadas.

    Greetings.

    Comment by Ricardo — December 3, 2008 @ 10:59 pm BST Dec 3,2008 | Reply

  2. Ricardo,

    This was on XP Pro – but the platform isn’t like to make any difference to something like this. I won’t be able to get my hands on the specific machine for a couple of days, but I’ll repeat the test – and then we may have to cross-check startup parameters.

    I’ve edited your first post – you lost some bit because of a “less than” symbol in the SQL. I think I’ve managed to fill in the missing bits correctly.

    Comment by Jonathan Lewis — December 4, 2008 @ 12:11 am BST Dec 4,2008 | Reply

  3. I remember this issue well from a time when the presence of simple sanity-based check constraints (“The date is between 1990 and 2050″) or data validation constraints (“All the characters are digits”) could cause a gross underestimate of the expected cardinality from a dimension table. Hit enough of those and you could whistle for a full table scan — lovely buffer cache hit ratio, though :D

    Comment by David Aldridge — December 4, 2008 @ 2:02 am BST Dec 4,2008 | Reply

  4. Ricardo,
    I’ve just re-run this on 10.2.0.1, cutting and pasting from the blog. It behaves the way I said.

    Can you check that your version really is 10.2.0.l – and if it has any patches, or any of the less common (optimizer) parameters set.

    Comment by Jonathan Lewis — December 5, 2008 @ 10:11 am BST Dec 5,2008 | Reply

  5. Jonathan, Richardo.

    I have tested this 9.2.0.7 and 10.2.0.3. It behaves exactly as Jonathan wrote on blog.

    9.2.0.7

    ----------------------------------------------------------------------------
    | Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |             |     1 |    10 |    10 |
    |   1 |  SORT AGGREGATE              |             |     1 |    10 |       |
    |*  2 |   TABLE ACCESS BY INDEX ROWID| T1          |     5 |    50 |    10 |
    |*  3 |    INDEX RANGE SCAN          | T1_PK       |    99 |       |     2 |
    ----------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("T1"."N1"+"T1"."N2">=0)
       3 - access("T1"."ID"<100)
    

    10.2.0.3

    ---------------------------------------------------------------------------
    | Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |       |     1 |     4 |     2   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE   |       |     1 |     4 |            |          |
    |*  2 |   INDEX RANGE SCAN| T1_PK |    99 |   396 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("ID"<100)
    

    Comment by Dilip Patel — December 19, 2008 @ 1:00 pm BST Dec 19,2008 | Reply

  6. Hi All,

    For me…it worked too in 10.2.0.1 Windows Machine.

    Regards
    Vivek

    Comment by Vivek — December 24, 2008 @ 1:05 pm BST Dec 24,2008 | Reply

  7. [...] lost count of the number of times I’ve told people that the predicate section of an execution plan is vital to understanding what’s really going on, and it’s usually the predicate section [...]

    Pingback by Old plan_table « Oracle Scratchpad — January 25, 2010 @ 8:29 am BST Jan 25,2010 | Reply

  8. Sorry for late comment, we are hitting issues with this in 9.2.0.6, please can you confirm if any workarounds are known.

    Comment by Padders — July 9, 2010 @ 9:37 am BST Jul 9,2010 | Reply

    • Padders,
      No workaround that I know of. (Other than upgrading).

      You might try adjusting the query_rewrite_enabled parameter – but that’s just a shot in the dark because it has had some unexpected side-effects in the past that have nothing (apparently) to do with query rewrite.

      Comment by Jonathan Lewis — July 9, 2010 @ 1:34 pm BST Jul 9,2010 | Reply

      • Just a note to say event 10195 “CBO don’t use check constraints for transitive predicates” seems helpful in scratch tests, pending buy-in from our DBAs.

        Comment by Padders — July 12, 2010 @ 2:37 pm BST Jul 12,2010 | Reply

        • Padders,

          Thanks for that note. I can’t think why I didn’t check the oraus.msg file myself – it’s always a good place to look if you can’t find anything suggestive in the full parameter list.

          Comment by Jonathan Lewis — July 12, 2010 @ 8:25 pm BST Jul 12,2010

  9. [...] Lewis @ 7:01 pm UTC Aug 31,2010 Here’s an example to remind you how important it is to look at the “Predicate Information” supplied with an execution plan. Here are two execution plans that look very similar in shape [...]

    Pingback by Filter “Bug” « Oracle Scratchpad — August 31, 2010 @ 7:02 pm BST Aug 31,2010 | Reply

  10. Just a heads up on a problem one might encounter while trying to get plan with predicates: if you have “_cursor_plan_unparse_enable” parameter set to false (for ex., Oracle Support asked you to do this to workaround a bug), you may not see predicates section. This was mentioned in this thread on SQL.ru.

    Comment by Timur Akhmadeev — November 12, 2010 @ 8:04 pm BST Nov 12,2010 | Reply

  11. […] estime les cardinalités avec des fonctions cela peut poser des problèmes. Jonathan Lewis donne un exemple intéressant sur son blog. De ce fait, en fonction des versions et des cas d’utilisation, Oracle tire partie ou pas de […]

    Pingback by Transitivité des contraintes CHECK ou quand les tables stockées dans SYS n’obéissent pas aux mêmes règles ! | ArKZoYd — August 6, 2013 @ 5:10 pm BST Aug 6,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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,530 other followers