Database links...again

At present remote queries across a database link are taking at least ten times as long to execute compared to being directly executed on the remote instance...I've included a test procedure at the end of this post...

Is there any reason why the procedure below shold be so slow ????

What parameters can I set to improve this performance ??

I've read about the Session Data Unit ( SDU ) parameter for tnsnames configuration....

The documentation says that increasing this MAY produce a better performance....what is the max level I shold be setting this to ?? And should this be set against the local or remote instance in the tnsnames file ??

Is there anything else that can be done to improve my predicament ???

I've included the test procedure I'm using to test these links........I've run this from sql plus and there is a time difference of approximatelly a factor of ten !!

I'm using :NT4 ( SP3 )

                 Oracle 8.1.5 ( each instance ) ....
                 SQL* Net8....
                 SQL*Plus 8.xxx,
                 Tnsnames is configured to use TCP/IP...

CREATE OR REPLACE PROCEDURE test_db_link AS

cursor cur_user_objects_on_remote( cp_num_rows number ) is select object_name from user_objects_at_dev where rownum < cp_num_rows order by 1;

cursor cur_user_objects( cp_num_rows number ) is select object_name from user_objects where rownum < cp_num_rows order by 1;

cursor cur_user_objects_on_remote2( cp_num_rows number ) is select object_name from user_objects_at_dev where rownum < cp_num_rows order by 1;

cursor cur_user_objects2( cp_num_rows number ) is select object_name from user_objects where rownum < cp_num_rows order by 1;

start_time number;
end_time number;
remote_time_taken number;
local_time_taken number;

l_num_rows number := 100;

begin

• execute cursors to select from the remote instance.....

start_time := dbms_utility.get_time;

for rec in cur_user_objects_on_remote( l_num_rows )loop  for rec2 in cur_user_objects_on_remote2( l_num_rows )loop    null;
 end loop;
end loop;

end_time := dbms_utility.get_time;
remote_time_taken := end_time - start_time;

start_time := dbms_utility.get_time;

for rec in cur_user_objects( l_num_rows )loop   for rec2 in cur_user_objects2( l_num_rows )loop    null;
 end loop;
end loop;

end_time := dbms_utility.get_time;

local_time_taken := end_time - start_time;

l_num_rows := l_num_rows * l_num_rows;
dbms_output.put_line('The local procedure took ' || local_time_taken * 10
|| ' milliseconds to retreive '|| l_num_rows ||' records. ');

dbms_output.put_line('The remote procedure took ' || remote_time_taken * 10
|| ' milliseconds to retreive '|| l_num_rows ||' records. ');

end;

Cheers

Steve Bright

steve.bright_at_capgemini.co.uk Received on Thu Jul 08 1999 - 10:26:21 CDT