title: ORACLE STORED PROCEDURE CURSOR IS WORKING VERY SLOW
body: <p>One of my stored procedure recently took around 6 hours which usually takes about 3 hours to complete.
  On checking, I found that the cursor is taking the time to execute.
  <br>
  Both the tables are present in my local DB instance.</p>
  <p>I need to know what could be the possible reason for this and how the procedure can be fine tuned.</p>
  <h3>My stored procedure:</h3>
  <pre><code>create or replace PROCEDURE VMS_DETAILS_D_1 IS
  LOG_D1 VARCHAR2(20);
  BEGIN
  
  /* IDENTIFY PARTITION */
  
  SELECT partition_name into LOG_D1 FROM all_tab_partitions a WHERE table_name = 'LOG' AND TABLE_OWNER='OWNER1' and partition_position IN 
  (SELECT MAX (partition_position-1) FROM all_tab_partitions b WHERE table_name = a.table_name AND a.table_owner = b.table_owner);
  
  execute immediate 'DROP TABLE TAB1 PURGE';
  COMMIT;
  
  EXECUTE IMMEDIATE 'create table TAB1 Nologging as
  select /*+ Parallel(20) */  TRANSACTIONID,TIME_STAMP from OWNER1.log partition('||LOG_D1||') 
  where ( MESSAGE = ''WalletUpdate| Request for Estel Update is Processed'' or MESSAGE = ''Voucher Core request processed'')';
  
  EXECUTE IMMEDIATE 'CREATE INDEX IDX_TAB1 on TAB1(TRANSACTIONID)';
  
  DBMS_STATS.GATHER_TABLE_STATS (ownname =&gt; 'OWNER2' , tabname =&gt; 'TAB1',cascade =&gt; true, estimate_percent =&gt; 10,method_opt=&gt;'for all indexed columns size 1', granularity =&gt; 'ALL', degree =&gt; 1);
  
  
  DECLARE
     CURSOR resp_cur
     IS
          select TRANSACTIONID,to_char(max(TIME_STAMP),'DD-MM-YYYY HH24:MI:SS') TIME_STAMP from TAB1 
          where TRANSACTIONID in (select ORDERREFNUM from TAB2
          where ORDERREFNUM like 'BV%') group by TRANSACTIONID;
  BEGIN
     FOR l IN resp_cur
     LOOP
        update TAB2 
        set TCTIME=l.TIME_STAMP 
        where ORDERREFNUM=l.TRANSACTIONID;
        COMMIT;
     END LOOP;
  END;
  
  end; 
  </code></pre>
  <hr />