Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Performance probs with db_links in PL/SQL
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