Home » RDBMS Server » Performance Tuning » Tuning of a sql querry
Tuning of a sql querry [message #222114] Thu, 01 March 2007 23:13 Go to next message
donind
Messages: 95
Registered: February 2007
Member
Hi,

Below is my querry. I just need a technique and how a below querry can be tuned

SELECT DISTINCT rop1.routingid,rh.routing_id,rh.start_date,rh.location_id,rh.material
FROM mst_routing_header rh,MST_ROUTINGOPERATION rop1
WHERE (rop1.routingid,rh.material,rh.location_id) IN (SELECT DISTINCT rop.routingid,itm.mfr_item_id,ibr.producedlocationid
FROM MST_ITEM itm,MST_ROUTINGHEADER rthdr,MST_ITEMBOMROUTING ibr,MST_ROUTINGOPERATION rop
WHERE rthdr.routingid =ibr.routingid
AND rthdr.location_id =ibr.producedlocationid
AND ibr.produceditemid=itm.item_id
AND rthdr.routingid=rop.routingid
AND rthdr.location_id=rop.location_id
AND rthdr.routingtype='TEST'
AND ibr.sys_ent_state='ACTIVE'
AND rthdr.sys_ent_state='ACTIVE'
AND rop.sys_ent_state='NEW')
AND rh.sys_ent_state='ACTIVE';

Thanks in advance
Re: Tuning of a sql querry [message #222117 is a reply to message #222114] Thu, 01 March 2007 23:26 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
add to initSID.ora
_make_query_tuned=TRUE.
Re: Tuning of a sql querry [message #222120 is a reply to message #222117] Thu, 01 March 2007 23:38 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
can you explain me little bit briefly about this with a sequence of steps. Where do we find that file. I searched for it in oracle folder but no results displayed.

Thanks for ur reply

[Updated on: Thu, 01 March 2007 23:43]

Report message to a moderator

Re: Tuning of a sql querry [message #222142 is a reply to message #222120] Fri, 02 March 2007 00:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
To get started in SQL Tuning, read the Performance Tuning Manual.

Or you can use this site as a guide to diagnosing the problem with your SQL.

Ross Leishman
Re: Tuning of a sql querry [message #222149 is a reply to message #222142] Fri, 02 March 2007 00:56 Go to previous messageGo to next message
donind
Messages: 95
Registered: February 2007
Member
Any help in tuning this querry

SELECT distinct a.routing_id,a.platform,a.equipment_group,a.equipment_category,a.priority as eqpp ,b.priority as rdtlp
FROM mst_equipment_priority a ,mst_routing_detail b,mst_resource_master c
WHERE a.routing_id=l_routingid
AND a.routing_id=b.routing_id
AND a.platform=b.platform
AND b.platform in (SELECT DISTINCT platform FROM mst_routing_detail WHERE routing_id=l_routingid)
AND a.equipment_group=c.equipment_group
AND a.equipment_category=c.equipment_category
AND c.planning='Y'
AND c.equipment_type =l_equipment_type
AND a.sys_ent_state='ACTIVE'
AND b.sys_ent_state='ACTIVE'
AND c.sys_ent_state='ACTIVE'
ORDER BY a.equipment_category desc,rdtlp asc,eqpp asc;

thanks
Re: Tuning of a sql querry [message #222218 is a reply to message #222149] Fri, 02 March 2007 06:10 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Query tuning is a non-trivial task.
If there was a simple step by step process that you could follow to get a tuned query at the far end, it would be automated.

Tuning is not a boolean process - Queries are not either Tuned or Untuned. There is frequently something else that you could do to to make a query faster, but a law of diminishing returns quickly takes effect.

1) You need to work out why you this query needs tuning (DBA complains of excessive IO, Users say it takes too long to return data)

2) You need to work out when you will have finished tuning it (What level of IO is acceptable, how quickly do the users need it to run)

3) You need to know how to tune queries. Go and read those links @rleishman pointed you to.

If you want us to look at it, then as the sticky says, give us more info.
At a minimum we'll need an Explain Plan for the query, details of what indexes are currently on the columns, sizes of the tables, and some idea of how many rows from each table are expected to be involved in the query.
Previous Topic: parallel DML
Next Topic: when use_hash related hint is used?
Goto Forum:
  


Current Time: Sun Dec 04 22:26:38 CST 2016

Total time taken to generate the page: 0.09414 seconds