Re: Explain Plan and Cardinality

<schonlinner_at_yahoo.com> wrote in message news:1126768337.887172.267330_at_g43g2000cwa.googlegroups.com...
> Hi,
>
> Database: Oracle 10g (10.1.0.4) 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
then

    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.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005