Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: query slow in 9i, but not slow in 8i

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

From: Guang Mei <gmei_at_incyte.com>
Date: Mon, 1 Mar 2004 23:44:16 -0500 (EST)
Message-ID: <Pine.GSO.4.33.0403012342550.18822-100000@bev-ssh> 





in 9i db:



SQL> alter session set sort_area_size = 200000000;



Session altered.



SQL> select NAME,VALUE from v$parameter where name like '%sort%';



NAME





VALUE





nls_sort




sort_area_size


200000000



sort_area_retained_size


9000000




SQL> select NAME,VALUE from v$parameter where name like '%hash%';



NAME





VALUE





hash_join_enabled


TRUE


hash_area_size


400000000




SQL> SET AUTOTRACE TRACEONLY EXPLAIN



SQL> 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));



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)






It did not seem to help.



Guang



On Mon, 1 Mar 2004, Wolfgang Breitling wrote:



> 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

>

> ----------------------------------------------------------------

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


Received on Mon Mar 01 2004 - 22:46:34 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US