SEO

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.

Example:
Wed Oct 5 11:00:43 2005
Errors in file /ebay/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 /ebay/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 /ebay/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

On 9 Oct 2005 03:28:37 -0700, "chao_ping" <zhuchao@gmail.com> wrote:

>Anyone have seen that before? THis appears only during heavy load time.
>Table is IOT table.

You need to fix your process instead of starting to complain Oracle
barks about it.

--
Sybrand Bakker, Senior Oracle DBA

This is hardly an answer.

At any rate, we found a similar problem recently on AIX 5.1, Oracle
9.2.0.5. After re-partitioning some tables using dbms_redefinition, a
primary key to be created on the new table version took two hours (1M
rows), and still wasn't validated. The creating session was waiting on
'direct path read' and consuming huge amounts of CPU. However, a
unique index and NOT NULL constraint were created on the same table and
column in less than two minutes.

I realize this is not exactly your problem, Zhu, but it sounds like
something is amiss in Oracle 9205 land with respect to constraints and
validation of such. I would be curious to see what is the session
waiting on in your case.

Regards,


Steve