BITMAP CONVERSION FROM/TO ROWID

Hi Folks,

Oracle EE 9.2.0.5
The weekly dbms_stats run kicked in to collect fresh stats on the stale PMT_INSTR table (sample pcnt 5%) and subsequently the execution plan of a well performing query changed from

| TABLE ACCESS BY INDEX ROWID |PMT_INSTR | 1 | 22 |
2 |
| INDEX UNIQUE SCAN |PMT_INSTR_PK | 1 |
| |

to

| TABLE ACCESS BY INDEX ROWID |PMT_INSTR | 1 | 22 |
701 |
| BITMAP CONVERSION TO ROWIDS | | |
| |
| BITMAP AND | | |
| |
| BITMAP CONVERSION FROM ROW| | |
| |
| INDEX RANGE SCAN |PMT_INSTR_PK | 1 |
| |
| BITMAP CONVERSION FROM ROW| | |
| |
| INDEX RANGE SCAN |PMT_INSTR_IX1 | 1 |
| |
| BITMAP CONVERSION FROM ROW| | |
| |
| INDEX RANGE SCAN |PMT_INSTR_IX2 | 1 |
| |

The common solution recommended for the above scenario is alter session set "_b_tree_bitmap_plans"=FALSE Of late, I am noticing quite a few occurrences of execution plans of this kind with the operation BITMAP CONVERSION FROM/TO ROWID resulting in sub-optimal execution plans.

1. If this is an optimizer anomaly with 9.2.0.5, would it be wise to set "_b_tree_bitmap_plans"=FALSE in init.ora? And what is the negative impact of this?
2. Would increasing dbms_stats sample pcnt to a higher value (say 20) help?
3. I think the operation BITMAP CONVERSION FROM/TO ROWID is inappropriate in the case of "BITMAP AND" and may be beneficial for "BITMAP OR". Please correct me if I am wrong.

Thanks and regards,
Arul

--
http://www.freelists.org/webpage/oracle-l