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

  • From: "MacGregor, Ian A." <ian@xxxxxxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 02 Mar 2004 08:44:39 -0800

The  ORA-01555  error indicates you need to p the value of the UNDO_RETENTION 
parameter.  You MAY also need to increase the UNDO tablespace size.  The error 
does not report the "Query Duration"  accurately.

Ian MacGregor
Stanford Linear Accelerator 
ian@xxxxxxxxxxxxxxxxx

-----Original Message-----
From: Guang Mei [mailto:gmei@xxxxxxxxxx] 
Sent: Monday, March 01, 2004 8:33 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: query slow in 9i, but not slow in 8i

I understand that the cost value does not mean anything. But from my 
experience, when I see a query's cost from explain plan reaches six digits, 
usually something is not right.

Anyway the actual query has much more stuff in it. The sql I showed in my 
orginal post was only part of it and that was the part that slowed the thing 
down.
I can not run the sql on 9i now since they are other stuff running. But the 
orgianl complex query took 20 minutes to finish on 8i, and the exact query 
produced this in 9i's alert log file:

ORA-01555 caused by SQL statement below (Query Duration=199335 sec, SCN:
0x0000.01db218e):
Sun Feb 29 23:20:46 2004
select  distinct accession, id
from    (
select  distinct to_char(PID) accession, identifier.ID
from    mt.genbankinfo, mt.seqtable, mt.identifier
where   seqtable.ID = genbankinfo.SeqtabID
        and     seqtable.GeneID = identifier.GeneID
        and     PID is not null
        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)
        and     nvl(seqtable.valid, 'Y') != 'N'
union all
select  distinct upper(strings.strtok(protein_id, '.')) accession,
        identifier.id
from    mt.genbankinfo, mt.seqtable, mt.identifier
where   seqtable.ID = genbankinfo.SeqtabID
        and     seqtable.GeneID = identifier.GeneID
        and     protein_id is not null
        and     identifier.t
.....


Needless to say, this is the message that got me started to work on this query.

Guang

On Mon, 1 Mar 2004, Justin Cave (DDBC) wrote:

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

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