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

On Mon, 22 May 2006 20:37:34 +0000, 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.

Jaap, the problem is in select on the other side. Oracle used to bring the whole remote table to the local database and then resolve the query. It usually didn't bother with indexes. Create views on the remote side and then access views, not the whole table. That usually cuts down on the number of records that you have to transfer and thus on the execution of the join.

-- 
http://www.mgogala.com