Query Tuning

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- -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- -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

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 Received on Wed Nov 12 2008 - 13:04:40 CST