Wrong execution plan

From: Joerg Jost <joerg.jost_at_unitrade.com>
Date: Wed, 10 Jun 2009 10:34:09 +0200
Message-Id: <1244622849.3848.16.camel_at_localhost>

Hi list,

the following SQL leads to an execution plan which is absolutely wrong.

SELECT pos_komnr
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:

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. 
Received on Wed Jun 10 2009 - 03:34:09 CDT

Original text of this message