Home » RDBMS Server » Performance Tuning » sql tuning (oracle 10g)
| sql tuning [message #378816] |
Fri, 02 January 2009 00:38  |
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  |
Frank Naude
Messages: 4580
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 #378864 is a reply to message #378858] |
Fri, 02 January 2009 03:44 |
Frank Naude
Messages: 4580
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 #378868 is a reply to message #378865] |
Fri, 02 January 2009 03:50 |
Frank Naude
Messages: 4580
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 #378876 is a reply to message #378870] |
Fri, 02 January 2009 04:04 |
Frank Naude
Messages: 4580
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 |
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
|
|
|
|
Goto Forum:
Current Time: Tue Jun 18 15:08:17 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
