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

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).