Wrong optimizer estimate ?

From: Michael Seiwert <Michael.Seiwert_at_gmx.de>
Date: Sun, 7 Nov 2010 09:53:49 +0100
Message-Id: <201011070953.49993.Michael.Seiwert_at_gmx.de>

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



Received on Sun Nov 07 2010 - 02:53:49 CST

Original text of this message