Select statement tuning - help required

From: barraboombarrabin <barraboombarrabing_at_yahoo.com>
Date: Thu, 01 Nov 2007 08:48:18 -0700
Message-ID: <1193932098.612855.223350@57g2000hsv.googlegroups.com>

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 Received on Thu Nov 01 2007 - 10:48:18 CDT