Re: Wrong optimizer estimate ?
- From: Thomas Roach <troach@xxxxxxxxx>
- To: jkstill@xxxxxxxxx
- Date: Mon, 8 Nov 2010 18:52:12 -0500
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@xxxxxxxxx> wrote:
> On Sun, Nov 7, 2010 at 1:53 AM, Michael Seiwert <Michael.Seiwert@xxxxxx>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@xxxxxxxxx
Other related posts:
