RE: Tuning this sql?

Guang -

You're joining three tables, but I don't see where you are joining category2gene to either of the other tables.

Carol Bristow
DPRA Inc.
1300 N 17th St Suite 950
Rosslyn, VA 22209
Work: 703-841-8025
Fax: 703-524-9415

-----Original Message-----
Sent: Friday, April 25, 2003 10:10 AM
To: Multiple recipients of list ORACLE-L

Hi:

All the joined columns have already been indexed. All tables are analyzed
also.

Guang

On Thu, 24 Apr 2003, chao_ping wrote:

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

-- 
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: Carol Bristow
  INET: Carol.Bristow_at_dpra.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).