Home » RDBMS Server » Performance Tuning » this query is taking 58 seconds to execute
this query is taking 58 seconds to execute [message #116404] Tue, 19 April 2005 17:33 Go to next message
rajuakula
Messages: 63
Registered: March 2005
Member

I have query as below and the output of the query is 92189 records.
select s1.abc_nbr,s1.1cd,s1.2cd,
s1.3cd,s1.4CD,s1.formula,s1.weight,
s2.reference,s3.srno
from owner1.subs s1,owner1.lot s2,OWNER2.MTAB s3
where s1.cid = s2.cid
and s1.nbr = s3.nbr
and s1.create_dt between to_date('04/19/2003','mm/dd/yyyy') and to_date('04/19/2004','mm/dd/yyyy')
order by s2.reference

In table MTAB srno is the pk and index is created on nbr field.
In table lot index is created on cid field and it is also a fk field from table substance.Index is created on reference field also.

In table subs cid is the pk and index is created on the field nbr.

now when I execute the above query it is taking 58 seconds.
How should I tune this query.I created the required indexes as mentioned above.How should I solve this problem.Even I tried with rule based hint also, but no change.

thanks,



Re: this query is taking 58 seconds to execute [message #116423 is a reply to message #116404] Tue, 19 April 2005 23:38 Go to previous message
chunyuh
Messages: 13
Registered: April 2005
Junior Member

Check the exectuioin plan and make sure the proper join sequence and indexes are used.

If you turn on the event 10046, you can get more detail information from the trace file.

Chunyu Hu
http://mtsmart.kmip.net
Previous Topic: lock_sga parameter cause out of memory error
Next Topic: Count oracle user process
Goto Forum:
  


Current Time: Thu Oct 01 03:45:47 CDT 2020