Question about Data transfer between Oracle instances

I have recently encountered an interesting situation and would appreciate some assistance. I want to retrieve 10,000 - 50,000 rows from a remote instance B to the local instance A. The local instance implies a session connected and executing the PL/SQL code. I retrieved the rows using two different methods. There was a significant difference in transfer rates between the methods.

The first method is a simple PL/SQL package that opens a cursor, which references the remote table _at_ the database link and fetches each row.

The second method is a PL/SQL package that calls a package on the remote instance. The remote package fetches all rows and assigns them to a PL/SQL table. The PL/SQL table is an output argument and therefore when the procedure terminates the data is transferred over the link.

The second method is 20 to 100 times faster! The results of the second method were inconsistent but always in the range of 20 to 100 times faster.

My guess is, fetching across the link is not using any batching and each fetch causes a request and response to go over the link.

Are there parameters that dictate how many rows oracle batches/blocks to send over the link?

Isn't there some way to cause the result set to transfer across the link in large blocks and then most of the fetches would be performed by the local instance?

The second method is not a suitable solution since, 30 different oracle instances are involved and would need the package maintained on them.

Please email since my news database never has more than a single days worth of postings. I will post the answers. Thanks for all help.

Dave Podnar
NCR Corp.
dave.podnar_at_daytonoh.ncr.com Received on Tue Sep 17 1996 - 00:00:00 CEST