Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> query's explain plan different in 8i and 9i?

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

From: G M <zlmei_at_hotmail.com>
Date: 21 Oct 2003 17:55:08 -0700
Message-ID: <50a5e6b6.0310211655.73df6466@posting.google.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)
Received on Tue Oct 21 2003 - 19:55:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US