Re: increase insert into local table from remote oracle table preformance

[Daniel Blanch Bataller <daniel.blanch.bataller@xxxxxxxxx>]

The Postgres command of choice to load bulk data is COPY https://www.postgresql.org/docs/current/static/sql-copy.html is much faster than anything else.

It’s likely that the slowest part could be Oracle exporting it’s data. Try to use sqlplus to export the data and see how long does it take, you won’t be able to make the process faster than Oracle can export it’s data.

If it’s fast enough, format the resulting file in a suitable format for Postgres ‘COPY FROM’ command.

Finally you can pipe the Oracle export command and the Postgres COPY FROM command, so the process can run twice as fast. 

You can make it even faster if you divide the exported data by any criteria and run those export | import scripts in parallel. 

El 15 ago 2018, a las 10:43, Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> escribió:

Inserting directly into the partition didnt help, the performance are just the same. I tried to increase the prefetch value to 1000(alter foreign table hist_oracle options (add prefetch '1000') but still no change - 15 minutes for one partition(6GB).

On the oracle side the plan is full scan on the partition (I'm copying the entire partition into a postgresql partition..)

2018-08-15 1:28 GMT+03:00 legrand legrand <legrand_legrand@xxxxxxxxxxx>:

main ideas are:

- inserting directly to the right partition:
  perform as many inserts as pg partitions found in main_table_hist, like
  INSERT INTO 14/08/2018_value1 select * from remote_oracle_hist where
day=to_date('14/08/2018','DD/MM/YYYY') and value='value1'

please check execution plan (in Oracle db) using EXPLAIN ANALYZE

- all those inserts should be executed in // (with 4 or 8 sql scripts)

- wal archiving should be disabled during hist data recovery only (not
during day to day operations)

- for prefetch see

https://github.com/laurenz/oracle_fdw

prefetch (optional, defaults to "200")

Sets the number of rows that will be fetched with a single round-trip
between PostgreSQL and Oracle during a foreign table scan. This is
implemented using Oracle row prefetching. The value must be between 0 and
10240, where a value of zero disables prefetching.

Higher values can speed up performance, but will use more memory on the
PostgreSQL server.

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html