Oracle - performing slow

Question

The below procedure is taking almost5 hours to complete 130000 records. Have tried optimizing it. Is there way to optimize it more? If i commit after each "for all" , does that increase the performance? Also i have given a limit of 1000 records, so here it is committing for 1000 record processing or when the count becomes 0? database version : oracle 10g

I am running the below procedure :

create or replace PROCEDURE       WeeklyClearQwAcctActivityLoad
AS


   v_no_of_Days_retention number;
   jobname      VARCHAR2 (30);
   loadstartdt  DATE;
   rcdcnt number;
   errorcode                 NUMBER;
   errormsg                  VARCHAR2 (100);

   /* Identify those records for which all CUST_ACCT_ID under the same Parent_id are closed (before retention days ) */
   CURSOR getdataforhist
   IS
        select /*+ parallel(qa2,128) */ CUST_ACCT_ID from qw_account qa2
        where exists
            ( select /*+ parallel(qaa2,128) */ 1 from QW_ACCT_ACTIVITY qaa2
                                                   where qaa2.cust_acct_id = qa2.cust_acct_id
                                                     and qaa2.actvy_end_date < sysdate - v_no_of_Days_retention
                                                 )
        and not exists
                 (
                    select 1 from (
                                    select /*+ parallel(qa,128) */  qa.PARENT_ID pidd from qw_account qa
                                     where  exists
                                                ( select /*+ parallel(qaa,128) */ 1 from QW_ACCT_ACTIVITY qaa
                                                   where qaa.cust_acct_id = qa.cust_acct_id
                                                     and qaa.actvy_end_date > sysdate - v_no_of_Days_retention
                                                 )
                                   ) pp where pp.pidd = qa2.PARENT_ID
                  );

   TYPE t_getDataForHist IS TABLE OF qw_acct_activity.cust_acct_id%TYPE;
   l_getDataForHist   t_getDataForHist;

   CURSOR orph_product
  IS
  SELECT  /*+ parallel( ram , 128) */ ram.rcr_prod_acct_id
    FROM  rcr_acct_mapping ram
   WHERE  1=1
     AND  ram.cust_acct_id IS NOT NULL
     AND  EXISTS
              ( SELECT /*+ parallel( rap , 128) */ 1
                        FROM  rcr_acct_profile rap
                         WHERE  rap.rcr_prod_acct_id = ram.rcr_prod_acct_id
                   AND  rap.cust_acct_id = ram.cust_acct_id
                       AND  rap.prod_acct_status in ('ACTIVE','INACTIVE','SUSPENDED')
               )
     AND  NOT EXISTS
                  ( SELECT /*+ parallel( qaa , 128 */  1
                      FROM  qw_acct_activity qaa
                     WHERE  qaa.cust_acct_id = ram.cust_acct_id
                   );

  TYPE  t_orph_product is table of rcr_acct_mapping.rcr_prod_acct_id%TYPE;
  l_orph_product t_orph_product;
  cnt number default 0;

BEGIN
   jobname := 'WEEKLY_CLEAN_QW_ACCT_ACTIVITY';
   loadstartdt := SYSDATE;
   rcdcnt := 0;

   INSERT INTO rcr_stage_audit (job_name,load_start_date,load_end_date,record_cnt,processed,process_date,process_cnt,ignore_cnt)
          VALUES (jobname,loadstartdt,NULL,NULL,'N',loadstartdt,NULL,NULL );
   COMMIT;

         BEGIN
             SELECT VALUE into v_no_of_Days_retention
               FROM rcr_online_svc_app_config
              WHERE NAME = 'noofdaystoenddateqwacctactivity';
          EXCEPTION
             WHEN NO_DATA_FOUND
             THEN
                errorcode := SQLCODE;
                errormsg := 'no of days to end date qw_accta_ctivity is not defined in rcr_code_translation table';
                raise_application_error (-20101, errorcode || ' - ' || errormsg, TRUE);
          END;

   OPEN getDataForHist;

   LOOP
      FETCH getdataforhist  BULK COLLECT INTO l_getdataforhist LIMIT 1000;
      --EXIT WHEN getdataforhist%NOTFOUND   ;
    EXIT WHEN l_getdataforhist.COUNT = 0;

        -- FORALL indx IN 1 .. l_getdataforhist.count
        -- insert into TEMPSLOT (CUST_ACCT_ID) values ( l_getdataforhist(indx) ) ;

         FORALL indx1 IN 1 .. l_getdataforhist.count
         INSERT INTO qw_acct_activity_hist
               SELECT qaa.*, SYSDATE
                 FROM qw_acct_activity qaa
                WHERE CUST_ACCT_ID = ( l_getdataforhist(indx1) );

          FORALL indx2 IN 1 .. l_getdataforhist.count
            DELETE FROM qw_acct_activity
                WHERE CUST_ACCT_ID = ( l_getdataforhist(indx2) );

                rcdcnt := rcdcnt + sql%rowcount;

    COMMIT;
   END LOOP;
  CLOSE getDataForHist;


  --- Clean porduct tables for orphan CUST_ACCT_ID

  OPEN orph_product;
  LOOP
  FETCH orph_product BULK COLLECT INTO l_orph_product LIMIT 1000;
  EXIT WHEN l_orph_product.COUNT = 0;

       FORALL indx10 IN 1 .. l_orph_product.COUNT
         INSERT INTO rcr_acct_mapping_hist
               SELECT a.*
                 FROM rcr_acct_mapping a
                WHERE rcr_prod_acct_id = ( l_orph_product(indx10) );

       FORALL indx11 IN 1 .. l_orph_product.COUNT
         DELETE FROM rcr_acct_mapping WHERE rcr_prod_acct_id = ( l_orph_product(indx11) );

       FORALL indx12 IN 1 .. l_orph_product.COUNT
         DELETE FROM rcr_addt_acct_prof_detail WHERE rcr_prod_acct_id = ( l_orph_product(indx12) );

       FORALL indx13 IN 1 .. l_orph_product.COUNT
         DELETE FROM rcr_acct_profile_detail WHERE rcr_prod_acct_id = ( l_orph_product(indx13) );

       FORALL indx14 IN 1 .. l_orph_product.COUNT
         DELETE FROM rcr_acct_profile WHERE rcr_prod_acct_id = ( l_orph_product(indx14) );
     COMMIT;

  END LOOP;

  close orph_product;

  UPDATE rcr_stage_audit
         SET load_end_date = SYSDATE,
             record_cnt =  rcdcnt,
             processed = 'Y'
       WHERE job_name = jobname
         AND process_date = loadstartdt
         AND load_start_date = loadstartdt;

  COMMIT;


EXCEPTION
   WHEN OTHERS
   THEN
        errorcode := SQLCODE;
        errormsg :=  substr(sqlerrm,1,255);
        raise_application_error (-20102, errorcode || ' - ' || errormsg, TRUE);
END WeeklyClearQwAcctActivityLoad;

Answer 1

trace it using oracle tools like as oradebug or dbms_monitor and using tkprof or other analysis tools check the trace file.do the below:

1.extract the sid

2.in sqlplus run: oradebug setorapid xxxx

3.in sqlplus run: oradebug tracefile_name

4.after the process complete in os run tkprof on trace file.(other tools is available also).

5.in trace file check long sections and work on them

Answer 2

One suggestion that I would recommend to you is to avoid explicit looping and cursors. It leads to poor performance, especially when you can directly use

insert into <table_name>(columns) select <your_query_goes_here>

This is most certainly going to perform faster than your looping constructs. In fact you can reproduce this behavior with simple benchmarks in a table generated with a million records.

So in short try to avoid looping and your code would also look more readable and less prone to errors.

I did a benchmark once where out of 10 million records only about 12,000 had to be updated and timing of explicit looping vs implicit looping lead was 1 minute 10 seconds v/s 1 second.