Re: BITMAP CONVERSION FROM/TO ROWID

Hi Arul,

Regarding 3), it should be a BITMAP AND if your query is: WHERE predicate AND predicate AND predicate.

As I've always understood it, you've got a BITMAP CONV TO ROWIDS because you've got three indices which could used on each of three predicates in your query. Oracle is sort of building three bitmap indices on the fly and doing a logical bitwise and on the results to get the intersection of the set of rowids from the three indices. (Hope someone corrects me if I'm wrong.)

Malcolm.

On 14/12/05, Arul Ramachandran <contactarul_at_gmail.com> wrote:
>
>
> 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