CBO issues

Hello,

This another proof that lower cost doesn't mean fastest execution plan.
I had to tune one of Oracle Discoverer report which was based on view defined in database.
My first approach was to add some indexes because there was none. According
to good practices I have been added a primary key and indexes on FK and join columns.

The looks more less like that

select
distinct
s.col1
s.col2
P.col1
P.col2
L.col1
C.col1
H.col1
G.col1
from
tab1 s,
tab2 c,
tab3 P,
tab4 H,
tab5 G,
tab6 B,
tab7 I,
tab8 L
where 1=1
AND G.col1 = 'Y'
AND G.col2 = P.col2
AND G.col2 = H.col2(+)
...
and b.col3 = (select min(b2.col3)
from tab6 b2
where b2.col1 = b.col1
and b2.col2 = b.col2
and b2.col3 = b.col3
and b2.col4 >= s.col4)
and c.col3 = (select max(f.col3)
from tab8 L2
where l2.col1 = L.col1
and l2.col2 = L.col2
and l2.col3 <= L.col3);

So there was two subqueries correlated with main query.
If there were indexes on tab6.col1, tab6.col2 and tab6.col3 for second subquery
and indexes on b1.col1, b1.col2 and b1.col3 for first subquery.
CBO decide that to use bitmap index conversion and bitmap join (using bitmap conversion to rowids and bitmap conversion from rowids) for that part which was cheaper then normal join (unfortunately for CBO only)




There is a original part of execution plan:



Whole cost of query was 8633.
Execution plan was about 3 do 3.5 minutes.

I have had a problem with that conversion before so I decide to disable it using
hidden parameter _b_tree_bitmap_plans. By default it is set to true and
it allow index conversion. I had changed it to false and execution plan had been changed to:


Whole cost had been changed to 9145, but execution time has been reduced to 30 secs.

Parameter change is a very significant change to whole system and I would like to avoid that.
I had performed more investigation using Oracle event 10053 to find out why it is a case.
All conversions had been used a indexes join so I have decided to drop one index from table tab6 called tab_col2_idx. That was it.
Query was executed faster and hidden parameter have been unchanged.

Right now I have to investigate more deeper why CBO decide to use bitmap conversion and if it is possible to avoid that in different way. But it has to wait until Monday when I will have a access to trace files.

regards,
Marcin