Statspack ORA-00001 Bug

最新推荐文章于 2025-05-01 23:24:04 发布

cmq888602

最新推荐文章于 2025-05-01 23:24:04 发布

阅读量110

点赞数

文章标签：数据库

本文记录了一次在Oracle 9.2.0.8版本中遇到的性能统计错误，包括详细的错误日志与解决步骤。文章指出，在特定条件下，如设置cursor_sharing为similar或force时，可能会触发此错误，并提供了禁用约束、创建视图等解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

From alert log :

< 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

environment : Redhat AS4 , oracle9208

Bug number : 2784796

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

2.sqlplus " / as sysdba"
SQL> spool run_statspack.txt

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
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;
SQL> select owner,object_name from dba_objects where status ='INVALID';
SQL> @?/rdbms/admin/utlrp.sql
SQL> select owner,object_name from dba_objects where status ='INVALID';
conn perfstat/perfstat_password execute
SQL> statspack.snap(i_snap_level=>5); spool off;