Archive

Posts Tagged ‘_B_TREE_BITMAP_PLANS’

BITMAP CONVERSION (FROM/TO) ROWIDS – SQL query execution plan steps.

Recently, in one of the database I support, I was notified that one of the SQL query worked very slow. I checked SQL execution plan and discovered two steps: BITMAP CONVERSION TO ROWIDS and BITMAP CONVERSION FROM ROWIDS which looked suspicious to me because there are no bitmap indexes in that database.
The “BITMAP CONVERSION TO ROWIDS” or “BITMAP CONVERSION FROM ROWIDS” execution plan steps were generally introduced in Oracle 9i when the default value for the hidden parameter _B_TREE_BITMAP_PLANS changed from FALSE to TRUE. The “BITMAP CONVERSION” plan is not always an optimal step when converting from b-tree indexes, and it can be very inefficient access plan in some cases. Bitmap conversion to ROWIDS does not require bitmap indexes, and it’s sometimes found in cases of SQL with complex WHERE clause conditions.
The bitmap conversion to rowids is sometimes seen with star transformations, so you could start with changing the initialization parameter STAR_TRANSFORMATION_ENABLED to FALSE. In my case it didn’t help, so I had to change the hidden parameter _B_TREE_BITMAP_PLANS to FALSE.
You can also turn off bitmap conversion at the session level, for testing:

alter session set _b_tree_bitmap_plans=false;

As always, notify Oracle technical support before changing any hidden parameters, as they can make your database unsupported.

Advertisements