pl/sql code running slow

From: Larry Pettit <larry.pettit_at_ps.net>
Date: Thu, 16 Sep 1999 13:23:29 -0600
Message-ID: <_nbE3.350$aW1.37012@news.uswest.net>

I'm running a PL/SQL database conversion program in SQL *Plus. The first time I ran it, my rollback segments filled up. It does several joins with external Oracle databases. I changed it to commit right after the insert statement, so my rollback segments wouldn't grow. It seems to run extremely slow, it does use one cursor. Can I do anything to speed it up, such as doing commits differently, logging etc.

Later on I added a DBMS_OUTPUT.PUT_LINE(' counter ', v_ctr) statement which probably makes it run even slower, but I'm trying to find out what's going on. It seems like it inserts all the data before displaying the counter back to the screen in SQL *Plus.

Any suggestions? Are there any settings to set in SQL *Plus other than 'set serveroutput on size XXXXXX'? Would it be better to run it in SQL Worksheet?

(background info, ORACLE 8, NT around 150,000 rows on the main table)

Thanks Received on Thu Sep 16 1999 - 14:23:29 CDT