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

  • From: "Justin Cave (DDBC)" <jcave@xxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 1 Mar 2004 21:03:59 -0700

You cannot directly compare the cost of two queries-- a query with a cost of 
100 may run significantly faster or significantly slower than a query with a 
cost of 1,000,000.  Two queries with a cost of 100 may take vastly different 
amounts of time to execute.

Are you actually timing the query, or are you just looking at the cost and 
trying to make decisions based on that?

Justin Cave
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Guang Mei
Sent: Monday, March 01, 2004 8:33 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: 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
----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
workarea_size_policy
AUTO

Would this value have some effect on my query?


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@xxxxxxxxxx>
> To: "Oracle-L-freelists" <oracle-l@xxxxxxxxxxxxx>
> 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@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
> > -----------------------------------------------------------------
> >
> >
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>

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


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