Re: PL/SQL code running slow

1. Your commit after every insert is adding a lot of overhead to the process. If you add an "IF" statement to commit after every x number of inserts, the process will be much faster. I.E. IF commit_cntr >= commit_limit THEN COMMIT ; commit_cntr := 1 ; ELSE commit_cntr := commit_cntr + 1 ; END IF ; This could also be be encapsulated in a procedure that performs the commit in a controlled manner. The commit_limit should be high, close to your RBS segmetn sizde limit. This will provide maximum performance.
2. The DBMS_OUTPUT.PUT_LINE will only flush its buffer when th eouter most PL*SQL block is exited. I.E. at the end of your program. The simplest method would be to log (INSERT) the information into a table just before the commit. Then you could query that table to see where the process is at.

HTH
    James

In article <jmbE3.348$aW1.36799_at_news.uswest.net>,   "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.
>
> 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
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Sep 20 1999 - 13:09:45 CDT