-
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 |
----------------------------------------------------------------------------------------------------
-
BTW, i have no bitmap index, weird.
-
alter session stat_transformation_enabled = FALSE ;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
-
There are a couple of bugs listed in Metalink... you might want to check Note:245251.1
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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.
-
set _b_tree_bitmap_plans to FALSE
-
Pando,
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".
-
Have you tried using a hint in your sql forcing an index to be used?
/*+ index(....,...) */
---------------
-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|