From oracle-l-bounce@freelists.org Mon Mar 1 11:46:34 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i21HkYh05630 for ; Mon, 1 Mar 2004 11:46:34 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i21HkUo05625 for ; Mon, 1 Mar 2004 11:46:30 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4D62F394D2E; Mon, 1 Mar 2004 12:47:08 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 01 Mar 2004 12:46:06 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from web80401.mail.yahoo.com (web80401.mail.yahoo.com [66.218.79.56]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 8D439394B84 for ; Mon, 1 Mar 2004 12:46:03 -0500 (EST) Message-ID: <20040301174936.40211.qmail@web80401.mail.yahoo.com> Received: from [216.145.53.41] by web80401.mail.yahoo.com via HTTP; Mon, 01 Mar 2004 09:49:36 PST Date: Mon, 1 Mar 2004 09:49:36 -0800 (PST) From: Sai Selvaganesan Subject: RE: query slow in 9i, but not slow in 8i To: oracle-l@freelists.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-649804037-1078163376=:39829" X-archive-position: 125 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: ssaisundar@sbcglobal.net Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l --0-649804037-1078163376=:39829 Content-Type: text/plain; charset=us-ascii can you try optimizer_features_enable=8.1.7 (ur earlier 8i version) and see whether the explain plan changes . the above can be set at session level. wolfgang talks about a few optimizer changes in 9i from 8i. so this could be a problem. thanks sai Guang Mei wrote: The rowcount are not exact the same, but close. The 9i dataset is loaded using the dump that was a few weeks old. And I ran DBMS_STATS.GATHER_TABLE_STATS after the loading. Guang -- from 8173: MT@atlas-SQL> select count(*) from mt.External_accession; COUNT(*) ---------- 38102138 MT@atlas-SQL> select count(*) from mt.identifier; COUNT(*) ---------- 127836 -- from 9204: SQL> select count(*) from mt.External_accession; COUNT(*) ---------- 36907691 SQL> select count(*) from mt.identifier; COUNT(*) ---------- 127612 -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Powell, Mark D Sent: Monday, March 01, 2004 12:29 PM To: 'oracle-l@freelists.org' Subject: RE: query slow in 9i, but not slow in 8i It would appear from the statistics that there may be a significant difference in the row counts (cardinality) for the tables involved between the two instances. If this is true then this is not a fair comparison. To compare differences in two version of the database the same data really should be used to generate the statistics and plans. -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Guang Mei Sent: Monday, March 01, 2004 12:23 PM To: oracle-l@freelists.org Subject: RE: query slow in 9i, but not slow in 8i Yes, The statistics is update to date on all the tables (in both 8i and 9i). I ran execute DBMS_STATS.GATHER_TABLE_STATS(, ,cascade => TRUE); on all the tables in the schema. Guang -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Juan Cachito Reyes Pacheco Sent: Monday, March 01, 2004 12:11 PM To: oracle-l@freelists.org Subject: Re: query slow in 9i, but not slow in 8i If you have statictis up to date with dbms package. We had a problem with the new views cbo feature try to use the /*+ NO_MERGE */ hint, if this is the same problem we had , this will fix that. Otherwise no idea. ----- Original Message ----- From: "Guang Mei" To: "Oracle-L-freelists" Sent: Monday, March 01, 2004 1:09 PM Subject: query slow in 9i, but not slow in 8i > 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@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 > ----------------------------------------------------------------- > ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 ----------------------------------------------------------------- --0-649804037-1078163376=:39829 Content-Type: text/html; charset=us-ascii
can you try optimizer_features_enable=8.1.7 (ur earlier 8i version)
 
and see whether the explain plan changes .
 
the above can be set at session level.
 
wolfgang talks about a few optimizer changes in 9i from 8i. so this could be a problem.
 
thanks sai

Guang Mei <gmei@incyte.com> wrote:
The rowcount are not exact the same, but close. The 9i dataset is loaded
using the dump that was a few weeks old. And I ran
DBMS_STATS.GATHER_TABLE_STATS after the loading.

Guang


-- from 8173:
MT@atlas-SQL> select count(*) from mt.External_accession;

COUNT(*)
----------
38102138

MT@atlas-SQL> select count(*) from mt.identifier;

COUNT(*)
----------
127836


-- from 9204:

SQL> select count(*) from mt.External_accession;

COUNT(*)
----------
36907691

SQL> select count(*) from mt.identifier;

COUNT(*)
----------
127612

-----Original Message-----
From: oracle-l-bounce@freelists.org
[mailto:oracle-l-bounce@freelists.org]On Behalf Of Powell, Mark D
Sent: Monday, March 01, 2004 12:29 PM
To: 'oracle-l@freelists.org'
Subject: RE: query slow in 9i, but not slow in 8i


It would appear from the statistics that there may be a significant
difference in the row counts (cardinality) for the tables involved between
the two instances. If this is true then this is not a fair comparison. To
compare differences in two version of the database the same data really
should be used to generate the statistics and plans.


-----Original Message-----
From: oracle-l-bounce@freelists.org
[mailto:oracle-l-bounce@freelists.org]On Behalf Of Guang Mei
Sent: Monday, March 01, 2004 12:23 PM
To: oracle-l@freelists.org
Subject: RE: query slow in 9i, but not slow in 8i


Yes, The statistics is update to date on all the tables (in both 8i and 9i).
I ran

execute DBMS_STATS.GATHER_TABLE_STATS(, ,cascade =>
TRUE);

on all the tables in the schema.


Guang

-----Original Message-----
From: oracle-l-bounce@freelists.org
[mailto:oracle-l-bounce@freelists.org]On Behalf Of Juan Cachito Reyes
Pacheco
Sent: Monday, March 01, 2004 12:11 PM
To: oracle-l@freelists.org
Subject: Re: query slow in 9i, but not slow in 8i


If you have statictis up to date with dbms package.

We had a problem with the new views cbo feature
try to use the /*+ NO_MERGE */ hint,
if this is the same problem we had , this will fix that.
Otherwise no idea.

----- Original Message -----
From: "Guang Mei"
To: "Oracle-L-freelists"
Sent: Monday, March 01, 2004 1:09 PM
Subject: query slow in 9i, but not slow in 8i


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


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@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
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@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
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@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
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@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
-----------------------------------------------------------------
--0-649804037-1078163376=:39829-- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@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 -----------------------------------------------------------------