Re: HELP: Commits Inside Cursor Loops

From: Gopal Santhanam <gopal_at_bisc.EECS.Berkeley.EDU>
Date: 11 Dec 1999 01:20:31 GMT
Message-ID: <82s8sv$754$1@agate.berkeley.edu>

Hi,

I've been following this thread with some interest since I am batching an interface table into my database, using a cursor loop in a stored procedure. However, I'm a little green with Oracle and could use some advice on improving the performance. It would also help to understand what is actually happening.

I have a cursor loop that needs to process about 20,000 interface table records. All of the sql statements happen inside the loop. I need fine grain rollback/commit on a per iteration basis since I parse the interface table into a bunch of internal tables. If the information in the interface table record is invalid, I need to rollback the stuff and set the interface record load_status to a failure string.

Here is a stripped version of my code highlighting the relevant statements.

DECLARE
    cursor cur is select rowid row_id, ... from interface

        where load_status = 'NEW';
BEGIN
    FOR cur_rec in cur LOOP

/* Bunch of stuff in here that deals with the interface table information

       and performs inserts on multiple tables */

/* If something goes wrong, we rollback the changes for this iteration */
    IF error = 1 THEN

        rollback;
        update interface set load_status = 'ERROR'
            WHERE rowid = cur_rec.row_id;
    ELSE
        update interface set load_status = 'SUCCESS'
            WHERE rowid = cur_rec.row_id;

    END IF;     commit;
    END LOOP
END load_proc;

First, I used the cursor with "for update" and saw I needed a huge rollback segment (the standard r01 with extentsize=128k and maxextents=2G). I understand that using commit on the "for update" cursor closes the cursor. I figure since my select statement is for "load_status = 'NEW'", I never repeat the completed records. However, I don't really understand why I'd ever get the dreaded "snapshot too old" error if I'm using "commit" at every loop iteration!

Without "for update" I can use a normal rollback segment (extentsize=10k maxextents=4096). But the latter scheme seems to be much slower. I have some guesses as to why this might be, but I'm not entirely sure.

What is going on here? I keep hearing about using control loops outside the cursor loop, but I don't what it is or how it might apply here.

Thanks in advance!
Gopal Received on Fri Dec 10 1999 - 19:20:31 CST