Re: query's explain plan different in 8i and 9i?

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" <ORACLE-L_at_fatcity.com>
> 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: gmei_at_incyte.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_at_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).
> >
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Richard Foote
> INET: richard.foote_at_bigpond.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_at_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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Guang Mei
  INET: gmei_at_incyte.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_at_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).