Wrong execution plan
- From: Joerg Jost <joerg.jost@xxxxxxxxxxxx>
- To: Oracle Mailinglist <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 10 Jun 2009 10:34:09 +0200
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:
