Re: Wrong optimizer estimate ?

From: Thomas Roach <troach_at_gmail.com>
Date: Mon, 8 Nov 2010 18:52:12 -0500
Message-ID: <AANLkTi=PAG93MPdb_=Y0M4JUw1fdcsKFV69BCPFh1qka_at_mail.gmail.com>

To add. I would also see if there are histograms on some of these columns such as

"PT1"."PRODUKTTITELART_ID"=1 AND
              "PT1"."OBJEKTBEREICH_ID"=0 AND "PT1"."REIHENFOLGE"=0 You may also want to look into Extended Statistics (depending on the version you are on).

Here is a plug for Tim Hall's site.

http://www.oracle-base.com/articles/11g/StatisticsCollectionEnhancements_11gR1.php#extended_statistics

Multi-Column Statistics Individual column statistics are fine for working out the selectivity of a specific column in a where clause, but when the where clause includes multiple columns from the same table, the individual column statistics provide no indication of the relationship between the columns. This makes working out the selectivity of the column group very difficult.

On Mon, Nov 8, 2010 at 5:51 PM, Jared Still <jkstill_at_gmail.com> wrote:

> On Sun, Nov 7, 2010 at 1:53 AM, Michael Seiwert <Michael.Seiwert_at_gmx.de>wrote:
>
>> Hi all,
>>
>> while doing performance tests of some new queries in our application I
>> noticed
>> one query where the estimated rows differ much from the actual rows in the
>> execution plan (Line 7 in the attached execution plan).
>
>
> For a start, you may want to investigate DBA_TABLES and DBA_INDEXES
> to determine when statistics were gathered.
>
> If the data has grown, the actual # of rows could easily be greater than
> what is stored in statistics.
>
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Oracle Blog: http://jkstill.blogspot.com
> Home Page: http://jaredstill.com
>

-- 
Thomas Roach
813-404-6066
troach_at_gmail.com

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Nov 08 2010 - 17:52:12 CST