Re: Query Tuning

On Nov 12, 11:04 am, Draki <gnavn..._at_gmail.com> wrote:
> 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
Note this spot, in my comment below.

> 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

I haven't looked too close, but I'm guessing that some of those conditions on the ordered_date or order_number disallow index usage, if you have one. Try this: create a table as select * from OE_ORDER_HEADERS_ALL OOHA with all those conditions, then do a select with all the and conditions after that on that table. If it's not a not exists problem, that should give a better idea how long it should take, and maybe a plan to sink your teeth into.

Also, state your version to 4 decimal places, and perhaps use the appropriate OEM or EM tool to see the plan.

jg

--
@home.com is bogus.
Bob Saget is dead?