Wrong execution plan

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. 
--
http://www.freelists.org/webpage/oracle-l