Jaap W. van Dijk wrote:
> On Tue, 23 May 2006 02:01:03 GMT, Mladen Gogala <gogala_at_sbcglobal.net>
> wrote:
>
>> 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
>>
>
> Mladen,
>
> With the pseudo-code written out some, I'm doing a
>
> INSERT /*+ APPEND */ INTO tableA
> SELECT * FROM tableB_at_dblink
>
> Where TableA doesn't have any indexes or triggers, and is truncated
> beforehand.
>
> The EXPLAIN PLAN shows that the SELECT is executed completely on the
> remote side (how could it be otherwise in this simple case?).
>
> Regards, Jaap.
>
About the only thing to tune here is the network. You might want to look
at the section on configuring the Session Data Unit size found here.
9i
http://download-west.oracle.com/docs/cd/B10501_01/network.920/a96580/advcfg.htm#475479
8i
http://download-west.oracle.com/docs/cd/A87860_01/doc/network.817/a76933/params.htm#477834
Received on Tue May 23 2006 - 14:27:16 CDT
