Bitmap Conversion To Rowids
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Bitmap Conversion To Rowids

  1. #1
    Join Date
    Nov 2000
    Posts
    440

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

  2. #2
    Join Date
    Nov 2000
    Posts
    440
    BTW, i have no bitmap index, weird.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  4. #4
    Join Date
    Nov 2000
    Posts
    440
    Already set at false

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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.

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    set _b_tree_bitmap_plans to FALSE

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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".

  9. #9
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Have you tried using a hint in your sql forcing an index to be used?
    /*+ index(....,...) */
    ---------------

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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
  •  



Click Here to Expand Forum to Full Width