Re: PL/SQL code running slow

On Thu, 16 Sep 1999 13:21:37 -0600, "Larry Pettit" <larry.pettit_at_ps.net> wrote:

>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.

AFAIK, the output from dbms_output.put_line won't be written to the screen before the PL/SQL procedure finishes. You could try to debug to a file instead, using utl_file, or you could log to a database table, although a COMMIT done in your log table would interfere with the main transaction.

>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 Mon Sep 20 1999 - 01:21:15 CDT