In attempto to solve a performance problem encountered on Oracle 10.2.0.2 with CBO i’ve encountered another problem.
Let’s start from the beginning, we have an application developed and tuned on Oracle 9iR2 with RBO. We started with 9.2.0.1 about four years ago and there were not great problems with performance. Application is OLTP. RBO has a great advantage: stabilitity. On the other hand tuning an application with CBO is complex. One day our manager decided to make an installation of a two node RAC standard edition, with 10g (applications will have had 400 users). It was a 10gR1 we made the great jump in to the dark migrating to CBO. Things gone well (apart from installation). Then we made another jump in the dark with installation of 10gR2 and there started the problems. With my experience it seems that optimizer in 10gR2 is worse than optimizer in 10gR1.
The problem
Well, i’m not guilty, our application has a table (i will call it X) that can join alternatively with other three tables (i repeat, i’m not guilty, i think that not a good idea and a bad design, i will call such tables A, B and C). The table X as three columns ID_A that is foreign key (?) with table A, ID_B that is foreign key with table B and ID_C that is foreign key with table C. Our application is a product, customizable, so for a client happens that this table has milions of records that join only table C, foreign keys for tables A and B have value “-1”. So application make a query like this:
SELECT * FROM X
WHERE
ID_A = n1 or ID_B = n2 or ID_C = n3
Where n1>0, n2>0, n3>0 and in table X all values for ID_A and ID_B are “-1”. There are three indexes on columns ID_A, ID_B and ID_C. Using histograms optimizer knows that the thee values n1,n2,n3 (>0) are very selective so it uses the three indexes on 10gR1. Also on 10gR2 if we use litteral values. Our application is a java application that uses Bind Variables. So it happened that suddenly in 10gR2 optimizer decided to do a triple full scan on the table making database server hang on I/O (table has over 20 milion of records). I think that it is a problem with bind peeking, but i’m not been able to reproduce it, so we tried STORED OUTLINES to stabilize execution plan using indexes.
So i encountered the BUG 4323868, optimizer as required from hints of stored outlines used indexes but making index full scan instead of index range scan, making database server hang on I/O.
It is interesting to note what Oracle has made, i’ve not found patch for this bug, with patchset 10.2.0.3 Oracle has introduced “code improvement” adding two new hints: INDEX_RS_ASC and INDEX_RS_DESC