www.eygle.com

还应当提醒他建一个同样结构的非维一性的索引, 否则snap多了, 要生成一个report很慢的.

我们已经这样做了好多次了.

恩，这个错误我是第一次见到，Metalink上好多说明，懒得看了。

你们出问题的系统是RAC环境么？

我们是在做statspack升级时发现的

我们一个crm库，非rac，
cursor_sharing string EXACT
一样还是遇到了同样的问题。

metalink有个workaround

重建view

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
where ( plan_hash_value > 0
or executions > 0
or parse_calls > 0
or disk_reads > 0
or buffer_gets > 0
)
group by hash_value, address;

create or replace public synonym STATS$V$SQLXS for STATS$V_$SQLXS;

我们是由于v$sqlarea里存在4句相同的语句导致，第一次遇到这么奇怪的情况，sigh，浪费了一点eygle的电话费，呵呵。

当时正在参加F5的一个技术研讨会，啥都没帮上：）

如果是rac环境，提示ora-00001错误，但是在单机情况下不报错，会是什么原因呢？

我的环境 rac 9.2.0.5 一般在一台数据库重启后就会出现该错误 我一般把历史数据清掉就ok了 有时过几天不去管他 又好了 我怀疑跟内存数据有关系

$ sqlplus perfstat

SQL*Plus: Release 9.2.0.5.0 - Production on Mon Feb 27 10:34:21 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning and Real Application Clusters options
JServer Release 9.2.0.5.0 - Production

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 1361
ORA-06512: at "PERFSTAT.STATSPACK", line 2442
ORA-06512: at "PERFSTAT.STATSPACK", line 91
ORA-06512: at line 1
过几分钟

SQL> exec statspack.snap

PL/SQL procedure successfully completed.

oracle 10gR2 里面还有一个bug ，今天遇到，ft

Errors in file /opt/oracle/admin/forum/bdump/forum11_j000_17729.trc:
ORA-12012: error on auto execute of job 62
ORA-00001: unique constraint (PERFSTAT.STATS$MUTEX_SLEEP_PK) violated
ORA-06512: at "PERFSTAT.STATSPACK", line 5264
ORA-06512: at "PERFSTAT.STATSPACK", line 104

SQL> select index_name,column_name from dba_ind_columns where table_name = 'STATS$MUTEX_SLEEP';

INDEX_NAME
------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
STATS$MUTEX_SLEEP_PK
SNAP_ID

STATS$MUTEX_SLEEP_PK
DBID

STATS$MUTEX_SLEEP_PK
INSTANCE_NUMBER

INDEX_NAME
------------------------------
COLUMN_NAME
------------------------------------------------------------------------------------------------------------------------
STATS$MUTEX_SLEEP_PK
MUTEX_TYPE

STATS$MUTEX_SLEEP_PK
LOCATION

SQL> alter table STATS$MUTEX_SLEEP disable constraint STATS$MUTEX_SLEEP_pk;

Table altered.

SQL> select index_name,column_name from dba_ind_columns where table_name = 'STATS$MUTEX_SLEEP';

no rows selected

SQL> create index STATS$MUTEX_SLEEP_pk on STATS$MUTEX_SLEEP(snap_id,dbid,INSTANCE_NUMBER,MUTEX_TYPE,LOCATION) tablespace users;

Index created.

SQL> select next_date from user_jobs;

NEXT_DATE
-------------------
2006-08-16 10:29:08
2006-08-16 12:00:00

SQL> exec dbms_job.run(62);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select count(*) from STATS$MUTEX_SLEEP;

COUNT(*)
----------
5

SQL> select * from STATS$MUTEX_SLEEP;

SNAP_ID DBID INSTANCE_NUMBER MUTEX_TYPE LOCATION
---------- ---------- --------------- -------------------------------- ----------------------------------------
SLEEPS WAIT_TIME
---------- ----------
5298 1834086304 1 Cursor Parent kkspsc0 [KKSPRTLOC26]
2 100

5298 1834086304 1 Cursor Pin kksLockDelete [KKSCHLPIN6]
1881 10384899

5298 1834086304 1 Cursor Pin kkslce [KKSCHLPIN2]
877 6442099

SNAP_ID DBID INSTANCE_NUMBER MUTEX_TYPE LOCATION
---------- ---------- --------------- -------------------------------- ----------------------------------------
SLEEPS WAIT_TIME
---------- ----------
5298 1834086304 1 Cursor Pin kksfbc [KKSCHLFSP2]
296 1699393

5298 1834086304 1 Cursor Pin kksfbc [KKSCHLFSP2]
5835 116531144

SQL>