Re: Performance problem oracle's net8 dblink

On 22 Apr 1999 11:32:14 GMT, "Kunihito Ishibashi" <ishi_at_future.co.jp> wrote:
>
>here's a detailed description of the problem...
>
>we have setup an application server that is using dblink to access/update
>information in a much higher
>performance shared db server. the specs for each server is listed below.
>

What are you doing on the remote table ? Are you using PL/SQL cursors or PRO*C cursors ?

In oracle 7.x, when you use a PL/SQL cursor to fetch accross a DB_LINK, even if you use array processing in pro*C, the DB_LINK will only retrieve one row at a time, this is REALLY gonna take a long time for high volumes.

One solution to this is to bring the whole set of records in a temporary table locally (insert into temp select * from rmote_tab_at_remot_db and work with them locally and then delete the stuff from the temps table)

I've seen a benchmark of this where the processing time to process 3500 rows went from 24 seconds to under 4 seconds, including the copy of the rows....

Guy Dallaire
Sysadmin/DBA
Centre de recherche industrielle du Québec e-mail: gdallair-nospam_at_criq.qc.ca
http://www.criq.qc.ca

NOTE: To reply via e-mail, remove the '-nospam' from my e-mail adress Received on Thu Apr 22 1999 - 14:13:51 CDT