9 Replies Latest reply: Feb 18, 2014 9:40 AM by rp0428 RSS

How to turn off bitmap to rowid over dblink

wtlshiers Newbie
Currently Being Moderated

I've been working with a query that's explain plan recently decided to start using bitmap to rowid conversions and, as a result, started running ridiculously long (query has run fine up till now). 

 

After some research and testing - I added the hint /*+ OPT_PARAM('_B_TREE_BITMAP_PLANS','FALSE') */ to the query and, the query starts running normally again (testing directly on the db it normally executes against).

 

However, the problem is this query runs over a db link normally and, the hint doesn't seem to go over the DB link (confirmed by the query continuing to run long and, no sign of the hint in v$sql on the remote db when the query is running).

 

So - Im not sure if this specific hint just won't work over a db link or??  I've also thought about alter session but I don't know if you can run the alter session over a db link (or how)... i.e. alter session set "_b_tree_bitmap_plans"=false

 

Any insight is appreciated...

  • 1. Re: How to turn off bitmap to rowid over dblink
    Martin Preiss Expert
    Currently Being Moderated

    I would not recommend the use of the underscore parameter hint in production - if there is another option to change the optimizer's mind.

     

    In some situations a remote query can be optimized by using a driving_site hint that "instructs the optimizer to execute the query at a different site than that selected by the database" http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#SQLRF50704. Some other option to optimize distributed queries are mentioned in http://docs.oracle.com/cd/E11882_01/server.112/e25494/ds_appdev.htm#ADMIN12210. If you don't find another option to tune the remote part of the query you could perhaps add hints to a view on the remote site (but again: I would try to avoid this).

  • 2. Re: How to turn off bitmap to rowid over dblink
    wtlshiers Newbie
    Currently Being Moderated

    Well - it needs the index it is hitting - it just does the bitmap to rowid conversion on it (where it never used to).  If I choose to tell Oracle not to use the index at all the query won't return (resorts to full tablescans which - in this case - are catostrophic as far as performance).

     

    I honestly think this may be due to fragmented indexes (as to why it started doing this in the first place) and, that the indexes may just need to be rebuilt but, I've no idea how to go about proving that...

  • 3. Re: How to turn off bitmap to rowid over dblink
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    Can you show us the original plan and the current plan ?

     

    Regards

    Jonathan Lewis

  • 4. Re: How to turn off bitmap to rowid over dblink
    Etbin Guru
    Currently Being Moderated

    IMHO the easiest way to "fool" the optimizer is to "imitate" it. There are lots of ways queries can be written (especially complex ones). Try to split the bunch of predicates causing conversion to bitmap and redistribute them among the subqueries, try subquery factoring in a way some appropriate indexes can still be used, forcing the sequence of joins using parenthesis with ansii notation might succeed too ... you know yor data for sure, so your chances of influencing the optimizer are much better than ours

     

    Regards

     

    Etbin

  • 5. Re: How to turn off bitmap to rowid over dblink
    davidp 2 Pro
    Currently Being Moderated

    The problem will not be the bitmap to rowid conversions as such. The problem is either changed actual cardinalities or changed estimated cardinalities that mean the optimiser is expecting to encounter lots of rows and is probably combining two non-very-selective index scans using a bitmap  (finding rows referenced in both indexes).

    You need to look at the old plan (if possible) and the new plan and work out why the new plan is slow (it's not the bitmap conversion, it's the number of rows it actually hits or the amount of I/O it needs to do that)

    Actual row counts are really helpful - the output from

    set serverout off

    alter session set statistics_level=all

    <your query>

    select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'))

    includdes estimates and actual row counts and is often helpful.

     

    Regards,

    David

  • 6. Re: How to turn off bitmap to rowid over dblink
    wtlshiers Newbie
    Currently Being Moderated

    I'll try that but - the query has literally gone from 20 minutes to not finishing in 10 hours.  There's a high degree of cardinality so, I don't think that's the problem (joining on sequence based unique columns)...

     

    Using the previously mentioned hint reverts the query back to it's expected run time.

     

    Honestly - I was hoping it would be as easy as issuing the alter session before executing the query but - the remote execution seems to make that impossible (unless I have a function on the remote instance for that purpose)... ugh..

  • 7. Re: How to turn off bitmap to rowid over dblink
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    Still waiting to see a couple of execution plans.

     

    There's little point in asking for help in solving a problem with execution plans if you don't let people know what the before and after versions of the plan look like.

     

    Regards

    Jonathan Lewis

  • 8. Re: How to turn off bitmap to rowid over dblink
    wtlshiers Newbie
    Currently Being Moderated

    I wasn't asking for help with execution plans - I can figure that out thanks.  I was asking for a means to disable the bitmap conversions remotely. I've worked through numerous changes to the SQL to get it to use various execution plans... EVERY time that index in question is used it does a bitmap conversion... Everytime... Once it does this - the performance goes straight down the tube... once I add the hint or, alter the session - everything is great again.  I've reviewed the number of rows and degree of cardinality (based on historical data) and it's varied less than 1 percent in months...

     

    If you can't use this hint over a db link - I'll look at breaking up the data...

  • 9. Re: How to turn off bitmap to rowid over dblink
    rp0428 Guru
    Currently Being Moderated
    wasn't asking for help with execution plans

    Yes - that is EXACTLY what you ask:

    I've been working with a query that's explain plan recently decided to start using bitmap to rowid conversions and, as a result, started running ridiculously long (query has run fine up till now). 

    That says that you have TWO plans: a new one that uses the conversion and an original one that doesn't use it. And you want to know why.

     

    Well - we can NOT help you without seeing what each of those plans is doing.

    I can figure that out thanks.

    Well then - go ahead and 'figure that out'. What are you bugging us about?

      I was asking for a means to disable the bitmap conversions remotely.

    Identifying potential solutions generally depends on knowing what is causing the problem. Examination of the two execution plans is likely the best way to determine what is causing the problem.

     

    You likely already have all the help you are going to get unless you choose to post both plans. The choice is yours, of course.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points