Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Jai Shree Ram Oracle
  • tar and untar a dolder with all its subfolder. Linux/Unix
  • oracle Dba site Oracle
  • adding new line after specific pattern using sed Linux/Unix
  • find_string_in_database.sql Oracle
  • Small sample shell program Linux/Unix
  • How to hide author name in WordPress BLOG PHP/MYSQL/Wordpress
  • Implementation of key based authentications Linux/Unix
  • tblwopk.sql tablewopk.sql Oracle
  • restarting network in linux Linux/Unix
  • Read CSV File using Python Python/PySpark
  • Directory wise folder wise space usage Linux/Unix
  • find_longsql.sql Oracle
  • PLSQL Table Syntax 1 Oracle
  • pvm_metric.sql for gathering report from vmstat tables Oracle

_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

  • SQL_PROFILE – I explaination Oracle
  • DBMS_UTILITY PACKAGE Oracle
  • DB Console Mainenance. Oracle
  • Jai Shree Ram Oracle
  • EXTPROC Oracle
  • Best approach for Oracle database patching sequence to latest/required patchset along with CPU/PSU/any-other-one-off patch ID 865255.1 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
  • Removing Blank lines from file using grep Linux/Unix
  • Convert multiple rows to single column Oracle
  • Running select from V$ views from remote server Linux/Unix
  • EXTPROC Oracle
  • To see mem usage and CPU usage system wide. Linux/Unix
  • CTAS with LONG Column for 9i and higher Oracle
  • Find Stale DR Physical Standby Oracle
  • Reading config file from other folder inside class Python/PySpark

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme