Re: pl/sql code running slow

Larry,

The technique of tracing, which works very well for debugging programs in different languages, does not work the same way when you debug Oracle PLSQL in SQLPlus. The reason is in the way it is executed: the whole plsql block is sent to the server and executed there, any "output" from dbms_output is placed into some internal table and the lines that you see on your screen are gotten by SQLPlus AFTER the code is executed.

If you want to diagnoze runtime problems, you may want to debug it with PLSQL debuggers instead. Those debuggers are available, f.e., in Dev2k, TOAD, Platinum stuff.

You should choose a good tradeof between the frequency of committing and the size of rollback segments. Frequent commits slow down performance. You may try committing every 10 or 100 records, f.e.

But the first thing to do is to check you queries individually. If they perform badly, tune them first. The last thing to do is to set NOLOGGING and other things.

Regards,
Karen Abgarian.

Larry Pettit 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.
>
> 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 Fri Sep 17 1999 - 10:15:08 CDT