Oracle Scratchpad

February 22, 2010

CBO Surprise

Filed under: CBO,Execution plans,Troubleshooting — Jonathan Lewis @ 7:35 pm BST Feb 22,2010

Someone sent me an email recently with the following observation:

    “I’ve got a query involving a range scan on a date column. When I change the query to split the range into two consecutive ranges with an ‘OR’ the execution plan changes.”

Despite the title, this isn’t really a surprise, even though it might seem a little unexpected. The CBO assumptions for disjuncts (predicates with OR) means that the selectivity is almost guaranteed to change when you do this, and a change in selectivity could easily lead to a change in the execution plan. Here are the results from a model I created to reproduce, as closely as I could, the behaviour displayed by example plans that I was sent:

First the query and execution plan with a single range:

select
	small_vc
from	t1
where
	bit_col = 5
and	t1.date_col between to_date('01-feb-2010','dd-mon-yyyy')
	            and     to_date('25-feb-2010','dd-mon-yyyy')
;

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1312 | 28864 |   435 |
|*  1 |  TABLE ACCESS FULL| T1   |  1312 | 28864 |   435 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("BIT_COL"=5 AND "T1"."DATE_COL">=TO_DATE('2010-02-01
              00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
              "T1"."DATE_COL"<=TO_DATE('2010-02-25 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss'))

Then the query and execution plan after splitting the single range into two non-overlapping ranges


select
	small_vc
from	t1
where
	bit_col = 5
and	(
	t1.date_col between to_date('01-feb-2010','dd-mon-yyyy')
	            and     to_date('12-feb-2010','dd-mon-yyyy')
	or
	t1.date_col between to_date('13-feb-2010','dd-mon-yyyy')
	            and     to_date('25-feb-2010','dd-mon-yyyy')
	)
;

-------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes |TempSpc| Cost  |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  1269 | 27918 |       |   434 |
|   1 |  TABLE ACCESS BY INDEX ROWID      | T1      |  1269 | 27918 |       |   434 |
|   2 |   BITMAP CONVERSION TO ROWIDS     |         |       |       |       |       |
|   3 |    BITMAP AND                     |         |       |       |       |       |
|   4 |     BITMAP CONVERSION FROM ROWIDS |         |       |       |       |       |
|*  5 |      INDEX RANGE SCAN             | T1_BIT  |       |       |       |    10 |
|   6 |     BITMAP OR                     |         |       |       |       |       |
|   7 |      BITMAP CONVERSION FROM ROWIDS|         |       |       |       |       |
|   8 |       SORT ORDER BY               |         |       |       |   424K|       |
|*  9 |        INDEX RANGE SCAN           | T1_DATE |       |       |       |    36 |
|  10 |      BITMAP CONVERSION FROM ROWIDS|         |       |       |       |       |
|  11 |       SORT ORDER BY               |         |       |       |   456K|       |
|* 12 |        INDEX RANGE SCAN           | T1_DATE |       |       |       |    39 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("BIT_COL"=5)
   9 - access("T1"."DATE_COL">=TO_DATE('2010-02-01 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "T1"."DATE_COL"<=TO_DATE('2010-02-12 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss'))
  12 - access("T1"."DATE_COL">=TO_DATE('2010-02-13 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss') AND "T1"."DATE_COL"<=TO_DATE('2010-02-25 00:00:00', 'yyyy-mm-dd
              hh24:mi:ss'))

The first thing to note about these queries is that they are not logically equivalent even though they may have produced the same result. It’s possible that the end user knows that the data in column date_col is always date-only (which is the constraint which would ensure the two queries return identical results) but the database (hence the optimizer) doesn’t know this. If you want to make these two queries equivalent you’ll have to add the check constraint check (date_col = trunc(date_col) to the table. (I’ll do that in a follow-up article, and show you what happens).

So the first thing we have to do is correct the query – which means changing the date_col predicate(s) to:

and	(
		(
		    t1.date_col >= to_date('01-feb-2010','dd-mon-yyyy')
		and t1.date_col <  to_date('13-feb-2010','dd-mon-yyyy')
		)
	or
		(
		     t1.date_col >= to_date('13-feb-2010','dd-mon-yyyy')
		 and t1.date_col <= to_date('25-feb-2010','dd-mon-yyyy')
		)
	)

I’ve converted both between clauses – the first because it needs correction so that the range goes up to, but excludes, the start of the second range; the second just for consistency in style. In passing, though, if you are using range-partitioned tables then you should get into the habit of using this style because the content of a partition is “greater than or equal to” its lower bound and “strictly less than” its upper bound – and there are cases where stating your range predicates with strict accuracy can make a worthwhile difference to the performance. (That’s also a topic for a follow-up article.) In this case, correcting the predicate didn’t make any difference to the execution plan or the cardianlity estimates.

Se we can make the two queries equivalent, but even so the optimizer thinks the split range will return less data and chooses a different execution plan.

If you’ve got Cost Based Oracle – Fundamentals you will know why. The selectivity of “P1 or P2″ is given by the formula “selectivity (P1) + selectivity(P2) – selectivity(P1 and P2)”. We have a problem with dependent predicates. We can look at the two date ranges and recognise that they don’t overlap, and therefore the “selectivity(P1 and P2)” is zero – but the optimizer is treating the two predicates as independent and trying to allow for the data that appears in both date ranges. Different selectivities lead to different cardinalities and (sometimes) different execution plans.

There is another factor in this example that introduces another variation in the selectivities – but I’m going to ignore that at present: suffice to say that if you change the number of distinct date values in my test case then the way that the optimizer handles ranges means that the first query might end up with a lower cardinality than the second – so the two execution plans could, in principle, switch over.

The test case is fairly sensitve to change, so you may find it hard to reproduce my results. The code to generate the table and indexes is below, but you also need the following setup details:

    Version 10.2.0.3
    Brand New locally managed tablespace
    Freelist management, not ASSM
    8KB block size
    db_file_multiblock_read_count not set
    System statistics disabled
    no silly settings for other optimizer parameters
create table t1
as
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		rownum <= 10000
)
select
	mod(rownum,20)				bit_col,
	to_date('01-Jan-2010','dd-mon-yyyy') +
		trunc(dbms_random.value(0,100))	date_col,
	lpad(rownum,10)				small_vc,
	rpad('x',175)				padding
from
	generator	v1,
	generator	v2
where
	rownum <= 100000
;

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

create index t1_date on t1(date_col);
create index t1_bit on t1(bit_col);

Note – despite the name of the column, the index on bit_col is a B-tree index; and I create the indexes after collecting stats knowing that the index statistics will be computed as the indexes are created. If you want to try this on 9i then you could add an explicit “compute statistics” to the “create index” commands.

10 Comments »

  1. Nice.

    I wonder what happens if you set DYNAMIC_SAMPLING to above the default value of 2 for V10.2? I’d predict the CBO would check the guesses about correlation it made and revert back to the full tabe scan…
    If I get chance, I’ll try to replicate your test.

    Comment by mwidlake — February 22, 2010 @ 8:01 pm BST Feb 22,2010 | Reply

    • Martin,

      I just did a quick re-run with optimizer_dynamic_sampling set to 4, and it didn’t change the plans. (sampling took place in both cases, and – with the 10g seed() call in the sample size – the estimated cardinality was the same for both plans at 1,238 rows. That’s not surprising given the actual data – it favours Oracle getting a reasonable estimate using its standard arithmetic.

      I was just a little surprised that this drop didn’t make the full tablescan plan change to a bitmap conversion, but I still don’t have a complete picture of the optimizer’s costing of bitmaps and bitmap conversions.

      Update: I’ve just realised that another effect on costing is that the nature of the predicate forces Oracle to consider taking two bites (not bytes) at the index, so we’re also seeing an anomaly similar to the “OR” adjustment where the cost of scanning two adjacent sections of an index is not the same as the cost of scanning the entire range in one bite.

      News for today – I’ve decided to switch on the “Nero” ratings on the blog to see if this tells me anything about how useful my notes are. I may switch them off if they seem to be slowing down the speed that pages load.

      Comment by Jonathan Lewis — February 23, 2010 @ 10:11 am BST Feb 23,2010 | Reply

  2. Insightful.

    Comment by Ung Kok Aik — February 23, 2010 @ 9:31 am BST Feb 23,2010 | Reply

  3. >> If you want to make these two queries equivalent you’ll have to add the check constraint check (date_col = trunc(date_col). (We’ll do that later, and see what happens).

    I think that the problem of check constraints causing incorrect estimated cardinalities wasn’t fixed until 10.2.0.4, as I recall. Another CBO surprise!

    Comment by David Aldridge — February 23, 2010 @ 10:17 am BST Feb 23,2010 | Reply

  4. Hi Jonathan,
    nice blog post.

    Just one short question to the following “assumption”:
    > It’s possible that the end user knows that the data in column date_col is always date-only (which is the constraint which would ensure the two queries return identical results) but the database (hence the optimizer) doesn’t know this

    I guess, that the column date_col is of colum n type DATE.
    So why shouldn’t the optimizer know that the result of the “OR results” always will be date? You only can store “date”-data in this column, or?

    Thanks and Regards
    Stefan

    Comment by Stefan — March 19, 2010 @ 9:33 am BST Mar 19,2010 | Reply

    • Stefan,

      The column is of type DATE, but you’re overlooking the fact that a DATE column in Oracle is implicitly date-and-time with a granularity of one second.

      Comment by Jonathan Lewis — March 20, 2010 @ 1:45 pm BST Mar 20,2010 | Reply

      • Hello Jonathan,
        thanks for your response.

        Maybe i am overlooking something very common or my first post was not clear enough.

        You said:
        > The first thing to note about these queries is that they are not logically equivalent even
        though they may have produced the same result

        If i check the access predicates between the following code:
        ——————-8<——————–
        (
        t1.date_col
        between to_date('01-feb-2010','dd-mon-yyyy') and to_date('12-feb-2010','dd-mon-yyyy')
        or
        t1.date_col
        between to_date('13-feb-2010','dd-mon-yyyy')
        and to_date('25-feb-2010','dd-mon-yyyy')
        )
        ——————-8<——————–

        and the original predicates
        ——————-8<——————–
        t1.date_col
        between to_date('01-feb-2010','dd-mon-yyyy')
        and to_date('25-feb-2010','dd-mon-yyyy')
        ——————-8<——————–

        Why are they logical different?

        If i check the acess predicates between the original statement and the OR concetanation, the access predicates on DATE_COL are the logical the same, or?

        Thanks and Regards
        Stefan

        Comment by Stefan — March 20, 2010 @ 4:05 pm BST Mar 20,2010 | Reply

        • Stefan,

          Because date columns can include time components, one of the versions loses the data that starts after (day only) 12th Feb and ends before (day only) 13th Feb – i.e. everything from “12-Feb-2010 00:00:01″ to “12-Feb-2010 23:59:59″.

          Physically there may be no such data (because of the application code, perhaps), but logically the database has no information that tells it that such rows cannot exist.

          Comment by Jonathan Lewis — March 20, 2010 @ 8:14 pm BST Mar 20,2010

  5. [...] Filed under: CBO,Execution plans,Partitioning — Jonathan Lewis @ 7:45 pm UTC Feb 24,2010 In an earlier article I gave a description of how splitting a single date ranges into a pair of date ranges with an OR [...]

    Pingback by CBO Surprise 2 « Oracle Scratchpad — June 29, 2010 @ 9:35 am BST Jun 29,2010 | 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,985 other followers