Oracle-L: query's explain plan different in 8i and 9i?

From: Guang Mei <gmei_at_incyte.com>
Date: Tue, 21 Oct 2003 14:44:33 -0800
Message-ID: <F001.005D3E67.20031021144433@fatcity.com>

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;

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)

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). Received on Tue Oct 21 2003 - 17:44:33 CDT