RE: bitmap conversion to rowids operation with btree indexes?

Thanks Jose...
*Looks* like just not bitmap conversion is causing this, but due to possible incorrect predicate selection and/or driving table selection..Driving table is POLICE in the first (inefficient ) explain plan and it is object PK in the second case (efficient one). Driving table selection may be driving the predicate selection to access the PK object.

Not sure whether _b_tree_bitmap_plans is purely statistics based or a mix of heuristics & statistics. It might be interesting to see why optimizer chose this inefficient access paths.

Inefficient:

        72       NESTED LOOPS 
    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
       110          BITMAP AND 
    429463           BITMAP CONVERSION FROM ROWIDS
  98347027            INDEX RANGE SCAN PK_3  (object id 103815)
    406451           BITMAP CONVERSION FROM ROWIDS
   2262366            INDEX RANGE SCAN PK_1  (object id 103814)

Efficient:
        72       NESTED LOOPS 
        75        TABLE ACCESS BY INDEX ROWID PK  (object id 103596)
       229         INDEX RANGE SCAN PK_3  (object id 103815)
        72        TABLE ACCESS BY INDEX ROWID POLICE  (object id 104075)
        75         INDEX UNIQUE SCAN POLICE_1  (object id 104091)

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: Wednesday, January 12, 2005 8:36 AM To: rshamsud_at_jcpenney.com; oracle-l_at_freelists.org Subject: RE: bitmap conversion to rowids operation with btree indexes?

I will try to get it because the DBA is currenlty out of the office. We didn't prepare any trace files after he has changed the parameter because the response time was as expected (excellent).=20 But I have the run-time plan prepared by RBO for the same statement where the response time was 0.02s. I suspect that the plan=20 prepared by CBO will be quite similar.

I will also try to get 10053 trace to study it.=20 It smells me like a bug in CBO.

Kind 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
        75        TABLE ACCESS BY INDEX ROWID PK  (object id 103596)
       229         INDEX RANGE SCAN PK_3  (object id 103815)
        72        TABLE ACCESS BY INDEX ROWID POLICE  (object id 104075)
        75         INDEX UNIQUE SCAN POLICE_1  (object id 104091)
        72       TABLE ACCESS BY INDEX ROWID FI_OSEBE  (object id =
103479)
        72        INDEX UNIQUE SCAN FI_OSEBE_1  (object id 103494)
        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 DOK_PLACILA  (object id =
103466)
        72      INDEX UNIQUE SCAN DOK_PLACILA_1  (object id 103467)
         3    NESTED LOOPS =20
         3     NESTED LOOPS =20
         3      NESTED LOOPS =20
         3       NESTED LOOPS =20
        75        TABLE ACCESS BY INDEX ROWID PK  (object id 103596)
       229         INDEX RANGE SCAN PK_3  (object id 103815)
         3        TABLE ACCESS BY INDEX ROWID POLICE  (object id 104075)
        75         INDEX UNIQUE SCAN POLICE_1  (object id 104091)
         3       TABLE ACCESS BY INDEX ROWID PR_OSEBE  (object id =
104162)
         3        INDEX UNIQUE SCAN PR_OSEBE_1  (object id 104165)
         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 DOK_PLACILA  (object id =
103466)
         3      INDEX UNIQUE SCAN DOK_PLACILA_1  (object id 103467)

-----Original Message-----

From: rshamsud_at_jcpenney.com [mailto:rshamsud_at_jcpenney.com] Sent: Wednesday, January 12, 2005 3:08 PM To: Joze Senegacnik; oracle-l_at_freelists.org Subject: 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

--

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 Received on Wed Jan 12 2005 - 09:51:50 CST