You Asked
We have two Oracle 12c databases, A and B. The required data resides on Database A. Database B is responsible for processing this data. In the prior year, this all resided on the same database. Now that we have two separate databases, A and B, we are faced with the challenge of running the same process without degrading performance.
Four necessary tables are on Database A, two of which grow up to 20 Million records through several months. We run an query joining these tables retrieving one record's data at a time to pass through as parameter values to Oracle PL/SQL package procedure to process. We process about 480,000 records daily. The PL/SQL package procedure that initiates the data processing and all referenced objects/procedures reside on Database B.
Is it better practice to replicate this data using Materialized Views and then run procedure locally on Database B or to run the procedure 480,000 times on Database A using database link?
Thank You
and Connor said...
Sounds to me like the latency is gonna kill you there. Even at (say) 1ms per call, that's a minimum of 480 seconds just in latency.
Less network trips is definitely going to help, no matter how you implement it.
A materialized view is definitely an option. Or if you dont want the overhead of doing so, you could refactor your process to defer the row-by-row calls into batches, ie
a- fetch (say) 10,000 rows from database A (ie, one fetch not 10000 fetches)
b- perform 10,000 local plsql calls on db B
c- get the next 10k
etc
Even better would be to see if its possible to refactor the procedure in (b) to process the 10,000 rows en masse rather than row-by-row (Obviously very much depends on what the proc is doing).
Hope this helps.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment