Re: Select statement tuning - help required

On Nov 1, 11:48 am, barraboombarrabin <barraboombarrab..._at_yahoo.com> wrote:
> Hi,
> I have a query which is running longer than I would like to. The query
> used to run in one hour and the run time has increased over time
> currently more than 4 hours. I am pasting the query below.
>
> Question 1: When I look at the explain plan, I see a lot of nested
> loops which probably answers why the time has increased. As data
> volume has increased, the query is looping over more row. So my first
> question is that a correct interpretation ?
>
> Question 2: Now I would like to improve the run time of the query.
> When I run this query just the way it is, the records start spooling
> out in a few seconds. However after having spooled some records, the
> spooling slows down - I mean instead of the data spooling continuously
> a few rows a spooled and then the spooling stops for a few minutes and
> then another few records are spoole and then it stops again. How do I
> interpret this ? How do I find out what what is the query doing in the
> back ground ?
>
> Question 3: I did try to use the hint /*+ USE_HASH (ooh ool wsd) */
> but that caused the query to do full table scan which did not improve
> the run time. I do understand that full table scans are not always
> evil. However I don't know whether they are the best option here. Can
> you give me some idea about how do I decide what is optimal plan ?
> What should I look at to determine whether a full table scan would be
> optimal ?
>
> Question 4: The tables being accessed in this query are re-org'ed
> every few months. If I want to make a case for a re-org to be done on
> these tables what should I look for ?
>
> ---------------------------------------------------------------------------­--------------------------------------------------------------
> SELECT 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
> --Modified by jayakumar to support multi product line added
> product line
> FROM oe_order_headers_all_at_edw_wh_eul_us_to_psym ooh,
> oe_order_lines_all_at_edw_wh_eul_us_to_psym ool,
> oe_order_sources_at_edw_wh_eul_us_to_psym oos,
> hr_all_organization_units_at_edw_wh_eul_us_to_psym hao,
> oe_transaction_types_tl_at_edw_wh_eul_us_to_psym ott,
> 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%' -- Change 4 - Using EDI% to get all
> EDI orders
> 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') -- Change 4 -
> Added lookup code B
> AND ool.flow_status_code IN ('BOOKED','AWAITING_SHIPPING')
> AND ooh.attribute7 IS NOT NULL;
> ---------------------------------------------------------------------------­--------------------------------------------------------------
> Plan
> SELECT STATEMENT REMOTE CHOOSECost: 31,546 Bytes: 166 Cardinality:
> 1
> 19 TABLE ACCESS BY INDEX ROWID ONT.OE_TRANSACTION_TYPES_TL Cost: 2
> Bytes: 19 Cardinality: 1
> 18 NESTED LOOPS Cost: 31,546 Bytes: 166 Cardinality: 1
> 16 NESTED LOOPS Cost: 31,544 Bytes: 147 Cardinality: 1
> 13 NESTED LOOPS Cost: 31,543 Bytes: 137 Cardinality: 1
> 10 NESTED LOOPS Cost: 31,542 Bytes: 122 Cardinality: 1
> 7 NESTED LOOPS Cost: 31,540 Bytes: 86 Cardinality: 1
> 4 NESTED LOOPS Cost: 30,936 Bytes: 12,986 Cardinality: 302
> 1 INDEX RANGE SCAN UNIQUE APPLSYS.FND_LOOKUP_VALUES_U1 Cost:
> 3 Bytes: 26 Cardinality: 1
> 3 TABLE ACCESS BY INDEX ROWID WSH.WSH_DELIVERY_DETAILS Cost:
> 30,933 Bytes: 4,778,564 Cardinality: 281,092
> 2 INDEX RANGE SCAN NON-UNIQUE WSH.WSH_DELIVERY_DETAILS_N8
> Cost: 3,133 Cardinality: 281,092
> 6 TABLE ACCESS BY INDEX ROWID ONT.OE_ORDER_LINES_ALL Cost: 2
> Bytes: 43 Cardinality: 1
> 5 INDEX UNIQUE SCAN UNIQUE ONT.OE_ORDER_LINES_U1 Cost: 1
> Cardinality: 1
> 9 TABLE ACCESS BY INDEX ROWID ONT.OE_ORDER_HEADERS_ALL Cost: 2
> Bytes: 36 Cardinality: 1
> 8 INDEX UNIQUE SCAN UNIQUE ONT.OE_ORDER_HEADERS_U1 Cost: 1
> Cardinality: 1
> 12 TABLE ACCESS BY INDEX ROWID ONT.OE_ORDER_SOURCES Cost: 1
> Bytes: 15 Cardinality: 1
> 11 INDEX UNIQUE SCAN UNIQUE ONT.OE_ORDER_SOURCES_U1 Cardinality:
> 1
> 15 TABLE ACCESS BY INDEX ROWID HR.HR_ALL_ORGANIZATION_UNITS Cost:
> 1 Bytes: 10 Cardinality: 1
> 14 INDEX UNIQUE SCAN UNIQUE HR.HR_ORGANIZATION_UNITS_PK
> Cardinality: 1
> 17 INDEX RANGE SCAN UNIQUE ONT.OE_TRANSACTION_TYPES_TL_U1 Cost: 1
> Cardinality: 1

Looks like all of these tables are being queried accross a dblink. Any reason why these can't be done locally?

If you can do them locally, then a materialized view might be the way to go.

Roger Gorden Received on Thu Nov 01 2007 - 11:49:22 CDT