7 Replies Latest reply: Feb 23, 2014 2:02 PM by AndyH RSS

    APEX Performance issue  due to Pagination

    karthick_muthaiyan

      Hi ,

       

      My Apex version is 4.2.0.00.27 running with APEX Listener 2.0.5 287.04.27 standalone on a Standard Edition Patchset 11.2.0.2.0.

       

      There is a page designed by my developer which is taking 10+ minutes to run an interactive report .  I am able to identify the issue is due to the face that in the pagination tab the value for "Max row Count"

      was given as 200Million . After removing the pagination (making it to null) the report completes in seconds.

       

      I see that APEX converts the Report SQL into

       

      APEX converts the SQL to

       

      select apxws_row_pk, <columns in the report>

      count(*) over () as apxws_row_cnt

      from (

      select *

      from

      (select b.ROWID apxws_row_pk, b.*

      from

      (select *

      from ( ACTUAL SQL ) r

      where rownum <= to_number(:APXWS_MAX_ROW_CNT) order by "IN_TIME",apxws_row_pk

       

       

      And this is when the problem starts and in DB I see CPU wait for this SQL .   The page never works for any value of Row Count  (200, 2000, 200000 ) it works only if it is Null and the SQL is not rewritten by APEX.

       

      I am not sure what is the reason .

       

      Few blogs that I referred but no luck.

      http://oraclequirks.blogspot.com/2010/01/dispatcher-process-taking-99-of-cpu-on.html

      http://www.talkapex.com/2010/10/apex-reports-no-limit-downloads.html

       

      Thanks,

      Karthick M

        • 1. Re: APEX Performance issue  due to Pagination
          AndyH

          Presumably you've checked to see that "IN_TIME" column is indexed and is up to date?

           

          Does the query behave differently outside of the APEX environment?

          • 2. Re: APEX Performance issue  due to Pagination
            karthick_muthaiyan

            I don't understand  but yes if I remove the APEX rewrite part of the SQL and just run the SQL in the DB it completes in seconds. I will verify the SQL in the test environment to see its plan where it is running fine even with the APEX rewrite of the actual SQL.

            • 3. Re: APEX Performance issue  due to Pagination
              karthick_muthaiyan

              Hi Andy,

               

              The same SQL when run in TEST and PROD provides different end Query i think  this is due to the report being an Interactive one and the data is different . But in PROD where I have issues Ithe SQL wait on CPU during execution .

               

              The tkprof result is

               

              call         count       cpu    elapsed       disk      query       current        rows

              -------      ------  -------- ---------- ---------- ----------                      ----------  ----------

              Parse        2      0.00       0.00          0          0                      0           0

              Execute     2      0.00       0.00          0          0                      0           0

              Fetch       17    928.05     931.18      84110  292809761          0          17

              -------       ------  -------- ---------- ---------- ----------                      ----------  ----------

              total       21    928.05     931.18      84110  292809761          0          17

               

              Elapsed times include waiting on following events:

                Event waited on                             Times   Max. Wait  Total Waited

                ----------------------------------------             Waited  ----------  ------------

                db file sequential read                    101260        0.00          1.36

                asynch descriptor resize                        3        0.00          0.00

              • 4. Re: APEX Performance issue  due to Pagination
                karthick_muthaiyan

                And the SQL is

                 

                select

                       apxws_row_pk,

                       "TICKET_NUMBER",

                       "RECEIPT_DATE",

                       "VENDOR_GROUP",

                       "SUPPLIER",

                       "VENDOR_SITE",

                       "ROUTE_NO",

                       "ROUTE_DESC",

                       "IN_TIME_H",

                       "OUT_TIME_H",

                       "TIME_DIFF",

                       "DISPATCH_DATE",

                       "DISPATCH_TIME",

                       "DIFF_DISPATCH_INTIME",

                       "TANKER_NUMBER",

                       "COMPARTMENT",

                       "MILK_GRADE",

                       "STATUS",

                       "APPROVED_BY",

                       "DIVERSION",

                       "CONVERSION_AT",

                       "RECEIPT_CREATED",

                       "RECEIPT_PRINTED",

                       "DISPATCH_WGT_KGS",

                       "DISPATCH_WGT_LTRS",

                       "GROSS_WGT",

                       "GROSS_MANUAL",

                       "TARE_WGT",

                       "TARE_MANUAL",

                       "QTY_ACCEPTED_KGS",

                       "QTY_ACCEPTED_LTRS",

                       "FAT_KGS",

                       "FAT_LTRS",

                       "SNF_KGS",

                       "SNF_LTRS",

                       "DESPATCH_TEMP",

                       "DESPATCH_FAT",

                       "DESPATCH_CLR",

                       "DESPATCH_SNF",

                       "DESPATCH_ACIDITY",

                       "MPS_LAB_REPORT_NUMBER",

                       "FAT",

                       "SNF",

                       "PROTEIN",

                       "ORGANOLEPTIC",

                       "TEMPERATURE",

                       "CLR_DMR",

                       "ACIDITY",

                       "CLOT_ON_BOILING",

                       "ALCOHOL_TEST",

                       "CARBONATE_BICAR_TEST",

                       "UREA_TEST",

                       "SALT_TEST",

                       "GLUCOSE_TEST",

                       "BORAX_TEST",

                       "STARCH_TEST",

                       "MALTODEXTRIN_TEST",

                       "SUGAR_TEST",

                       "SULPHATE_TEST",

                       "VEGOIL_BR_READING",

                       "MINERAL_OIL_TEST",

                       "FORMALEIN_TEST",

                       "H2O2_TEST",

                       "LACTOSE",

                       "SOMATIC_CELLS",

                       "ANTIBIOTIC_TEST",

                       "SULFONAMIDE_TEST1",

                       "SULFONAMIDE_TEST",

                       "AMINOGLYCOSIDE_TEST",

                       "PH_TEST",

                       "SODIUM_ION_TEST",

                       "POTASSIUM_TEST",

                       "SEDIMENT_TEST",

                       "MELAMINE_TEST",

                       "AFLATOXIN_M1",

                       "NITRATE",

                       "NITRITE",

                       "MBRT",

                       "RM_VALUE",

                       "MILKOSCAN_FAT",

                       "MILKOSCAN_SNF",

                       "MILKOSCAN_PROTEIN",

                       "MILKOSCAN_LACTOSE",

                       "MILKOSCAN_TS",

                       "MILKOSCAN_CASIN_PROTEIN",

                       "PROTEIN_ON_SNF_BASIS",

                       "LACTOSE_ON_SNF_BASIS",

                       "FREEZING_POINT",

                       "ADDED_WATER",

                       "DETERGENT",

                       "AMMONIA_BASED_SALT",

                       "IN_TIME",

                       "OUT_TIME",

                       "CHLORAMPHENICOL_TEST",

                       count(*) over () as apxws_row_cnt

                from (

                select  * from (select b.ROWID apxws_row_pk, b.* from (select *  from (

                select r.ticket_number,

                r.receipt_date,

                decode(v.vendor_group,

                'BARAMATI TALUKA',

                'BARAMATI TALUKA',

                'SAHYADRI AGRO',

                'SAHYADRI AGRO',

                'OWN NETWORK - CHILLING CENTER',

                'OWN NETWORK - CHILLING CENTER',

                'OWN NETWORK - BULK COOLER',

                'OWN NETWORK - BULK COOLER',

                'SOLAPUR ZILLA',

                'SOLAPUR ZILLA',

                'OTHERS') vendor_group,

                v.vendor_name supplier,

                vs.vendor_site,

                r.in_time,

                r.out_time,

                to_char(r.in_time, 'HH24:MI') in_time_h,

                to_char(r.out_time, 'HH24:MI') out_time_h,

                substr(TO_CHAR(TO_DATE('20000101', 'YYYYMMDD') +

                (r.out_time - r.in_time),

                'YYYY MM DD HH24:MI:SS'),

                12,

                2) || ':' || substr(TO_CHAR(TO_DATE('20000101', 'YYYYMMDD') +

                (r.out_time - r.in_time),

                'YYYY MM DD HH24:MI:SS'),

                15,

                2) time_diff,

                r.dispatch_date,

                r.dispatch_time,

                --Following code added by ganesh to calculate diff between In_time & Dispatch time.

                CASE WHEN r.dispatch_time is not null THEN

                substr(TO_CHAR(TO_DATE('20000101', 'YYYYMMDD') +

                (in_time - CASE WHEN r.dispatch_date is not null THEN decode(substr(upper(r.dispatch_time),instr(upper(r.dispatch_time),'P',1), 1),

                'P', to_date(to_char(r.dispatch_date, 'DD-MON-YYYY') || ' ' ||

                --CASE WHEN r.dispatch_time IS NOT NULL THEN

                decode(substr(substr(r.dispatch_time, 1, 4), 1,instr(r.dispatch_time, ':') - 1),

                12,12||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                1,13||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                2,14||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                3,15||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                4,16||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                5,17||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                6,18||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                7,19||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                8,20||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                9,21||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                10,22||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                11,23||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':'))

                ),

                --ELSE '00' END, --End Case for dispatch time

                'DD-MON-YYYY HH24:MI:SS'),

                to_date(to_char(r.dispatch_date,'DD-MON-YYYY') ||' '|| -- Indicates AM or am

                nvl(substr(r.dispatch_time, 1, 5),'00'),

                'DD/MM/YYYY HH24:MI:SS'))

                ELSE NULL END ), --End Case for dispatch date

                'YYYY MM DD HH24:MI:SS'),

                12,

                2) || ':' || substr(TO_CHAR(TO_DATE('20000101', 'YYYYMMDD') +

                (in_time - CASE WHEN r.dispatch_date IS NOT NULL THEN decode(substr(upper(r.dispatch_time),instr(upper(r.dispatch_time),'P',1), 1),

                'P', to_date(to_char(r.dispatch_date, 'DD-MON-YYYY') || ' ' ||

                --CASE WHEN r.dispatch_time IS NOT NULL THEN

                decode(substr(substr(r.dispatch_time, 1, 4), 1,instr(r.dispatch_time, ':') - 1),

                12,12||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                1,13||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                2,14||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                3,15||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                4,16||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                5,17||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                6,18||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                7,19||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                8,20||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                9,21||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                10,22||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':')),

                11,23||substr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),

                instr(decode(instr(substr(upper(r.dispatch_time), 1, 5),'P',1), 5,substr(r.dispatch_time, 1, 4),substr(r.dispatch_time, 1, 5)),':'))

                ),

                --ELSE '00' END, --END Case for dispatch time

                'DD-MON-YYYY HH24:MI:SS'),

                to_date(to_char(r.dispatch_date,'DD-MON-YYYY') ||' '|| --Indicates AM or am

                nvl(substr(r.dispatch_time, 1, 5),'00')

                ,'DD/MM/YYYY HH24:MI:SS'))

                ELSE NULL END ), --End Case for dispatch date

                'YYYY MM DD HH24:MI:SS'),

                15,

                2)

                --below code is added by praveen as per new MPS Dispatch_date field.

                when (r.dispatch_date is not null and R.DISPATCH_TIME IS NULL AND  r.receipt_date >= '08-DEC-2011') THEN --new MPS & new dispatch date with timestamp format

                --

                substr(TO_CHAR(TO_DATE('20000101', 'YYYYMMDD') +

                (r.in_time - r.dispatch_date),

                'YYYY MM DD HH24:MI:SS'),

                12,

                2) || ':' || substr(TO_CHAR(TO_DATE('20000101', 'YYYYMMDD') +

                ( in_time - r.dispatch_date),

                'YYYY MM DD HH24:MI:SS'),

                15,

                2)

                --

                ELSE NULL END Diff_Dispatch_Intime,

                r.tanker_number,

                (SELECT SMLV.LOOKUP_VALUE

                FROM SD_MPS_LOOKUP_TYPE SMLT, SD_MPS_LOOKUP_VALUES SMLV

                WHERE SMLV.LOOKUP_TYPE_ID = SMLT.LOOKUP_TYPE_ID

                AND SMLT.LOOKUP_TYPE_NAME = 'COMPARTMENT_TYPE'

                AND SMLT.ENABLED_FLAG = 'Y'

                AND SMLV.ENABLED_FLAG = 'Y'

                AND SMLT.SYSTEM_FLAG IS NULL

                AND SMLV.LOOKUP_CODE = r.compartment) compartment,

                (SELECT SMLV.LOOKUP_VALUE

                FROM SD_MPS_LOOKUP_TYPE SMLT, SD_MPS_LOOKUP_VALUES SMLV

                WHERE SMLV.LOOKUP_TYPE_ID = SMLT.LOOKUP_TYPE_ID

                AND SMLT.LOOKUP_TYPE_NAME = 'MILK_GRADE'

                AND SMLT.ENABLED_FLAG = 'Y'

                AND SMLV.ENABLED_FLAG = 'Y'

                AND SMLT.SYSTEM_FLAG IS NULL

                AND SMLV.LOOKUP_CODE = r.MILK_GRADE) MILK_GRADE,

                (SELECT SMLV.LOOKUP_VALUE

                FROM SD_MPS_LOOKUP_TYPE SMLT, SD_MPS_LOOKUP_VALUES SMLV

                WHERE SMLV.LOOKUP_TYPE_ID = SMLT.LOOKUP_TYPE_ID

                AND SMLT.LOOKUP_TYPE_NAME = 'LAB_ANALYSIS_STATUS'

                AND SMLT.ENABLED_FLAG = 'Y'

                AND SMLV.ENABLED_FLAG = 'Y'

                AND SMLT.SYSTEM_FLAG IS NULL

                AND SMLV.LOOKUP_CODE = l.status) STATUS,

                (SELECT SMLV.LOOKUP_VALUE

                FROM SD_MPS_LOOKUP_TYPE SMLT, SD_MPS_LOOKUP_VALUES SMLV

                WHERE SMLV.LOOKUP_TYPE_ID = SMLT.LOOKUP_TYPE_ID

                AND SMLT.LOOKUP_TYPE_NAME = 'DIVERSION_TYPE'

                AND SMLT.ENABLED_FLAG = 'Y'

                AND SMLV.ENABLED_FLAG = 'Y'

                AND SMLT.SYSTEM_FLAG IS NULL

                AND SMLV.LOOKUP_CODE = r.diversion_type) diversion,

                r.conversion_at,

                ri.route_number Route_no,

                ri.route_desc route_desc,

                (select u.user_description

                from sd_mps_users u

                where u.user_id = l.approved_by) APPROVED_BY,

                (select u.user_description

                from sd_mps_users u

                where u.user_id = r.created_by) Receipt_created,

                (select u.user_description

                from sd_mps_users u

                where u.user_id = r.updated_by) Receipt_printed,

                r.dispatch_wgt_kgs,

                r.dispatch_wgt_ltrs,

                r.gross_wgt,

                r.tare_wgt,

                r.net_wgt_kgs Qty_accepted_kgs,

                r.net_wgt_ltrs Qty_accepted_ltrs,

                (r.net_wgt_kgs * lr.fat) / 100 fat_kgs,

                (r.net_wgt_ltrs * lr.fat) / 100 fat_ltrs,

                (r.net_wgt_kgs * lr.snf) / 100 snf_kgs,

                (r.net_wgt_ltrs * lr.snf) / 100 snf_ltrs,

                (select TO_NUMBER(to_char(vq.test_value, '00.00'))

                from sd_mps_vendor_qc_info vq, sd_mps_tests_info t1

                where vq.mps_receipt_number = r.mps_receipt_number

                and vq.mps_test_id = t1.mps_test_id

                and t1.test_code = 'Temperature') despatch_temp,

                (select TO_NUMBER(to_char(vq.test_value, '00.00'))

                from sd_mps_vendor_qc_info vq, sd_mps_tests_info t1

                where vq.mps_receipt_number = r.mps_receipt_number

                and vq.mps_test_id = t1.mps_test_id

                and t1.test_code = 'Fat') despatch_fat,

                (select TO_NUMBER(to_char(vq.test_value, '00.00'))

                from sd_mps_vendor_qc_info vq, sd_mps_tests_info t1

                where vq.mps_receipt_number = r.mps_receipt_number

                and vq.mps_test_id = t1.mps_test_id

                and t1.test_code = 'CLR/DMR') despatch_clr,

                (select TO_NUMBER(to_char(vq.test_value, '00.00'))

                from sd_mps_vendor_qc_info vq, sd_mps_tests_info t1

                where vq.mps_receipt_number = r.mps_receipt_number

                and vq.mps_test_id = t1.mps_test_id

                and t1.test_code = 'SNF') despatch_snf,

                (select TO_NUMBER(to_char(vq.test_value, '000'))

                from sd_mps_vendor_qc_info vq, sd_mps_tests_info t1

                where vq.mps_receipt_number = r.mps_receipt_number

                and vq.mps_test_id = t1.mps_test_id

                and t1.test_code = 'Acidity') despatch_acidity,

                lr.*,

                decode(r.gross_weight_manual_flag, 'N', 'No', 'Y', 'Yes', 'No') Gross_Manual,

                decode(r.tare_weight_manual_flag, 'N', 'No', 'Y', 'Yes', 'No') Tare_Manual

                from sd_mps_receipt_info r,

                sd_mps_lab_report l,

                sd_mps_route_info ri,

                sd_mps_vendors v,

                sd_mps_vendor_site vs,

                sd_mps_lab_results_v lr

                where r.mps_receipt_number = l.mps_receipt_number

                and r.mps_vendor_id = v.mps_vendor_id

                and r.mps_vendor_site_id = vs.mps_vendor_site_id

                and l.mps_lab_report_number = lr.mps_lab_report_number

                and ri.mps_route_id = r.mps_route_id

                and r.org_id=1

                and (r.status_code = 'APPROVED' or r.status_code = 'REJECTED')

                and r.receipt_date='01-feb-2014'

                ) ) b) r

                where ("RECEIPT_DATE" between to_date(:APXWS_EXPR_1,'YYYYMMDDHH24MISS') and to_date(:APXWS_EXPR2_1,'YYYYMMDDHH24MISS'))

                ) r where rownum <= to_number(:APXWS_MAX_ROW_CNT)

                order by "IN_TIME",apxws_row_pk

                • 5. Re: APEX Performance issue  due to Pagination
                  karthick_muthaiyan

                  And the Row source stats is

                   

                  Misses in library cache during parse: 0

                  Optimizer mode: ALL_ROWS

                  Parsing user id: 84     (recursive depth: 1)

                  Number of plan statistics captured: 1

                   

                   

                  Rows (1st) Rows (avg) Rows (max)  Row Source Operation

                  ---------- ---------- ----------  ---------------------------------------------------

                           2          2          2  NESTED LOOPS  (cr=24 pr=0 pw=0 time=267 us)

                          18         18         18   NESTED LOOPS  (cr=16 pr=0 pw=0 time=168 us cost=3 size=48 card=1)

                           3          3          3    TABLE ACCESS BY INDEX ROWID SD_MPS_LOOKUP_TYPE (cr=7 pr=0 pw=0 time=71 us cost=2 size=22 card=1)

                           3          3          3     INDEX RANGE SCAN MPS_LOOKUP_TYPE_N1 (cr=4 pr=0 pw=0 time=38 us cost=1 size=0 card=1)(object id 74629)

                          18         18         18    INDEX RANGE SCAN MPS_LOOKUP_VALUES_N1 (cr=9 pr=0 pw=0 time=60 us cost=0 size=0 card=4)(object id 74632)

                           2          2          2   TABLE ACCESS BY INDEX ROWID SD_MPS_LOOKUP_VALUES (cr=8 pr=0 pw=0 time=65 us cost=1 size=26 card=1)

                           6          6          6  NESTED LOOPS  (cr=82 pr=0 pw=0 time=1655 us)

                         252        252        252   NESTED LOOPS  (cr=61 pr=0 pw=0 time=1114 us cost=3 size=48 card=1)

                           6          6          6    TABLE ACCESS BY INDEX ROWID SD_MPS_LOOKUP_TYPE (cr=13 pr=0 pw=0 time=114 us cost=2 size=22 card=1)

                           6          6          6     INDEX RANGE SCAN MPS_LOOKUP_TYPE_N1 (cr=7 pr=0 pw=0 time=57 us cost=1 size=0 card=1)(object id 74629)

                         252        252        252    INDEX RANGE SCAN MPS_LOOKUP_VALUES_N1 (cr=48 pr=0 pw=0 time=491 us cost=0 size=0 card=4)(object id 74632)

                           6          6          6   TABLE ACCESS BY INDEX ROWID SD_MPS_LOOKUP_VALUES (cr=21 pr=0 pw=0 time=666 us cost=1 size=26 card=1)

                           1          1          1  NESTED LOOPS  (cr=9 pr=0 pw=0 time=88 us)

                           4          4          4   NESTED LOOPS  (cr=8 pr=0 pw=0 time=46 us cost=3 size=48 card=1)

                           1          1          1    TABLE ACCESS BY INDEX ROWID SD_MPS_LOOKUP_TYPE (cr=3 pr=0 pw=0 time=25 us cost=2 size=22 card=1)

                           1          1          1     INDEX RANGE SCAN MPS_LOOKUP_TYPE_N1 (cr=2 pr=0 pw=0 time=16 us cost=1 size=0 card=1)(object id 74629)

                           4          4          4    INDEX RANGE SCAN MPS_LOOKUP_VALUES_N1 (cr=5 pr=0 pw=0 time=17 us cost=0 size=0 card=4)(object id 74632)

                           1          1          1   TABLE ACCESS BY INDEX ROWID SD_MPS_LOOKUP_VALUES (cr=1 pr=0 pw=0 time=19 us cost=1 size=26 card=1)

                           3          3          3  TABLE ACCESS BY INDEX ROWID SD_MPS_USERS (cr=6 pr=0 pw=0 time=53 us cost=1 size=19 card=1)

                           3          3          3   INDEX UNIQUE SCAN SYS_C0011004 (cr=3 pr=0 pw=0 time=23 us cost=0 size=0 card=1)(object id 74674)

                           1          1          1  NESTED LOOPS  (cr=13 pr=0 pw=0 time=92 us)

                           5          5          5   NESTED LOOPS  (cr=9 pr=0 pw=0 time=52 us cost=3 size=48 card=1)

                           1          1          1    TABLE ACCESS BY INDEX ROWID SD_MPS_LOOKUP_TYPE (cr=3 pr=0 pw=0 time=22 us cost=2 size=22 card=1)

                           1          1          1     INDEX RANGE SCAN MPS_LOOKUP_TYPE_N1 (cr=2 pr=0 pw=0 time=11 us cost=1 size=0 card=1)(object id 74629)

                           5          5          5    INDEX RANGE SCAN MPS_LOOKUP_VALUES_N1 (cr=6 pr=0 pw=0 time=19 us cost=0 size=0 card=4)(object id 74632)

                           1          1          1   TABLE ACCESS BY INDEX ROWID SD_MPS_LOOKUP_VALUES (cr=4 pr=0 pw=0 time=21 us cost=1 size=26 card=1)

                           1          1          1  TABLE ACCESS BY INDEX ROWID SD_MPS_USERS (cr=2 pr=0 pw=0 time=17 us cost=1 size=19 card=1)

                           1          1          1   INDEX UNIQUE SCAN SYS_C0011004 (cr=1 pr=0 pw=0 time=7 us cost=0 size=0 card=1)(object id 74674)

                           3          3          3  TABLE ACCESS BY INDEX ROWID SD_MPS_USERS (cr=6 pr=0 pw=0 time=49 us cost=1 size=19 card=1)

                           3          3          3   INDEX UNIQUE SCAN SYS_C0011004 (cr=3 pr=0 pw=0 time=23 us cost=0 size=0 card=1)(object id 74674)

                      161168     161168     161168  NESTED LOOPS  (cr=5194887 pr=83707 pw=0 time=22587595 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161211 pr=0 pw=0 time=2049488 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=42 pr=0 pw=0 time=762911 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=5033676 pr=83707 pw=0 time=18900721 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=2081192 pr=18174 pw=0 time=20344390 us cost=2 size=0 card=45)(object id 172277)

                      161167     161167     161167  NESTED LOOPS  (cr=4273776 pr=0 pw=0 time=15850847 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161173 pr=0 pw=0 time=2078230 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=4 pr=0 pw=0 time=778328 us cost=0 size=0 card=1)(object id 74657)

                      161167     161167     161167   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4112603 pr=0 pw=0 time=12143751 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1160122 pr=0 pw=0 time=17141345 us cost=2 size=0 card=45)(object id 172277)

                          17         17         17  NESTED LOOPS  (cr=82 pr=6 pw=0 time=1148 us cost=4 size=29 card=1)

                          17         17         17   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=34 pr=0 pw=0 time=260 us cost=1 size=17 card=1)

                          17         17         17    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=17 pr=0 pw=0 time=119 us cost=0 size=0 card=1)(object id 74657)

                          17         17         17   TABLE ACCESS BY INDEX ROWID SD_MPS_VENDOR_QC_INFO (cr=48 pr=6 pw=0 time=707 us cost=3 size=12 card=1)

                          68         68         68    INDEX RANGE SCAN MPS_VENDOR_QC_N1 (cr=31 pr=1 pw=0 time=312 us cost=2 size=0 card=4)(object id 74678)

                          17         17         17  NESTED LOOPS  (cr=82 pr=0 pw=0 time=769 us cost=4 size=29 card=1)

                          17         17         17   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=34 pr=0 pw=0 time=238 us cost=1 size=17 card=1)

                          17         17         17    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=17 pr=0 pw=0 time=106 us cost=0 size=0 card=1)(object id 74657)

                          17         17         17   TABLE ACCESS BY INDEX ROWID SD_MPS_VENDOR_QC_INFO (cr=48 pr=0 pw=0 time=356 us cost=3 size=12 card=1)

                          68         68         68    INDEX RANGE SCAN MPS_VENDOR_QC_N1 (cr=31 pr=0 pw=0 time=215 us cost=2 size=0 card=4)(object id 74678)

                           0          0          0  NESTED LOOPS  (cr=82 pr=0 pw=0 time=701 us cost=4 size=29 card=1)

                          17         17         17   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=34 pr=0 pw=0 time=232 us cost=1 size=17 card=1)

                          17         17         17    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=17 pr=0 pw=0 time=101 us cost=0 size=0 card=1)(object id 74657)

                           0          0          0   TABLE ACCESS BY INDEX ROWID SD_MPS_VENDOR_QC_INFO (cr=48 pr=0 pw=0 time=332 us cost=3 size=12 card=1)

                          68         68         68    INDEX RANGE SCAN MPS_VENDOR_QC_N1 (cr=31 pr=0 pw=0 time=202 us cost=2 size=0 card=4)(object id 74678)

                          17         17         17  NESTED LOOPS  (cr=82 pr=0 pw=0 time=739 us cost=4 size=29 card=1)

                          17         17         17   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=34 pr=0 pw=0 time=229 us cost=1 size=17 card=1)

                          17         17         17    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=17 pr=0 pw=0 time=97 us cost=0 size=0 card=1)(object id 74657)

                          17         17         17   TABLE ACCESS BY INDEX ROWID SD_MPS_VENDOR_QC_INFO (cr=48 pr=0 pw=0 time=337 us cost=3 size=12 card=1)

                          68         68         68    INDEX RANGE SCAN MPS_VENDOR_QC_N1 (cr=31 pr=0 pw=0 time=214 us cost=2 size=0 card=4)(object id 74678)

                          17         17         17  NESTED LOOPS  (cr=82 pr=0 pw=0 time=740 us cost=4 size=29 card=1)

                          17         17         17   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=34 pr=0 pw=0 time=229 us cost=1 size=17 card=1)

                          17         17         17    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=17 pr=0 pw=0 time=101 us cost=0 size=0 card=1)(object id 74657)

                          17         17         17   TABLE ACCESS BY INDEX ROWID SD_MPS_VENDOR_QC_INFO (cr=48 pr=0 pw=0 time=342 us cost=3 size=12 card=1)

                          68         68         68    INDEX RANGE SCAN MPS_VENDOR_QC_N1 (cr=31 pr=0 pw=0 time=200 us cost=2 size=0 card=4)(object id 74678)

                      161168     161168     161168  NESTED LOOPS  (cr=4347314 pr=0 pw=0 time=15686929 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161176 pr=0 pw=0 time=2054443 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=7 pr=0 pw=0 time=778886 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4186138 pr=0 pw=0 time=12016976 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1233652 pr=0 pw=0 time=16891402 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=10054572 pr=0 pw=0 time=26003795 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161173 pr=0 pw=0 time=2055955 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=4 pr=0 pw=0 time=778018 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=9893399 pr=0 pw=0 time=22325352 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=6940608 pr=0 pw=0 time=21447420 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=6571631 pr=0 pw=0 time=19687950 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161176 pr=0 pw=0 time=2046455 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=7 pr=0 pw=0 time=757918 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=6410455 pr=0 pw=0 time=16025751 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=3457975 pr=0 pw=0 time=18089403 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4590730 pr=0 pw=0 time=16180934 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161173 pr=0 pw=0 time=2028914 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=4 pr=0 pw=0 time=755746 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4429557 pr=0 pw=0 time=12540795 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1477070 pr=0 pw=0 time=16798396 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4332544 pr=0 pw=0 time=15545024 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161176 pr=0 pw=0 time=1987034 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=7 pr=0 pw=0 time=723090 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4171368 pr=0 pw=0 time=11949871 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1218881 pr=0 pw=0 time=16713419 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=5077507 pr=0 pw=0 time=17033749 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161173 pr=0 pw=0 time=2032094 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=4 pr=0 pw=0 time=768006 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4916334 pr=0 pw=0 time=13384916 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1963782 pr=0 pw=0 time=17375906 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4314577 pr=0 pw=0 time=15784445 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161176 pr=0 pw=0 time=2014620 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=7 pr=0 pw=0 time=745732 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4153401 pr=0 pw=0 time=12147414 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1200905 pr=0 pw=0 time=16845349 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4278015 pr=0 pw=0 time=15505179 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161173 pr=0 pw=0 time=2046274 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=4 pr=0 pw=0 time=772553 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4116842 pr=0 pw=0 time=11844517 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1164339 pr=0 pw=0 time=16576808 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4107379 pr=0 pw=0 time=15182417 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161176 pr=0 pw=0 time=2041976 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=7 pr=0 pw=0 time=775043 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=3946203 pr=0 pw=0 time=11523189 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=993709 pr=0 pw=0 time=16459308 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4288465 pr=0 pw=0 time=15484481 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161173 pr=0 pw=0 time=2033579 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=4 pr=0 pw=0 time=766903 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4127292 pr=0 pw=0 time=11836255 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1174793 pr=0 pw=0 time=16791433 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4259877 pr=0 pw=0 time=15427246 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161176 pr=0 pw=0 time=2011861 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=7 pr=0 pw=0 time=745889 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4098701 pr=0 pw=0 time=11799524 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1146207 pr=0 pw=0 time=16566347 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4221930 pr=0 pw=0 time=15572326 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161173 pr=0 pw=0 time=2030948 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=4 pr=0 pw=0 time=762572 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4060757 pr=0 pw=0 time=11923680 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1108269 pr=0 pw=0 time=16541799 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4280080 pr=0 pw=0 time=15522026 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161176 pr=0 pw=0 time=2033046 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=7 pr=0 pw=0 time=758280 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4118904 pr=0 pw=0 time=11877233 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1166404 pr=0 pw=0 time=16656131 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4233637 pr=0 pw=0 time=15463382 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161173 pr=0 pw=0 time=2036375 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=4 pr=0 pw=0 time=773387 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4072464 pr=0 pw=0 time=11800220 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1119960 pr=0 pw=0 time=16481951 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4230231 pr=0 pw=0 time=15416909 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161176 pr=0 pw=0 time=2018748 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=7 pr=0 pw=0 time=755603 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4069055 pr=0 pw=0 time=11787131 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1116569 pr=0 pw=0 time=16523113 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4126611 pr=0 pw=0 time=15570371 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161176 pr=0 pw=0 time=2015315 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=7 pr=0 pw=0 time=752754 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=3965435 pr=0 pw=0 time=11940304 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1012951 pr=0 pw=0 time=16800735 us cost=2 size=0 card=45)(object id 172277)

                      160370     160370     160370  NESTED LOOPS  (cr=5837114 pr=0 pw=0 time=18452932 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161179 pr=0 pw=0 time=2010687 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=10 pr=0 pw=0 time=747183 us cost=0 size=0 card=1)(object id 74657)

                      160370     160370     160370   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=5675935 pr=0 pw=0 time=14817489 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=2723437 pr=0 pw=0 time=17919823 us cost=2 size=0 card=45)(object id 172277)

                      160370     160370     160370  NESTED LOOPS  (cr=4646210 pr=0 pw=0 time=16151996 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161190 pr=0 pw=0 time=2012095 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=21 pr=0 pw=0 time=749606 us cost=0 size=0 card=1)(object id 74657)

                      160370     160370     160370   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4485020 pr=0 pw=0 time=12530326 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1532522 pr=0 pw=0 time=16859945 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4094725 pr=0 pw=0 time=15116516 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161207 pr=0 pw=0 time=2005209 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=38 pr=0 pw=0 time=737019 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=3933518 pr=0 pw=0 time=11504395 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=981038 pr=0 pw=0 time=16589346 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4008886 pr=0 pw=0 time=14950747 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161213 pr=0 pw=0 time=2002675 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=44 pr=0 pw=0 time=740107 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=3847673 pr=0 pw=0 time=11330107 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=895196 pr=0 pw=0 time=16422011 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4057807 pr=0 pw=0 time=15165710 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161213 pr=0 pw=0 time=2004109 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=44 pr=0 pw=0 time=745806 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=3896594 pr=0 pw=0 time=11540829 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=944116 pr=0 pw=0 time=16620923 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4361305 pr=0 pw=0 time=15739085 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161213 pr=0 pw=0 time=2017124 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=44 pr=0 pw=0 time=750165 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4200092 pr=0 pw=0 time=12113211 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1247601 pr=0 pw=0 time=17067241 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4623266 pr=0 pw=0 time=16128505 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161224 pr=0 pw=0 time=2006019 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=55 pr=0 pw=0 time=739643 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4462042 pr=0 pw=0 time=12500509 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1509563 pr=0 pw=0 time=16903494 us cost=2 size=0 card=45)(object id 172277)

                       21164      21164      21164  NESTED LOOPS  (cr=10444632 pr=0 pw=0 time=25238174 us cost=4 size=19 card=1)

                      161169     161169     161169   INDEX UNIQUE SCAN SYS_C0010991 (cr=11 pr=0 pw=0 time=727275 us cost=0 size=3 card=1)(object id 74658)

                       21164      21164      21164   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=10444621 pr=0 pw=0 time=23208215 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=7471448 pr=0 pw=0 time=21156316 us cost=2 size=0 card=45)(object id 172277)

                       30783      30783      30783  NESTED LOOPS  (cr=10447303 pr=0 pw=0 time=25320918 us cost=4 size=19 card=1)

                      161169     161169     161169   INDEX UNIQUE SCAN SYS_C0010991 (cr=11 pr=0 pw=0 time=754720 us cost=0 size=3 card=1)(object id 74658)

                       30783      30783      30783   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=10447292 pr=0 pw=0 time=23221979 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=7476647 pr=0 pw=0 time=21137381 us cost=2 size=0 card=45)(object id 172277)

                       30783      30783      30783  NESTED LOOPS  (cr=10603291 pr=0 pw=0 time=26416513 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161190 pr=0 pw=0 time=2035461 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=21 pr=0 pw=0 time=738525 us cost=0 size=0 card=1)(object id 74657)

                       30783      30783      30783   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=10442101 pr=0 pw=0 time=23047382 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=7471445 pr=0 pw=0 time=21116830 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4027584 pr=0 pw=0 time=15063506 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161179 pr=0 pw=0 time=1993720 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=10 pr=0 pw=0 time=737655 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=3866405 pr=0 pw=0 time=11460603 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=913930 pr=0 pw=0 time=16525958 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4131411 pr=0 pw=0 time=15202795 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161354 pr=0 pw=0 time=2004573 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=185 pr=0 pw=0 time=746609 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=3970057 pr=0 pw=0 time=11589233 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1017566 pr=0 pw=0 time=16441681 us cost=2 size=0 card=45)(object id 172277)

                      152209     152209     152209  NESTED LOOPS  (cr=9556999 pr=0 pw=0 time=25207578 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161383 pr=0 pw=0 time=2010913 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=214 pr=0 pw=0 time=745040 us cost=0 size=0 card=1)(object id 74657)

                      152209     152209     152209   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=9395616 pr=0 pw=0 time=21605955 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=6442973 pr=0 pw=0 time=20768677 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=3960285 pr=0 pw=0 time=14929409 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161336 pr=0 pw=0 time=2032062 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=167 pr=0 pw=0 time=752348 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=3798949 pr=0 pw=0 time=11279461 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=846477 pr=0 pw=0 time=16223826 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=3992666 pr=0 pw=0 time=14889884 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161391 pr=0 pw=0 time=2004763 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=222 pr=0 pw=0 time=742331 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=3831275 pr=0 pw=0 time=11277759 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=878788 pr=0 pw=0 time=16457879 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4086673 pr=0 pw=0 time=15145630 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161515 pr=0 pw=0 time=1994201 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=346 pr=0 pw=0 time=733653 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=3925158 pr=0 pw=0 time=11544965 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=972677 pr=0 pw=0 time=16434663 us cost=2 size=0 card=45)(object id 172277)

                       65801      65801      65801  NESTED LOOPS  (cr=10429946 pr=0 pw=0 time=26685200 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161552 pr=0 pw=0 time=2023849 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=383 pr=0 pw=0 time=735759 us cost=0 size=0 card=1)(object id 74657)

                       65801      65801      65801   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=10268394 pr=0 pw=0 time=23244833 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=7306631 pr=0 pw=0 time=21848663 us cost=2 size=0 card=45)(object id 172277)

                       65801      65801      65801  NESTED LOOPS  (cr=10566871 pr=0 pw=0 time=26852952 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161336 pr=0 pw=0 time=2009024 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=167 pr=0 pw=0 time=733163 us cost=0 size=0 card=1)(object id 74657)

                       65801      65801      65801   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=10405535 pr=0 pw=0 time=23435894 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=7443770 pr=0 pw=0 time=21293917 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4256272 pr=0 pw=0 time=15471328 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161336 pr=0 pw=0 time=2017585 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=167 pr=0 pw=0 time=747049 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4094936 pr=0 pw=0 time=11845008 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1142450 pr=0 pw=0 time=16827163 us cost=2 size=0 card=45)(object id 172277)

                           0          0          0  NESTED LOOPS  (cr=392 pr=0 pw=0 time=2002326 us cost=5 size=33 card=1)

                           0          0          0   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=392 pr=0 pw=0 time=1285945 us cost=1 size=17 card=1)

                           0          0          0    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=392 pr=0 pw=0 time=549958 us cost=0 size=0 card=1)(object id 74657)

                           0          0          0   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=0 pr=0 pw=0 time=0 us cost=4 size=16 card=1)

                           0          0          0    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4776921 pr=0 pw=0 time=16538335 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161587 pr=0 pw=0 time=1988081 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=418 pr=0 pw=0 time=728882 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4615334 pr=0 pw=0 time=12937535 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1662848 pr=0 pw=0 time=17064368 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4226416 pr=0 pw=0 time=15380317 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161587 pr=0 pw=0 time=2018103 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=418 pr=0 pw=0 time=756260 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4064829 pr=0 pw=0 time=11749473 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1112346 pr=0 pw=0 time=16466264 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4194181 pr=0 pw=0 time=15372259 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161447 pr=0 pw=0 time=2037420 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=278 pr=0 pw=0 time=766597 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4032734 pr=0 pw=0 time=11718775 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1080260 pr=0 pw=0 time=16534732 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4180426 pr=0 pw=0 time=15372414 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161524 pr=0 pw=0 time=2014475 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=355 pr=0 pw=0 time=751894 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4018902 pr=0 pw=0 time=11737306 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1066426 pr=0 pw=0 time=16625769 us cost=2 size=0 card=45)(object id 172277)

                           0          0          0  NESTED LOOPS  (cr=315 pr=0 pw=0 time=2003128 us cost=5 size=33 card=1)

                           0          0          0   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=315 pr=0 pw=0 time=1284428 us cost=1 size=17 card=1)

                           0          0          0    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=315 pr=0 pw=0 time=548367 us cost=0 size=0 card=1)(object id 74657)

                           0          0          0   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=0 pr=0 pw=0 time=0 us cost=4 size=16 card=1)

                           0          0          0    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4203222 pr=0 pw=0 time=15339611 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161724 pr=0 pw=0 time=1978801 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=555 pr=0 pw=0 time=724684 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4041498 pr=0 pw=0 time=11755839 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1089018 pr=0 pw=0 time=16550072 us cost=2 size=0 card=45)(object id 172277)

                      118056     118056     118056  TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=10005663 pr=0 pw=0 time=22365767 us cost=5 size=16 card=1)

                     7258635    7258635    7258635   INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=7051808 pr=0 pw=0 time=21227109 us cost=3 size=0 card=45)(object id 172277)

                      118056     118056     118056  TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=10311256 pr=0 pw=0 time=22958252 us cost=5 size=16 card=1)

                     7258635    7258635    7258635   INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=7357408 pr=0 pw=0 time=21248352 us cost=3 size=0 card=45)(object id 172277)

                      113344     113344     113344  TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=10359863 pr=0 pw=0 time=22883505 us cost=5 size=16 card=1)

                     7258635    7258635    7258635   INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=7405283 pr=0 pw=0 time=20821289 us cost=3 size=0 card=45)(object id 172277)

                      113344     113344     113344  TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=10365376 pr=0 pw=0 time=23104884 us cost=5 size=16 card=1)

                     7258635    7258635    7258635   INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=7410796 pr=0 pw=0 time=20880829 us cost=3 size=0 card=45)(object id 172277)

                       74916      74916      74916  TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=10423319 pr=0 pw=0 time=23167303 us cost=5 size=16 card=1)

                     7258635    7258635    7258635   INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=7463610 pr=0 pw=0 time=20995623 us cost=3 size=0 card=45)(object id 172277)

                       74916      74916      74916  TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=10428708 pr=0 pw=0 time=23342447 us cost=5 size=16 card=1)

                     7258635    7258635    7258635   INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=7468994 pr=0 pw=0 time=20977953 us cost=3 size=0 card=45)(object id 172277)

                      161168     161168     161168  NESTED LOOPS  (cr=4254553 pr=0 pw=0 time=15399863 us cost=5 size=33 card=1)

                      161169     161169     161169   TABLE ACCESS BY INDEX ROWID SD_MPS_TESTS_INFO (cr=161246 pr=0 pw=0 time=2024521 us cost=1 size=17 card=1)

                      161169     161169     161169    INDEX UNIQUE SCAN MPS_TEST_INFO_U1 (cr=77 pr=0 pw=0 time=755241 us cost=0 size=0 card=1)(object id 74657)

                      161168     161168     161168   TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_RESULTS (cr=4093307 pr=0 pw=0 time=11757709 us cost=4 size=16 card=1)

                     7258635    7258635    7258635    INDEX RANGE SCAN MPS_LAB_RESULT_N3 (cr=1140825 pr=0 pw=0 time=16482634 us cost=2 size=0 card=45)(object id 172277)

                          17         17         17  WINDOW SORT (cr=292809209 pr=84104 pw=0 time=931178722 us cost=391 size=2403 card=1)

                          94         94         94   COUNT STOPKEY (cr=292809209 pr=84104 pw=0 time=520361021 us)

                          94         94         94    HASH JOIN  (cr=292809209 pr=84104 pw=0 time=520360824 us cost=391 size=2403 card=1)

                          94         94         94     NESTED LOOPS  (cr=419 pr=17 pw=0 time=14996 us)

                          94         94         94      NESTED LOOPS  (cr=325 pr=17 pw=0 time=13767 us cost=8 size=242 card=1)

                          94         94         94       NESTED LOOPS  (cr=307 pr=15 pw=0 time=12316 us cost=7 size=216 card=1)

                          94         94         94        NESTED LOOPS  (cr=209 pr=13 pw=0 time=5415 us cost=6 size=174 card=1)

                          94         94         94         NESTED LOOPS  (cr=111 pr=13 pw=0 time=3442 us cost=5 size=154 card=1)

                          94         94         94          TABLE ACCESS BY INDEX ROWID SD_MPS_RECEIPT_INFO (cr=13 pr=13 pw=0 time=1186 us cost=4 size=140 card=1)

                         102        102        102           INDEX RANGE SCAN MPS_RECEIPT_INFO_N1 (cr=3 pr=3 pw=0 time=496 us cost=3 size=0 card=1)(object id 74637)

                          94         94         94          TABLE ACCESS BY INDEX ROWID SD_MPS_VENDOR_SITE (cr=98 pr=0 pw=0 time=1137 us cost=1 size=14 card=1)

                          94         94         94           INDEX UNIQUE SCAN SYS_C0011009 (cr=4 pr=0 pw=0 time=416 us cost=0 size=0 card=1)(object id 74681)

                          94         94         94         TABLE ACCESS BY INDEX ROWID SD_MPS_ROUTE_INFO (cr=98 pr=0 pw=0 time=1078 us cost=1 size=20 card=1)

                          94         94         94          INDEX UNIQUE SCAN SYS_C0010987 (cr=4 pr=0 pw=0 time=396 us cost=0 size=0 card=1)(object id 74649)

                          94         94         94        TABLE ACCESS BY INDEX ROWID SD_MPS_VENDORS (cr=98 pr=2 pw=0 time=1193 us cost=1 size=42 card=1)

                          94         94         94         INDEX UNIQUE SCAN SYS_C0011006 (cr=4 pr=0 pw=0 time=380 us cost=0 size=0 card=1)(object id 74676)

                          94         94         94       INDEX UNIQUE SCAN MPS_LAB_REPORT_N1 (cr=18 pr=2 pw=0 time=521 us cost=0 size=0 card=1)(object id 74622)

                          94         94         94      TABLE ACCESS BY INDEX ROWID SD_MPS_LAB_REPORT (cr=94 pr=0 pw=0 time=414 us cost=1 size=26 card=1)

                      161169     161169     161169     VIEW  SD_MPS_LAB_RESULTS_V (cr=292808790 pr=84087 pw=0 time=931592304 us cost=381 size=348234345 card=161145)

                      161169     161169     161169      FILTER  (cr=161170 pr=380 pw=0 time=944890 us)

                      161169     161169     161169       INDEX FULL SCAN DESCENDING SYS_PK (cr=161170 pr=380 pw=0 time=584418 us cost=381 size=3061755 card=161145)(object id 172271)

                  • 6. Re: APEX Performance issue  due to Pagination
                    karthick_muthaiyan

                    I know the SQL can be rewritten efficiently by using WITH clause but I wonder how the same SQL was able to run fine in TEST .  I can create an SQL Plan baseline but the resultant SQL that I see in PROD is different from TEST so I don't know what the IR report does to the original SQL . Haven't had time to find that out .

                    • 7. Re: APEX Performance issue  due to Pagination
                      AndyH

                      gayanthick wrote:

                       

                      I know the SQL can be rewritten efficiently by using WITH clause but I wonder how the same SQL was able to run fine in TEST .

                       

                      It's because your TEST environment is different e.g. quantity/style of data, indexing/statistics, hardware, load, etc.

                       

                      From your report it looks like the biggest piece of work is sorting the data (15 minute?) - perhaps it's using busy disks to do this work? Also, accessing the view SD_MPS_LAB_RESULTS_V also takes 15 minutes...