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

From: Jesse, Rich <Rich.Jesse_at_quadtechworld.com>
Date: Tue, 2 Mar 2004 08:31:03 -0600
Message-ID: <FBE1FCA40ECAD41180400050DA2BC54004E93756@qtiexch2.qgraph.com>

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_at_incyte.com] Sent: Monday, March 01, 2004 10:33 PM
To: oracle-l_at_freelists.org
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_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 Tue Mar 02 2004 - 08:30:00 CST