| 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
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.
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.
Anyway, here is the workarea_size_policy value in my 9i db:
SQL> select NAME,VALUE from v$parameter where name like '%work%';
NAME
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
> -----------------------------------------------------------------
>
-- 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 - 21:35:48 CST
 |
 |