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

O erro ORA-00001: unique constraint (PERFSTAT.STATS$SQL_SUMMARY_PK) violated ao executar a procedure statspack.snap para geração de um novo snapshot, pode estar relacionado ao bug 2784796.

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 "SYS.STATSPACK", line 4380
ORA-06512: at "SYS.STATSPACK", line 5729
ORA-06512: at "SYS.STATSPACK", line 105
ORA-06512: at line 1

• Workaround:

Como medida de contorno para este erro, podemos alterar a view STATS$V_$SQLXS adicionando a clausula where abaixo. A execução deve ser feita com o usuário SYS.

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

--VIEW COMPLETA

create or replace view STATS$V_$SQLXS as
select max(sql_text)        sql_text
     , max(sql_id)          sql_id
     , sum(sharable_mem)    sharable_mem
     , sum(sorts)           sorts
     , min(module)          module
     , sum(loaded_versions) loaded_versions
     , sum(fetches)         fetches
     , sum(executions)      executions
     , sum(px_servers_executions) px_servers_executions
     , sum(end_of_fetch_count) end_of_fetch_count
     , sum(loads)           loads
     , sum(invalidations)   invalidations
     , sum(parse_calls)     parse_calls
     , sum(disk_reads)      disk_reads
     , sum(direct_writes)   direct_writes
     , sum(buffer_gets)     buffer_gets
     , sum(application_wait_time)  application_wait_time
     , sum(concurrency_wait_time)  concurrency_wait_time
     , sum(cluster_wait_time)      cluster_wait_time
     , sum(user_io_wait_time)      user_io_wait_time
     , sum(plsql_exec_time)        plsql_exec_time
     , sum(java_exec_time)         java_exec_time
     , sum(rows_processed)  rows_processed
     , max(command_type)    command_type
     , address              address
     , old_hash_value       old_hash_value
     , max(hash_value)      hash_value
     , count(1)             version_count
     , sum(cpu_time)        cpu_time
     , sum(elapsed_time)    elapsed_time
     , null                 avg_hard_parse_time
     , max(outline_sid)     outline_sid
     , max(outline_category) outline_category
     , max(is_obsolete)     is_obsolete
     , max(child_latch)     child_latch
     , max(sql_profile)     sql_profile
     , max(program_id)      program_id
 , max(program_line#)   program_line#
     , max(exact_matching_signature) exact_matching_signature
     , max(force_matching_signature) force_matching_signature
     , max(last_active_time)         last_active_time
  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 old_hash_value, address
 ;

Após a recriação da view, será necessário limpar as tabelas do statspack. Para isso vamos usar o script sptrunc.sql. 

É recomendável que seja realizado um backup do owner antes de realizar a limpeza, assim você não perderá dados históricos para consultas futuras.

SQL> conn perfstat/senha
Connected.
SQL>
SQL>
SQL> show user;
USER is "PERFSTAT"
SQL> @?/rdbms/admin/sptrunc.sql

Warning
~~~~~~~
Running sptrunc.sql removes ALL data from Statspack tables.  You may
wish to export the data before continuing.

About to Truncate Statspack Tables
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
If would like to exit WITHOUT truncating the tables, enter any text at the
begin_or_exit prompt (e.g. 'exit'), otherwise if you would like to begin
the truncate operation, press <return>

Enter value for begin_or_exit:
Entered at the 'begin_or_exit' prompt

... Starting truncate operation

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

Table truncated.

268 rows deleted.

5 rows deleted.

Commit complete.

Package altered.

... Truncate operation complete

SQL> SQL>

Concluído o processo de truncate, basta desabilitar a constraint e recriar os índices associados a mesma. Feito isso o processo executará normalmente.

.

Supporting metalink note: Cannot Run Statspack.Snap Ora-00001 [ID 267244.1]