Query taking too long to finish

[ananthaisin]

Hi,

I have two procs which is getting executed one below the other in a package

package1.calc_cl_bal
package1.calc_zone

This is how the procs are defined

procedure pre_decre_calc_clbal is
c number;
lcnt number(5);
lcnt1 number;
l_msg varchar2(200);
l_mob varchar2(15);
type string_array is table of varchar2(100)index by binary_integer;
type number_array is table of number(13,2) index by binary_integer;
 
clbalm string_array;
clbala number_array;
clbal_tmp number_array;
 
cursor c3 is
select mob from prepaid_sms_bhr_it where name in ('Anantha');
 
 
begin
 
select count(*) into lcnt1 from auto_sh_load where trunc(loaddate)=trunc(sysdate) and filegroup in ('TNSTAT');
 
if lcnt1=1 then
 
select count(*) into lcnt from in_decrement_proc_status where report_date=trunc(sysdate)
and report_name in ('TNCLBAL') and circle='TN';
 
if lcnt=0 then
declare
 
cursor c1 is
select /*+ rule */ mob,nvl(sum(balance),0)
from in_CHE where dum in ('A','G')   group by
mob;
begin
dbms_output.put_line('Closing balance updation started');
open c1;
loop
fetch c1 bulk collect into clbalm,clbala limit 50000;
if clbalm.count >0 then
for i in clbalm.first..clbalm.last loop
clbal_tmp(i) := clbala(i);
end loop;
 
forall ix in clbalm.first..clbalm.last
update in_decrement_base_tn  v set v.clbal=clbal_tmp(ix) where  v.mob=clbalm(ix);
else
exit when c1%notfound;
end if;
end loop;
 
commit;
close c1;
commit;
end;
 
 
insert into in_decrement_proc_status values ('TNCLBAL','COMPLETED',sysdate,trunc(sysdate),'TN','Y');
commit;
 
dbms_output.put_line('Closing balance updation ended');
 
end if;
 
else
 
 
            l_msg:=l_msg||' '||'CLBAL Files not received , TRANS Report will be delayed.';
                open c3;
                loop
                    fetch c3 into l_mob;
                    exit when c3%notfound;
 
                        insert into sms(from_num,to_num,msg,sent_flag)
                        select 'SMS',l_mob,l_msg,'N' from dual;
 
                        commit;
 
                        insert into prepaid_decre_sms_log
                        select l_mob,l_msg,sysdate,'N','TN' from dual;
 
                        commit;
 
                end loop;
 
 
 
 
 
end if;
end;
 

The above proc gets completed in 20 mins or maximum 30 mins. Its mapping 13.8 million records

This the second proc which is very similar to the previous one

procedure pre_decre_calc_zone is
c number;
lcnt number(5);
lcnt1 number;
l_msg varchar2(200);
l_mob varchar2(15);
type string_array is table of varchar2(200)index by binary_integer;
type number_array is table of number(13,2) index by binary_integer;
 
zonem string_array;
zonea string_array;
zone_tmp string_array;
 
 
cursor c3 is
select mob from prepaid_sms_bhr_itwhere name in ('Anantha');
 
 
begin
 
 
select count(*) into lcnt from in_decrement_proc_status where report_date=trunc(sysdate)
and report_name in ('TNDECREMENT') and circle='TN';
 
if lcnt=1 then
 
select count(*) into lcnt1 from in_decrement_proc_status where report_date=trunc(sysdate) and report_name in ('TNZONE');
 
if lcnt1=0 then
 
declare
 
cursor c1 is
select /*+ rule */ mob,v.zone from cwn_prepaid_zone_mast_new v;
 
begin
dbms_output.put_line('ZONE updation started');
open c1;
loop
fetch c1 bulk collect into zonem,zonea limit 50000;
if zonem.count >0 then
for i in zonem.first..zonem.last loop
zone_tmp(i) := zonea(i);
end loop;
 
forall ix in zonem.first..zonem.last
update in_decrement_base_tn  v set v.zone=zone_tmp(ix) where  v.mob=zonem(ix);
commit;
else
exit when c1%notfound;
end if;
end loop;
 
commit;
close c1;
commit;
end;
 
 
insert into in_decrement_proc_statusvalues ('TNZONE','COMPLETED',sysdate,trunc(sysdate),'TN','Y');
commit;
 
dbms_output.put_line('ZONE updation ended');
 
end if;
 
else
 
 
            l_msg:=l_msg||' '||'ZONE Updation not done, TRANS Report will be delayed.';
                open c3;
                loop
                    fetch c3 into l_mob;
                    exit when c3%notfound;
 
                        insert into sms(from_num,to_num,msg,sent_flag)
                        select 'TNDECRESMS',l_mob,l_msg,'N' from dual;
 
                        commit;
 
                        insert into prepaid_decre_sms_log
                        select l_mob,l_msg,sysdate,'N','TN' from dual;
 
                        commit;
 
                end loop;
 
 
 
 
 
end if;
end;
 
 
end decrement_tn;
/
 
 

This proc also deals with around 13.9 million records but whereas the updation goes for 3 hrs.

I have asked the DBA to rebuild all indexes and also analyze the tables but no performance boost.

Though both the procs are almost identical the time taken for the second one is beyond the scale.

The table in_che and cwn_prepaid_zone_mast_new both contains 13.9 million records.

Note : When i run the procs no other process is running in the server.

Please help in optimizing if possible