Re: performance problem on big tables

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(at)gmail(dot)com>:

> On Thu, Aug 24, 2017 at 4:51 AM, Mariel Cherkassky
> <mariel(dot)cherkassky(at)gmail(dot)com> 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(at)gmail(dot)com>:
> >>
> >> On Mon, Aug 21, 2017 at 5:00 AM, Mariel Cherkassky
> >> <mariel(dot)cherkassky(at)gmail(dot)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.
> >
> >
>
> 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 );
>