| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: query slow in 9i, but not slow in 8i
then you did not properly analyze the tables. look at the cardinality in the plan.
card=estimated rows. estimated runs is determined by dba_tables.num_rows which is populated when tables are analyzed.
do the following
exec dbms_stats_gather_table_stats(ownname=>'blah',tabnam=>'blah',method_opt='FOR ALL COLUMNS',cascade=>true);
on both tables in 9204.
>
> From: "Guang Mei" <gmei_at_incyte.com>
> Date: 2004/03/01 Mon PM 12:44:23 EST
> To: <oracle-l_at_freelists.org>
> Subject: RE: query slow in 9i, but not slow in 8i
>
> 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_at_atlas-SQL> select count(*) from mt.External_accession;
>
> COUNT(*)
> ----------
> 38102138
>
> MT_at_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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Powell, Mark D
> 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
>
>
> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Guang Mei
> Sent: Monday, March 01, 2004 12:23 PM
> To: oracle-l_at_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(<owenr>, <table_name> ,cascade =>
> TRUE);
>
> on all the tables in the schema.
>
>
> Guang
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Juan Cachito Reyes
> Pacheco
> Sent: Monday, March 01, 2004 12:11 PM
> To: oracle-l_at_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" <gmei_at_incyte.com>
> To: "Oracle-L-freelists" <oracle-l_at_freelists.org>
> 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_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
> > -----------------------------------------------------------------
> >
>
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
-- 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 - 12:52:15 CST
 |
 |