Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> query's explain plan different in 8i and 9i?
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