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

  • From: "Juan Cachito Reyes Pacheco" <jreyes@xxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 2 Mar 2004 15:35:50 -0400

The problem here is you are doing several things
like != what is forcing to do full scans

What I'll try if this query is really important is
-- Create indexes on all columns you query, toget a full index scan instead
a full table scan.
-- Be sure, there is an index on nvl(seqtable.valid, 'Y')
-- test to add to pid is not null to nvl(pid,'~') ='~' and create a index on
that

I think you have to optimize the access table by table, and then the order
of tables, I agree you should try to find a way, if possible, to avoid
distinct.

----- Original Message ----- 
From: "Jamadagni, Rajendra" <Rajendra.Jamadagni@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Tuesday, March 02, 2004 3:29 PM
Subject: RE: query slow in 9i, but not slow in 8i


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

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