Home » RDBMS Server » Performance Tuning » what is BITMAP CONVERSION TO ROWIDS in Plan?
what is BITMAP CONVERSION TO ROWIDS in Plan? [message #171496] Wed, 10 May 2006 04:27 Go to next message
kumarsz
Messages: 180
Registered: March 2005
Location: canada
Senior Member

Hi,
Oracle Version - Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

OBJ table created from user_objects table.
Column OWNER does not have any Index on OBJ
Column OBJECT_ID have unique index on OBJ (Primary key column)

on this context why i am getting following Plan? Is that Index is being accessed twice here?
What is this "BITMAP" all about when there is no Bitmap index involved at all?

DEV13>explain plan set statement_id='p9' into pz_table for select * from obj where object_id = 1 or object_id=2 and owner='PRATAPZ';

Explained.

DEV13>select lpad(' ',level-1) || operation || ' ' || options || ' ' || object_name "Plan"
2 from pz_table
3 connect by prior id = parent_id and prior statement_id = statement_id
4 start with id = 0 and statement_id = '&1' order by id;
Enter value for 1: p9
old 4: start with id = 0 and statement_id = '&1' order by id
new 4: start with id = 0 and statement_id = 'p9' order by id

Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
TABLE ACCESS BY INDEX ROWID OBJ
BITMAP CONVERSION TO ROWIDS
BITMAP OR
BITMAP CONVERSION FROM ROWIDS
INDEX RANGE SCAN PK_OBJ
BITMAP CONVERSION FROM ROWIDS
INDEX RANGE SCAN PK_OBJ

8 rows selected.

Please suggest

Thanks in Advance,
Pratap
Re: what is BITMAP CONVERSION TO ROWIDS in Plan? [message #171621 is a reply to message #171496] Wed, 10 May 2006 10:50 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Known behaviour.
Sometimes, the CBO may choose a bitmap access over a btree acccess.
here i collect the first plan as STATEMENT_1
change the default behaviour
the same sql with different ID will produce a different plan
SQL> explain plan set statement_id='STATEMENT_1' into plan_table for select * from obj where object_id = 1 or object_id=2 and owner='SYS';

Explained.

SQL> alter session set "_b_tree_bitmap_plans"=FALSE;

Session altered.

SQL> explain plan set statement_id='STATEMENT_2' into plan_table for select * from obj where object_id = 1 or object_id=2 and owner='SYS';

Explained.

SQL> set verify off
SQL> get a
  1  select lpad(' ',level-1) || operation || ' ' || options || ' ' || object_name "Plan"
  2  from plan_table
  3  connect by prior id = parent_id and prior statement_id = statement_id
  4* start with id = 0 and statement_id = '&1' order by id
SQL> @a
Enter value for 1: STATEMENT_1

Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
 TABLE ACCESS BY INDEX ROWID OBJ
  BITMAP CONVERSION TO ROWIDS
   BITMAP OR
    BITMAP CONVERSION FROM ROWIDS
     INDEX RANGE SCAN PK_OBJ
    BITMAP CONVERSION FROM ROWIDS
     INDEX RANGE SCAN PK_OBJ

8 rows selected.

SQL> @a
Enter value for 1: STATEMENT_2

Plan
--------------------------------------------------------------------------------
SELECT STATEMENT
 CONCATENATION
  TABLE ACCESS BY INDEX ROWID OBJ
   INDEX UNIQUE SCAN PK_OBJ
  TABLE ACCESS BY INDEX ROWID OBJ
   INDEX UNIQUE SCAN PK_OBJ

6 rows selected.


[Updated on: Wed, 10 May 2006 15:35]

Report message to a moderator

Re: what is BITMAP CONVERSION TO ROWIDS in Plan? [message #184647 is a reply to message #171621] Thu, 27 July 2006 05:57 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
AFAIK there is some Oracle bugs ( 2546446 and 2742886 ) causing "BITMAP conversion to ROWID" access path.

You can try setting stat_transformation_enabled = FALSE either at session or system levels to by-pass it.

HTH.
Michael
Previous Topic: urgent .. i need help
Next Topic: Pl/sql procedure experience a high parse count using oracle 9i db-link.
Goto Forum:
  


Current Time: Mon Dec 05 12:44:47 CST 2016

Total time taken to generate the page: 0.10736 seconds