Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Explain Plan and Cardinality

Re: Explain Plan and Cardinality

From: Jonathan Lewis <>
Date: Thu, 15 Sep 2005 07:36:01 +0000 (UTC)
Message-ID: <dgb891$cgl$>

<> wrote in message
> Hi,
> Database: Oracle 10g ( on HP/UX PA Risc 64 bit.
> When doing an explain plan on a sql select statement I can see the
> cardinality in different steps of the plan.
>>From the Oracle documentation I've read that the cardinality is how
> many rows Oracle expects from this step. So if there is something like
> "TABLE ACCESS BY LOCAL INDEX ROWID" with cardinality 25 displayed by
> explain plan, then I thought that Oracle thinks that it retrieves round
> about 25 rows from this step.
> Now sometimes the cardinality is totally wrong: I know that a specific
> step returns 200000 rows, but the cardinality in Oracle displays only
> 2-5 rows.
> I thought it may be the statistics which is wrong. But the tables have
> been analyzed (by the default analyze job which is automatically
> configured in 10g on our system).
> Is there anything which I can do to give Oracle more information so it
> guesses the cardinality better? Why is it "wrong"?
> Regards,
> Alex

There are many reasons why it might be wrong, and until you know what's wrong, you can't decide what strategy to adopt to fix it.

For example: using 10g and its 'automatic' choice of stats collection, I have an example where 10g built a histogram that wasn't needed, hence derived the wrong 'density' for a column, and then returned a cardinality of 1 for a class of queries which returned 10,000 rows (and in the absence of the redundant histogram, the optimizer would have worked that out).

Example 2: If you have range-scans on date ranges in a column where you have created a special value that is extreme compared to the range - the optimizer will produce the wrong cardinality if the stats collection has FAILED to produce a suitable histogram - and the 10g automatic stats collection can fail to spot that trap. (Typical design error unfortunately:

    "we don't use nulls, we use 01-Dec-9999     to show missing dates"

A fairly common problem is the dependent columns trap: Oracle says:

    If there are 100 distinct values in column A     and 100 distinct values in column B

    where A = 'xxx'
    and B = 'xxx'
is going to return one row in every 10,000. i.e. one in (100 * 100). But you may know that there is some relationship between A and B that means the typical return is 1 row in 250.

One option for addressing the last problem is to use the dynamic_sampling hint when you know (a) the query is quite expensive anyway, (b) the optimizer is likely to get the cardinality wrong.

Dynamic sampling takes a 32 block sample from (some of) the tables in the query - hence the comment about expensive queries, you don't want the solution to be more expensive than the problem.


Jonathan Lewis
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005
The Co-operative Oracle Users' FAQ
Public Appearances - schedule updated 4th Sept 2005
Received on Thu Sep 15 2005 - 02:36:01 CDT

Original text of this message