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

  • From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 2 Mar 2004 08:31:03 -0600 

This may not help much, but I remember not-so-fondly when we had the same
situation moving from 8.0 RBO to 8.1.7 CBO.  Turns out that the newer
version of Oracle and the newer features we decided to use were less
tolerant of poor design and poorly written SQL.

We have a few DSS-like queries such as yours below that users don't think
perform very well.  But if you look at it, there's not a whole lot for
Oracle to filter on other than speciesid.  Perhaps extreme, but for the
long-term, maybe a redesign is in order?

Like I said, this may not help much.  GL!  :)

Rich



-----Original Message-----
From: Guang Mei [mailto:gmei@xxxxxxxxxx]
Sent: Monday, March 01, 2004 10: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
.....
----------------------------------------------------------------
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:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2004