Oracle-L: Tuning this sql?

From: Guang Mei <gmei_at_incyte.com>
Date: Thu, 24 Apr 2003 19:06:43 -0800
Message-ID: <F001.005891A3.20030424190643@fatcity.com>

Hi:

The following sql is called a lot in an application. I am trying to see if there is any way to speed it up:

select name, category2gene.id cid
from diseasemodels,
observationdetails, category2gene where geneid = 155928 and diseasemodels.phenotypedetailsid = observationdetails.phenotypedetailsid and category2gene.id = category2geneid order by name asc;

The execution plan and "10046 trace name context forever, level 12" are:

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=105 Card=4 Bytes=168
          )

   1    0   SORT (ORDER BY) (Cost=105 Card=4 Bytes=168)
   2    1     HASH JOIN (Cost=52 Card=4 Bytes=168)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY2GENE' (Cost
          =10 Card=11 Bytes=110)

   4    3         INDEX (RANGE SCAN) OF 'CATEGORY2GENE_GENEID_INDEX' (
          NON-UNIQUE) (Cost=3 Card=11)

   5    2       HASH JOIN (Cost=41 Card=11368 Bytes=363776)
   6    5         TABLE ACCESS (FULL) OF 'DISEASEMODELS' (Cost=28 Card
          =4337 Bytes=99751)

   7    5         TABLE ACCESS (FULL) OF 'OBSERVATIONDETAILS' (Cost=11
           Card=33015 Bytes=297135)



=====================

PARSING IN CURSOR #1 len=269 dep=0 uid=19 oct=3 lid=19 tim=2044352232 hv=735974675 ad=
'9a3a2f7c'
select distinct name, category2gene.id cid from diseasemodels,
observationdetails, category2gene where geneid = 155928 and diseasemodels.phenotypedetailsid = observationdetails.phenotypedetailsid and category2gene.id = category2geneid order by name asc END OF STMT
PARSE #1:c=0,e=1,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=2044352232 BINDS #1:
EXEC #1:c=1,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=2044352232 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0 FETCH #1:c=11,e=11,p=0,cr=132,cu=8,mis=0,r=1,dep=0,og=4,tim=2044352243 WAIT #1: nam='SQL*Net message from client' ela= 1 p1=1650815232 p2=1 p3=0 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0 FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=2044352244 WAIT #1: nam='SQL*Net message from client' ela= 0 p1=1650815232 p2=1 p3=0 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0 FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=4,tim=2044352244 WAIT #1: nam='SQL*Net message from client' ela= 1 p1=1650815232 p2=1 p3=0 WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1650815232 p2=1 p3=0 FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=12,dep=0,og=4,tim=2044352245 *** 2003-04-24 21:54:46.069
WAIT #1: nam='SQL*Net message from client' ela= 1257 p1=1650815232 p2=1 p3=0

STAT #1 id=1 cnt=43 pid=0 pos=0 obj=0 op='SORT UNIQUE '
STAT #1 id=2 cnt=43 pid=1 pos=1 obj=0 op='HASH JOIN '
STAT #1 id=3 cnt=90 pid=2 pos=1 obj=184472 op='TABLE ACCESS BY INDEX ROWID

CATEGORY2GE
NE '

STAT #1 id=4 cnt=91 pid=3 pos=1 obj=185376 op='INDEX RANGE SCAN '
STAT #1 id=5 cnt=12619 pid=2 pos=2 obj=0 op='HASH JOIN '
STAT #1 id=6 cnt=4337 pid=5 pos=1 obj=184528 op='TABLE ACCESS FULL

DISEASEMODELS '
STAT #1 id=7 cnt=33015 pid=5 pos=2 obj=184773 op='TABLE ACCESS FULL OBSERVATIONDETAILS
'

And here are the row counts for each table:

MT_at_max-SQL> select count(*) from DISEASEMODELS;

COUNT(*)

4337

MT_at_max-SQL> select count(*) from OBSERVATIONDETAILS;

COUNT(*)

33015

MT_at_max-SQL> select count(*) from CATEGORY2GENE;

COUNT(*)

801871

Any suggestions?

Thank you very much.

Guang

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Guang Mei
  INET: gmei_at_incyte.com

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: ListGuru_at_fatcity.com (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 Apr 24 2003 - 22:06:43 CDT