Re: sql query optimization

From: Mark Richard <mrichard_at_transurban.com.au>
Date: Tue, 10 Jun 2003 16:37:36 -0700
Message-ID: <F001.005AEB79.20030610155450@fatcity.com>

This is an interesting (and relatively complex) query with what I think are several opportunities to tune it. I'd probably spend some time looking at the following to see if they might help you out:

Look at the sub-select with the connect by clause... Try executing that query on it's own and get an idea of it's execution time and the number of rows returned for different bind variables. Depending on the number of distinct values of arctype there may be some scope to optimise this component. Possibly create a table containing on the arctype 2999999 and 3000000 records and then remove this clause from the query - this could avoid accessing the table at all. I have no idea if creating such a table is practical for your scenario though.
Consider a concatenated index (perhaps termid, parenttermid or parenttermid,termid - too early for my brain to remember without trying)
Are the distinct and order by clausing really needed. Often a distinct is included to hide a fault in the query (like a missing join or criteria) - distinct can be very expensive at times but since your query runs fairly fast you probably aren't removing many rows. How many rows does the query return with versus without the distinct clause?
In the order by clause is "mt.blast.pvaltonumber(pval)" This looks like a function call - if you have a way to avoid this function call you may see a performance increase. You could test this by creating a table which stores the calculated result already and modify the query (remember to index and analyze the same as the original table). Does this help? Is it practical to store the result? Again though, the benefit will be determined by the number of rows being ordered and the amount of query time spent doing this - for large data sets a function call is murder though.
Finally, I just realised at the last minute that DUALBLASTRESULTS appears to be a view. Try bypassing the view and going straight to the base tables with the most restrictive criteria you have. Sometimes Oracle doesn't handle views really well within queries. I've seen improvements where the entire logic of the view was moved within the query - it shouldn't have changed anything from a theoretical point of view but it did.

Hopefully this gives you some options to look at.

Regards,

Mark.

                                                                                       
                                               
                      "gmei"                                                           
                                               
                      <[EMAIL PROTECTED]>        To:       Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>                  
                      Sent by:                 cc:                                     
                                               
                      [EMAIL PROTECTED]        Subject:  sql query optimization        
                                               
                      .com                                                             
                                               
                                                                                       
                                               
                                                                                       
                                               
                      11/06/2003 07:59                                                 
                                               
                      Please respond to                                                
                                               
                      ORACLE-L

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).

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
   Privileged/Confidential information may be contained in this message.
          If you are not the addressee indicated in this message
       (or responsible for delivery of the message to such person),
            you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
           by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
                Internet e-mail for messages of this kind.
        Opinions, conclusions and other information in this message
              that do not relate to the official business of
                         Transurban City Link Ltd
         shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Mark Richard
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