How to optimize the slow SQL Execution caused by converting B-tree indexes into BITMAP indexes (BITMAP CONVERSION)

At Oracle 9i or above, Oracle sometimes chooses to convert the B-Tree index to BITMAP for SQL Execution, resulting in an extremely bad execution plan.

The conversion is determined by the hidden parameter _ B _tree_bitmap_plans. If this parameter is set to true, it is converted. Otherwise, no conversion is performed. In 8i, the default value of this parameter is false,

Therefore, only when a bitmap index exists will the B-tree index be converted to bitmap; and In 9i, if this parameter is set to true by default, any index may be performed.

Bitmap conversion.

 

There are two solutions:

1. Disable converting B-tree indexes to Bitmap indexes by adjusting the _ B _tree_bitmap_plans Parameter

 

Alter system set "_ B _tree_bitmap_plans" = false;

Select Name, Value From v $ parameter Where Name = '_ B _tree_bitmap_plans'

 

In bitmap conversion from/to rowids is the execution plan after bitmap conversion:

In the customer's database system, once this SQL statement executes bitmap conversion, the execution time will be extended to about 50 seconds, and a single process will occupy 100% of the CPU resources, and switch is disabled, the CPU resource usage is normal and can be completed in 40 milliseconds.

SQL> alter session set "_ B _tree_bitmap_plans" = false;
Session altered.
Elapsed: 00:00:00. 01
SQL> @ SQL
COUNT (DISTINCTTAB2.WFID)
------------------------
708
Elapsed: 00:00:00. 41
SQL> alter session set "_ B _tree_bitmap_plans" = true;
Session altered.
Elapsed: 00:00:00. 00
SQL> @ SQL
COUNT (DISTINCTTAB2.WFID)
------------------------
708

Elapsed: 00:00:52. 80

Finally, set the global implicit parameter _ B _tree_bitmap_plans to disable bitmap conversion.

2. Execute the package begin dbms_stats.gather_table_stats ('user', 'table name'); END; Re-calculate the table information