Re: [PERFORM] performance problem on big tables

On Mon, Aug 21, 2017 at 5:00 AM, Mariel Cherkassky
<mariel.cherkassky@gmail.com> wrote:
> To summarize, I still have performance problems. My current situation :
>
> I'm trying to copy the data of many tables in the oracle database into my
> postgresql tables. I'm doing so by running insert into local_postgresql_temp
> select * from remote_oracle_table. The performance of this operation are
> very slow and I tried to check the reason for that and mybe choose a
> different alternative.
>
> 1)First method - Insert into local_postgresql_table select * from
> remote_oracle_table this generated total disk write of 7 M/s and actual disk
> write of 4 M/s(iotop). For 32G table it took me 2 hours and 30 minutes.
>
> 2)second method - copy (select * from oracle_remote_table) to /tmp/dump
> generates total disk write of 4 M/s and actuval disk write of 100 K/s. The
> copy utility suppose to be very fast but it seems very slow.

Have you tried increasing the prefetch option in the remote table?

If you left it in its default, latency could be hurting your ability
to saturate the network.