I have a cursored SELECT statement in a stored procedure in database LOCAL that selects a bunch of columns from a single table on database REMOTE via a public DB link. The cursor loops and runs the SELECT 1000 times. If you run this procedure locally on the REMOTE database, it fetches 8,000 rows in 30 seconds over 1000 executes - not bad. However, when run over the DB link, it takes 330 seconds to fetch the same rows with the same executes - not good at all. I have tried several solutions, but have not brought the remote fetch time down. When the procedure is running, however, using a monitoring tool I see alot of system waits under the "SQL*Net Message from dblink" and "SQL*Net Messages to dblink" categories. Does anyone have any suggestions as to what may be slowing this query down, or further items to check on?
This is a duplicate post ( http://www.dbasupport.com/forums/sho...threadid=21477 )
In the future, please post your question on only one forum.