Re: Query Tuning

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 03 Dec 2008 18:01:24 +0100
Message-ID: <4936bb66$0$200$e4fe514c@news.xs4all.nl>

Shakespeare schreef:

> Draki schreef:

>> Hi All,
>> One developer wrote this SQL for Oracle E-Biz vision DB, but it's
>> taking hours to fetch the data. I did last query tuning 4-5 years ago.
>> Any pointer would be appreciated:
>>
>> EXPLAIN PLAN SET STATEMENT_ID = 'ABC' FOR
>> SELECT COUNT(1) FROM OE_ORDER_HEADERS_ALL OOHA WHERE OOHA.ORDER_NUMBER
>>> = 1 AND OOHA.ORDER_NUMBER <= 999999 AND OOHA.ORG_ID = '204'
>> AND TRUNC(OOHA.ORDERED_DATE) >= TRUNC(NVL(TO_DATE('01-JAN-1995','DD-
>> MON-YYYY'),OOHA.ORDERED_DATE)) AND TRUNC(OOHA.ORDERED_DATE) <= TRUNC
>> (NVL(TO_DATE('31-DEC-2007','DD-MON-YYYY'),OOHA.ORDERED_DATE))
>> AND TRUNC(OOHA.CREATION_DATE) >= TRUNC(NVL(TO_DATE('01-JAN-1995','DD-
>> MON-YYYY'),OOHA.CREATION_DATE)) AND TRUNC(OOHA.CREATION_DATE) <= TRUNC
>> (NVL(TO_DATE('31-DEC-2007','DD-MON-YYYY'),OOHA.CREATION_DATE))
>> AND
>> NOT EXISTS (SELECT 1 FROM ar.ra_customer_trx_lines_ALL
>> rctl,ar.ra_customer_trx_ALL rct,ar.ar_payment_schedules_ALL aps
>> WHERE rctl.interface_line_attribute1 = OOHA.ORDER_NUMBER
>> AND rctl.interface_line_attribute2 IN (SELECT otl.NAME FROM
>> ont.oe_transaction_types_tl otl
>> WHERE otl.LANGUAGE = (SELECT
>> language_code FROM
>> applsys.fnd_languages WHERE installed_flag = 'B')
>> AND otl.transaction_type_id =
>> OOHA.order_type_id)
>> AND rctl.interface_line_context = 'ORDER ENTRY'
>> AND rctl.customer_trx_id = rct.customer_trx_id
>> AND rct.customer_trx_id = aps.customer_trx_id
>> AND rct.complete_flag = 'N'
>> AND aps.status = 'OP')
>>
>>
>>
>>
>>
>> OPERATION OBJECT
>>
>>
>> SELECT STATEMENT ()
>> SORT (AGGREGATE)
>> FILTER ()
>> TABLE ACCESS (FULL) OE_ORDER_HEADERS_ALL
>> TABLE ACCESS (BY INDEX ROWID) AR_PAYMENT_SCHEDULES_ALL
>> NESTED LOOPS ()
>> NESTED LOOPS ()
>> NESTED LOOPS ()
>> TABLE ACCESS (BY INDEX ROWID) OE_TRANSACTION_TYPES_TL
>> INDEX (UNIQUE SCAN) OE_TRANSACTION_TYPES_TL_U1
>> TABLE ACCESS (BY INDEX ROWID) FND_LANGUAGES
>> INDEX (RANGE SCAN) FND_LANGUAGES_N1
>> TABLE ACCESS (BY INDEX ROWID) RA_CUSTOMER_TRX_LINES_ALL
>> INDEX (RANGE SCAN) ADS_RA_CUSTOMER_TRX_LINES_ALL
>> TABLE ACCESS (BY INDEX ROWID) RA_CUSTOMER_TRX_ALL
>> INDEX (UNIQUE SCAN) RA_CUSTOMER_TRX_U1
>> INDEX (RANGE SCAN) AR_PAYMENT_SCHEDULES_N2
>> SELECT STATEMENT ()
>> SORT (AGGREGATE)
>> FILTER ()
>> TABLE ACCESS (FULL) OE_ORDER_HEADERS_ALL
>> TABLE ACCESS (BY INDEX ROWID) AR_PAYMENT_SCHEDULES_ALL
>> NESTED LOOPS ()
>> NESTED LOOPS ()
>> NESTED LOOPS ()
>> TABLE ACCESS (BY INDEX ROWID) OE_TRANSACTION_TYPES_TL
>> INDEX (UNIQUE SCAN) OE_TRANSACTION_TYPES_TL_U1
>> TABLE ACCESS (BY INDEX ROWID) FND_LANGUAGES
>> INDEX (RANGE SCAN) FND_LANGUAGES_N1
>> TABLE ACCESS (BY INDEX ROWID) RA_CUSTOMER_TRX_LINES_ALL
>> INDEX (RANGE SCAN) ADS_RA_CUSTOMER_TRX_LINES_ALL
>> TABLE ACCESS (BY INDEX ROWID) RA_CUSTOMER_TRX_ALL
>> INDEX (UNIQUE SCAN) RA_CUSTOMER_TRX_U1
>> INDEX (RANGE SCAN) AR_PAYMENT_SCHEDULES_N2
>>
>>
>> TIA.
>> Regards,
>> Navneet

> 
> 
> Looks like you pasted your explain plan twice.
> Anyway,I would check this part (hard to tell without the cost in the 
> plan, though):
> 
> TABLE ACCESS (BY INDEX ROWID)    RA_CUSTOMER_TRX_LINES_ALL
> INDEX (RANGE SCAN)    ADS_RA_CUSTOMER_TRX_LINES_ALL
> 
> to check if it is using the most efficient index.
> Look for an index on interface_line_attribute1 , 
> rctl.interface_line_attribute2,  customer_trx_id   and 
> interface_line_context, and if I had to guess (which I hate by the way) 
> i'd say  customer_trx_id should be the first column in this index.
> I don't know what the index ADS_RA_CUSTOMER_TRX_LINES_ALL columns are, 
> but it looks they are not in correct order for this query. Do you run 
> cost based anyway? I don't see costs here....
> 
> And I wonder what is expected here: TRUNC(NVL(TO_DATE('01-JAN-1995','DD- 
> MON-YYYY'),OOHA.ORDERED_DATE)).....
> 
> Shakespeare

On second thoughts: I think interface_line_attribute1 should be the first column in the index.

Shakespeare Received on Wed Dec 03 2008 - 11:01:24 CST