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

How did you generate this plan? Was it from a 10046 trace? Are the statistics up to date?

The cardinality in the plan indicates that Oracle believes that only one row will be returned at many of the steps. Oracle will typically try to join the tables with the smallest predicted cardinality near the start of the join process, if possible, yet it is first joining a table with a (predicted) cardinality of 1 with another table with a (predicted) cardinality of 281,092.

If possible, take a look at the plan when the SQL statement is run _from_ the remote site. Does the SQL statement have the same plan, with the same join order. If not, and the SQL statement runs much faster on the remote size, consider listing the tables in the FROM clause in the order in which the tables were joined on the remote site, and then add an ORDERED hint to the SQL statement to see if it makes a difference.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Nov 01 2007 - 15:13:02 CDT