Oracle Scratchpad

December 3, 2008

Predicate Problems

Filed under: CBO,dbms_xplan,Execution plans,Troubleshooting — Jonathan Lewis @ 8:47 pm GMT 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.

23 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT 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 GMT Aug 6,2013 | Reply

  12. What is wrong with this query and its output:

    SELECT num
      FROM (SELECT 1 AS num, (SELECT COUNT (*) FROM DUAL) AS cnt FROM DUAL)
     WHERE num - cnt > 0;
    
           NUM
    ----------
             1
    1 row selected.
    

    and plan

    -----------------------------------------------------------------
    | Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------
    |   0 | SELECT STATEMENT |      |     1 |     4   (0)| 00:00:01 |
    |*  1 |  FILTER          |      |       |            |          |
    |   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
    |   3 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       1 - filter( EXISTS (SELECT 0 FROM "SYS"."DUAL" "DUAL"))
    

    Comment by Clint — August 8, 2014 @ 12:24 pm GMT Aug 8,2014 | Reply

    • Clint,

      The query looks valid (though a little unusual).
      The result looks wrong. (Which version were you using – I’ve only tested it on 12.1.0.2).

      The inner select is valid and returns “1,1”
      The full select returns no rows (and the plan changes significantly) if you select “num, cnt” rather than just “num”.

      Oracle has some cunning transformations relating to DUAL and existence, I suspect you’ve found an emergent case – you might like to raise a bug for it.

      Comment by Jonathan Lewis — August 15, 2014 @ 10:54 am GMT Aug 15,2014 | Reply

      • On Oracle 9i, 10g works fine. On 11g, 12c the select returns wrong result.

        If you test it without DUAL, example below:

        —–
        create table table1 (a number);
        create table table2 (a number);

        insert into table2 values(1);

        commit;

        select num
        from (select 1 AS num, (select count(*) from table1) as cnt from table2)
        where num – cnt > 0;
        —–

        same “problem” occurs.

        Comment by Andrej — August 28, 2014 @ 10:09 am GMT Aug 28,2014 | Reply

        • The bug is quite generic, then. It seems to be in the way the optimizer manages to do some view merging and predicate generation. Something to do with the “select count(*) from table1″ being copied down into the main predicate, and then an illegal transformation into an existence subquery. If you add a no_merge hint to the first in-line view you bypass the problem (in 12.1.0.2, at least):

          
          select /*+ no_merge(@inline1) */ num
          from (select /*+ qb_name(inline1) */ 1 AS num, (select count(*) from table1) as cnt from table2)
          where num - cnt > 0;
          
          

          Comment by Jonathan Lewis — August 28, 2014 @ 10:55 am GMT Aug 28,2014

  13. Hi Jonathan,

    I have tested this on the versions that i am able to access right now
    10.2.0.1.0 – 64bit on Linux

    JAGDEEP@css>SELECT num
      FROM (SELECT 1 AS num, (SELECT COUNT (*) FROM DUAL) AS cnt FROM DUAL)
     WHERE num - cnt > 0;  2    3
    
    no rows selected
    
    JAGDEEP@css>select * from table(dbms_xplan.display_cursor());
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  ahpvjrahruzdf, child number 0
    -------------------------------------
    SELECT num   FROM (SELECT 1 AS num, (SELECT COUNT (*) FROM DUAL) AS cnt
    FROM DUAL)  WHERE num - cnt > 0
    
    Plan hash value: 89114469
    
    -----------------------------------------------------------------
    | Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------
    |   0 | SELECT STATEMENT |      |       |     4 (100)|          |
    |*  1 |  FILTER          |      |       |            |          |
    |   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
    |   3 |   SORT AGGREGATE |      |     1 |            |          |
    |   4 |    FAST DUAL     |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(1->0)
    

    This is fine in this for me.

    This on 11.2.0.2.0 XE on windows XP

    SQL> SELECT num
      2    FROM (SELECT 1 AS num, (SELECT COUNT (*) FROM DUAL) AS cnt FROM DUAL)
      3   WHERE num - cnt > 0;
    
    no rows selected
    
    SQL> select * from table(dbms_xplan.display_cursor());
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------
    SQL_ID  ahpvjrahruzdf, child number 0
    -------------------------------------
    SELECT num   FROM (SELECT 1 AS num, (SELECT COUNT (*) FROM DUAL) AS cnt
    FROM DUAL)  WHERE num - cnt > 0
    
    Plan hash value: 89114469
    
    -----------------------------------------------------------------
    | Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------
    |   0 | SELECT STATEMENT |      |       |     4 (100)|          |
    |*  1 |  FILTER          |      |       |            |          |
    |   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
    |   3 |   SORT AGGREGATE |      |     1 |            |          |
    |   4 |    FAST DUAL     |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(1->0)
    

    Again same as to that of 10g

    Now on 12.1.0.1.0 EE 64 Bit Prod on Windows 7

    C##JAGDEEP@orcl12c1&gt;SELECT num
      2    FROM (SELECT 1 AS num, (SELECT COUNT (*) FROM DUAL) AS cnt FROM DUAL)
      3   WHERE num - cnt &gt; 0;
    
           NUM
    ----------
             1
    
    C##JAGDEEP@orcl12c1&gt;select * from table(dbms_xplan.display_cursor());
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------
    SQL_ID  ahpvjrahruzdf, child number 0
    -------------------------------------
    SELECT num   FROM (SELECT 1 AS num, (SELECT COUNT (*) FROM DUAL) AS cnt
    FROM DUAL)  WHERE num - cnt &gt; 0
    
    Plan hash value: 1685426719
    
    -----------------------------------------------------------------
    | Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
    -----------------------------------------------------------------
    |   0 | SELECT STATEMENT |      |       |     4 (100)|          |
    |*  1 |  FILTER          |      |       |            |          |
    |   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
    |   3 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter( IS NOT NULL)
    

    Here i can see bug result set but i am not having same predicate section as that of yours

    Can you through some light on this?

    Regards,

    Comment by jagdeepsangwan — August 29, 2014 @ 8:15 am GMT Aug 29,2014 | Reply

    • And here is the test on the basis of Andrej which seems to be quite contrary

      reate table table1 (a number);
      create table table2 (a number);
      
      insert into table2 values(1);
      
      commit;
      
      select num
      from (select 1 AS num, (select count(*) from table1) as cnt from table2)
      where num - cnt > 0;
      
      select * from table(dbms_xplan.display_cursor());
      
      select /*+ no_merge(@inline1) */ num
      from (select /*+ qb_name(inline1) */ 1 AS num, (select count(*) from table1) as cnt from table2)
      where num - cnt > 0;
      
      select * from table(dbms_xplan.display_cursor());
      
      drop table table1 purge;
      drop table table2 purge;
      

      The first one actually results in the correct result but the second one when hinted results with wrong one

      output

      Table created.
      
      
      Table created.
      
      
      1 row created.
      
      
      Commit complete.
      
      
      no rows selected
      
      
      PLAN_TABLE_OUTPUT
      -----------------------------------------------------------------------------
      SQL_ID  b632g8yy87fdt, child number 0
      -------------------------------------
      select num from (select 1 AS num, (select count(*) from table1) as cnt
      from table2) where num - cnt > 0
      
      Plan hash value: 2713259162
      
      ---------------------------------------------------------------------
      | Id  | Operation          | Name   | Rows  | Cost (%CPU)| Time     |
      ---------------------------------------------------------------------
      |   0 | SELECT STATEMENT   |        |       |     5 (100)|          |
      |*  1 |  FILTER            |        |       |            |          |
      |   2 |   TABLE ACCESS FULL| TABLE2 |     1 |     3   (0)| 00:00:01 |
      |   3 |   TABLE ACCESS FULL| TABLE1 |     1 |     2   (0)| 00:00:01 |
      ---------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter( IS NOT NULL)
      
      Note
      -----
         - dynamic statistics used: dynamic sampling (level=2)
      
      
      25 rows selected.
      
      
             NUM
      ----------
               1
      
      
      PLAN_TABLE_OUTPUT
      -----------------------------------------------------------------------------
      SQL_ID  f0m6qbw6h8943, child number 0
      -------------------------------------
      select /*+ no_merge(@inline1) */ num from (select /*+ qb_name(inline1)
      */ 1 AS num, (select count(*) from table1) as cnt from table2) where
      num - cnt > 0
      
      Plan hash value: 3895212545
      
      -----------------------------------------------------------------------------
      | Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
      -----------------------------------------------------------------------------
      |   0 | SELECT STATEMENT   |        |       |       |     5 (100)|          |
      |   1 |  SORT AGGREGATE    |        |     1 |       |            |          |
      |   2 |   TABLE ACCESS FULL| TABLE1 |     1 |       |     2   (0)| 00:00:01 |
      |*  3 |  VIEW              |        |     1 |    16 |     5   (0)| 00:00:01 |
      |   4 |   TABLE ACCESS FULL| TABLE2 |     1 |       |     3   (0)| 00:00:01 |
      -----------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         3 - filter("NUM"-"CNT">0)
      
      Note
      -----
         - dynamic statistics used: dynamic sampling (level=2)
      
      
      27 rows selected.
      
      
      Table dropped.
      
      
      Table dropped.
      

      Regards,

      Comment by jagdeepsangwan — August 29, 2014 @ 11:40 am GMT Aug 29,2014 | Reply

      • Jagdeepsagwan,

        Look carefully at the difference in data between the DUAL test and the t1/t2 test and think about which result in your t1/t2 test is giving the correct result and which is giving the wrong one.

        Comment by Jonathan Lewis — September 1, 2014 @ 10:29 am GMT Sep 1,2014 | Reply

    • Jagdeepsangwan,

      The reason you won’t see the same predicate is that the Andrej has used dbms_xplan.display() to report from a plan table and you’ve used dbms_xplan.display_cursor() which doesn’t report subquery predicates properly. Secondly, it’s a bug – which means it’s not necessarily predictable – and it depends on cost-based query transformation – which means there may be several reasons why your versions of Oracle may not pick the plan that exposes the bug.

      Comment by Jonathan Lewis — September 1, 2014 @ 10:28 am GMT Sep 1,2014 | Reply

  14. Jonathan,

    Thanks for response i will try to understand it more clearly.

    Regards,

    Comment by jagdeepsangwan — September 2, 2014 @ 12:40 pm GMT Sep 2,2014 | 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,429 other followers