Re: Slow PL/SQL bulk bind & forall performance. Is this a bug ?

It looks like you are opening a large cursor with an "order by" clause, and updating in batches.

The updates seem to be updating all the
columns in the selected data, so will be generating a large amount of UNDO.

The rows are being updated in an order
that does not match the physical order
of the table - so your next batch selected could be reading rows from blocks which
were updated earlier in the main loop -
and could therefore be reading large
amounts of UNDO segment to make
the blocks consistent with the start
of the main process.

Check which files your reads are going
to - I'd guess its the rollback files.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





Michael Carter wrote in message ...


>Hi

>

>I've been driven half mad trying to figure out what is happening with

>the following code. I've stripped this code out from a package and

>trimmed it into a kind of demonstration of my problem so someone

>explain this:

>

>I have two procedures proc_1 and proc_2 (which I will attach later in

>the thread)

>

>proc_1 demonstrates the basic functionality I want to achieve.

>1) Open cursor

>2) Loop

>3) Fetch data into nested tables (100 rows each time).

>4) Modify that fetched data inside the nested tables in some way.

>5) Write the modified data back to the original table. (100 rows each

>time)

>6) Go to the top of the loop and continue until no more data.

>