No Statspack Snapshot for You!

This Statspack bug is an older one, but I ran into it this week again.
The work around is very simple to correct the problem, but its surprising how
disconcerting it can be for anyone seeing “unique constraint violated”.

Seen in the alert log:
Tue Dec 14 09:00:43 2010
Errors in file <dir>/xxxx_j000_xxxx.trc:
ORA-12012: error on auto execute of job xxx
ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated
ORA-06512: at “PERFSTAT.STATSPACK”, line 5264
ORA-06512: at “PERFSTAT.STATSPACK”, line 104
ORA-06512: at line 1

*****************************************************
Pulled from the trace file listed above from the alert log:
<dir>/xxxx_j000_xxxx.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = <dir>
System name: Linux
Node name: HOST
Release: 2.6.18-194.3.1.el5
Version: #1 SMP Sun May 2 04:17:42 EDT 2010
Machine: x86_64
Instance name: xxxx
Redo thread mounted by this instance: 1
Oracle process number: 696
Unix process pid: 6465, image: xxxx (J000)

*** SERVICE NAME:(SYS$USERS) 2010-12-14 09:00:43.979
*** SESSION ID:(4982.60126) 2010-12-14 09:00:43.979
*** 2010-12-14 09:00:43.979
ORA-12012: error on auto execute of job 261
ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated
ORA-06512: at “PERFSTAT.STATSPACK”, line 5264
ORA-06512: at “PERFSTAT.STATSPACK”, line 104
ORA-06512: at line 1

**************************************************************
Bug Info with Oracle Recommendations:

Oracle: PERFSTAT.STATS$MUTEX_SLEEP_PK violated
There is a known problem with Oracle statspack report in 10g, which could
cost You at least one lost statspack snapshot.

Pretty clear indicator you have the problem…yep, same lines for both:

ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated
ORA-06512: at ???PERFSTAT.STATSPACK???, line 5264
ORA-06512: at ???PERFSTAT.STATSPACK???, line 104
ORA-06512: at line 1

View Metalink Note 382993.1 to get the low-down on this one, but the
workaround is an easy implementation-
Per Oracle:
Disable the Statspack snapshot job, and perform the following:

alter table perfstat.stats$mutex_sleep disable constraint STATS$MUTEX_SLEEP_PK;

create index perfstat.STATS$MUTEX_SLEEP_PK on

STATS$MUTEX_SLEEP(SNAP_ID,DBID,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION);
Once complete, enable the job with the correct next date in dba_jobs

and you are back in business!