Oracle interrupts work without an obvious reason

Our Oracle-DB stops doing anything during a transaction of our client program. The processor of either the client and the server is totally unused, we can not find any other activity on both machines, but the running transaction is not finished until this point. The strange thing about this is, that Oracle can be accessed by any other program (e.g. management programs, SQL Plus) with a normal reaction.

Our environment:
Server and clients on Win NT 4.0
Oracle 7.3.4
Program written in VB 6.0, using ADO 2.1 with MSDAORA as OLE DB-Provider

Here is what we did:
We wrote a program to import a huge amount of data into our program-database. The data is read out of a text-file (about 48 MB, but not completely imported). To store the data we use two different users. One of them holds the data in the format of the text-file, the other user contains the database of our program. On each of these users we open one transaction for the whole import.
The first step of our program imports a part of data from the text-file into first user. The data is split to over 66 tables and merged with their old content. After the import some SQL-statements run on this user to fill a linked table to each of the 66 data-tables. After this the data is read by the client to be translated and transferred into the other user. For that purpose we put together large SELECT-statements (each with some kB code) to receive the data from the first user. The result is stored in an ADO-recordset-object. We step through these recordsets an generate INSERTs for the second user.
At this point our program makes trouble. While stepping through the recordsets we found that the performance sometimes gets very slow and then nothing more happens. Performing a number of tests shows, that this effect occurs unpredictable at various points. When running our program in the debugger it is not possible to break it when the process is interrupted. As told at the beginning the Orcale-server is still active and responding any other requests. But without other requests the server is doing nothing. And it seems not to be a temporary state.
There is no error-message or other abnormal messages in the logfiles we browsed. Swapping could not be a reason too, because there is no activity on any harddisk and only 125MB of 1GB RAM is used. The processor is only working for watching the server by pcAnywhere. The process ORACLE73 shows no activity at all.

A network error should not have occurred, because this program did his task with another (old) server in the same network (but incredibly slow, far more than 24 hours against an estimated time of two or three hours on the new server).
When we first tried to run the program on the new server then dimension of the temporary-data-tablespace was not large enough (with 150MB), but this was the reason for an error message. The result was that a recordset contained no more data in a row that should otherwise be valid . After resizing it the problem was fixed.

Has anyone noticed such a phenomenon too? Has anyone an idea what we can do to find out what the problem is? We would appreciate any hint.

I think the key could be the amount of data being processed in the two transactions. They will be committed only when everything from reading the text-file over translation of data and storing it in the second user is done without an error. The rollback-segments seem not to be very large (maximum 15MB). But if any space on disk or in memory is exceeded I expect an error.

Thanks

Georg Schulte Althoff Received on Fri Oct 29 1999 - 05:51:38 CDT