Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Find all users who have DML privileges Oracle
  • Pending Transaction Neighbors Script Oracle
  • secure crt settings Linux/Unix
  • To check whether standby is recovering properly or not?? Oracle
  • cold backup scripts to copy locally Linux/Unix
  • To find explain plan for a statement that occurred in past. Oracle
  • Create type and Grant on it. Oracle
  • How to find password change date for user Oracle
  • Monitor and Trace Unix processes using truss Linux/Unix
  • arch_configUOCIOTTO.ora Oracle
  • Metalink Note: Note:250655.1 : ADDM Basics USING THE AUTOMATIC DATABASE DIAGNOSTIC MONITOR Oracle
  • tab.sql Oracle
  • newupload.html PHP/MYSQL/Wordpress
  • get_vmstat_solaris Oracle
  • oracle 10g on linux Linux/Unix

_B_TREE_BITMAP_PLANS issue during 8.1.7 to 9.2.0.8 upgrade

Posted on 30-Aug-2007 By Admin No Comments on _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

Oracle, SQL scripts

Post navigation

Previous Post: Multiple listeners
Next Post: reset Sequence

Related Posts

  • How to connect to Oracle Database with Wallet with Python. Oracle
  • Metalink Note: Note:250655.1 : ADDM Basics USING THE AUTOMATIC DATABASE DIAGNOSTIC MONITOR Oracle
  • import-export with multiple files Oracle
  • OEM-Commnds Oracle
  • find_err.sql for finding errors from dba_errors. Oracle
  • sid_wise_sql.sql Further explaination Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • How does one SELECT a value from a table into a Unix variable? From SQL to Shell Linux/Unix
  • secure crt settings Linux/Unix
  • GSQ.sql Oracle
  • .profile Linux/Unix
  • Virtual Indexes in Oracle Oracle
  • pvm_rbs1.sql (to collect rbs info from db) Oracle
  • tar and untar a dolder with all its subfolder. Linux/Unix
  • Oracle 10g Wait Model Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme