| 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: query slow in 9i, but not slow in 8i
At 08:33 PM 3/1/2004, you wrote:
>When I put hint use_hash in the sql, in 9i the subquery did take the hint
>and explain plan showed it used "hash join" instead of "merge join", but
>the cost was higher (than "merge join"), and "SORT (UNIQUE)" still caused
>cost to jump 10 times. Therefore using hint "use_hash" didn't solve my
>problem.
Never mind the cost. They don't mean a thing outside the optimizer. Does the sql perform better with the hint that causes it to use hash join?
>I can not bounce the 9i db to try all the init parameters now since there
>are other programs running. But I strongly suspect the "problem" is not
>due to "hash join" and "merge join" (becuase both showed acceptable cost
>values in explain plan, and "hash join" has been working for this sql for the
>past year or so, never failed), but due to some optimizer change(s) that
>oracle
>made on 9i about sorting of a large view result set.
>
>I have had four cases so far that a sql which ran fast in 8i became
>painfully slow in 9i, I need either setting some obsolete parameters or
>re-write sql to have them run fast in 9i. So far I have not had a
>situation that the some sql automatically runs much faster in 9i than 8i.
>I have spent quite some time "fixing" these situations and I have not had
>a complete successful run on our application yet.
>
>I have not set "optimizer_features_enable=8.1.7" yet, but it looks like I
>might have to.
That should be a very last resort.
>Anyway, here is the workarea_size_policy value in my 9i db:
>
>SQL> select NAME,VALUE from v$parameter where name like '%work%';
>
>NAME
>----------------------------------------------------------------
>VALUE
>--------------------------------------------------------------------------------
>workarea_size_policy
>AUTO
>
>Would this value have some effect on my query?
It could. What is the pga_aggregate_target? I couldn't quickly find what
overrides what:
Does setting sort_area_size or hash_area_size override
workare_size_policy=auto or vice versa?
Does anyone know? (Rhetorical question - someone'll know). I lean towards
the former.
Run a 10053 event trace when explaining the sql - both in 8i and in 9i. It may be too big to post. You can e-mail it to me.
>Guang
>
>
>
>On Tue, 2 Mar 2004, zhu chao wrote:
>
> > hi, guang:
> > If all other SQL performs well in the new 9i instance, I would consider
> > using dbms_stats to transfer the statistics from 8i to 9i and check if the
> > explain plan goes better, if it still does not work, I would use HINT.
> Since
> > using use_hash get the good plan, what do you means by saying: sort
> > opration costs too high? Is it the actual SQL cost more time or just the
> > explain cost get high? IF only the explain cost high, I won't care about it
> > at all.
> > Do you use workarea_size_policy=auto in 9i?
> > If more SQLs get bad, I would consider using
> > optimizer_features_enable=8.1.7 and other optimizer related parameter the
> > same as 8i.
> >
> > Regards
> > Zhu Chao
> >
> > ----- Original Message -----
> > From: "Guang Mei" <gmei_at_incyte.com>
> > To: "Oracle-L-freelists" <oracle-l_at_freelists.org>
> > Sent: Tuesday, March 02, 2004 1:09 AM
> > Subject: query slow in 9i, but not slow in 8iut
> >
> >
> > > 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
>-----------------------------------------------------------------
Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com
-- 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 - 22:03:49 CST
 |
 |