Re: database links and performance

John,

> We are considering using database links in Oracle 9i but are concerned about
> performance.
>
> Presumably the network can be a bottleneck when using database links.
>
> Does anyone have any useful experiences with performance of database links?

Well, first a big question in return...

What are your alternatives to database links? Shell scripting? File transfer with some kind of "unload" on one side and "load" on the other? Something custom written in C, C++, or Java?

You absolutely need transactional controls (i.e. COMMIT and ROLLBACK logic). No "if"s or "but"s about it.

Chances are good that many of the alternate solutions under consideration will not support transactions, which is crucial if you want to be certain that the data gets from point "A" to point "B". You don't want the odds to be "good" for reliable transfer -- five 9's (i.e. 99.999%) reliability is unacceptable. You want it airtight 100% reliable and able to be rolled back cleanly in the event of any failure.

Just try to implement two-phase commit in a shell script or 3GL program -- I dare ya! :-) It really isn't easy and Oracle mastered this a long time ago.

If you have a reliable solution to that question, then to answer your original question, database links perform best with bulk operations using arrays. Row-at-a-time processing, which stinks up PL/SQL performance even without database links, is even stinkier with database links. PL/SQL and SQL provide numerous mechanisms for non-chatty communication over database links, including PL/SQL tables (a.k.a. collections or "arrays").

Also, in general, I find "pulling" data across a database link to perform better than "pushing" data across. That is, "INSERT INTO local-table SELECT ... FROM remote-table" seems to perform better than "INSERT INTO remote-table SELECT ... FROM local-table", but you should test it yourself and not take my word for it. I mention this only to alert you that this could be a consideration for performance. There is no trade-off in reliability.

Also, be aware that parallel execution is possible across database links, if you are trying to move truly large volumes of data. Again, pulling seems to work better than pushing, in my experience; your mileage may vary...

Upshot:

1. Please don't believe everything you've read and heard. Try it out.
2. Use PL/SQL bulk operations and collections, if appropriate.
3. Reliability is far more important than speed.

Hope this helps...

-Tim

--
http://www.freelists.org/webpage/oracle-l