Home » RDBMS Server » Performance Tuning » Sql query performance (10g)
Sql query performance [message #378181] Mon, 29 December 2008 04:25 Go to next message
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.
./fa/5497/0/
  • Attachment: explain.gif
    (Size: 75.01KB, Downloaded 731 times)
Re: Sql query performance [message #378182 is a reply to message #378181] Mon, 29 December 2008 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

In the end, read Performances Tuning sticky and provide the requested and usual information.

Regards
Michel

Re: Sql query performance [message #378205 is a reply to message #378182] Mon, 29 December 2008 05:51 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #378207 is a reply to message #378206] Mon, 29 December 2008 05:53 Go to previous messageGo to next message
huda10
Messages: 23
Registered: December 2008
Junior Member
The Date columns are indexed.
Re: Sql query performance [message #378210 is a reply to message #378207] Mon, 29 December 2008 05:56 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Great.

What about the rest of the information I mentioned?
Re: Sql query performance [message #378212 is a reply to message #378207] Mon, 29 December 2008 05:57 Go to previous messageGo to next message
huda10
Messages: 23
Registered: December 2008
Junior Member
Even if we remove the date conditions the cost of the query
is not reduced.
Re: Sql query performance [message #378215 is a reply to message #378212] Mon, 29 December 2008 05:58 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Scroll back up. Read it slowly. Do what it asks.
Re: Sql query performance [message #378221 is a reply to message #378215] Mon, 29 December 2008 06:09 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
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.

TRUNC(col) > some_date
is equivalent to
col > trunc(some_date) + 0.9999
which is also equivalent to
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
Previous Topic: Fetch is taking too long
Next Topic: Merge join Cartesian and Buffer Sort
Goto Forum:
  


Current Time: Mon Dec 05 07:12:58 CST 2016

Total time taken to generate the page: 0.10677 seconds