PL/SQL large select & update 1

[anuktac]

hi guys,
I have a problem with a select and update on a large table (around 1.7 million rows), My table, which is called intersite
looks like thisI have separated the columns by a '|')

INTERSITE TABLE
-------------------------------

calling_site|called_site|no_of_calls|b_2_a_no_of_calls|
A |B |3 |null|
CDF |FT |9 |null|
B |A |1 |null|
FT |CDF |2 |null|
LM |MN |6 |null|

The no_of_calls column depict the calls made from calling_site to called_site (say, A to B). The b_2_a_no_of_calls column at this stage remains null, but it has to be populated with the no_of_calls made from B to A . I have written a procedure for this.

After the procedure is run, this is how the data will look:

calling_site|called_site|no_of_calls|b_2_a_no_of_calls|
A |B |3 |1 |
CDF |FT |9 |2 |
B |A |1 |3 |
FT |CDF |2 |9 |
LM |MN |6 |0 |

My problem is this procedure takes a long time to run (around 20 min), which is not acceptable. How can I make it run faster? I have a composite unique index on (calling_site,called_site).
I have been reading about Bulk Collects and Forall loops, but I have not used these features before, and I am not clear as to how
to fit them in my procedure. Can anyone help?

This is what I have done so far:

CREATE OR REPLACE procedure intersite_b_2_a_update
as
cursor c1 is select
calling_site,called_site
from intersite;

v_no_of_calls number;

begin
for calling_site_rec in c1
Loop

begin
select no_of_calls
into v_no_of_calls,
from intersite
where calling_site= calling_site_rec.called_site
and called_site= calling_site_rec.calling_site;

exception when no_data_found then
v_no_of_calls:=0;
end;

update intersite set b_2_a_no_of_calls=v_no_of_calls
where calling_site=calling_site_rec.calling_site
and called_site=calling_site_rec.called_site;

commit;
end loop;

end;
/

 

[BJCooperIT]

Committing every iteration within the loop will slow your procedure down. If I understand you correctly the following might work faster:

CREATE OR REPLACE procedure intersite_b_2_a_update
as
cursor c1 is 
select calling_site, called_site
from   intersite;

v_no_of_calls number;
v_counter     number := 0;

begin
for cs_rec in c1
Loop
    v_counter := v_counter + 1;
    begin
      select no_of_calls
      into   v_no_of_calls,
      from   intersite
      where  calling_site = cs_rec.called_site
      and    called_site  = cs_rec.calling_site;

      exception when no_data_found then
      v_no_of_calls:=0;
    end;

update intersite set b_2_a_no_of_calls=v_no_of_calls
where calling_site=calling_site_rec.calling_site
and called_site=calling_site_rec.called_site;

if     v_counter > 999 then   -- commit 1000 rows
       commit;
       v_counter := 0;
end if;

end loop;
end;
commit;
/

Good luck!


[sup]Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
[sup]When posting code, please use TGML to help readability. Thanks![sup]

 

[anuktac]

Thanks, BJCOOPERIT. So, Bulk Collect and Forall is not going to work in this case, is that it?

 

[BJCooperIT]

I, unfortunately, have no experience with these. Perhaps one of my DBA colleagues might care to comment.

By the way, I should have written:

end loop;
commit;
end;
/

[sup]Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000
[sup]When posting code, please use TGML to help readability. Thanks![sup]

 

[sem]

I still can not understand the purpose of your procedure. Isn't it the same

update intersite set b_2_a_no_of_calls=v_no_of_calls;

If so I suppose that
1. You may dedicate a larger rollback segment to this transaction
2. You may speed up your procedure by using WHERE CURRENT OF construct and commiting after BJCooperIT's suggestion.

If your rollback segment is large enough and fits into memory the first way will be the fastest.

Regards, Dima

 

[anuktac]

Hello sem,
update intersite set b_2_a_no_of_calls=no_of_calls is not what I want,bcoz no_of_calls column gives me the no of calls made from A to B. The b_2_a_no_of_calls column should be updated with the calls made from B to A (the value of which is contained in a different row, row# 3).So, when calling_site is A, the b_2_a column will contain calls made from B to A. But in the row where calling site is B, the b_2_a column will contain the calls made from A to B.
So, basically the table would contain the no of calls made from site 1 to site 2, in both directions.
Similarly, for row#2 (CDF->FT), for the b_2_a column, i have to pick up the value for (FT->CDF), which is row#4, and update row#2 with '2'.When I reach row#4,(FT->CDF), the b_2_a value would have to be picked from row#2, and row#4 will be updated with '9'.
For (LM->MN), there are no calls from (MN->LM), so the b_2_a column will be updated with zero.
I don't know if I am making this any clear...
-Anukta

 

[sem]

Oops.

update intersite dst set b_2_a_no_of_calls = (select nvl(max(v_no_of_calls),0)
from intersite src where
where src.calling_site = trg.called_site
and src.called_site = trg.calling_site)

In any case switching context will eat a lot of resources, thus I'd recommend to use pure sql + larger rollback segment. The drawback of using cursor loop (besides its inefficiency) is that data may be changed during its execution, so you may get inconsistent data.

Regards, Dima

 

[anuktac]

Sem,
I just tried the update statement as you said, but it seems to be taking more than twice the time of the pl/sql, I had to kill it after 45 min.
Thanks for your help, though.
Could anyone tell me, if bulk collects and foralls would work in this case, and how? Or are they going to make this process faster or not?


-Anukta

 

[sem]

Did you specify an appropriate rollback segment? That was the key, not the statement itself. I suppose that most of time was spent in expanding RBS.
How do you plan to use bulk collect? To select 1M+ records into memory variable? I don't think it may help.

Regards, Dima

 

[anuktac]

Hi Sem, u were right....bulk collects are not helping, because of loading the whole 1 million rows into memory .

I have 4 rollback segs (other than sys), which are all of the same size.It won't help, will it?
I am now trying updating by rowid.

 

[anuktac]

Using rowid to update the table, instead of the where clause, improved the time considerably. Now the time is 12 min.
Thanks all you guys for your help.
-Anukta

 

[anuktac]

I modified the procedure, and this one now works in 2.5 min

CREATE OR REPLACE procedure intersite_b_2_a_update_1
as
cursor c1 is select
i2.no_of_calls,
i1.rowid myrowid
from intersite i1, intersite i2
where i1.calling_site=i2.called_site
and i1.called_site=i2.calling_site;


v_no_of_calls number;
v_counter number := 0;

begin
for calling_site_rec in c1
LOOP

v_counter := v_counter + 1;

update intersite set b_2_a_no_of_calls=calling_site_rec.no_of_calls
where rowid=calling_site_rec.myrowid;

if v_counter > 999 then -- commit 1000 rows
commit;
v_counter := 0;
end if;

end loop;
commit;

end;
/