Re: Wrong optimizer estimate ?

unfortunately you did not provide all informations which might be required. (as others have asked for some statistics etc.)

I'd suggest to use Albertos xplan [1] - this really collect more tan 99% of information you will ever need for such analyses. (except an 19953 trace - of course)

But also without these informations: the optimizer estimates it will get 1 row out of the join (and filter) of TIMP and PT1. (this seems to be true in the current state) Then it estimates there will be approximately 12 TITELSUCHE_ID in PT2 - this is quite easy: as you have 22878255 rows in PT1 and 1992312 TITELSUCHE_ID => 22878255/1992312 = 11.4 Well, but you have 198 equal TITELSUCHE_ID there.

It's quite hard for the optimizer to know at the optimization-time what the value of pt1.TITELSUCHE_ID will be. But this would be necessary to do a better calculation.

I hope this helps a little bit and is not too wrong. I'm by far no optimizer-pro.  Martin

[1] http://www.adellera.it/scripts_etcetera/xplan/index.html

Am 07.11.2010 um 09:53 schrieb Michael Seiwert:

> 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). Now I ask you kindly
> to help me finding out if this is a bug in the optimizers estimation or is
> there another reason (maybe strange data) which could explain the difference.
>
> Fortunately there is no performance problem with the query yet, but I've
> learned that even small differences in data combined with small
> miscalculations may have a huge impact on the query perfomance in the future.
>
> For better readability I attached the tests I did so far as an text file (hope
> this is ok ?). If there are any numbers, statistics ... helping to debug this
> further, please let me know and I'll post them to the list.
>
> In the attached file you'll find the query in question along with the
> execution plan gathered with the gather_plan_statistics hint. There are two
> executions plans with two different sets of optimizer statistics. (first test
> without histograms, the second test with histograms). Also attached the column
> stats for the main table involved. The table t_sendung_import1 has only 1 row.
>
> Thank you very much in advance for helping my deepen my oracle knowledge.
>
> Very best regards
>
> Michael
>
>
>
>
>
>
>
>
> <wrong_estimate.txt><column_stats_produkttitelinstanzen.html>

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