Thanks. It is working now after I made the change. Guang
On Wed, 22 Oct 2003, Richard Foote wrote: > I've just posted the answer to this question at the > comp.databases.oracle.server newsgroup !! > > Here it is again. > > If you look closely at the execution plans, they're performing a BITMAP > CONVERSION rather than using bitmap indexes per se. This where Oracle > converts btree indexes to bitmaps on the fly and uses subsequent and/or row > eliminations. > > The reason for this is behaviour is because a rather important parameter > _B_TREE_BITMAP_PLANS has had it's default changed from false to true. This > parameter has been undocumented for a while but like all these "hidden" > changes, can have unwanted repercussions. > > Cheers > > Richard > > ----- Original Message ----- > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Wednesday, October 22, 2003 8:44 AM > > > > Hi: > > > > I found that a query worked quite well on Oracle 8173 is running very slow > > on Oracle 9i. > > I doubled check init paramters and they are the same. The table involved > > has about 20M rows. The tables has been analyzed in both cases. Is there > > any thing I should look or set in 9i so that query can run as fast as 8i? > > Better yet, what could be the reason that this query is using "BITMAP" > > instead of regular index range scan? TIA. > > > > Guang > > > > PS: The query is > > > > SELECT queryid, subjid, 100.0*identity/matchlen pct from blastresults > > where ((subjspid in (456,789) and queryid = 123) > > or (queryspid in (456,789) and subjid = 123)) > > and (identity/matchlen >= .200 or positive/matchlen >= .400) > > order by blast.pvalToNumber(pval) asc, score desc; > > > > -- 9i: > > > > Execution Plan > > ---------------------------------------------------------- > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6987 Card=1 Bytes=42 > > ) > > > > 1 0 SORT (ORDER BY) (Cost=6987 Card=1 Bytes=42) > > 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRESULTS' (Cost=69 > > 85 Card=1 Bytes=42) > > > > 3 2 BITMAP CONVERSION (TO ROWIDS) > > 4 3 BITMAP OR > > 5 4 BITMAP AND > > 6 5 BITMAP CONVERSION (FROM ROWIDS) > > 7 6 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QUERYID_IN > > DEX' (NON-UNIQUE) (Cost=3) > > > > 8 5 BITMAP OR > > 9 8 BITMAP CONVERSION (FROM ROWIDS) > > 10 9 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SSPID_IN > > DEX' (NON-UNIQUE) (Cost=1528) > > > > 11 8 BITMAP CONVERSION (FROM ROWIDS) > > 12 11 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SSPID_IN > > DEX' (NON-UNIQUE) (Cost=1528) > > > > 13 4 BITMAP AND > > 14 13 BITMAP CONVERSION (FROM ROWIDS) > > 15 14 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SUBJID_IND > > EX' (NON-UNIQUE) (Cost=3) > > > > 16 13 BITMAP OR > > 17 16 BITMAP CONVERSION (FROM ROWIDS) > > 18 17 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QSPID_IN > > DEX' (NON-UNIQUE) (Cost=1282) > > > > 19 16 BITMAP CONVERSION (FROM ROWIDS) > > 20 19 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QSPID_IN > > DEX' (NON-UNIQUE) (Cost=1282) > > > > > > -- 8i: > > > > Execution Plan > > ---------------------------------------------------------- > > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=184 Card=2 Bytes=84) > > 1 0 SORT (ORDER BY) (Cost=184 Card=2 Bytes=84) > > 2 1 CONCATENATION > > 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRESULTS' (Cost= > > 11 Card=1 Bytes=42) > > > > 4 3 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SUBJID_INDEX' (N > > ON-UNIQUE) (Cost=3 Card=1) > > > > 5 2 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRESULTS' (Cost= > > 11 Card=1 Bytes=42) > > > > 6 5 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QUERYID_INDEX' ( > > NON-UNIQUE) (Cost=3 Card=1) > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Guang Mei > > INET: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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). > > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Richard Foote > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
