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

From: Guang Mei <gmei_at_incyte.com>
Date: Mon, 1 Mar 2004 12:53:24 -0500
Message-ID: <NJEDKDKJDGAKAEKKNEEJEEOBCOAA.gmei@incyte.com>

Please see my answers in-line.

Guang

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mladen Gogala Sent: Monday, March 01, 2004 12:29 PM
To: oracle-l_at_freelists.org
Subject: Re: query slow in 9i, but not slow in 8i

Are all tables analyzed? Do you have index histograms? Did you collect system
statistics?

all application tables are anaylzed, not sure about index histograms, but don't.
system statistics: yes
9i:

SQL> select NAME, Value from v$parameter where name = 'timed_statistics';

NAME

VALUE

timed_statistics
TRUE

MT_at_atlas-SQL> select NAME, Value from v$parameter where name = 'timed_statistics';

NAME

VALUE

timed_statistics
TRUE What is are the values of the following parameters:

- hash_area_size
- hash_join_enabled
- optimizer_index_caching
- optimizer_index_cost_adj

SQL> select NAME, Value from v$parameter where name = 'hash_area_size';

NAME

VALUE

hash_area_size
180000000

SQL> select NAME, Value from v$parameter where name = 'hash_join_enabled';

NAME

VALUE

hash_join_enabled
TRUE SQL> select NAME, Value from v$parameter where name like 'optimizer%';

NAME

VALUE

optimizer_features_enable
9.2.0

optimizer_mode
CHOOSE optimizer_max_permutations
2000

NAME

VALUE

optimizer_index_cost_adj
100

optimizer_index_caching
0

optimizer_dynamic_sampling
1

MT_at_atlas-SQL> select NAME, Value from v$parameter where name = 'hash_area_size';

NAME

VALUE

hash_area_size
180000000

MT_at_atlas-SQL> select NAME, Value from v$parameter where name = 'hash_join_enabled';

NAME

VALUE

hash_join_enabled
TRUE MT_at_atlas-SQL> select NAME, Value from v$parameter where name like 'optimizer%';

NAME

VALUE

optimizer_features_enable
8.1.7

optimizer_mode
CHOOSE optimizer_max_permutations
80000

NAME

VALUE

optimizer_index_cost_adj
100

optimizer_index_caching
0

optimizer_percent_parallel
0

On 03/01/2004 12:09:48 PM, Guang Mei wrote:

> 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:
>
> 1. What is the reason that in 9204 the sort opration costs that high while
> it does not in 8173?
>
> 2. 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:49:27 CST