Hi, yes indeed I'm using laurenz`s oracle_fdw extension. I tried to run it but I'm getting error
dbch=# ALTER FOREIGN TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 );
ERROR: syntax error at or near "10240"
LINE 1: ...N TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 );
dbch=# alter foreign table tc_sub_rate_ver_prod OPTIONS (SET prefetch '10240');
ERROR: option "prefetch" not found
2017-08-24 19:14 GMT+03:00 Claudio Freire <klaussfreire@xxxxxxxxx>:
On Thu, Aug 24, 2017 at 4:51 AM, Mariel Cherkassky
<mariel.cherkassky@xxxxxxxxx> wrote:
> Hi Claudio, how can I do that ? Can you explain me what is this option ?
>
> 2017-08-24 2:15 GMT+03:00 Claudio Freire <klaussfreire@xxxxxxxxx>:
>>
>> On Mon, Aug 21, 2017 at 5:00 AM, Mariel Cherkassky
>> <mariel.cherkassky@xxxxxxxxx> 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.
>
>
Please don't top-post.
I'm assuming you're using this: http://laurenz.github.io/oracle_fdw/
If you check the docs, you'll see this:
https://github.com/laurenz/oracle_fdw#foreign-table- options
So I'm guessing you could:
ALTER FOREIGN TABLE remote_table OPTIONS ( SET prefetch 10240 );
