Bitmap Conversion To Rowids

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


	
		03-23-2007, 12:53 PM
		steeve123
	
	
	
	
		BTW, i have no bitmap index, weird.


	
		03-23-2007, 02:03 PM
		PAVB
	
	
	
	
		alter session stat_transformation_enabled = FALSE ;


	
		03-23-2007, 02:51 PM
		steeve123
	
	
	
	
		Already set at false


	
		03-23-2007, 03:03 PM
		PAVB
	
	
	
	
		There are a couple of bugs listed in Metalink... you might want to check Note:245251.1


	
		03-23-2007, 06:39 PM
		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.


	
		03-25-2007, 01:42 PM
		pando
	
	
	
	
		set _b_tree_bitmap_plans to FALSE


	
		03-25-2007, 09:41 PM
		tamilselvan
	
	
	
	
		Pando,




	Quote:
	
		
		
			
				Originally Posted by 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".


	
		03-25-2007, 11:53 PM
		reydp
	
	
	
	
		Have you tried using a hint in your sql forcing an index to be used?

/*+ index(....,...) */


	
		03-26-2007, 07:40 AM
		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?


	
		03-26-2007, 08:29 AM
		steeve123
	
	
	
	
		Here is how i collected statistics on my materialized view:

execute dbms_stats.gather_table_stats(ownname=> 'MUS_GEN3', tabname=> 'MV_SONG' , estimate_percent=>

DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=> DBMS_STATS.AUTO_CASCADE, degree=> null, no_invalidate=> DBMS_STATS.AUTO_INVALIDATE,

granularity=> 'AUTO',method_opt=> 'FOR ALL COLUMNS SIZE AUTO');



Will cascade=> DBMS_STATS.AUTO_CASCADE collect stats on indexes related to my mv?



Should i have collected stats on my mv a different way?



The index used in the plan is correct btw, the same as in my dev database.

It is the bitmap conversion that is not right.  So by increasing the “blevel” or leaf blocks values in one of the indexes will make the plan use another index? Or simply stop bitmap conversion on me?



And again, there is no bitmap index in my database.  I have a oltp system.


	
		03-26-2007, 09:36 AM
		davey23uk
	
	
	
	
		its nothing to do with bitmap indexes, so dont get confused. If it causing you a problem set that parameter pando mentioned


	
		03-26-2007, 02:46 PM
		steeve123
	
	
	
	
		_b_tree_bitmap_plans is already set to false.  It is still happening.


	
		03-26-2007, 02:48 PM
		PAVB
	
	
	
	
		Have you checked the bugs I posted earlier?


	
		03-26-2007, 03:21 PM
		steeve123
	
	
	
	
		Im on 10.2.0.3.  The metalink note is for 9i.  The doc says to change the parameter wish i did.


	
		03-26-2007, 05:47 PM
		davey23uk
	
	
	
	
		dont wish it - do it!


	
		03-26-2007, 07:13 PM
		PAVB
	
	
	
	
		When talking about bugs, you don't trust literally what a Oracle Note says about affected versions.



You are in 10g and bug affected 9i?... check the date of the note, chances are Ora10g wasn't an active product when it was written. Bug might be there, alive and kicking


	
		03-27-2007, 02:29 PM
		steeve123
	
	
	
	
		I have change the parameter, still happening.


	
		03-27-2007, 11:24 PM
		reydp
	
	
	
	
		so why not try using index/es hint?