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

Jaap W. van Dijk wrote:
> 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.

"SQL*Net more data from dblink" is an idle wait event, similar to "SQL*Net more data from client". It's just the source of the data that's different.

To speed it up, you could try tuning the query. If you post it here we might be able to help more. Please post the explain plan for the query as well. Received on Mon May 22 2006 - 15:48:42 CDT