Re: Select statement tuning - help required
- From: Charles Hooper <hooperc2000@xxxxxxxxx>
- Date: Thu, 08 Nov 2007 17:26:03 -0800
On Nov 7, 10:46 pm, barraboombarrabin <barraboombarrab...@xxxxxxxxx>
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@EDW_WH_EUL_US_TO_PSYM OOH,
HR_ALL_ORGANIZATION_UNITS@EDW_WH_EUL_US_TO_PSYM HAO,
OE_TRANSACTION_TYPES_TL@EDW_WH_EUL_US_TO_PSYM OTT,
OE_ORDER_SOURCES@EDW_WH_EUL_US_TO_PSYM OOS,
OE_ORDER_LINES_ALL@EDW_WH_EUL_US_TO_PSYM OOL,
WSH.WSH_DELIVERY_DETAILS@EDW_WH_EUL_US_TO_PSYM WSD,
APPLSYS.FND_LOOKUP_VALUES@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.
.
- References:
- Select statement tuning - help required
- From: barraboombarrabin
- Re: Select statement tuning - help required
- From: Charles Hooper
- Re: Select statement tuning - help required
- From: barraboombarrabin
- Select statement tuning - help required
- Prev by Date: Re: Lost last space
- Next by Date: Re: Select statement tuning - help required
- Previous by thread: Re: Select statement tuning - help required
- Next by thread: Re: Select statement tuning - help required
- Index(es):
Relevant Pages
|
