RE: bitmap conversion to rowids operation with btree indexes?
- From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
- To: Joze.Senegacnik@xxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 12 Jan 2005 09:47:25 -0600
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Joze Senegacnik
Sent: Wednesday, January 12, 2005 8:36 AM
To: rshamsud@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxx [mailto:rshamsud@xxxxxxxxxxxx]
Sent: Wednesday, January 12, 2005 3:08 PM
To: Joze Senegacnik; oracle-l@xxxxxxxxxxxxx
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
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
