Home > Oracle, RDBMS > BITMAP CONVERSION (FROM/TO) ROWIDS – SQL query execution plan steps.

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
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: