statspack report hangs

Problem: Statspack report hangs 

Executing @?/rdbms/admin/spreport.sql report hangs in midway & unable to proceed further until cancelled

Even if we try to keep it for Hours after undo_retention limit it will throw "ORA-01555 snapshot too old" & come out

Such issue is observed in pre-10g databases where there is no other substitute to stats-pack & its due to huge db object count

Analysis:

Initial analysis,  i kept the report running for over night & it kicked me out with ORA-01555 error after 9 Hours

Further , PERFSTAT.STATS$SQL_SUMMARY table found to be the problem causing table which was doing "db file sequential read" infinitely 


db file read wait event lead us towards indexes which were not found as there were constraints present on the table

SQL> select owner,CONSTRAINT_NAME,TABLE_NAME,STATUS from dba_constraints  where table_name='STATS$SQL_SUMMARY';

OWNER        CONSTRAINT_NAME      TABLE_NAME           STATUS
------------ -------------------- -------------------- --------
PERFSTAT     SYS_C0032056         STATS$SQL_SUMMARY    ENABLED
PERFSTAT     SYS_C0032057         STATS$SQL_SUMMARY    ENABLED
PERFSTAT     SYS_C0032058         STATS$SQL_SUMMARY    ENABLED
PERFSTAT     SYS_C0032059         STATS$SQL_SUMMARY    ENABLED
PERFSTAT     STATS$SQL_SUMMARY_PK STATS$SQL_SUMMARY    DISABLED
PERFSTAT     STATS$SQL_SUMMARY_FK STATS$SQL_SUMMARY    ENABLED

6 rows selected.


Primary key constraint found to be disabled.


Now , why was it disabled ?

Here it comes ..

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


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

Workaround was to disable the constraint & we did it quickly without knowing impact

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



Solution:

Solution for spreport slowness is to enable constraint followed by statistics gathering so that table would be Index scanned to retrieve report faster. 

Enable constraint:

ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY ENABLE CONSTRAINT STATS$SQL_SUMMARY_PK ;

OR

ALTER TABLE PERFSTAT.STATS$SQL_SUMMARY modify CONSTRAINT STATS$SQL_SUMMARY_PK ENABLE NOVALIDATE; 

Gather Stats:

exec dbms_stats.gather_table_stats('PERFSTAT','STATS$SQL_SUMMARY',estimate_percent => 100,cascade => true , no_invalidate => false);
OR

exec dbms_stats.gather_schema_stats('PERFSTAT',estimate_percent => 100,cascade => true , no_invalidate => false);
There are chances of ORA-02437 while enabling constraint again ,  so you may enable the constraints post @?/rdbms/admin/sptrunc.sql

Long term solution for ORA-02437/ORA-00001 would be to upgrade to 10g