How do I speed up processing the data from a database link?

Oracle 9.2.0.5 (local) / 8.1.7.4 (remote) Open VMS 7.3-2.

Processing data from a remote table over a database link seems to take much more CPU, than processing data from a local table.

Kindly take a look at these two cases:

If I insert into a local table A, selecting from a local table B, this takes about 10 minutes, and the significant wait events and CPU are

Wait event: db file scattered read
Number of waits: 7772
Total time of waits: 57,3 seconds
CPU used by this session: 489,85 seconds

But when I insert into the local table A, from a remote table (with the same contents as the local table B) over a database link, this takes about 36 minutes, with

Wait event: SQL*Net more data from dblink Number of waits: 1635610
Total time waited: 84,73 seconds
CPU used by this session 1496,86 seconds

So getting the data across is not the bottleneck, but the increase in CPU consumption is. Seemingly, processing data from a database link takes much more time than processing data from the full table scan.

Do I interpret these figures correctly, and if so, is there any way I can speed up processing of the data from the database link?

The number of records loaded is 29984031 The number of roundtrips is 1635610 (so 18 records/roundtrip) The average number of bytes/roundtrip is about 2000

Kind regards,

Jaap. Received on Mon May 22 2006 - 15:37:34 CDT