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

  • From: "Guang Mei" <gmei@xxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 1 Mar 2004 12:53:24 -0500

Please see my answers in-line.

Guang

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mladen Gogala
Sent: Monday, March 01, 2004 12:29 PM
To: oracle-l@xxxxxxxxxxxxx
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

-- 8i:

MT@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

-- 9i:

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



-- 8i:

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

NAME
----------------------------------------------------------------
VALUE
----------------------------------------------------------------------------
----
hash_area_size
180000000


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

NAME
----------------------------------------------------------------
VALUE
----------------------------------------------------------------------------
----
hash_join_enabled
TRUE


MT@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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2004