problem: unique constraint error reported in alert log and load spike in unix box

From: Zhu Chao <zhuchao_at_gmail.com>
Date: 9 Oct 2005 03:36:00 -0700
Message-ID: <1128854160.290808.277080@g43g2000cwa.googlegroups.com>

hi, all,
  We are running 9.2.0.5 on solaris 8, and we have some major problem with unique constraint error.
  Generally when unique constraint hit, application will get an ORA-00001 and that's it. But in our case recently we see some weird behavior from oracle.
  When we hit the unique constraint error and it got loged into alert log, and the load on unix box spikes. Application has been running for 1+ year and recently we upgraded database from 8174 to 9205.

Example:
Wed Oct 5 11:00:43 2005
Errors in file /oracle/TCASH/trace/udump/tcash_ora_2448.trc: ORA-00001: unique constraint (TRANSACTION_CACHE_PK) violated Wed Oct 5 11:00:43 2005
Errors in file /oracle/TCASH/trace/udump/tcash_ora_70.trc: ORA-00001: unique constraint (TRANSACTION_CACHE_PK) violated Wed Oct 5 11:00:43 2005
Errors in file /oracle/TCASH/trace/udump/tcash_ora_17249.trc: ORA-00001: unique constraint (TRANSACTION_CACHE_PK) violated Wed Oct 5 11:04:40 2005

And hte load on unix box(10 second per data)

 15.52
 16.32
 16.30
 16.32
 18.05
 18.59
 20.82
 90.97


 86.30
 76.52
 69.13
 61.43
 54.98
 50.22

 45.51
 41.21

That makes our site suffers.

Anyone have seen that before? THis appears only during heavy load time.

Table is IOT table.

Thanks Received on Sun Oct 09 2005 - 05:36:00 CDT