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

From: Bernhard Graeuler <fuerspam_at_gmx.de>
Date: Sun, 19 Dec 2004 13:18:44 +0100
Message-ID: <32l9t4F3pbdpsU1@individual.net>

Jim Smith <jim_at_jimsmith.demon.co.uk> schrieb:
> In message <32ge68F3mhfnuU1_at_individual.net>, Bernhard Graeuler
> <fuerspam_at_gmx.de> writes
> > Hi,
> > [...]
> > 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.
> > [...]
> Has this only happened once, or does it happen every time a high
> volume of records is imported?

At first this was not reproducible over here, and we had no chance to find out the reason by asking questions to our customer. We got complete dumps of his database and the source database, imported them here and everything ran fine. But then, "suddenly", and of course without changing anything, and by using completely other data it happens here, too. *sick* These huge delays of one day or more dont occur here, but we got measurable delays of one or two hours for each block of 500 imported records.

> 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?

Well, regarding to your hint I checked this in detail and found this:

Import log (see the delay of 35 minutes for the last block of 200 steps):

17.12.2004 12:46:32,0,  *** Step : 12000
17.12.2004 12:46:32,0, delay : 17.12.2004 12:46:32 - 01:07:14 (00:01:07)
17.12.2004 12:52:59,0,  *** Step : 12200
17.12.2004 12:52:59,0, delay : 17.12.2004 12:52:59 - 01:13:41 (00:06:26)
17.12.2004 13:28:51,0,  *** Step : 12400
17.12.2004 13:28:51,0, delay : 17.12.2004 13:28:51 - 01:49:33 (00:35:52)
*****************

Time - fitting snippet of the alert log: (see the entry on 13:28:20 which was written during the 35min block above)

Fri Dec 17 13:01:38 2004
Thread 1 advanced to log sequence 36500
  Current log# 3 seq# 36500 mem# 0: C:\ORACLE\ORADATA\MDP20\LOG3.ORA Fri Dec 17 13:28:20 2004
Thread 1 advanced to log sequence 36501
  Current log# 4 seq# 36501 mem# 0: C:\ORACLE\ORADATA\MDP20\LOG4.ORA Fri Dec 17 13:56:38 2004
Thread 1 advanced to log sequence 36502
  Current log# 5 seq# 36502 mem# 0: C:\ORACLE\ORADATA\MDP20\LOG5.ORA Fri Dec 17 13:57:27 2004
Errors in file D:\oracle\admin\mdp20\udump\ORA02320.TRC:

Fri Dec 17 13:59:46 2004
Thread 1 advanced to log sequence 36503
Current log# 6 seq# 36503 mem# 0: C:\ORACLE\ORADATA\MDP20\LOG6.ORA

I took a look in tho the ORA02320.TRC file, but it seems that this is the error message that occurs after I killed the DCOM server that was executing the import.

Best regards

Bernhard Gräuler Received on Sun Dec 19 2004 - 06:18:44 CST