Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

BITMAP CONVERSION FROM ROWIDS

User511015-OCMar 27 2014 — edited Mar 27 2014

Hi There,

DB/OS Version - ORACLE 11.2.0.3.0 on Oracle Solaris SPARC 64 Bit

I have come across a very peculiar issue after upgrading my databases from Oracle 10.2.0.5 ->  ORACLE 11.2.0.3.0. One of my sql is taking quite longer to execute in production environment where as in test environment, the query is executing in sub seconds.

It seems that my database has hidden parameter "_b_tree_bitmap_plans" with value as 'TRUE' in all DEV/Test and Prod environments.

Please see below my findings so far-

In prod support environment

Parameter "_b_tree_bitmap_plans" is set to be TRUE

SQL>explain plan for select * from usr.OUTGOING_DOCUMENT_EVENTS ode where ODE.CURRENT_STATUS_ID = 12 and ODE.DELIVERY_TYPE_ID = 10;

@$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2700369628

--------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                          |   282K|    70M| 57682   (1)| 00:11:33 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| OUTGOING_DOCUMENT_EVENTS |   282K|    70M| 57682   (1)| 00:11:33 |

|*  2 |   INDEX RANGE SCAN          | ODEV_DOSA_FK_I           |  2258K|       |  2642   (1)| 00:00:32 |

--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------

   1 - filter("ODE"."DELIVERY_TYPE_ID"=10)

   2 - access("ODE"."CURRENT_STATUS_ID"=12)

alter session set "_b_tree_bitmap_plans" = false;

explain plan for select * from usr.OUTGOING_DOCUMENT_EVENTS ode where ODE.CURRENT_STATUS_ID = 12 and ODE.DELIVERY_TYPE_ID = 10;

@$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2700369628

--------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                          |   282K|    70M| 57682   (1)| 00:11:33 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| OUTGOING_DOCUMENT_EVENTS |   282K|    70M| 57682   (1)| 00:11:33 |

|*  2 |   INDEX RANGE SCAN          | ODEV_DOSA_FK_I           |  2258K|       |  2642   (1)| 00:00:32 |

--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------

   1 - filter("ODE"."DELIVERY_TYPE_ID"=10)

   2 - access("ODE"."CURRENT_STATUS_ID"=12)

In Prod

_b_tree_bitmap_plans is set to be true

explain plan for select * from usr.OUTGOING_DOCUMENT_EVENTS ode where ODE.CURRENT_STATUS_ID = 12 and ODE.DELIVERY_TYPE_ID = 10;

@$ORACLE_HOME/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2845871922

-------------------------------------------------------------------------------------------------------------

| Id  | Operation                        | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                 |                          |   280K|    70M| 30155   (1)| 00:06:02 |

|   1 |  TABLE ACCESS BY INDEX ROWID     | OUTGOING_DOCUMENT_EVENTS |   280K|    70M| 30155   (1)| 00:06:02 |

|   2 |   BITMAP CONVERSION TO ROWIDS    |                          |       |       |            |          |

|   3 |    BITMAP AND                    |                          |       |       |            |          |

|   4 |     BITMAP CONVERSION FROM ROWIDS|                          |       |       |            |          |

|*  5 |      INDEX RANGE SCAN            | ODEV_DOSA_FK_I           |  2245K|       |  2578   (1)| 00:00:31 |

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

|   6 |     BITMAP CONVERSION FROM ROWIDS|                          |       |       |            |          |

|*  7 |      INDEX RANGE SCAN            | ODEV_DLTP_FK_I           |  2245K|       |  8716   (1)| 00:01:45 |

-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   5 - access("ODE"."CURRENT_STATUS_ID"=12)

   7 - access("ODE"."DELIVERY_TYPE_ID"=10)

alter session set "_b_tree_bitmap_plans" = false;

explain plan for select * from usr.OUTGOING_DOCUMENT_EVENTS ode where ODE.CURRENT_STATUS_ID = 12 and ODE.DELIVERY_TYPE_ID = 10;

@/oracle/product/11g/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2700369628

--------------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                          |   280K|    70M| 57478   (1)| 00:11:30 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| OUTGOING_DOCUMENT_EVENTS |   280K|    70M| 57478   (1)| 00:11:30 |

|*  2 |   INDEX RANGE SCAN          | ODEV_DOSA_FK_I           |  2245K|       |  2578   (1)| 00:00:31 |

--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------

   1 - filter("ODE"."DELIVERY_TYPE_ID"=10)

   2 - access("ODE"."CURRENT_STATUS_ID"=12)

Definitely in production setting value for _b_tree_bitmap_plans to FALSE is improving performance as bitmap conversion is bypassed. But my concern is that why the same behaviour is NOT replicated in prod support  environment which is daily refreshed from production? Even when the value of this parameter is true in prod support, it is completely ignored. I read somewhere the we can not change this parameter before reporting to Oracle support so I am not inclined to change this parameter in prod environment as to what could be the other unforeseen effect of this change.

Has anyone come across a similar problem? I will appreciate your help.

Thanks in advance
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 24 2014
Added on Mar 27 2014
#general-database-discussions
6 comments
2,509 views