- check your sort_area_size when you use distinct you need to sort. It might be too
small and you may be sorting to the temp tablespace which is very slow.
- how much does querySeq.use='Y' and otehr where clauses like it limit the number of
rows you are looking for? I have found that if I craete a another table that just has
the rows I need, I can often significantly improve performance.
>
> From: "Niall Litchfield" <[EMAIL PROTECTED]>
> Date: 2003/06/12 Thu PM 01:15:08 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: RE: sql query optimization
>
> Given the low elapsed time for each iteration of the query I wonder if
> the problem might be susceptible to either of the following approaches.
>
> 1. Calling the query less often. I'm guessing from the object names etc
> that this is some sort of scientific analysis program, and it may be
> that you are repeatedly calling the same logic with different binds when
> you could be doing some sort of batch processing.
>
> 2. Creating a Materialized view that will serve the query and could be
> stored if necessary in the keep pool.
>
>
> Niall
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
> > Behalf Of gmei
> > Sent: 10 June 2003 22:59
> > To: Multiple recipients of list ORACLE-L
> > Subject: sql query optimization
> >
> >
> > Hi:
> >
> > I have been trying for two days to see if I could optimize
> > this query without much success. One of the programs here
> > calls this query many many times and I hope I could make it
> > run faster. It typically take about 1 sec to get the result.
> > I have tried using "exists" to replace "in" and the result is
> > not good. All the columns involved in the "where" clause have
> > been indexed. b1 and b2 are bind variables that are passed in.
> >
> > ----
> >
> > select distinct observationlist.geneid, pval, score,
> > Decode(evidenceCode, 3000900, 'E', 3000902, 'P', 3000906),
> > proteomeRefID, Decode(ReferenceType, 'I', 'Y', 'N'), reftarget
> > from mt.dualblastresults, mt.seqtable querySeq, isi.observationlist,
> > isi.termobs
> > where subjID = :b1
> > and queryID = QuerySeq.AASeqID
> > and querySeq.use='Y'
> > and querySeq.geneID=observationlist.geneid
> > and curationStatus='E'
> > and evidenceCode in (3000900,3000902,3000906)
> > and observationlist.id=obsID
> > and target='GeneID'
> > and termobs.termid in (select termid from isi.arc
> > where arctype in (2999999,3000000)
> > start with termid = :b2
> > connect by prior
> > termid=parenttermid) order by mt.blast.pvaltonumber(pval)
> > asc, score desc, geneid,
> > decode(proteomerefid, null, 0, 1) desc;
> >
> > --
> >
> > This query typically returns 10 or less rows.
> > mt.dualblastresults is a view, all others are tables. BTW, I
> > need "distinct" and "order by" in the query.
> >
> > Here is the explain plan and row counts in tables and their
> > definition. Anyone has any suggestions to make it run faster?
> >
> > TIA.
> >
> > Guang
> >
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=715 Card=1 Bytes=124
> > )
> >
> > 1 0 SORT (ORDER BY) (Cost=715 Card=1 Bytes=124)
> > 2 1 SORT (UNIQUE) (Cost=662 Card=1 Bytes=124)
> > 3 2 NESTED LOOPS (Cost=609 Card=1 Bytes=124)
> > 4 3 NESTED LOOPS (Cost=553 Card=1 Bytes=118)
> > 5 4 NESTED LOOPS (Cost=550 Card=1 Bytes=106)
> > 6 5 NESTED LOOPS (Cost=280 Card=30 Bytes=1830)
> > 7 6 VIEW OF 'DUALBLASTRESULTS' (Cost=112 Card=168
> > Bytes=8232)
> >
> > 8 7 UNION-ALL
> > 9 8 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES
> > ULTS' (Cost=102 Card=118 Bytes=2360)
> >
> > 10 9 INDEX (RANGE SCAN) OF 'BLASTRESULTS_SUBJ
> > ID_INDEX' (NON-UNIQUE) (Cost=3 Card=118)
> >
> > 11 8 TABLE ACCESS (BY INDEX ROWID) OF 'BLASTRES
> > ULTS' (Cost=10 Card=50 Bytes=1000)
> >
> > 12 11 INDEX (RANGE SCAN) OF 'BLASTRESULTS_QUER
> > YID_INDEX' (NON-UNIQUE) (Cost=3 Card=50)
> >
> > 13 6 TABLE ACCESS (BY INDEX ROWID) OF 'SEQTABLE' (C
> > ost=1 Card=57344 Bytes=688128)
> >
> > 14 13 INDEX (UNIQUE SCAN) OF 'ST_ASI_UN' (UNIQUE)
> > 15 5 TABLE ACCESS (BY INDEX ROWID) OF 'OBSERVATIONLIS
> > T' (Cost=9 Card=499 Bytes=22455)
> >
> > 16 15 INDEX (RANGE SCAN) OF 'OBSERVATIONLISTGENEID'
> > (NON-UNIQUE) (Cost=2 Card=499)
> >
> > 17 4 TABLE ACCESS (BY INDEX ROWID) OF 'TERMOBS' (Cost=3
> > Card=2388115 Bytes=28657380)
> >
> > 18 17 INDEX (RANGE SCAN) OF 'TERMOBSIDINDEX' (NON-UNIQ
> > UE) (Cost=2 Card=2388115)
> >
> > 19 3 VIEW OF 'VW_NSO_1' (Cost=56 Card=7 Bytes=42)
> > 20 19 SORT (UNIQUE) (Cost=56 Card=7 Bytes=126)
> > 21 20 FILTER
> > 22 21 CONNECT BY
> > 23 22 INDEX (RANGE SCAN) OF 'ARC_TERMID' (NON-UNIQ
> > UE) (Cost=1 Card=2 Bytes=12)
> >
> > 24 22 TABLE ACCESS (BY USER ROWID) OF 'ARC'
> > 25 22 INDEX (RANGE SCAN) OF 'ARC_TYPETERMPARENT' (
> > UNIQUE) (Cost=3 Card=8 Bytes=144)
> >
> >
> >
> > SQL> select count(*) from mt.dualblastresults;
> >
> > COUNT(*)
> > ----------
> > 22332188
> >
> > SQL> select count(*) from mt.seqtable ;
> >
> > COUNT(*)
> > ----------
> > 373505
> >
> > SQL> select count(*) from isi.observationlist;
> >
> > COUNT(*)
> > ----------
> > 2290858
> >
> > SQL> select count(*) from isi.termobs;
> >
> > COUNT(*)
> > ----------
> > 2388115
> >
> > SQL> select count(*) from isi.arc;
> >
> > COUNT(*)
> > ----------
> > 207375
> >
> > SQL> desc mt.dualblastresults
> > Name Null? Type
> > ----------------------------------------- --------
> > -------------------
> > ID NUMBER
> > QUERYID NUMBER
> > SUBJID NUMBER
> > MATCHLEN NUMBER
> > IDENTITY NUMBER
> > POSITIVE NUMBER
> > GAP NUMBER
> > PVAL VARCHAR2(16)
> > SCORE NUMBER
> > QUERYSTART NUMBER
> > QUERYEND NUMBER
> > SUBJSTART NUMBER
> > SUBJEND NUMBER
> > CCOMMENT VARCHAR2(300)
> > BLASTDATE DATE
> > QFRAME NUMBER
> > SFRAME NUMBER
> > QUERYSPID NUMBER
> > SUBJSPID NUMBER
> >
> > SQL> desc mt.seqtable ;
> > Name Null? Type
> > ----------------------------------------- --------
> > --------------------
> > ID NOT NULL NUMBER
> > AASEQID NUMBER
> > DNASEQID NUMBER
> > GENEID NOT NULL NUMBER
> > USE CHAR(1)
> > ALTSPLICE VARCHAR2(128)
> > MUTANT VARCHAR2(128)
> > STRAIN VARCHAR2(128)
> > CDSSTRING VARCHAR2(2000)
> > VALID CHAR(1)
> > GENOMEPROJ CHAR(1)
> > CDNA CHAR(1)
> > PARTIALNTERM CHAR(1)
> > PARTIALCTERM CHAR(1)
> > TRANSID NUMBER
> > CCOMMENT VARCHAR2(300)
> > EST CHAR(1)
> > CLASS VARCHAR2(128)
> > SEQDATE DATE
> > CURID NUMBER
> >
> > SQL> desc isi.observationlist;
> > Name Null? Type
> > ----------------------------------------- --------
> > --------------------
> > ID NOT NULL NUMBER
> > GENEID NUMBER
> > CURATIONTYPE NUMBER
> > PROTEOMEREFID NUMBER
> > SOURCEID NUMBER
> > SOURCETABLE VARCHAR2(25)
> > DESTID NUMBER
> > DESTTABLE VARCHAR2(25)
> > DESTDATE DATE
> > REFERENCETYPE VARCHAR2(1)
> > EVIDENCECODE NUMBER
> > CURATORID NUMBER
> > EDITORID NUMBER
> > UPDATESTAMP DATE
> > CURATIONSTATUS VARCHAR2(1)
> > ORIGINALSTAMP DATE
> > NEXTOBS NUMBER
> > TARGET VARCHAR2(15)
> > REFTARGET VARCHAR2(15)
> > TOOL VARCHAR2(25)
> > OLDGENEID NUMBER
> >
> > SQL> desc isi.termobs;
> > Name Null? Type
> > ----------------------------------------- -------- -----------
> > OBSID NUMBER
> > TERMID NUMBER
> > CONTEXT NUMBER
> >
> > SQL> desc isi.arc
> > Name Null? Type
> > ----------------------------------------- -------- ---------------
> > TERMID NOT NULL NUMBER
> > PARENTTERMID NOT NULL NUMBER
> > OBSID NUMBER
> > ARCTYPE NOT NULL NUMBER
> > ARCID NUMBER
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: gmei
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> > San Diego, California -- Mailing list and web hosting services
> > ---------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
> > and in the message BODY, include a line containing: UNSUB
> > ORACLE-L (or the name of mailing list you want to be removed
> > from). You may also send the HELP command for other
> > information (like subscribing).
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Niall Litchfield
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Thu Jun 12 2003 - 13:36:59 CDT
