Oracle Scratchpad

August 22, 2010

Cardinalilty One

Filed under: CBO,Infrastructure,Performance,Tuning — Jonathan Lewis @ 6:36 pm BST Aug 22,2010

I think anyone who has read Wolfgang Breitling’s material about the optimizer will be familiar with the concept of Cardinality Feedback and one particular detail that when Oracle gets a cardinality estimate of one for a “driving” table then there’s a good chance that the execution plan will go wrong. (That’s not rule, by the way, just a fairly common observation after things have gone wrong.)

A recent note on OTN reminded me of a particular scenario where this specific problem can occur. It’s not particularly common, but it may hit people who are building data warehouses from multiple different sources. We start with an unlikely looking data set and very simple query:

drop table t1;

create table t1 as
select
	rownum id1, 
	rownum id2
from
	all_objects
where 
	rownum <= 10000
;

execute dbms_stats.gather_table_stats(user,'t1');

set autotrace traceonly

select 
	count(*) 
from 
	t1 
where 
	id1 = id2
;

What do you think Oracle estimated cardinality will be for this predciate ? We know, because we saw the data being built, that we’re going to identify 10,000 rows. But the optimizer doesn’t see it that way – check line 2 of the execution plan. The optimizer thinks it will find just one row:

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |     7 |     8   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID1"="ID2")

Roughly speaking the problem is that Oracle doesn’t know how to compare the two columns, so it considers two options: “id1 = {unknown constant}” and “id2 = {unknown constant}”, and uses the arithmetic that gives the lower cardinality. But the selectivity of “column = {constant}”, in the absence of histograms, is 1/(number of distinct values). Since our columns are unique, the resulting cardinality is one (and in any case where one of the columns was “close to unique” the cardinality would be very small.

If you get caught in this trap here are a couple of straightforward solutions to consider.


select /*+ dynamic_sampling(t1 2) */ 
	count(*) 
from 
	t1 
where 
	id1 = id2
;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 10000 | 70000 |     8   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("ID1"="ID2")

Note
-----
   - dynamic sampling used for this statement

The first workaround is to add a hint to force Oracle to take a dynamic sample of the critical table. In this case I’ve instructed Oracle to use a 64 block sample. For small tables (and big queries) this is probably sufficient, and cheap enough, to be a good option. Note how Oracle has got the correct cardinality in line 2.

alter table t1 add n1 generated always as (id1 - id2) virtual;
execute dbms_stats.gather_table_stats(user,'t1');

select 
	count(*) 
from 
	t1 
where 
	id1 - id2 = 0
;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     9 |     8   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     9 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   | 10000 | 90000 |     8   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("T1"."N1"=0)

For users of 11g, this is a wonderful example of how virtual columns (or, in other cases, extended statistics) can help. Assuming id1 and id2 are numeric we know that id1 – id2 is zero if and only if id1 = id2 (we don’t have to worry about the effects of nulls in this case). So create a virtual column on id1 – id2 and query for the rows where the virtual column is zero.

Note, again, that the cardinality is correct in line 2. Note also that the predicate from my query “id1 – id2 = 0″ has been rewritten to substitute the virtual column “n1 = 0″.

The stats collection was necessary after creating the virtual column – but I was a little lazy in typing, I could have restricted it to method_opt => ‘for columns n1 size 1′.

I have to say that I would probably use this specific example only where the two columns were declared to be integers – there’s always a slight possibility that some odd rounding error in real number arithmetic could result in a zero when the two inputs weren’t identical.

This example is just one demonstration of why virtual columns (and extended statistics) are such a fantastic feature to have in the database.

9 Comments »

  1. Jonathan,

    Thanks for the blog and explaining it very nicely including the new virtual column feature in 11g.

    Thanks
    Aswath Rao

    Comment by Aswath Rao — August 23, 2010 @ 1:56 am BST Aug 23,2010 | Reply

  2. Jonathan,

    As always your blogs are fascinating readings and I can see the problem, but why would you create a table with 2 columns exactly the same? Is that like the proverbial Irishman wearing 2 condoms (“to be sure, to be sure”)? Or have I missed something?

    Comment by John Seaman — August 23, 2010 @ 3:50 am BST Aug 23,2010 | Reply

  3. John, I see it a bit when you have to store both a Postal address and a Residential address against the same individual. For people, 99% of the time they are the same value, but you have two columns just for that occasional case when they are not.

    Comment by Gary — August 23, 2010 @ 4:53 am BST Aug 23,2010 | Reply

  4. Nice post !!!

    But why dont we use FBI instead of Virtual Column? What are the benefits of Virtual Column against FBI when Disk space is not a concern?
    If possible please distinguish these features along with Extended Statistics.

    Comment by Yasser — August 23, 2010 @ 5:12 am BST Aug 23,2010 | Reply

  5. Yasser,

    In this example I was highlighting the reason why the optimizer would think the query was going to find a very small amount of data when we knew it was going to find a large amount of data. The virtual column gave us a clean and simple way of getting the right statistics to the optimizer, but in earlier versions of Oracle it would have been necessary to create a function-based index to create this information, so your question is valid.

    The answer is the standard answer for indexes. Taking a very simple-minded view: I used the virtual column to tell Oracle that it was going to collect a lot of data, which means I wouldn’t have wanted Oracle to use an index to get to that data. If I had created the virtual column to tell Oracle that it would find only a TINY amount of data I might have wanted Oracle to use an index to get to that data, so I might have created a function-based index.

    P.S. Here’s a note I wrote a few years ago about the fact that there’s no such thing as a function-based index. A “function-based index” is just an index on a virtual column – if all you want is the statistics, don’t pay the price of creating and maintaining the index. (N.B. You may say that space is irrelevant, but you generate undo and redo as you maintain the index, and the competition for the index blocks introduces latching costs, buffer busy waits, cache demands for CR blocks, and extra block I/Os.

    Comment by Jonathan Lewis — August 24, 2010 @ 7:25 am BST Aug 24,2010 | Reply

  6. But Jonathan, first two queries (w/o dynamic sampling and with it) show different plans only in autotrace output.
    If you turn on tracing plans in trace file will be excactly the same.

    Rows Row Source Operation
    ——- —————————————————
    1 SORT AGGREGATE (cr=21 pr=0 pw=0 time=3770 us)
    10000 TABLE ACCESS FULL T1 (cr=21 pr=0 pw=0 time=33 us)

    Comment by Eugene B — August 25, 2010 @ 7:02 pm BST Aug 25,2010 | Reply

  7. Eugene,

    All THREE plans are the same – they do a table scan, then sort aggregate. As does your traced execution plan.

    The difference is in the predicted number of rows. All THREE plans will return 10,000 rows because of the way I designed the data – but one of the predictions is hugely wrong.

    The error doesn’t matter in this case but (for example) in a query that joins two tables this error could lead the optimizer to choose this table as the driving table in a nested loop when a different plan would be more appropriate.

    Comment by Jonathan Lewis — August 25, 2010 @ 10:35 pm BST Aug 25,2010 | Reply

  8. Hi Jonathan, I know this area is not completely related to my question but I find it a better place to ask. I was going through your excellent book CBO- fundamentals ..
    On the page 47,48
    where month_no = 25 — outside high_value
    10g has shown the cardinality as ‘100’
    but 11G- the Optimizer calculated the cardinality as ‘1’ –
    Was it because of Dynamic sampling used by the CBO?

    Many thanks !!

    PLAN_TABLE_OUTPUT
    SQL_ID  45ub942t9vhz8, child number 0
    -------------------------------------
    select count(*) from audience where month_no=25
     
    Plan hash value: 3337892515
     
    -------------------------------------------------------------------------------
    | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |          |       |       |     3 (100)|          |
    |   1 |  SORT AGGREGATE    |          |     1 |    13 |            |          |
    |*  2 |   TABLE ACCESS FULL| AUDIENCE |     1 |    13 |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter("MONTH_NO"=25)
     
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    

    Comment by Bix — November 21, 2011 @ 3:58 pm BST Nov 21,2011 | 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,910 other followers