RE: Optimizing a query
- From: Daniel Fink <danielwfink@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 3 Apr 2006 14:58:04 -0700 (PDT)
Amir,
Remember that cost is an estimate of resource consumption and may or may not
represent what actually happens. The best thing to do is to trace an actual
execution of this query (especially important as you are using bind variables)
and look at the execution plan (STAT lines in the sql trace file). Run tkprof
on the trace file and post the output.
Or you can make a pass at using autotrace (TRACEONLY EXPLAIN STATISTICS) and
post the output.
Look at reducing the number of logical i/os and minimizing throwaway (reading
data from a step that is discarded in a subsequent step).
Regards,
Daniel Fink
"Hameed, Amir" <Amir.Hameed@xxxxxxxxx> wrote: I can paste the entire query
but over 90% of the cost is coming out from this subquery.
SELECT TSCI.ORDER_TYPE, TSCI.DELIVERY_DETAIL_ID, TSCI.SHIP_METHOD_CODE,
TSCI.SHIP_TYPE, TSCI.CUSTOMER_ID, TSCI.SHIP_TO_SITE_USE_ID,
TSCI.SHIP_TO_LOCATION_ID, TSCI.SHIP_TO_CONTACT_ID, TSCI.NET_WEIGHT,
NVL(TO_CHAR(TSCI.SHIP_TO_CONTACT_ID),OEH.CUST_PO_NUMBER) CUST_PO_NUMBER,
TSCI.RELEASED_STATUS, TSCI.ORDER_HEADER_ID, TSCI.ORDER_LINE_ID,
WDA.DELIVERY_ID, WC.FREIGHT_CODE CARRIER_CODE, OEH.ORG_ID,
TSCI.SHIP_FROM_ORGANIZATION_ID, WC.CARRIER_ID, OEH.ORDER_NUMBER,
OEH.CUST_PO_NUMBER CUST_PO_NUM
FROM TXRSCI0_SUPPLIES_CARR_INTF TSCI,
OE_ORDER_HEADERS_ALL OEH,
( SELECT DELIVERY_DETAIL_ID,DELIVERY_ID
FROM
( SELECT MIN(DELIVERY_DETAIL_ID) OVER (PARTITION BY DELIVERY_ID)
MIN_DELIVERY_DET_ID, DELIVERY_DETAIL_ID,DELIVERY_ID
FROM WSH_DELIVERY_ASSIGNMENTS
)
WHERE MIN_DELIVERY_DET_ID = DELIVERY_DETAIL_ID
) WDA,
WSH_CARRIER_SERVICES WCS,
WSH_CARRIERS WC
WHERE PROCESS_STATUS_CODE = 'PENDING' AND
ORDER_TYPE = 'SHIPMENT' AND
TSCI.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID AND
WDA.DELIVERY_ID = NVL(:B3 ,WDA.DELIVERY_ID) AND
OEH.HEADER_ID = TSCI.ORDER_HEADER_ID AND
OEH.ORDER_NUMBER = NVL(:B2 ,OEH.ORDER_NUMBER) AND
WCS.SHIP_METHOD_CODE = TSCI.SHIP_METHOD_CODE AND
WCS.CARRIER_ID = WC.CARRIER_ID AND
WC.FREIGHT_CODE = NVL(:B1 ,WC.FREIGHT_CODE) AND
TSCI.SHIP_METHOD_CODE = WCS.SHIP_METHOD_CODE AND
UPPER(WCS.MODE_OF_TRANSPORT) = 'PARCEL'
I am thinking parallel hint will improve the response time a little bit
better.
---------------------------------
From: LiShan Cheng [mailto:exriscer@xxxxxxxxx]
Sent: Monday, April 03, 2006 5:45 PM
To: Hameed, Amir
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Optimizing a query
Hi
If you dont have any condition then I dont see any possibility of using
index at all?
On 4/3/06, Hameed, Amir <Amir.Hameed@xxxxxxxxx> wrote: Folks,
I need to optimize the following query:
SELECT DELIVERY_DETAIL_ID,DELIVERY_ID
FROM
( SELECT MIN(DELIVERY_DETAIL_ID) OVER (PARTITION BY DELIVERY_ID)
MIN_DELIVERY_DET_ID,
DELIVERY_DETAIL_ID,DELIVERY_I
FROM apps.WSH_DELIVERY_ASSIGNMENTS
)
WHERE MIN_DELIVERY_DET_ID = DELIVERY_DETAIL_ID
/
This table has the following indices:
COL DISTINCT
INDEX NAME COLUMN NAME POS
ROWS
SELECTIVITY
------------------------------ ------------------------- --- -----------
-------
WSH_DELIVERY_ASSIGNMENTS_N1 DELIVERY_ID 1
630,301
8.29
WSH_DELIVERY_ASSIGNMENTS_N2 PARENT_DELIVERY_ID 1 0
0.00
WSH_DELIVERY_ASSIGNMENTS_N3 DELIVERY_DETAIL_ID 1 7,605,650
100.00
WSH_DELIVERY_ASSIGNMENTS_N4 PARENT_DELIVERY_DETAIL_ID 1
377,456
4.96
WSH_DELIVERY_ASSIGNMENTS_U1 DELIVERY_ASSIGNMENT_ID 1 7,605,650
100.00
What would be the best way to optimize it. It currently does a FTS on
this table. Any help will be appreciated.
Thanks
Amir
--
//www.freelists.org/webpage/oracle-l
Other related posts:
