RE: Optimizing a query

From: Daniel Fink <danielwfink_at_yahoo.com>
Date: Mon, 3 Apr 2006 14:58:04 -0700 (PDT)
Message-ID: <20060403215804.72146.qmail@web37206.mail.mud.yahoo.com>

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_at_xerox.com> 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_at_gmail.com] Sent: Monday, April 03, 2006 5:45 PM
To: Hameed, Amir
Cc: oracle-l_at_freelists.org
Subject: Re: Optimizing a query

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_at_xerox.com> 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

--
http://www.freelists.org/webpage/oracle-l






--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 03 2006 - 16:58:04 CDT