Quote Originally Posted by malay_biswal View Post
You can set this in session level and execute to see if bitmap conversion is happening.
_b_tree_bitmap_plans = false

However i think problem is with NL join, did you try excuting query with FULL hint?

This "LOWER(CONVERT('%813982%','WE8MSWIN1252','WE8MACROMAN8')))" is where it is doing bitmap conversion 367018 many times, so lets c after setting _b_tree_bitmap_plans = false what's happening.

BTW, how long it was taking to execute before and what changes done after which this query taking long time?
That I suppose is a hidden parameter. I will try to do it.
Yes, I tried it full hint and it took around 5 and a half hours then. No changes were made to this query. There is a wizard at application level that lets the users form any query.
Earlier, this query should have executed in less than 2 minutes if customer didn't complain before. Even if I remove all columns in select list and use count(*), then this query executes in 2 seconds. Then if I start from 1 st column of select list and start adding columns one by one (by looking into the query posted above), the query starts taking long when I reach one particular column. If I think that it is the problematic column and use only this column in select list, even then the query will again start executing in 2 seconds. So, I am not able to drill down the cause.

Just another question: Do the indexes on columns present in select list have anything to do with faster query retrieval? I think it's no but I have seen change in execution plan if I create index on column of select list and this column is not present in where clause.