5 Replies Latest reply on Feb 21, 2017 4:57 PM by mdtaylor

    List of values very slow

    3401357

      I created a Record Group (List of values) to select the list of suppliers using Oracle Forms.

      The query takes over 5 minutes to return.

      Why is it so slow?

      Does anybody know how I can speed this up?

        • 1. Re: List of values very slow
          mdtaylor

          If you are doing this in forms, enable SQL Trace right before selecting the LOV with Help->Diagnostics->Trace->Trace with Binds, then extract the SQL from the database server and paste into SQL Developer and look at the execution plan.  See if you have merge join cartesian or other reasons for the poor performance.

          • 2. Re: List of values very slow
            3401357

            As you see there i have a request with UNION ALL 4 times

            select TEMP.party_name ,

                   TEMP.vendor_number,

                   TEMP.vendor_id ,

                   TEMP.num_1099 ,

                   TEMP.vat_registration_num ,

                   TEMP.paid_to_name ,

                   TEMP.active ,

                   TEMP.auto_calculate_interest_flag ,

                   TEMP.num_active_pay_sites,

                   TEMP.num_inactive_pay_sites,

                   TEMP.party_id ,

                   TEMP.payment_function ,

                   TEMP.vendor_type_lookup_code,

                   TEMP.employee_number

            from  ( select asup.vendor_name party_name,

                   asup.segment1 vendor_number,

                   asup.vendor_id vendor_id,

                   asup.num_1099  num_1099,

                   asup.vat_registration_num  vat_registration_num,

                   hp.party_name paid_to_name,

                   DECODE(SIGN(TO_DATE(TO_CHAR(asup.START_DATE_ACTIVE,'DD-MM-YYYY'),

                           'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),

                           1, '', DECODE(SIGN(TO_DATE(TO_CHAR(asup.END_DATE_ACTIVE ,'DD-MM-YYYY'),

                           'DD-MM-YYYY') -  TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),

                           -1, '', 0, '', '*')) active,

                   asup.auto_calculate_interest_flag  auto_calculate_interest_flag ,

                   null       num_active_pay_sites,

                   null      num_inactive_pay_sites,

                   asup.party_id party_id ,

                   iep.payment_function  payment_function,

                   asup.vendor_type_lookup_code  vendor_type_lookup_code,

                   emp.employee_num      employee_number  /* Bug 7529759*/

              from ap_suppliers asup,

                   iby_external_payees_all iep,

                   hz_parties hp,

                   per_employees_x emp,

                   ap_supplier_sites_all apss /* Bug 8868947 */

            where (:parameter.pay_in_full_party_id is null   and  asup.party_id=iep.payee_party_id)

               and ((:parameter.pay_in_full_payment_function is null and   iep.payment_function  = iep.payment_function)

                  or (:parameter.pay_in_full_payment_function is not  null and  iep.payment_function  = :parameter.pay_in_full_payment_function))    

               and asup.party_id = iep.payee_party_id and asup.party_id = hp.party_id

               and asup.employee_id = emp.employee_id(+) and asup.vendor_id = apss.vendor_id /* Bug 8868947 */

               and apss.org_id = nvl(nvl(:PAY_SUM_FOLDER.ORG_ID,:PAYMENTS_QF.ORG_ID), apss.org_id) /* Bug 8868947 */

               AND (   iep.supplier_site_id is null OR iep.supplier_site_id = apss.vendor_site_id )      

            group by asup.vendor_name, hp.party_name, asup.segment1, asup.vendor_id, asup.num_1099, asup.vat_registration_num,

                   DECODE(SIGN(TO_DATE(TO_CHAR(asup.START_DATE_ACTIVE,'DD-MM-YYYY'),

                           'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),

                           1, '', DECODE(SIGN(TO_DATE(TO_CHAR(asup.END_DATE_ACTIVE ,'DD-MM-YYYY'),

                           'DD-MM-YYYY') -  TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),

                           -1, '', 0, '', '*')),

                  auto_calculate_interest_flag, asup.party_id, payment_function, asup.vendor_type_lookup_code, emp.employee_num

            UNION ALL

            select asup.vendor_name party_name,

                   asup.segment1 vendor_number,

                   asup.vendor_id vendor_id,

                   asup.num_1099  num_1099,

                   asup.vat_registration_num  vat_registration_num,

                   hp.party_name paid_to_name,

                   DECODE(SIGN(TO_DATE(TO_CHAR(asup.START_DATE_ACTIVE,'DD-MM-YYYY'),

                           'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),

                           1, '', DECODE(SIGN(TO_DATE(TO_CHAR(asup.END_DATE_ACTIVE ,'DD-MM-YYYY'),

                           'DD-MM-YYYY') -  TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),

                           -1, '', 0, '', '*')) active,

                   asup.auto_calculate_interest_flag  auto_calculate_interest_flag ,

                   null       num_active_pay_sites,

                   null      num_inactive_pay_sites,

                   asup.party_id party_id ,

                   iep.payment_function  payment_function,

                   asup.vendor_type_lookup_code  vendor_type_lookup_code,

                   emp.employee_num      employee_number  /* Bug 7529759*/

              from ap_suppliers asup,

                   iby_external_payees_all iep,

                   hz_parties hp,

                   per_employees_x emp,

                   ap_supplier_sites_all apss /* Bug 8868947 */

            where (:parameter.pay_in_full_party_id is not null   and  asup.party_id=:parameter.pay_in_full_party_id)

               and ((:parameter.pay_in_full_payment_function is null and   iep.payment_function  = iep.payment_function)

                  or (:parameter.pay_in_full_payment_function is not  null and  iep.payment_function  = :parameter.pay_in_full_payment_function))    

               and asup.party_id = iep.payee_party_id and asup.party_id = hp.party_id

               and asup.employee_id = emp.employee_id(+) and asup.vendor_id = apss.vendor_id /* Bug 8868947 */

               and apss.org_id = nvl(nvl(:PAY_SUM_FOLDER.ORG_ID,:PAYMENTS_QF.ORG_ID), apss.org_id) /* Bug 8868947 */

               AND (   iep.supplier_site_id is null OR iep.supplier_site_id = apss.vendor_site_id)      

            group by asup.vendor_name, hp.party_name, asup.segment1, asup.vendor_id, asup.num_1099, asup.vat_registration_num,

                   DECODE(SIGN(TO_DATE(TO_CHAR(asup.START_DATE_ACTIVE,'DD-MM-YYYY'),

                           'DD-MM-YYYY') - TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),

                           1, '', DECODE(SIGN(TO_DATE(TO_CHAR(asup.END_DATE_ACTIVE ,'DD-MM-YYYY'),

                           'DD-MM-YYYY') -  TO_DATE(TO_CHAR(SYSDATE,'DD-MM-YYYY'),'DD-MM-YYYY')),

                           -1, '', 0, '', '*')),

                   auto_calculate_interest_flag, asup.party_id, payment_function, asup.vendor_type_lookup_code, emp.employee_num

            UNION ALL

            select hp.party_name party_name,

                   null vendor_number,

                   null vendor_id,

                   hp.jgzz_fiscal_code num_1099,

                   hp.tax_reference vat_registration_num,

                   hp.party_name paid_to_name,

                   null active,

                   null auto_calculate_interest_flag,

                   null num_active_pay_sites,

                   null num_inactive_pay_sites,

                   hp.party_id party_id,

                   iep.payment_function payment_function,

                   null vendor_type_lookup_code,

                   null employee_number

              from hz_parties hp,

                   iby_external_payees_all iep

            where hp.party_id          = iep.payee_party_id

               AND :parameter.pay_in_full_party_id IS NULL

               and ( ( iep.payment_function = :parameter.pay_in_full_payment_function

                       and :parameter.pay_in_full_payment_function is not null

                     )

                   OR :parameter.pay_in_full_payment_function IS NULL

                   )

               and hp.party_id = iep.payee_party_id

               and not exists

                   (select 'x' from ap_suppliers asup, ap_supplier_sites_all apss

                     where asup.party_id = iep.payee_party_id

                       and asup.vendor_id = apss.vendor_id /* Bug 9469788 */

                       and apss.org_id = nvl(nvl(:PAY_SUM_FOLDER.ORG_ID,:PAYMENTS_QF.ORG_ID),apss.org_id)

                   )

            group by hp.party_name, hp.jgzz_fiscal_code, hp.tax_reference, hp.party_id, iep.payment_function

            UNION ALL         

            select  /*+ INDEX(hp  HZ_PARTIES_U1) no_expand */  hp.party_name party_name,

                   null vendor_number,

                   null vendor_id,

                   hp.jgzz_fiscal_code num_1099,

                   hp.tax_reference vat_registration_num,

                   hp.party_name paid_to_name,

                   null active,

                   null auto_calculate_interest_flag,

                   null num_active_pay_sites,

                   null num_inactive_pay_sites,

                   hp.party_id party_id,

                   iep.payment_function payment_function,

                   null vendor_type_lookup_code,

                   null employee_number

              from hz_parties hp,

                   iby_external_payees_all iep

            where hp.party_id          = :parameter.pay_in_full_party_id

               AND :parameter.pay_in_full_party_id IS NOT NULL

               AND ( ( iep.payment_function = :parameter.pay_in_full_payment_function

                       and :parameter.pay_in_full_payment_function is not null

                     )

                   OR :parameter.pay_in_full_payment_function IS NULL

                   )

               and hp.party_id = iep.payee_party_id

               and not exists

                   (select 'x' from ap_suppliers asup , ap_supplier_sites_all apss

                     where asup.party_id = iep.payee_party_id

                       and asup.vendor_id = apss.vendor_id /* Bug 9469788 */

                       and apss.org_id = nvl(nvl(:PAY_SUM_FOLDER.ORG_ID,:PAYMENTS_QF.ORG_ID),apss.org_id)

                   )

            group by hp.party_name, hp.jgzz_fiscal_code, hp.tax_reference, hp.party_id, iep.payment_function         

            order by 1

            )  TEMP

            where  1=1

            • 4. Re: List of values very slow
              John_K

              Looks like you've taken that query from a standard form or process? If so I'd recommend reproducing the performance issue in there and then raising an SR against the standard module.

              • 5. Re: List of values very slow
                mdtaylor

                Do you have any idea what your database version is or if the database parameters are set correctly?  This query takes about .5 seconds against Vision 12.2.4 on 12.1.0.2 RDBMS on a physical Linux server.

                 

                Please have the DBA provide the output of bde_chk_cbo.sql

                 

                bde_chk_cbo.sql - EBS initialization parameters - Healthcheck (Doc ID 174605.1)