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

j.w.vandijk.removethis_at_hetnet.nl (Jaap W. van Dijk) wrote:
> On Mon, 22 May 2006 22:03:28 +0100, "Jonathan Lewis"
> <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> >
> >"Jaap W. van Dijk" <j.w.vandijk.removethis_at_hetnet.nl> wrote in message
> >news:44722104.1586781_at_news.hetnet.nl...
> >> 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

Precisely how are you getting these numbers?

....
>
> Indeed I'm doing a simple
>
> INSERT /*+ APPEND */ INTO tableA
> SELECt * FROM tableB_at_dblink
>
> For fun I'll try your suggestion of performing a local bulk collect /
> insert. I'm not sure though what you would try to proof with the
> result from that test.

I think Jonathan's point is that if the piece-wise method takes about as long as the remote database method, then you will no why the difference exists-- because the ordinary "insert into select <no where>" is highly optimized in a way the remote (or piece-wise) one cannot be, so you can give up on that avenue.

> I would be surprised if it would not result in
> an even higher CPU consumption, because of the high number of context
> switches.
>
> What occupies me:
> Is the higher CPU consumption in case of the remote table to be
> expected?

I'm not even convinced the CPU consumption is real, as opposed to some kind of accounting/reporting artefact. If you monitor the oracle processes (both local server and remote server) at the OS level, what do you see?

The other I would try would be just running the select, without the insert, reading but discarding the rows and see what that give you for stats.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB