Re: Looking for a scalable solution for many DB links

Paul Moore wrote:
> I'm setting up a large data collection process, which will contact a
> significant number (currently nearly 100, expected to grow) of
> databases, and collect data from them.
>
> I have to "pull" data from the remote databases (firewall issues - I
> can see the remote DBs, but they can't see me...) and it seems to me
> that there are 2 possible ways of handling the processing:
>
> 1. 3GL code of some sort. Maybe a multi-threaded Python script, which
> connects to the source and target DBs, and does "select * from
> source_table" into an array, followed by "insert into target_table
> values (...)" via array inserts.
> 2. Use DB links - "insert into target_table select * from
> source_table_at_db_link".
>
> Solution (1) is, in many ways, pretty straightforward. But the
> boilerplate code around the SQL is fairly complex, and relies on a 3rd
> party language interpreter. On the other hand, (2) is all in the
> database, and is trivially simple to do.
>
> The problem with (2) is that I'm not sure it scales. I need DB links
> for every database I'm collecting from, as well as being limited on
> the number of open links per session. What's the best way round that?
> Collect from each database in a separate DBMS_JOB? How scalable is
> running 100+ DBMS_JOBS at regular intervals?

I'm not sure why you believe (1) is more efficient than (2). In fact, it's almost certainly less efficient because of the additional layers of database access code, additional network transport and less efficient sql implementation. The database parameter OPEN_LINKS is how you control how many links can be open simultaneously in one session (up to 255). Reading the documentation for that parameter points to the other solution. You can use 'ALTER SESSION CLOSE DATABASE LINK' to close a database link explicitly when you're done with it.

I think it depends somewhat on what you mean by "regular intervals" as well. If you're going to be opening and closing these connections very frequently then maybe that's a factor. However, neither solution solves that problem unless you intend to let this process run continuously and wake up periodically to do updates. I'd probably partition them into sets of 255 rather than having one process for every database if you intend to do that. You must have some control table for this that lists the databases so that should be simple enough to implement. Hopefully you're using some mechanism to keep from copying the same data over and over again as well.

<snip>

--
Richard