Home » RDBMS Server » Performance Tuning » SQL tuning -- need help urgent
SQL tuning -- need help urgent [message #247442] Mon, 25 June 2007 22:23 Go to next message
AshaTyagi
Messages: 2
Registered: June 2007
Location: Pune, India
Junior Member
Can some one please help me to tune the below query,this is an alert to get all customers name who have credited 70 to 100 % of their credit amount,this takes 60 mins to execute, we wants to reduce the cost and execution time, since all of the standard tables are used, i believe some of you may can help me out.

Attached is the trace file, that have the query used for the Alert also.



Re: SQL tuning -- need help urgent [message #247444 is a reply to message #247442] Mon, 25 June 2007 22:43 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
from trace file
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       85      0.03       0.03          0          0          0           0
Execute     95      0.07       0.71         52        151        288          61
Fetch       56     42.42     213.03     165790    2639201          0         110
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      236     42.52     213.79     165842    2639352        288         171


I'm having a problem reconciling the fact that this took less than 4 minutes (236 seconds) & the statement below:
>this takes 60 mins to execute,
If the trace file does not contain or reflect the whole problem period, it is borderline useless.

I will conceed that spending 4 minutes to return 171 rows is slightly excessive.

But since a whole lot of details are not present, I'm not going to waste more time on this.
Re: SQL tuning -- need help urgent [message #247477 is a reply to message #247442] Tue, 26 June 2007 01:23 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
You may try following:

1. Change your nested statement to
SELECT 
	   98 org_id, 
	   rc.customer_number, 
	   rc.customer_name, 
	   cpa.currency_code cr_limit_curr_code, 
	   cpa.overall_credit_limit credit_limit, 
	   rc.customer_id, 
	   NVL((SELECT SUM(NVL(apt_ndd.amount_due_remaining,0)) 
	   		FROM AR_PAYMENT_SCHEDULES_ALL apt_ndd 
			WHERE 1=1 
			AND apt_ndd.customer_id = rc.customer_id 
			AND apt_ndd.due_date  = 
			   (SELECT MIN(apt_ndd2.due_date) 
			      FROM AR_PAYMENT_SCHEDULES_ALL apt_ndd2 
                              WHERE 1=1 
                                 AND NVL(apt_ndd2.amount_due_remaining,0) <> 0 
                                 AND apt_ndd2.due_date > SYSDATE
                                 AND apt_ndd2.customer_id = rc.customer_id 
                                 AND apt_ndd2.org_id = 98) 
           ),0)Forecast_ndd, 
        NVL(ttl.amount_due_remaining,0) Open_Receivables , 
        NVL((SELECT SUM(ROUND(ool.pricing_quantity,2) * ROUND(ool.unit_selling_price,2)) 
 			 FROM OE_ORDER_LINES_ALL ool, 
			 	  OE_ORDER_HEADERS_ALL ooh 
			 WHERE ooh.sold_to_org_id = rc.customer_id 
			 AND ool.header_id = ooh.header_id 
			 AND ooh.order_category_code = 'ORDER' 
			 AND ool.flow_status_code IN ('PICKED','AWAITING_SHIPPING') 
			 AND ool.org_id = 98 
            ),0) Open_Sales_Orders 
FROM  apps.RA_CUSTOMERS rc, 
      apps.HZ_CUST_PROFILE_AMTS cpa,
      (SELECT customer_id, SUM(ROUND(aps.amount_due_remaining,2)) amount_due_remaining
       FROM AR_PAYMENT_SCHEDULES_ALL aps
       WHERE org_id = 98
       GROUP BY customer_id) TTL
WHERE 1 = 1 
AND cpa.cust_account_id = rc.customer_id 
AND cpa.currency_code = 'JPY' 
AND rc.status = 'A' AND ttl.customer_id(+) = rc.customer_id


2. Create a new index:
CREATE INDEX ... ON AR_PAYMENT_SCHEDULES_ALL ( customer_id, due_date ) ...

Post new TKPROF. I think you copied some rows twice:

 969148  TABLE ACCESS BY INDEX ROWID AR_PAYMENT_SCHEDULES_ALL (Orphan Entry)
 969148  INDEX RANGE SCAN AR_PAYMENT_SCHEDULES_N6 (object id 28914)(Orphan Entry)
     58   SORT AGGREGATE 
  10967  TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL (Orphan Entry)
1622936  NESTED LOOPS  (Orphan Entry)
 605709  TABLE ACCESS BY INDEX ROWID OE_ORDER_HEADERS_ALL (Orphan Entry)
 615126  INDEX RANGE SCAN OE_ORDER_HEADERS_N2 (object id 41421)(Orphan Entry)
1017169  INDEX RANGE SCAN OE_ORDER_LINES_N1 (object id 41462)(Orphan Entry)
     58   SORT AGGREGATE 
 969148  TABLE ACCESS BY INDEX ROWID AR_PAYMENT_SCHEDULES_ALL (Orphan Entry)
 969148  INDEX RANGE SCAN AR_PAYMENT_SCHEDULES_N6 (object id 28914)(Orphan Entry)
     58   SORT AGGREGATE 
  10967  TABLE ACCESS BY INDEX ROWID OE_ORDER_LINES_ALL (Orphan Entry)
1622936  NESTED LOOPS  (Orphan Entry)
 605709  TABLE ACCESS BY INDEX ROWID OE_ORDER_HEADERS_ALL (Orphan Entry)
 615126  INDEX RANGE SCAN OE_ORDER_HEADERS_N2 (object id 41421)(Orphan Entry)
1017169  INDEX RANGE SCAN OE_ORDER_LINES_N1 (object id 41462)(Orphan Entry)



Michael
Re: SQL tuning -- need help urgent [message #247508 is a reply to message #247477] Tue, 26 June 2007 03:00 Go to previous message
AshaTyagi
Messages: 2
Registered: June 2007
Location: Pune, India
Junior Member
Thanks ! michael
Previous Topic: Finally moving to LMT
Next Topic: spreport - tuning paramters!
Goto Forum:
  


Current Time: Sat Dec 10 10:42:11 CST 2016

Total time taken to generate the page: 0.25213 seconds