Cannot Oracle handle the issue of big data concurrency?

 

Cannot Oracle handle the issue of big data concurrency?

 

The day before yesterday the user suddenly reported that a software always reported a ora-00603 error. At the beginning, a common tablespace is insufficient, but it is not that simple to read logs.

 

Part of the captured logs are as follows:

Thu Nov 05 15:28:53 2009
Errors in file D: \ oracle \ admin \ orcl \ udump \ orcl_ora_4684.trc:
ORA-00603: Oracle Server session terminated by fatal error
ORA-01114: Io Error writing block to file 11 (block #42773)
ORA-27041: Unable to open file
OSD-04002: Unable to open file
O/s-error: (OS 32) another program is using this file and the process cannot be accessed.
ORA-01114: Io Error writing block to file 11 (block #42773)
ORA-27041: Unable to open file
OSD-04002: Unable to open file
O/s-error: (OS 32) another program is using this file and the process cannot be accessed.
ORA-01114: Io Error writing block to file 11 (block #42773)
ORA-27041: Unable to open file
OSD-04002: Unable to open file
O/s-error: (OS 32) another program is using this file and the process cannot be accessed.

 

File 11 is a data file of the tablespace used by my program. A total of four data files in the tablespace add up to about 8 GB, and the overall usage is around 70%. The data file numbers are 9, 11, 13, and 14 respectively. The problematic file number is not necessarily the same, and the time is also random. It may repeat the above log in one minute, or it may be repeated in several seconds.

The following is a snippet of the orcl_ora_4684.trc file:

 

Jserver release 9.2.0.1.0-Production
Windows 2000 version 5.2 Service Pack 2, CPU type 586
Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 30

Windows Thread ID: 4684, image: Oracle. exe

* ** Session ID: (39.280) 15:28:52. 000
* ** 15:28:52. 000
Ksedmp: internal or fatal error
ORA-01114: Io error when writing block to file 11 (block #42773)
ORA-27041: Unable to open file
OSD-04002: Unable to open file
O/s-error: (OS 32) another program is using this file and the process cannot be accessed.
ORA-01114: Io error when writing block to file 11 (block #42773)
ORA-27041: Unable to open file
OSD-04002: Unable to open file
O/s-error: (OS 32) another program is using this file and the process cannot be accessed.
ORA-01114: Io error when writing block to file 11 (block #42773)
ORA-27041: Unable to open file
OSD-04002: Unable to open file
O/s-error: (OS 32) another program is using this file and the process cannot be accessed.
Current SQL statement for this session:
Insert into "vio_jdczp" ("XH", "hphm", "hpzl", "photo1") values (: 1,: 2,: 3,: 4) Returning rowid: 5
----- Call stack trace -----
Calling call entry argument values in hex
Location Type Point (? Means dubious value)
----------------------------------------------------------------------------
_ Ksedmp + 147 callrel _ ksedst + 0
.. 1.44 _ 7. Fig _ ksedmp + 0 3
+ FC
.. 1.1 _ 3. Fig + A callrel _ ksupop + 0 2
F
_ Ttcpip + a86 callreg 00000000 5E 14 8ace738 0
_ Opitsk + 2f4 callrel _ ttcpip + 0
_ Opiino + 5fc callrel _ opitsk + 0 0 0 a616320 6d1f564 C2 0
_ Opiodr + 4CD callreg 00000000 3C 4 8acfbd8
_ Opidrv + 233 callrel _ opiodr + 0 3C 4 8acfbd8 0
_ Sou2o + 19 callrel _ opidrv + 0
_ Opimai + 10a callrel _ sou2o + 0
_ Oraclethreadstart @ callrel _ opimai + 0
4 + 35c
7c0000826 callreg 00000000
From this log, we can see that "Current SQL statement for this session:
Insert into "vio_jdczp" ("XH", "hphm", "hpzl", "photo1") values (: 1,: 2,: 3,: 4) Returning rowid: 5 "is executing" insert into "vio_jdczp" ("XH", "hphm", "hpzl", "photo1") values (: 1,: 2,: 3,: 4) Returning rowid into: 5 ", and some errors are the same. This statement writes an image to the database.

 

The software is structured in Cs and has eight clients in total. Each three seconds, eight clients input a data entry. The data includes some text information and an image with a size ranging from 80 KB to kb. Images are directly stored in the database.

 

Currently, this problem does not occur when a single user is input. This problem will soon occur when eight users are input together. The Oracle parameters are checked, and there are no limits on the number of users and the limit on the session.

 

It is strange now that Oracle is a large database and it is impossible to have such problems similar to concurrent access. From the simple log analysis, it can be seen that when one user data is not written, another user writes data, causing data files to be occupied. As tested in the program, the use of transactions is the same as the use of non-transaction results, basically can eliminate the reason for the transaction to lock the file.

 

I don't know which of my friends in the garden can explain this problem. I can't find any reason on the Internet. I can only ask you for advice here.