Query performance problem

From: <gcm281p_at_my-deja.com>
Date: Tue, 07 Dec 1999 17:10:37 GMT
Message-ID: <82jf28$hh8$1@nnrp1.deja.com>

I'm having a problem with a query in a stored procedure. When I run the query in SQL*Plus or any other tool it runs in about 1 second. When I put the query in a stored procedure as a cursor the execution time jumps to about an hour. Anybody have any suggestions on where to look?

Thanks,
Greg

Here's the query just in case...

SELECT LC.HSC_ID             HSC_ID,
       OT.ORDER_ID           ORDER_ID,
       OT.ORDER_QTY          ORDER_QTY,
       OT.DAY_DT             TRANSACTION_DT,
       nvl(-1*(ORR.TRANSACTION_AMT +
        OS.LIABILITY_KASH_AMT),0)    POINTS_QTY,
       OT.ITEM_ID            ITEM_ID,
       ORR.TRANSACTION_AMT   TRANSACTION_AMT,
       OS.LIABILITY_KASH_AMT LIABILITY_KASH_AMT
FROM   IWADMIN.ORDER_TRANSACTION_at_iwprod    OT,
       IWADMIN.ORDER_RETURN_at_iwprod         ORR,
       IWADMIN.ORDER_SHIPMENT_at_iwprod       OS,
       LOYADMIN.LOY_CUSTOMER_at_obsprod       LC,
       IWADMIN.CUSTOMER_NUMBER_XREF_at_iwprod XR

WHERE OT.CUSTOMER_ID = XR.CUSTOMER_ID

AND   LC.HSC_ID BETWEEN v_low_limit AND v_up_limit
AND   LC.HSC_ID = XR.SUBSIDIARY_CUSTOMER_ID
AND   OT.ORDER_ID    = OS.ORDER_ID
AND   OT.ORDER_ID    = ORR.ORDER_ID(+)
AND  (ORR.TRANSACTION_AMT+OS.LIABILITY_KASH_AMT)>0
AND   OT.DAY_DT      >= LC.START_DT
AND   OT.DAY_DT      <= v_end_dt

AND OT.STATUS_CODE = 'P'

AND  (ORR.RETURN_REASON_CODE IS NULL OR
      ORR.RETURN_REASON_CODE IN
         (SELECT RETURN_REASON_CODE
          FROM iwadmin.RETURN_REASON_REF_at_iwprod))

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Dec 07 1999 - 11:10:37 CST