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

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Tue, 2 Mar 2004 14:29:01 -0500
Message-ID: <A186CBDC8B1D61438BC50F1A77E91F73057C1AD4@xchgbrsm1.corp.espn.pvt>

removing redundant "distinct" might help in this query too ...

Raj

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. select standard_disclaimer from company_requirements; QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
Guang Mei wrote:

> 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 - 13:25:52 CST