10 Replies Latest reply: May 15, 2014 7:54 AM by 968361 RSS

    issue in troubleshooting the code performance error in different environment

    968361

      Hi,

       

       

      The Issue that i have encountered is that  here in Development environment I have only 100,000 records and the total time it takes is 2 hours to complete. But in UAT environment the record count can be 500,000. The process takes 16 hours to process 100,000 records and gets slow and slow.

       

      I have following questions:-

      1) As after completing all the insert and Update and once the process completes then only I do a commit. So can that be the bottle neck? as after 500,000 records are inserted and equally 500,000 records are updated. After all this process is done then only I do a commit can Redo log be an issue. If so how can i check it?

       

      eg.

      begin

      insert into tab1

      select * from tab2;

       

      for idx in 1...500,000 loop

      update tab1

      set vaue := 'val1'

       

      update tab1

      set vaue := 'val2';

      end loop;

       

      commit;

      end;

       

       

      2) Say i do like

       


      Example 1:-

       

       

      begin

      For i in 1...rec.count Loop ---- rec.count = 500,000

      update tab1

      set value := rec(idx).val1

      where pk1 = rec(idx).pk1 ;

      end  loop;

       

      end ;

       

      example 2

       

      begin

      For i in 1...rec.count Loop ---- rec.count = 500,000

      rec2(idx).value  := rec(idx).value

      end  loop;

       

      forall idx2  in 1..rec2.count

       

      update tab1

      set value = rec2(idx).value

      where pk1 = rec2(idx).pk1 ;

       

      end ;

       

      In Example 2 i will do an update at the end . but I assume that if i keep 500,000 records in a plsql variable and do a update at last that might overload the PGA.

      Can you please suggest which approach will be better?