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. ----- Original Message ----- To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Wednesday, June 11, 2003 11:54 AM > > 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). > > > > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>> > 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 > 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).
