Re: Why CBO choose the wrong plan?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 6 Jul 2007 07:20:00 +0100
Message-ID: <nKSdnfClu-ebQhDbnZ2dnUVZ8rOdnZ2d@bt.com>

By a strange coincidence, I was on a site yesterday that was seeing a very similar problem -

    10.2,
    partitioned tables
    histograms

A query was doing a full tablescan (of 4 partitions) to find 38 rows which could have been found much more efficiently using the primary key - but the tablescan cost was about 600 and the index cost was about 9M.

I may have a chance to investigate it further today - but time-pressure may simply require them to drop the histograms and forget the problem.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

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


"wblxx" <wangbinlxx_at_gmail.com> wrote in message 
news:1183355411.752590.179540_at_j4g2000prf.googlegroups.com...


> Hi Jonathan,

>

> The original query is

> select sum(TBL_FIN_ACC_STAT_SUMM.AMOUNT)

> from TBL_ACC_STAT_SUMM TBL_FIN_ACC_STAT_SUMM

> where ACCOUNT_ID = 156800 and

> TBL_FIN_ACC_STAT_SUMM.SETTLED_DT BETWEEN

> to_date('20070601','yyyymmdd')  AND to_date('20070602','yyyymmdd');

>

> I guess this is an example, as you point out in your book, Oracle uses

> partition-level statistics with a single-known partition, while uses

> table-level statistics with a multiple-known partitions. I cannot tell

> what's wrong with table statistics yet.

>

> Thanks,

> Bin

>

>

Received on Fri Jul 06 2007 - 01:20:00 CDT