Re: Remote cursors (cursors across DB links)

One problem you are probably running into is that no indexes are used when a remote table is accessed over a db link..so your select statement is doing a full table scan for each row returned...Combine this with the added overhead of the link itself, and slow is the normal result..

mlm <not_at_listed.com> wrote:

>I need some advice regarding a PL/SQL procedure that creates a cursor
>against a table that is in another database. We have a DB link to the
>remote database, and I create this cursor using the @ sign to signify
>that the table is a remote table. Our site has a T1 connection to this
>remote database, but the procedure runs very slowly. The procedure runs
>for hours and I kill it before it completes.
>
>My solution was to create a temporary table in the local database, copy
>all of the remote tables contents to this temp table and create the
>cursor against this temp table. This method cuts the execution time of
>the proc to ~13 minutes. There are about 90K records in this table.
>
>Are there any other ways to accomplish this task? I would prefer not to
>use this solution, if there was some way to make the remote cursor run
>faster. Has anyone run into problems like this before? Can anyone
>point me to any resources on the net that talk about this issue? I've
>looked all over and can't find anything that references my problem.
>
>Thanks,
>Mike
Received on Tue May 29 2001 - 11:03:23 CDT