Re: Wrong execution plan
- From: Frits Hoogland <frits.hoogland@xxxxxxxxx>
- To: joerg.jost@xxxxxxxxxxxx
- Date: Wed, 10 Jun 2009 16:42:05 +0200
Try to restore the 'wrong' situation
and generate a CBO trace using event 10053.
Although cryptic, that trace tells you how the CBO comes to the
decision for choosing the bad plan.
The reason for being wrong also tells you what you need to do to make it
right.
More information on the 10053 trace:
http://www.centrexcc.com/A%20Look%20under%20the%20Hood%20of%20CBO%20-%20the%2010053%20Event.ppt.pdf
frits
On Wed, Jun 10, 2009 at 10:34 AM, Joerg Jost <joerg.jost@xxxxxxxxxxxx>wrote:
> Hi list,
>
> the following SQL leads to an execution plan which is absolutely wrong.
>
> SELECT pos_komnr
> FROM po
> WHERE pos_art = l_tmp_art1
> AND pos_num = l_tmp_num1
> AND pos_pos = l_tmp_pos;
>
> (it is from a pl/sql procedure)
>
> The table PO is quite big (40.000.000 records) so this wrong execution
> plan leads to really bad performance over the whole database because of
> excessive I/O.
>
> The primary key of this table is a combination of the three fields
> pos_art, pos_num and pos_pos!
>
> The optimizer choose an index on the fields pos_art, pos_status.
>
> POS_STATUS is a field with only 6 different values of one character, so
> really bad for this where clause.
>
> Normally i think in this cases, ok, bad bind peeking. But this table
> don't have histograms on the fields pos_art, pos_num, pos_pos and
> pos_status. I removed them because of other problems with bind peeking.
>
> I resolved the actual situation with removing the plan from the shared
> pool. After this the optimizer generated a plan with the primary key.
>
> What else but histograms can lead the optimizer to such bad execution
> plans?
>
> We are going to install a new version of the Package with a hint leading
> to the primary key. But i am not really satisfied with this resolution
> because of about 80 other systems running the same software without
> problems like this.
>
> Here some Informations about the system:
>
> Oracle 10.2.0.1
> AIX 5.4
> Database is about 120 Gig
> about 750 Users
>
>
> Thanks in advance
>
> Jörg Jost
>
> --
> You can have it: Fast, Right or Cheap, pick 2 of the 3.
> Fast + Right is Expensive
> Fast + Cheap will be incorrect.
> Right + Cheap will take a while.
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
