Re: Operations in huge transaction slow down suddenly. Why?

In message <32ge68F3mhfnuU1_at_individual.net>, Bernhard Graeuler <fuerspam_at_gmx.de> writes
>Hi,
>
>my problem is not very easy to explain. Basically what we're doing is an
>import of many records to the database. Therefore, at first we load the new
>data by creating new local tables with "Create Table as select * from ..."
>over a database link.
>
>Then we have to merge the existing data with te newly loaded data. In this
>step (roughly explained), new records were inserted in the existing tables.
>Information about newr- and changes on existing data were reported as
>conflicts into a separate table. These conflicts have to be resolved
>manually after the import finishes.
>This second step is running in one single transaction. Many thousands of
>records were inserted into the table containing the conflicts.
>
>Ok. Now what is the problem? After inserting about 15000 records into the
>conflict table and executing several thousand updates or inserts into the
>existing tables the process suddenly slows down. We detect this by writing a
>report entry containing a timestamp, every block of 500 processed steps.
>

Has this only happened once, or does it happen every time a high volume of records is imported?

Once cause of slow updates can be problems with redo logs and log archiving. Does your alert log show any issues with log switching or archiver waits?

-- 
Jim Smith
Because of their persistent net abuse, I ignore mail from
these domains (among others) .yahoo.com .hotmail.com .kr .cn .tw
For an explanation see <http://www.jimsmith.demon.co.uk/spam>