_b_tree_bitmap_plans

Oracle B-Tree Bitmap Plans

最新推荐文章于 2025-07-28 07:30:00 发布

转载最新推荐文章于 2025-07-28 07:30:00 发布 · 277 阅读

文章标签：

#数据库

探讨了Oracle数据库中_b_tree_bitmap_plans参数的作用及其默认设置的变化。该参数允许成本基于优化器(CBO)为仅包含B-树索引的表生成位图计划，即使不存在位图索引也能使用位图访问路径。

有时，我们会看到执行计划里有这样的内容：

bitmap conversion from rowids

Metalink note 225466.1

· symptom: Execution plan operation shows bitmap conversion from rowids

· symptom: No bitmap indexes

· symptom: Execution plan shows BITMAP CONVERSION

· cause: In 7.3.4 and in 8.1.7 default value of _b_tree_bitmap_plans is FALSE
whereas as of 9.0.1 (and 9.2) the default value is TRUE When _b_tree_bitmap_plans set to true (advice not to change the default setting yourself) the optimizer is allowed to produce bitmap plans for normal b*tree indexes even if no bitmap indexes set.

fix:

This is intended behaviour if _b_tree_bitmap_plans set to true
If this is unwanted leave _b_tree_bitmap_plans at default value (false)

_b_tree_bitmap_plans un-documented parameter in Oracle

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.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..

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10173379/viewspace-661982/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10173379/viewspace-661982/