Re: Query Tuning

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 03 Dec 2008 17:54:08 +0100
Message-ID: <4936b9b3$0$187$e4fe514c@news.xs4all.nl>

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 Received on Wed Dec 03 2008 - 10:54:08 CST