| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: sql query optimization
All very good suggestions, but given the low elapsed time and cost figures,
I suspect most of the time is taken up jumping around buffer cache locating
and pinning blocks.
What would help a lot is to eliminate the "table access by index rowid" by including all query columns in your indexes. There will be a hit on inserts/updates/deletes, but presumably an acceptable hit compared to your "many many" selects.
>
> 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:
>
> 1) 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.
>
> 2) Consider a concatenated index (perhaps termid, parenttermid or
> parenttermid,termid - too early for my brain to remember without trying)
>
> 3) 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?
>
> 4) 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.
>
> 5) 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).
>
>
>
>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim 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 Tue Jun 10 2003 - 20:22:55 CDT
 |
 |