Re: Select statement tuning

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 08 Nov 2007 17:26:03 -0800
Message-ID: <1194571563.193130.199280@q5g2000prf.googlegroups.com>

On Nov 7, 10:46 pm, barraboombarrabin <barraboombarrab..._at_yahoo.com> wrote:
> There is no difference in the plan on the remote site or the local
> site or in the trace.
> When I run the query in SQL Plus, I have observed that the query
> starts returning rows very quickly i.e. in a few seconds, however as
> the records spool, the rate at which they returned slows down and then
> stops. After some time, I get an ORA-1555.
> Can some one tell me how do I analyze this and if I have to make a
> case of changing the retention period for UNDO how do I make that
> case ?

I was hoping to see the trace file to obtain an idea why it is running slow. The behavior where a couple rows at a time are displayed is likely a result of the extensive index based access. I wonder how accurate the cardinality values are - when was the last time you analyzed (using dbms_stats) the table and indexes?

Let's try an experiment with a hint to try a different access path to delay the joining of the table that is expected to return 4,778,564 bytes in 281,092 rows:
SELECT /*+ ORDERED */

  TO_NUMBER(LTRIM(OOH.ATTRIBUTE2,'0')) CUSTOMER,
  TO_NUMBER(LTRIM(HAO.ATTRIBUTE1,'0')) CORP,
  TO_NUMBER(OOL.ATTRIBUTE1) ITEM,
  TO_NUMBER(HAO.ATTRIBUTE3) ADC,

OTT.NAME ORDER_TYPE,

  DECODE(OOL.FLOW_STATUS_CODE, 'AWAITING_SHIPPING', 'RO'
                             , 'BOOKED'           , 'RE'
                             , 'CANCELLED'        , 'CL'
                             , 'CLOSED'           , 'SH'
                             , 'ENTERED'          , 'IN',

OOL.FLOW_STATUS_CODE) STATUS,

  TRUNC(OOH.ORDERED_DATE) ORDERED_DATE,
  TRUNC(OOL.SCHEDULE_ARRIVAL_DATE) IN_STORE_DATE,
  TRUNC(OOL.SCHEDULE_SHIP_DATE) SHIP_DATE,
  OOL.ORDERED_QUANTITY ADJUSTED_QTY,
  OOL.ORDERED_QUANTITY SHIP_TO_QTY,

OOL.ORDERED_QUANTITY ORIGINAL_QTY,
OOL.ORDERED_QUANTITY UP_TO_QTY,

  'N',
  'N',
  'N',
  'N',

OOH.ATTRIBUTE7
FROM

  OE_ORDER_HEADERS_ALL_at_EDW_WH_EUL_US_TO_PSYM OOH,
  HR_ALL_ORGANIZATION_UNITS_at_EDW_WH_EUL_US_TO_PSYM HAO,
  OE_TRANSACTION_TYPES_TL_at_EDW_WH_EUL_US_TO_PSYM OTT,
  OE_ORDER_SOURCES_at_EDW_WH_EUL_US_TO_PSYM OOS,
  OE_ORDER_LINES_ALL_at_EDW_WH_EUL_US_TO_PSYM OOL,

WSH.WSH_DELIVERY_DETAILS_at_EDW_WH_EUL_US_TO_PSYM WSD, APPLSYS.FND_LOOKUP_VALUES_at_EDW_WH_EUL_US_TO_PSYM APL WHERE
OOH.HEADER_ID = OOL.HEADER_ID

  AND OOH.ORDER_SOURCE_ID  = OOS.ORDER_SOURCE_ID
  AND OOS.NAME LIKE 'EDI%'
  AND OOH.SHIP_FROM_ORG_ID = HAO.ORGANIZATION_ID
  AND OOH.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID
  AND OOL.HEADER_ID = WSD.SOURCE_HEADER_ID
  AND OOL.LINE_ID = WSD.SOURCE_LINE_ID
  AND OOL.SHIP_FROM_ORG_ID = WSD.ORGANIZATION_ID
  AND WSD.RELEASED_STATUS = APL.LOOKUP_CODE
  AND APL.LOOKUP_TYPE ='PICK_STATUS'
  AND APL.LOOKUP_CODE IN ('N','R','S','Y','B')
  AND OOL.FLOW_STATUS_CODE IN ('BOOKED','AWAITING_SHIPPING')
  AND OOH.ATTRIBUTE7 IS NOT NULL;

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Nov 08 2007 - 19:26:03 CST

Re: Select statement tuning - help required