Systems Engineering and RDBMS

_b_tree_bitmap_plans un-documented parameter in Oracle

Posted by decipherinfosys on February 28, 2007

We typically never start using un-documented parameters in a production environment unless it has been specifically recommended by Oracle support.  However, we do keep ourselves abreast of all the un-documented parameters that are out there and Jonathan Lewis’s latest book covers them as well.  It is an excellent book to have for other reasons as well if you want to learn and hone your Oracle skills for CBO.

Now, regarding this particular parameter – this has caused us issues more than once.  The default value of this parameter is set to TRUE in Oracle 9iR2 and Oracle 10g.  When this parameter is set to TRUE in the init<sid>.ora parameter file, it enables bitmap plans to be generated for tables with only B-Tree indexes.  CBO (Cost Based Optimizer) can choose to use bitmap access paths without the existence of bitmap indexes and in order to do so, it uses BITMAP CONVERSION FROM ROWIDS and BITMAP CONVERSION TO ROWIDS operations. Those operations are CPU intensive.  So, if you have a query in the system for which those operations are performed selects a small number of rows, then there isn’t much of an impact. However, if those queries select a large number of rows, the cost involved gets escalated pretty soon.  The cost is not incurred during the parse, it’s all incurred during the fetching.  In order to prevent issues arising from the wrong plan getting generated, if you are facing any such issues, this parameter should be explicitly set to False either at the database level or at the session level if you can identify a certain portion of the code running into such an issue and can isolate the connections based on program name etc..

This is one of those silver bullet un-documented items.  However, you should check whether you are running into such issues (check the execution plans to see if bitmap conversions are taking place) and then only take an action.

About these ads

One Response to “_b_tree_bitmap_plans un-documented parameter in Oracle”

  1. […] 2007 We had talked about one of the undocumented parameters in Oracle before in one of our blog posts. Here is a SQL to get all the undocumented parameters and their session and instance […]

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 85 other followers

%d bloggers like this: