simplified version of the code
t1 at local: 100k records
t2 at remote: 50k records
declare
cursor c1 is select * from t1;
begin
loop
open c1
begin
select records from t2@remote b
where c.id = b.id
<some other joins with cursor>
end
use records to do some_other_stuff;
end loop;
end;
I traced and the proc call takes 30 seconds, 28 of are the 100k calls to t2 so I need to take those calls out if possible.
A) should I build the call to t2 into my cursor definition in a join?
B) should I pull t2 locally in a once off call cursor/temporary table in the procedure? how to do same?