ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated

发表于 2012 年 4 月 5 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

出现如下错误(ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated)

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name:    AIX
Node name:      zwq_bill_1
Release:        1
Version:        6
Machine:        00F64FF34C00
Instance name: bill1
Redo thread mounted by this instance: 1
Oracle process number: 30
Unix process pid: 46531060, image: oracle@zwq_bill_1 (J000)

*** SESSION ID:(218.47085) 2012-04-02 19:30:45.561
*** 2012-04-02 19:30:45.561
ORA-12012: error on auto execute of job 1
ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 1361
ORA-06512: at "PERFSTAT.STATSPACK", line 2471
ORA-06512: at "PERFSTAT.STATSPACK", line 91
ORA-06512: at line 1

这个是oracle的一个Bug 2784796,提供解决方法有
1.run the statspack at level 0
2.restart the instance
3.set cursor sharing to exact (probably not feasible)
4.禁用主键，创建合适非唯一index

ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY MODIFY 
CONSTRAINT STATS$SQL_SUMMARY_PK DISABLE NOVALIDATE;

5.修改STATS$V_$SQLXS视图
分析思路如下:
1)根据主键冲突找到主键包含列(spctab.sql)

create table          STATS$SQL_SUMMARY
(snap_id              number(6)        not null
,dbid                 number           not null
,instance_number      number           not null
,text_subset          varchar2(31)     not null
,sql_text             varchar2(1000)
,sharable_mem         number
,sorts                number
,module               varchar2(64)
,loaded_versions      number
,fetches              number
,executions           number
,loads                number
,invalidations        number
,parse_calls          number
,disk_reads           number
,buffer_gets          number
,rows_processed       number
,command_type         number
,address              raw(8)
,hash_value           number
,version_count        number
,cpu_time             number
,elapsed_time         number
,outline_sid          number
,outline_category     varchar2(64)
,child_latch          number
--注意下面5列构成主键
,constraint STATS$SQL_SUMMARY_PK primary key
    (snap_id, dbid, instance_number, hash_value, text_subset)
 using index tablespace &&tablespace_name
   storage (initial 1m next 1m pctincrease 0)
,constraint STATS$SQL_SUMMARY_FK foreign key (snap_id, dbid, instance_number)
                references STATS$SNAPSHOT on delete cascade
)tablespace &&tablespace_name
storage (initial 1m next 1m pctincrease 0) pctfree 5 pctused 40;

2)找到该表插入数据(spcpkg.sql)

insert into stats$sql_summary
            ( snap_id
            , dbid
            , instance_number
            , text_subset
            , sharable_mem
            , sorts
            , module
            , loaded_versions
            , fetches
            , executions
            , loads
            , invalidations
            , parse_calls
            , disk_reads
            , buffer_gets
            , rows_processed
            , command_type
            , address
            , hash_value
            , version_count
            , cpu_time
            , elapsed_time
            , outline_sid
            , outline_category
            , child_latch
            )
       select l_snap_id
            , p_dbid
            , p_instance_number
            , substrb(sql_text,1,31)
            , sharable_mem
            , sorts
            , module
            , loaded_versions
            , fetches
            , executions
            , loads
            , invalidations
            , parse_calls
            , disk_reads
            , buffer_gets
            , rows_processed
            , command_type
            , address
            , hash_value
            , version_count
            , cpu_time
            , elapsed_time
            , outline_sid
            , outline_category
            , child_latch
         from stats$v$sqlxs
        where is_obsolete = 'N'
          and (   buffer_gets   > l_buffer_gets_th
               or disk_reads    > l_disk_reads_th
               or parse_calls   > l_parse_calls_th
               or executions    > l_executions_th
               or sharable_mem  > l_sharable_mem_th
               or version_count > l_version_count_th
              );

3)找出stats$v$sqlxs对象(spcusr.sql)

create or replace view STATS$V_$SQLXS as
select max(sql_text)        sql_text
     , sum(sharable_mem)    sharable_mem
     , sum(sorts)           sorts
     , min(module)          module
     , sum(loaded_versions) loaded_versions
     , sum(fetches)         fetches
     , sum(executions)      executions
     , sum(loads)           loads
     , sum(invalidations)   invalidations
     , sum(parse_calls)     parse_calls
     , sum(disk_reads)      disk_reads
     , sum(buffer_gets)     buffer_gets
     , sum(rows_processed)  rows_processed
     , max(command_type)    command_type
     , address              address
     , hash_value           hash_value
     , count(1)             version_count
     , sum(cpu_time)        cpu_time
     , sum(elapsed_time)    elapsed_time
     , max(outline_sid)     outline_sid
     , max(outline_category) outline_category
     , max(is_obsolete)     is_obsolete
     , max(child_latch)     child_latch
  from v$sql
 group by hash_value, address;
create or replace public synonym STATS$V$SQLXS for STATS$V_$SQLXS;

4)通过这里可以看出，要是的STATS$SQL_SUMMARY主键不重复，只要是的STATS$V_$SQLXS查询出来的记录唯一，所以解决方案就是在STATS$V_$SQLXS视图中增加下列条件，确保查询出来的记录唯一，从而不会发生主键冲突

where 
( plan_hash_value > 0 
or executions > 0
or parse_calls > 0
or disk_reads > 0
or buffer_gets > 0
)

该bug在10g中修复，对于不能及时升级的数据库，建议采用第五种方法解决问题，比较治标治本，对业务基本上无影响

此条目发表在 ORA-xxxxx 分类目录，贴了 , 标签。将固定链接加入收藏夹。

《ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated》有 1 条评论

  1. 惜分飞 说：
    2012 年 4 月 5 日20:05

    Cannot Run Statspack.Snap Ora-00001 [ID 267244.1]

    Applies to:
Oracle Server - Enterprise Edition - Version: 9.0.1.4 to 10.2.0.4 - Release: 9.0.1 to 10.2
Oracle Solaris on SPARC (64-bit)

Symptoms
While taking a statspack snapshot, ORA-00001 against "PERFSTAT.STATS$SQL_SUMMARY_PK" is received:

SQL> exec statspack.snap
BEGIN statspack.snap; END;
*
ERROR at line 1:
ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 1148
ORA-06512: at "PERFSTAT.STATSPACK", line 2134
ORA-06512: at "PERFSTAT.STATSPACK", line 72
ORA-06512: at line 1
SQL>

Cause
This is Bug:2784796

Solution
Bug:2784796is fixed in Oracle 10g.

Workarounds:
run the statspack at level 0
restart the instance
set cursor sharing to exact (probably not feasible)
disable the constraint as follows:
ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY MODIFY CONSTRAINT STATS$SQL_SUMMARY_PK DISABLE NOVALIDATE;

This is not a permanent workaround due to performance problems associated with leaving  the INDEX disabled.

When a primary key is disabled, Oracle Server also drops the unique index that was enforcing disabled primary key. That is expected behavior, but in this case this index is used not only for enforcing of the uniqueness, but also for performance reasons. After dropping the index the performance of the spreport degrades. The problem can be solved by creating NON-UNIQUE index which can be used for queries. After creating the appropriate index the spreport performance is restored. In urgent situations, it is possible to apply a Statspack workaround to allow the continued collection of SQL data (i.e. level 5 snapshots and above).

The fix is to re-create the Statspack view stats$v_$sqlxs with a WHERE clause to eliminate rows which will be filtered out by IS_OBSOLETE when this bug is fixed.

The Statspack workaround is to add the following where clause to the VIEW creation of STATS$V_$SQLXS, which is in the file spcusr.sql

Copy the view creation of STATS$V_$SQLXS from spcusr.sql to a new file. ONLY COPY THE VIEW CREATE OF THIS VIEW, and no other text from this file
Edit the view create to add the WHERE clause:

where 
( plan_hash_value > 0 
or executions > 0
or parse_calls > 0
or disk_reads > 0
or buffer_gets > 0
)
The rest of CREATE should remain unchanged. Log in as a user with SYSDBA priviledge, and run the new file you have created. The view should create without errors.
Snapshots (>= level 5) should not fail with this workaround.

Note:
There is a secondary bug in 10g with the same symptoms of this which is not feasible to fix for an obcelete product. This bug is:  Bug:6796656 where running certain SQL statements whose  OLD_HASH_VALUE and the first 31 bytes of SQL_TEXT are the same. There is no workaround other than to use AWR functionality.