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

Guang,

One of the problems with 8 -> 9 is that there are quite a few 'hidden' parameters that are introduced or have different default values. One of these _b_tree_bitmap_plans may be causing the problem. I'm not 100% on this (Wolfgang Breitling is the man to provide the definitive answer), but it is worth a shot.

I'd recommend reading "What is new in the CBO" on Wolfgang's website (www.centrexcc.com). It will really help in your migration to 9i.

Daniel Fink

Guang Mei wrote:

> 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: Daniel Fink
  INET: Daniel.Fink_at_Sun.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).