Home » RDBMS Server » Performance Tuning » sql tuning (oracle 10g)
sql tuning [message #378816] Fri, 02 January 2009 00:38 Go to next message
radhika_naidu
Messages: 5
Registered: January 2007
Junior Member
Hi ,

The following query has to be tuned for increasing performance. can someone help on this.

SELECT OEL.LINE_ID, WSH.INVENTORY_ITEM_ID, WSH.ORGANIZATION_ID, OEL.SHIP_TO_ORG_ID,  
OEL.ACTUAL_SHIPMENT_DATE, WSH.DATE_SCHEDULED, WSH.UNIT_PRICE, OEL.ORDER_QUANTITY_UOM, 
OEL.LATEST_ACCEPTABLE_DATE, WSH.DELIVERY_DETAIL_ID, 
ACCOUNT.PARTY_ID,
WSH.SUBINVENTORY, WSH.RELEASED_STATUS, 
WSH.SHIP_METHOD_CODE, WSH.CREATED_BY,  WSH.LAST_UPDATED_BY, WSH.CREATION_DATE, 
WSH.LAST_UPDATE_DATE, WSH.SHIPPED_QUANTITY,  WSH.REQUESTED_QUANTITY, WSH.NET_WEIGHT,  
WSH.VOLUME, WSH.WEIGHT_UOM_CODE,  WSH.VOLUME_UOM_CODE, WSH.SHIPMENT_PRIORITY_CODE,  
OEH.HEADER_ID, OEH.ORG_ID,  OEH.CONVERSION_RATE, OEH.TRANSACTIONAL_CURR_CODE,  
OEH.SOLD_TO_ORG_ID, 
OEH.SALES_CHANNEL_CODE, 
OEL.INVOICE_TO_ORG_ID,
OEH.ORDERED_DATE,
WSH.FREIGHT_TERMS_CODE,
OEH.PAYMENT_TERM_ID,
OEH.PAYMENT_TYPE_CODE,
OEH.SALESREP_ID,
SITE.TERRITORY_ID,
OEL.SHIPMENT_NUMBER,
OEH.ORDER_NUMBER,
OEL.LINE_NUMBER,
OEH.CONVERSION_TYPE_CODE,
OEL.OPTION_NUMBER, OEL.COMPONENT_NUMBER, WSH.LOCATOR_ID, OEH.ORDER_SOURCE_ID, 
WSH.REQUESTED_QUANTITY_UOM, WSH.PICKABLE_FLAG, WSH.PICKED_QUANTITY,
OEL.LAST_UPDATE_DATE
FROM
WSH_DELIVERY_DETAILS  WSH, OE_ORDER_LINES_ALL OEL, OE_ORDER_HEADERS_ALL OEH, 
HZ_CUST_ACCOUNT_ROLES ACCOUNT, HZ_CUST_SITE_USES_ALL SITE
WHERE
(TRUNC(OEL.LAST_UPDATE_DATE) >  
TO_DATE(:LAST_EXTRACT_DATE, 'MM/DD/YYYY HH24:MI:SS')  OR 
TRUNC(WSH.LAST_UPDATE_DATE) > 
TO_DATE(:LAST_EXTRACT_DATE, 'MM/DD/YYYY HH24:MI:SS')) AND
WSH.SOURCE_CODE = 'OE' AND
WSH.SOURCE_LINE_ID = OEL.LINE_ID  AND
WSH.SOURCE_HEADER_ID = OEH.HEADER_ID  AND
WSH.SHIP_TO_CONTACT_ID = ACCOUNT.CUST_ACCOUNT_ROLE_ID (+) AND
OEL.SHIP_TO_ORG_ID = SITE.SITE_USE_ID (+) AND
OEL.ORG_ID IN ('266','271','272','273') AND OEL.OPEN_FLAG = 'Y' AND OEH.OPEN_FLAG = 'Y'


Regards,
Radhika
Re: sql tuning [message #378838 is a reply to message #378816] Fri, 02 January 2009 01:29 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
With the amount of info provided, probably not!

Please post an explain plan, execution stats and table/index structures. Please also let us know what optimizer you are using and how/ how frequently you collect statistics.
Re: sql tuning [message #378854 is a reply to message #378838] Fri, 02 January 2009 02:51 Go to previous messageGo to next message
radhika_naidu
Messages: 5
Registered: January 2007
Junior Member
explain plan:
please find attachment for the explain plan and cost based optimizer is used.
Re: sql tuning [message #378855 is a reply to message #378854] Fri, 02 January 2009 03:22 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Please remember to upload the attachment.
Re: sql tuning [message #378858 is a reply to message #378855] Fri, 02 January 2009 03:29 Go to previous messageGo to next message
radhika_naidu
Messages: 5
Registered: January 2007
Junior Member
file is uploaded
Re: sql tuning [message #378864 is a reply to message #378858] Fri, 02 January 2009 03:44 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
From the limited info provided it looks like you are doing full table scans on HZ_CUST_SITE_USES_ALL and WSH_DELIVERY_DETAILS.

How big are these tables? Can you index the columns used in your query?
Re: sql tuning [message #378865 is a reply to message #378864] Fri, 02 January 2009 03:47 Go to previous messageGo to next message
radhika_naidu
Messages: 5
Registered: January 2007
Junior Member
These tables are very huge...
no we cann't index the columns
Re: sql tuning [message #378868 is a reply to message #378865] Fri, 02 January 2009 03:50 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
In that case you should expect it to be slow. If you have spare CPUs you can experiment with parallel query to see if it helps or not.
Re: sql tuning [message #378870 is a reply to message #378868] Fri, 02 January 2009 03:52 Go to previous messageGo to next message
radhika_naidu
Messages: 5
Registered: January 2007
Junior Member
can't we use any hints to speed up the query
Re: sql tuning [message #378876 is a reply to message #378870] Fri, 02 January 2009 04:04 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Well, if you analyse your tables and indexes frequently, hints will probably not help.

What hints do you have in mind anyway? INDEX will not work as you don't have any. PARALLEL - maybe, you can experiment with it. You can also try to switch the HASH JOIN to NESTED LOOPS with a USE_NL, etc.

Bottom line, you need to try different methods to see if it helps or not. However, my guess is that you should simply expect it to be slow, as you cannot index the data properly.
Re: sql tuning [message #378905 is a reply to message #378865] Fri, 02 January 2009 08:26 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

These tables are very huge...


Define huge. 1 Million, 10 Million, 1 Billion, etc. Without quantifying it is difficult to explain. Moreover the explain plan you have posted is extremely primitive. It just explains the path but it is not giving us any information about the cardinality. Do you have a TKProf output of the query. Compare it with the plan. If you don't give enough information to the optimizer then it cannot come up with a meaningful plan. In general hints should be used as a last resort and you cannot rely on it because optimizer is evolving in every single release and the hints which could make the query work efficient in the current release could be disastrous in the next releases. So it is always advisable to find out why optimizer is choosing the path which you think is not the right thing to do. For this you need to understand the volumetrics and the predicate information.

All these information are already explained in a more detailed fashion in the very first post of this section. Please read it and come back to us with the requested information. Without that as @Frank already mentioned it is very difficult to help you to sort this performance problem.

Regards

Raj
Previous Topic: SQL/PL/SQL Tunning Hints
Next Topic: Performance issue in procedure due to cursors
Goto Forum:
  


Current Time: Fri Dec 09 13:50:42 CST 2016

Total time taken to generate the page: 0.15840 seconds