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