Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Performance probs with db_links in PL/SQL

Performance probs with db_links in PL/SQL

From: Dwight Walker <dwightw_at_qld.mim.com.au>
Date: Thu, 1 Jul 1999 09:47:27 +1000
Message-ID: <7leaai$ffd$1@news.mel.aone.net.au>


I have a procedure that has a cursor for loop that basically selects all columns from a remote ( but on the same physical system ) database and inserts them into a local table.

It takes hours to insert 100,000 rows using the PL/SQL procedure, but a couple of minutes via a " create table as select .... " or "insert into table select..." SQL statements. There are no indexes on the local tables, and no indexes used on the remote table as it is selecting every row anyway.

After some investigation, it seems that if the cursor returns any more than 43 columns the time taken to run startes to blow out 100 fold, but less than 43 columns, run times are acceptable.

Have even taken out the insert statement so the cursor just does a count of the number of rows processed, and reduced the number of rows returned and the times are still shockingly slow e.g over 8 minutes for 10,000 rows

If the procedure is run using all local tables it is lightening fast!

Any help would be muchly appreciated.

Dwight Walker Received on Wed Jun 30 1999 - 18:47:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US