Bitmap Conversion To Rowids

[steeve123]

This is the plan used for my query in my prod database.
10.2.0.3
In dev same oracle version, the plan is not doing bitmap conversion.
prod query time: 4minutes
dev query time:20 seconds

how can i tell oracle to stop bitmap conversing on me?

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1934 | 267K| 3025 (1)| 00:00:37 |
| 1 | SORT ORDER BY | | 1934 | 267K| 3024 (25)| 00:00:37 |
| 2 | UNION-ALL | | | | | |
| 3 | MAT_VIEW ACCESS BY INDEX ROWID | MV_SONG | 92 | 14076 | 2304 (1)| 00:00:28 |
| 4 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 5 | BITMAP AND | | | | | |
| 6 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 7 | INDEX RANGE SCAN | IDX_MV_SONG_USCFALB | | | 417 (2)| 00:00:06 |
| 8 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 9 | SORT ORDER BY | | | | | |
|* 10 | DOMAIN INDEX | MV_SON_TITLE_FULLT_IDX | | | 1827 (0)| 00:00:22 |
| 11 | MAT_VIEW ACCESS BY INDEX ROWID | MV_SONG | 1842 | 253K| 720 (1)| 00:00:09 |
|* 12 | INDEX RANGE SCAN | IDXF_MV_SONG_SONTUSFALB | 737 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

[steeve123]

BTW, i have no bitmap index, weird.

[PAVB]

alter session stat_transformation_enabled = FALSE ;

[steeve123]

Already set at false

[PAVB]

There are a couple of bugs listed in Metalink... you might want to check Note:245251.1

[tamilselvan]

BITMAP CONVERSION TO ROWIDS operation is certainly irritating developers and DBAs, and many times it is performing very badly.

There are couple of ways you can eliminate this operation.
1. Run the SQL with 10053 trace event and examine the trace file.
2. Check the indexes have proper statistics. You can increase the “blevel” or leaf blocks values in one of the indexes (or both). Try one at a time.

[pando]

set _b_tree_bitmap_plans to FALSE

[tamilselvan]

Pando,

set _b_tree_bitmap_plans to FALSE

I have seen the optimizer chose the BITMAP CONVERSION (ROWIDS) operation even after setting this parameter to FALSE in 8.1.7.4. This has happened after we applied the "APR 2006 CPU".

[reydp]

Have you tried using a hint in your sql forcing an index to be used?
/*+ index(....,...) */

[pando]

Hi Tamil

That parameter applies to btree indexes and AFIAK it is TRUE from 9i.

As the poster says his index is btree and not bitmap. May the bitmap conversion in your case was with a bitmap index?