Re: Tuning this sql?

Guang Mei,

        3 Table joins, hash join or nested loop join. If the result from geneid = :155928 does not fetch many rows, you can think of create index on joined columns and use nested loops.         

Regards
zhu chao
msn:chao_ping_at_163.com
www.cnoug.org(China Oracle User Group)

• 2003-04-24 19:06:00 ,you wrote£º=======

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

• = = = = = = = = = = = = = = = = = = =

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: chao_ping
  INET: chao_ping_at_vip.163.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).