5 Replies Latest reply on Jul 31, 2014 7:07 AM by davpet

    How to fine tune the sql query

    2622443

      Hi To All,

       

       

      This query returns 4 records and its taking too much time of 5 min.

      The table  apps.ap_invoice_distributions_all is taking more time to fetch the data.

      Please help in fine tuning this query.

       

       

       

       

       

       

      SELECT

               inv_iv.item_description Expenditure_Type,

               accta.card_number Amex_Card_No,

               inv_iv.Employee Employee,

               accta.billed_date Statement_Date,

               NVL (inv_iv.expenditure_item_date, NVL (accta.transaction_date, inv_iv.creation_date) ) Transaction_Date,

               accta.billed_amount CC_Trx_Amount,

               inv_iv.amount Expend_Amount,

               inv_iv.invoice_num Invoice_No,

               DECODE (inv_iv.code_combination_id,

                       '-1', '-1',

                       gcc.segment1

                       || '.'

                       || gcc.segment2

                       || '.'

                       || gcc.segment3

                       || '.'

                       || gcc.segment4

                       ) gl_code,

               accta.merchant_name1|| '- '|| accta.merchant_city|| '- ' || accta.merchant_province_state Merchant,

               inv_iv.justification Justification,

               inv_iv.description Purpose_of_Report,

               inv_iv.invoice_amount Invoice_Total,

               TRUNC (SYSDATE) trunc_date_param,

               ' ' description_param,

               inv_iv.credit_card_trx_id credit_card_trx_id

         FROM  apps.ap_credit_card_trxns_all accta,

               apps.gl_code_combinations gcc,

              (SELECT  papf.full_name Employee,

                       aia.invoice_num,

                       aia.invoice_amount,

                       invlines_iv.amount,

                       invlines_iv.posted_flag,

                       invlines_iv.credit_card_trx_id,

                       invlines_iv.expenditure_item_date,

                       aia.org_id,

                       invlines_iv.justification,

                       aia.description,

                       aia.vendor_id,

                       aia.SOURCE,

                       invlines_iv.creation_date,

                       invlines_iv.dist_code_combination_id code_combination_id,

                       invlines_iv.description item_description

               FROM    apps.ap_invoices_all aia,

                       apps.po_vendors pv,

                       apps.per_all_people_f papf,                             

                      (SELECT *

                       FROM   apps.ap_invoice_distributions_all aida

                       WHERE  NVL (TRUNC (aida.expenditure_item_date),

                                   NVL (TRUNC (aida.creation_date),

                                        TO_DATE (TRUNC (SYSDATE), 'DD-MON-YYYY')

                                       )

                                  )

                              BETWEEN '01-JAN-2000'

                              AND     '02-JAN-2000' ) invlines_iv

               WHERE   aia.invoice_id = invlines_iv.invoice_id

               AND     aia.vendor_id = pv.vendor_id

               AND     pv.employee_id = papf.person_id(+)

               AND     papf.effective_start_date(+) <= TRUNC (SYSDATE)

               AND     papf.effective_end_date(+) >= TRUNC (SYSDATE)) inv_iv

         WHERE inv_iv.credit_card_trx_id = accta.trx_id(+)

         AND   inv_iv.code_combination_id = gcc.code_combination_id(+);

        • 1. Re: How to fine tune the sql query
          AmitGargOCE

          is this from application or your custom query? If this is seeded application query then you should open SR, but if it is your custom query then you can use explain plan to find the cost and add joins & indexes to improve the performance. Please open thread in Database: SQL tuning

           

          Thanks

          Amit 

          • 3. Re: How to fine tune the sql query
            ORA-01033

            ap_invoice_distributions_all is likely to be a pretty big table and the thing that would make it most selective (creation_date) is not indexed by default. In any case, it's often a bad idea to apply the operator to the column (i.e. trunc(creation_date)) as it doesn't make best use of stats and also will mean (non-function-based) indexes aren't used.

            • 4. Re: How to fine tune the sql query
              srini_ebs

              Try indexed column to filter rows, may be try using TRX_DATE instead of Creation_date. Because WHO columns are not indexed in OEBS.

              • 5. Re: How to fine tune the sql query
                davpet

                I would rewrite the following portion of the query to

                • Add the where condition to invoices_all table instead of filtering at distribution_all table level.
                • Join the distribution_all table as part of where condition instead of having it as inner query.

                 

                FROM    apps.ap_invoices_all aia,

                                 apps.po_vendors pv,

                                 apps.per_all_people_f papf,                             

                                (SELECT *

                                 FROM   apps.ap_invoice_distributions_all aida

                                 WHERE  NVL (TRUNC (aida.expenditure_item_date),

                                             NVL (TRUNC (aida.creation_date),

                                                  TO_DATE (TRUNC (SYSDATE), 'DD-MON-YYYY')

                                                 )

                                            )

                                        BETWEEN '01-JAN-2000'

                                        AND     '02-JAN-2000' ) invlines_iv