_B_TREE_BITMAP_PLANS issue during 8.1.7 to 9.2.0.8 upgrade

Subject: Upgrading from 8.1.X to 9.X – Btree Bitmap Plan Issues – Diagnosing and Resolving

Doc ID: Note:259126.1 Type: TROUBLESHOOTING

Last Revision Date: 13-MAR-2006 Status: PUBLISHED

PURPOSE

The Oracle Cost Based Optimizer is continually been enhanced. These enhancements were designed to improve performance but in some cases can cause a minority of queries to perform no better or worse than before. This article discusses some of the potential problems you may encounter when moving SQL Queries from Oracle 8i to Oracle 9i. It mainly centres on changes within the Cost Based Optimizer.

SCOPE & APPLICATION

DBAs and Application Designers

Upgrading from 8.1.X to 9.X – Btree Bitmap Plan Issues – Diagnosing and Resolving

Btree Bitmap Plan Changes

Btree Bitmap plans is a technology that takes Btree index rowids and converts them in to bitmaps. After conversion, various bitmap comparison operations can be performed. Oracle 9i enables code that considers converting Btree indexes into bitmaps by default. The choice of a bitmap plan is a cost based choice and will only be made if the bitmap plan is deemed to be cheaper based upon the available statistics. Choice of a bitmap plan is controlled by the and (underscore). If this was set to >= 9.0.0. then the choice of bitmap plans would be enabled. This means that some 9i queries may start to use bitmap plans that were not considered before.

If an application has 8i to 9i upgrade related query tuning problems, please consider if btree bitmap plans are involved

Diagnosing Btree Bitmap Plan Issues:

Examine the explain plan and look for bitmap operations (but where not all the indexes involved are bitmap indexes)

Compare the current explain plan with the plan from the earlier version to see if the plan is different

Disable btree_bitmap_plan functionality and see if that resolves the problem

To workaround BTree to bitmap conversion issues, either disable the functionality or force a different plan in one of the following ways:

Ensure statistics are as accurate as possible, including column statistics for columns containing non uniform data distribution. This will ensure that the optimizer makes the best choice it can.

Set (underscore) = FALSE

alter session set “_B_TREE_BITMAP_PLANS” = false;

Set to a value less than “9.0.0”

This is a static parameter and cannot be changed on the fly. It has to be set in one of the instance initialisation files (e.g. initSID.ora).

Hint so that indexes are not chosen and cannot therefore be converted.

Use a stored outline See: Note 67536.1