0

We have to periodically copy data from the main database to an auxiliary database. I have the following procedure in the auxiliary db side:

procedure p_proc(start_date DATE, end_date DATE := sysdate) as
begin
    insert into foo select /*+DRIVING_SITE(f) PARALLEL(8)*/  f.* 
                    from remote_foo f 
                    where f.tsp >= start_date and f.tsp < end_date;
    insert into foo_c1 select /*+DRIVING_SITE(f) PARALLEL(8)*/ c.* 
                       from remote_foo_c1 c join remote_foo f 
                       on c.fk = f.pk 
                       where f.tsp >= start_date and f.tsp < end_date;
    --13 more child tables
    commit;
end;

On average of 7million records for the main table (foo), it takes like 20min.

Are there more improvements that I could apply apart from the parallelism and the driving site hints?

Explain plan for the 2nd query (foo_c1): https://pastebin.com/raw/79GWjj19

All the fields used in the filter have indexes, on both sides. Oracle 19c (19.11.0.0.0).

anat0lius
  • 2,145
  • 6
  • 33
  • 60
  • 1
    You have 15 statements apparently. Of the 20 minutes, how long does each one take? Is there one that takes more time that you can optimize? What is the query plan of each of the statements? Are there appropriate indexes for example? What are the wait events? Are you primarily waiting, for example, on the network? On I/O? CPU? – Justin Cave Mar 26 '22 at 19:33
  • 1
    Please boil down only to one `insert statement` (the procedure is most probably not relevant). Show clearly where the database link is used. You must understand, where is a bottleneck - in the *join* or in the *transfer* of the data. Here you need to investigate the [execution plan](https://stackoverflow.com/a/34975420/4808122) that will show you the statement(s) that are send to the remote site, so you can check if the *driving site* and *parallel* hints are used or ignored. Post also your Oracle version. – Marmite Bomber Mar 26 '22 at 22:40
  • 1
    remember that the data needs to physically move from the one database to another - the architecture of the underlying network could have an impact too. – Koen Lostrie Mar 27 '22 at 09:59
  • I've also tested using the same database machine instead of 2 and the times are the same, so there is no network bottleneck. – anat0lius Mar 28 '22 at 07:58
  • Well, than the bottleneck is in the join. The plan shows that a *parallel hash join* is opened, which is fine. What is may be surprising is that your filter on `start` and `end` date *does not* trigger *partition pruning* - is this expected? Also your child table is not filtered according to the filter of the parent table. So IMO a substantial increase of performance is possible only with a *physical redesing* - parent table partitioning on the filter criteria and possible also a referential partitioning of the child tables. @anat0lius pls learn also how to post the ex. plan in a *text form*. – Marmite Bomber Mar 28 '22 at 10:09
  • @MarmiteBomber I updated again with a pastebin link – anat0lius Mar 28 '22 at 10:26
  • 1
    We do have partitioning, but only for the main table and not by the `version_tsp` field but another one that is not used in the query. We know that repartitioning the db by the timestamp field will be the best but we want to avoid that since it will require an enormous downtime to apply the change. We will reconsider it. Thanks – anat0lius Mar 28 '22 at 10:34

0 Answers0