Home » RDBMS Server » Performance Tuning » Sql query performance (10g)
| Sql query performance [message #378181] |
Mon, 29 December 2008 04:25  |
huda10
Messages: 23
Registered: December 2008
|
Junior Member |
|
|
Hi,
Can you please help me in optimizing the query.Its present cost is 598151.
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 1 =1
AND WSH.SOURCE_HEADER_ID = OEH.HEADER_ID
AND WSH.SOURCE_LINE_ID = OEL.LINE_ID
AND OEH.header_id =oel.header_id
AND OEL.OPEN_FLAG = 'Y'
AND OEH.OPEN_FLAG = 'Y'
AND OEL.ORG_ID IN ('266','271','272','273')
AND (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.SHIP_TO_CONTACT_ID = ACCOUNT.CUST_ACCOUNT_ROLE_ID (+)
AND OEL.SHIP_TO_ORG_ID = SITE.SITE_USE_ID (+).
Attached the screen shot of the explain plan.
-
Attachment: explain.gif
(Size: 75.01KB, Downloaded 1909 times)
|
|
|
|
|
|
| Re: Sql query performance [message #378205 is a reply to message #378182] |
Mon, 29 December 2008 05:51  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
How many rows does it return?
If you removed BOTH of the LAST_UPDATE_DATE predicates, how many rows would it return? i.e. What if there was no date filtering.
If the date predicates reduce the volume by >90% then you might do better to index the date predicates:
SELECT /*+USE_CONCAT*/
...
...
AND (OEL.LAST_UPDATE_DATE > 0.99999 + TRUNC(TO_DATE('$$LAST_EXTRACT_DATE', 'MM/DD/YYYY HH24:MI:SS'))
OR WSH.LAST_UPDATE_DATE > 0.99999 + TRUNC(TO_DATE('$$LAST_EXTRACT_DATE', 'MM/DD/YYYY HH24:MI:SS')))
Of course, make sure that those date columns are indexed.
Ross Leishman
[Updated on: Mon, 29 December 2008 20:58] Report message to a moderator |
|
|
|
| Re: Sql query performance [message #378206 is a reply to message #378182] |
Mon, 29 December 2008 05:52 |
huda10
Messages: 23
Registered: December 2008
|
Junior Member |
|
|
Hi,
My present DB is "Oracle Database 10g Enterprise Edition Release 10.2.0.2.0"
I'm still not able to improve the performance even
after going thru the performance guide.
Thanks ,
Abdul
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Sql query performance [message #378221 is a reply to message #378215] |
Mon, 29 December 2008 06:09 |
huda10
Messages: 23
Registered: December 2008
|
Junior Member |
|
|
When we remove the date predicates and
run the query our system gets stuck.
yes ,depend on the data range the volume gets reduced
>90%.
I've hard coded the date values "LAST_UPDATE_DATE like sysdate"
It took alteast 2 hrs to retrive the output
of 5000 rows.
|
|
|
|
| Re: Sql query performance [message #378224 is a reply to message #378221] |
Mon, 29 December 2008 06:24 |
huda10
Messages: 23
Registered: December 2008
|
Junior Member |
|
|
Ross , I've idenfified that when we remove outer join the
following condition "AND WSH.SHIP_TO_CONTACT_ID = ACCOUNT.CUST_ACCOUNT_ROLE_ID(+)" the cost is reduced to
159541.
With the outer join the cost is 596852.
|
|
|
|
| Re: Sql query performance [message #378303 is a reply to message #378224] |
Mon, 29 December 2008 20:57 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Forget about the Cost - it is meaningless to compare the cost of two different queries.
If you are telling me that you typically run the SQL with $$LAST_EXTRACT_DATE set to a value that limits the matching rows by >90%, then try what I suggested above. The reason I suggested this change is because when you TRUNC() a column, you disable index usage.
is equivalent to col > trunc(some_date) + 0.9999 col >= trunc(some_date) + 1
The first construct - the one you use - will not scan an index, but either of the other two will.
Since you have two date comparisons separated by an OR, you want Oracle to scan them separately. It may do this automatically, but if it doesn't try adding the USE_CONCAT hint.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Tue Jul 02 01:35:21 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
