Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h9H3I4w30425 for ; Thu, 16 Oct 2003 22:18:04 -0500 X-ClientAddr: 66.27.56.210 Received: from ns3.fatcity.com (rrcs-west-66-27-56-210.biz.rr.com [66.27.56.210]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h9H3Gkc30387 for ; Thu, 16 Oct 2003 22:16:46 -0500 Received: from ns3.fatcity.com (localhost.localdomain [127.0.0.1]) by ns3.fatcity.com (8.12.8/8.12.8) with ESMTP id h9H0NQCb007785 for ; Thu, 16 Oct 2003 17:30:56 -0700 Received: (from root@localhost) by ns3.fatcity.com (8.12.8/8.12.5/Submit) id h9GDtNxi023506 for oracle-l@orafaq.net; Thu, 16 Oct 2003 06:55:24 -0700 Received: by fatcity.com (05-Jun-2003/v1.0g-b73/bab) via fatcity.com id 005D3601; Thu, 16 Oct 2003 06:54:25 -0800 Message-ID: Date: Thu, 16 Oct 2003 06:54:25 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Fedock, John (KAM.RHQ)" Sender: ml-errors@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Fedock, John (KAM.RHQ)" Subject: RE: bitmap conversion on a index that is not bitmapped ??? Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 73; ListGuru (c) 1996-2003 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C393EC.B93CDF08" ------_=_NextPart_001_01C393EC.B93CDF08 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thanks for your comments. You are correct that AIF_OUTBOUND_IX01 is = more selective than AIF_OUTBOUND_IX03. =20 >From what I am seeing, degree is affecting the plan. If I delete stats, = alter index parallel 10, analyze table estimate stats - the bitmap = conversion plan is used. =20 If I delete stats, alter index parallel 10 and renalyze, it goes back to = the index range scan. =20 =20 At this point, I can accept it - although I am curious as to why it has = happening. And can I use degree on other indexes where a index range = scan is being done to help performance? So many questions, so little = time. =20 =20 -----Original Message----- Sent: Wednesday, October 15, 2003 6:39 PM To: Multiple recipients of list ORACLE-L It seems that you have tow predicates in the where clause that can take = advantage of two different indexes. Also you have "AND" condition that Oracle tries to take advantage of = using "BITMAP AND" operation. =20 Since you like the performance of the plan that uses the BITMAP = conversion, I can say that index "'AIF.AIF_OUTBOUND_IX01" is more = selective than=20 "'AIF.AIF_OUTBOUND_IX03" =20 And in the second plan that one that does not use the BITMAP, if you = force it to use index IX01, it would be faster. =20 Degree has nothing to do to the index performance except when doing = Index FFS. =20 Regards, =20 Waleed -----Original Message----- Sent: Wednesday, October 15, 2003 12:50 PM To: Multiple recipients of list ORACLE-L Platform is 8.1.7.4, HP-UX 11.00 =20 I have a complex view. After rebuilding all indexes this weekend, the = view got much faster. I did a trace on it. Now, a few days later, the = view is getting much slower. Yes, an analyze has run since then, doing = an 'estimate statistics', but an estimate stats was done after the = rebuilds as well. The difference in the explain is below - please note = that bitmapped conversion info (in bold): =20 Good plan: =20 .67 .....FIRST ROW .66 ......VIEW OF 'GAPP_USER. (CARD=3D1 BYTES=3D13 ) .65 .......SORT (AGGREGATE) (CARD=3D1 BYTES=3D121 ) .64 ........NESTED LOOPS (COST=3D53 CARD=3D1 BYTES=3D121 ) .61 .........TABLE ACCESS (BY INDEX ROWID) OF 'AIF.AIF_OUTBOUND' = (COST=3D51 CARD=3D1 BYTES=3D86 ) .60 ..........BITMAP CONVERSION (TO ROWIDS) .59 ...........BITMAP AND .55 ............BITMAP CONVERSION (FROM ROWIDS) .54 .............INDEX (RANGE SCAN) OF 'AIF.AIF_OUTBOUND_IX03' = (NON-UNIQUE) (COST=3D4 ) .58 ............BITMAP CONVERSION (FROM ROWIDS) .57 .............SORT (ORDER BY) .56 ..............INDEX (RANGE SCAN) OF 'AIF.AIF_OUTBOUND_IX01' = (NON-UNIQUE) (COST=3D11 ) .63 .........TABLE ACCESS (BY INDEX ROWID) OF 'AIF.EDI_ROUTE' = (COST=3D1 CARD=3D65 BYTES=3D2275 ) .62 ..........INDEX (UNIQUE SCAN) OF 'AIF.EDI_ROUTE_PK' (UNIQUE) = (CARD=3D65 ) =20 =20 Bad plan: =20 .61 .....FIRST ROW .60 ......VIEW OF 'GAPP_USER. (CARD=3D1 BYTES=3D13 ) .59 .......SORT (AGGREGATE) (CARD=3D1 BYTES=3D121 ) .58 ........NESTED LOOPS (COST=3D52 CARD=3D1 BYTES=3D121 ) .55 .........TABLE ACCESS (BY INDEX ROWID) OF 'AIF.AIF_OUTBOUND' = (COST=3D51 CARD=3D1 BYTES=3D86 ) .54 ..........INDEX (RANGE SCAN) OF 'AIF.AIF_OUTBOUND_IX03' = (NON-UNIQUE) (COST=3D3 CARD=3D1 ) .57 .........TABLE ACCESS (BY INDEX ROWID) OF 'AIF.EDI_ROUTE' = (COST=3D1 CARD=3D65 BYTES=3D2275 ) .56 ..........INDEX (UNIQUE SCAN) OF 'AIF.EDI_ROUTE_PK' (UNIQUE) = (CARD=3D65 ) =20 =20 The interesting thing is, the table in question does not have any bit = mapped indexes on it. Some notes on MetaLink show other people = questioning the same issue, but no concrete info is given. Any ideas = out there? =20 =20 TIA. =20 John John Fedock=20 "K" Line America, Inc.=20 www.kline.com =20 * john.fedock@us.kline.com=20 =20 ------_=_NextPart_001_01C393EC.B93CDF08 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Thanks=20 for your comments.  You are correct that AIF_OUTBOUND_IX01 is more=20 selective than AIF_OUTBOUND_IX03.
 
From=20 what I am seeing, degree is affecting the plan.  If I delete stats, = alter=20 index parallel 10, analyze table estimate stats - the bitmap conversion = plan is=20 used.
 
If I=20 delete stats, alter index parallel 10 and renalyze, it goes back to = the=20 index range scan.  
 
At=20 this point, I can accept it - although I am curious as to why it has=20 happening.   And can I use degree on other indexes = where a=20 index range scan is being done to help performance?  So many = questions, so=20 little time.
 
 
-----Original Message-----
From: Khedr, Waleed=20 [mailto:Waleed.Khedr@FMR.COM]
Sent: Wednesday, October 15, = 2003 6:39=20 PM
To: Multiple recipients of list = ORACLE-L
Subject: RE:=20 bitmap conversion on a index that is not bitmapped = ???

It=20 seems that you have tow predicates in the where clause that can take = advantage=20 of two different indexes.
Also=20 you have "AND" condition that Oracle tries to take advantage of using = "BITMAP=20 AND" operation.
 
Since you like the performance of the plan that uses the = BITMAP=20 conversion, I can say that index "'AIF.AIF_OUTBOUND_IX01" is more selective than
"'AIF.AIF_OUTBOUND_IX03"
 
And in the second plan that = one that=20 does not use the BITMAP, if you force it to use index IX01, it would = be=20 faster.
 
Degree has nothing to do to = the index=20 performance except when doing Index=20 FFS.
 
Regards,
 
Waleed
-----Original Message-----
From: Fedock, John = (KAM.RHQ)=20 [mailto:John.Fedock@us.kline.com]
Sent: Wednesday, October = 15,=20 2003 12:50 PM
To: Multiple recipients of list=20 ORACLE-L
Subject: bitmap conversion on a index that is not = bitmapped ???

Platform is=20 8.1.7.4, HP-UX 11.00
 
I = have a complex=20 view.  After rebuilding all indexes this weekend, the view got = much=20 faster.  I did a trace on it.  Now, a few days later, the = view is=20 getting much slower. Yes, an analyze has run since then, doing = an=20 'estimate statistics', but an estimate stats was done after the = rebuilds as well.  The difference in the explain is below = - please=20 note that bitmapped conversion info (in bold):
 
Good=20 plan:
 
..67 .....FIRST=20 ROW
..66 ......VIEW OF 'GAPP_USER. (CARD=3D1 BYTES=3D13 )
..65 = ......SORT=20 (AGGREGATE)  (CARD=3D1 BYTES=3D121 )
..64 ........NESTED = LOOPS =20 (COST=3D53 CARD=3D1 BYTES=3D121 )
..61 .........TABLE ACCESS (BY = INDEX ROWID)=20 OF 'AIF.AIF_OUTBOUND'  (COST=3D51 CARD=3D1 BYTES=3D86 = )
..60=20 ..........BITMAP CONVERSION (TO ROWIDS)
..59 ...........BITMAP=20 AND
..55 ............BITMAP CONVERSION (FROM ROWIDS)
..54=20 .............INDEX (RANGE SCAN) OF 'AIF.AIF_OUTBOUND_IX03'=20 (NON-UNIQUE)  (COST=3D4 )
..58 ............BITMAP = CONVERSION=20 (FROM ROWIDS)
..57 .............SORT (ORDER BY)
..56=20 ..............INDEX (RANGE SCAN) OF 'AIF.AIF_OUTBOUND_IX01'=20 (NON-UNIQUE)  (COST=3D11 )
..63 .........TABLE ACCESS (BY = INDEX ROWID)=20 OF 'AIF.EDI_ROUTE'  (COST=3D1 CARD=3D65 BYTES=3D2275 )
..62=20 ..........INDEX (UNIQUE SCAN) OF 'AIF.EDI_ROUTE_PK' (UNIQUE)  = (CARD=3D65=20 )
 
 
Bad=20 plan:
 
..61 .....FIRST=20 ROW
..60 ......VIEW OF 'GAPP_USER. (CARD=3D1 BYTES=3D13 )
..59 = ......SORT=20 (AGGREGATE)  (CARD=3D1 BYTES=3D121 )
..58 ........NESTED = LOOPS =20 (COST=3D52 CARD=3D1 BYTES=3D121 )
..55 .........TABLE = ACCESS (BY INDEX=20 ROWID) OF 'AIF.AIF_OUTBOUND'  (COST=3D51 CARD=3D1 BYTES=3D86 = )
..54=20 ..........INDEX (RANGE SCAN) OF 'AIF.AIF_OUTBOUND_IX03' = (NON-UNIQUE) =20 (COST=3D3 CARD=3D1 )
..57 .........TABLE ACCESS (BY = INDEX ROWID) OF=20 'AIF.EDI_ROUTE'  (COST=3D1 CARD=3D65 BYTES=3D2275 )
..56 = .........INDEX=20 (UNIQUE SCAN) OF 'AIF.EDI_ROUTE_PK' (UNIQUE)  (CARD=3D65=20 )
 
 
The interesting=20 thing is, the table in question does not have any bit mapped indexes = on=20 it.  Some notes on MetaLink show other people questioning the = same=20 issue, but no concrete info is given.   Any ideas out = there? =20
 
TIA.
 
John

John Fedock
"K" Line = America,=20 Inc.
www.kline.com  =
* john.fedock@us.kline.com


 
------_=_NextPart_001_01C393EC.B93CDF08-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Fedock, John (KAM.RHQ) INET: John.Fedock@us.kline.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).