query slow in 9i, but not slow in 8i

From: Guang Mei <gmei_at_incyte.com>
Date: Mon, 1 Mar 2004 12:09:48 -0500
Message-ID: <NJEDKDKJDGAKAEKKNEEJIENOCOAA.gmei@incyte.com>

Hi:

I have a query which gave two very different explain plan on 8173 and 9204. The two instances (on two separate Sun Solaris boxes) both have the same db_file_multiblock_read_count (8), block_size (8k), sort_area_size
(90000000) and sort_area_retained_size (9000000). I narrowed down the part
which causeed this:

select distinct accession2, id from (
select accession2,

Identifier.id
from mt.External_accession, mt.identifier where external_accession.SEQTABLEID = identifier.seqtabid and

        identifier.type != 'A' and
        identifier.speciesid in

(24,31,2,19,18,17,23,21,27,32,20,34,30,22,25,26,28,29)
);

on 9204 (slow):

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=118228 Card=8046044
          Bytes=225289232)

   1    0   SORT (UNIQUE) (Cost=118228 Card=8046044 Bytes=225289232)
   2    1     MERGE JOIN (Cost=1102 Card=8046044 Bytes=225289232)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EXTERNAL_ACCESSION'
          (Cost=826 Card=28898835 Bytes=404583690)

   4    3         INDEX (FULL SCAN) OF 'EXTACC_SEQTABID_INDEX' (NON-UN
          IQUE) (Cost=26 Card=28898835)

   5    2       SORT (JOIN) (Cost=276 Card=51045 Bytes=714630)
   6    5         TABLE ACCESS (FULL) OF 'IDENTIFIER' (Cost=96 Card=51
          045 Bytes=714630)

on 8173 (not slow):

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=77324 Card=6490972 B
          ytes=188238188)

   1    0   SORT (UNIQUE) (Cost=77324 Card=6490972 Bytes=188238188)
   2    1     HASH JOIN (Cost=41347 Card=6490972 Bytes=188238188)
   3    2       TABLE ACCESS (FULL) OF 'IDENTIFIER' (Cost=95 Card=3892
          9 Bytes=545006)

   4    2       TABLE ACCESS (FULL) OF 'EXTERNAL_ACCESSION' (Cost=4111
          4 Card=38102138 Bytes=571532070)

It looks like 9i thinks MERGE JOIN is better than HASH JOIN for the subquery
(which is fine). The problem is that when I have "select distinct
accession2, id from ..." from the outside, the "SORT (UNIQUE)" part makes the cost 100 times higher in 9204 (from 1102 to 118228) while in 8173 it only increases the cost less than two times (from 41347 to 77324).

I tried the a couple of ways in 9i, such as adding a hint /*+ USE_HASH(identifier) */ in the subquery. This did results in the subquery using hash join instead of merge join, but it did not solve the problem. The cost still went to 100 times higher when I put "select distinct accession2, id from ..." there. I aslo changed the init parameter "optimizer_max_permutations" to 80000 for the session but it did not help either.

So my question is:

What is the reason that in 9204 the sort opration costs that high while it does not in 8173?
Any work around?

TIA. Guang

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Mar 01 2004 - 11:06:26 CST