RE: bitmap conversion to rowids operation with btree indexes?

I guess, AND_EQUAL / Logical OR is optimized to use bitmaps for obvious reasons, rather than ANDing or UNIONing the rowids. I can see that generating bitmap from rowids and degenerating rowids from bitmaps to be a costly operation, if the row source is smaller. But I always thought, if the # of rows from each index row source is high (98M & 2M in this case) and if the end result set is comparatively very small (just 226 rows here), then this specific access plan would be excellent.. Sort of using bitmap indices except the cardinality is much higher. Your experience just breaks that assumption!

Can you post the tuned explain plan also ? I am just wondering whether the reason for performance improvement is due to complete change in access plan or Is that by avoiding the rowid->bitmap->rowid conversion ?..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joze Senegacnik Sent: Tuesday, January 11, 2005 11:29 PM To: oracle-l_at_freelists.org
Subject: RE: bitmap conversion to rowids operation with btree indexes?

One of my customers had this problem just two days ago=20 after upgrade to 9.2.0.6. The query was using a lot of=20 CPU time - performing only LIO. Wes solved this by putting:

alter session set "_b_tree_bitmap_plans" =3D false=20

in the after logon trigger to resolve the problem.=20 I had to set this hidden parameter in several cases. =20
>From the below run-time plan from this last case=20
you can see that bitmap conversion from rowids=20 was very inefficient. The stats was fresh.

Regards, Joze

Rows Row Source Operation

---------- ---------------------------------------------------
        75  SORT ORDER BY=20
        75   UNION-ALL =20
        72    NESTED LOOPS =20
        72     NESTED LOOPS =20
        72      NESTED LOOPS =20
        72       NESTED LOOPS =20
    429463        TABLE ACCESS BY INDEX ROWID POLICE  (object id 104075)
    558622         INDEX RANGE SCAN POLICE_11  (object id 104077)
        72        TABLE ACCESS BY INDEX ROWID PK  (object id 103596)
       226         BITMAP CONVERSION TO ROWIDS=20
       110          BITMAP AND =20
    429463           BITMAP CONVERSION FROM ROWIDS=20
  98347027            INDEX RANGE SCAN PK_3  (object id 103815)
    406451           BITMAP CONVERSION FROM ROWIDS=20
   2262366            INDEX RANGE SCAN PK_1  (object id 103814)
        72       TABLE ACCESS BY INDEX ROWID DOK_PLACILA  (object id =
103466)
        72        INDEX UNIQUE SCAN DOK_PLACILA_1  (object id 103467)
        72      TABLE ACCESS BY INDEX ROWID PLAC_MESTA  (object id =
103912)
        72       INDEX UNIQUE SCAN PLAC_MESTA_1  (object id 103916)
        72     TABLE ACCESS BY INDEX ROWID FI_OSEBE  (object id 103479)
        72      INDEX UNIQUE SCAN FI_OSEBE_1  (object id 103494)
         3    NESTED LOOPS =20
         3     NESTED LOOPS =20
         3      NESTED LOOPS =20
         3       NESTED LOOPS =20
    129159        TABLE ACCESS BY INDEX ROWID POLICE  (object id 104075)
    558622         INDEX RANGE SCAN POLICE_11  (object id 104077)
         3        TABLE ACCESS BY INDEX ROWID PK  (object id 103596)
         3         BITMAP CONVERSION TO ROWIDS=20
         2          BITMAP AND =20
    129159           BITMAP CONVERSION FROM ROWIDS=20
  29577411            INDEX RANGE SCAN PK_3  (object id 103815)
    120469           BITMAP CONVERSION FROM ROWIDS=20
    772730            INDEX RANGE SCAN PK_1  (object id 103814)
         3       TABLE ACCESS BY INDEX ROWID DOK_PLACILA  (object id =
103466)
         3        INDEX UNIQUE SCAN DOK_PLACILA_1  (object id 103467)
         3      TABLE ACCESS BY INDEX ROWID PLAC_MESTA  (object id =
103912)
         3       INDEX UNIQUE SCAN PLAC_MESTA_1  (object id 103916)
         3     TABLE ACCESS BY INDEX ROWID PR_OSEBE  (object id 104162)
         3      INDEX UNIQUE SCAN PR_OSEBE_1  (object id 104165)

On Tue, 2005-01-11 at 11:12, Jonathan Lewis wrote:

>The default value is FALSE in 8i, and true in 9i,
>and some people have reported performance
>problems because there setup started doing this
>conversion too frequently in inappropriate cases.

>Regards

>Jonathan Lewis

--
http://www.freelists.org/webpage/oracle-l



-- Attached file included as plaintext by Ecartis --
-- Desc: Signature

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.


--
http://www.freelists.org/webpage/oracle-l