BITMAP CONVERSION FROM/TO ROWID
- From: Arul Ramachandran <contactarul@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 13 Dec 2005 18:00:23 -0800
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
Other related posts:
