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
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
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.
alter session set “_B_TREE_BITMAP_PLANS” = false;
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