The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views.
The statistics collected and processed by AWR include:
Object statistics that determine both access and usage statistics of database segments
Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL andV$SESS_TIME_MODEL views
Some of the system and session statistics collected in the V$SYSSTAT and V$SESSTAT views
SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and CPU time
- ASH statistics, representing the history of recent sessions activity
Started noticing ORA-600 errors in the alert log. Flushing system buffer cache did not resolve the issue. On close examination, there were missing objects in WRH$_ACTIUVE_SESSION_HISTORY, and indication of AWR corruption. Time to rebuild AWR. Steps to re-build it below:
SQL> conn /as sysdba
Connected.
SQL> show parameter cluster_database;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
SQL> show parameter statistics_level
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 0
-- Downtime starts from this point
SQL> shutdown immediate;
Datbase closed.
Database dismounted.
ORACLE instance shut down.
SQL>startup restrict;
ORACLE instance started.
SQL>@$ORACLE_HOME/rdbms/admin/catnoawr.sql
SQL>alter system flush shared_pool;
SQL>@$ORACLE_HOME/rdbms/admin/catawrtb.sql
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
-- On 11g it is required to also run:
SQL>@$ORACLE_HOME/rdbms/admin/execsvrm.sql
SQL>shutdown immediate;
SQL>startup;
-- End of Downtime
-- Check for invalid objects, re-compile
SQL> spool objects.list
SQL> set pagesize 500
SQL> set linesize 100
SQL> select substr(comp_name,1,40) comp_name, status, substr(version,1,10) versi
on from dba_registry order by comp_name;
COMP_NAME STATUS VERSION
---------------------------------------- ----------- ----------
JServer JAVA Virtual Machine VALID 11.1.0.7.0
OLAP Analytic Workspace VALID 11.1.0.7.0
OLAP Catalog VALID 11.1.0.7.0
Oracle Data Mining VALID 11.1.0.7.0
Oracle Database Catalog Views VALID 11.1.0.7.0
Oracle Database Java Packages VALID 11.1.0.7.0
Oracle Database Packages and Types VALID 11.1.0.7.0
Oracle Expression Filter VALID 11.1.0.7.0
Oracle Multimedia VALID 11.1.0.7.0
Oracle OLAP API VALID 11.1.0.7.0
Oracle Rule Manager VALID 11.1.0.7.0
Oracle Text VALID 11.1.0.7.0
Oracle Workspace Manager VALID 11.1.0.7.0
Oracle XDK VALID 11.1.0.7.0
Oracle XML Database VALID 11.1.0.7.0
Spatial VALID 11.1.0.7.0
SQL> select substr(object_name,1,40) object_name, substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner, object_type;
no rows returned.
SQL> select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type;
no rows returned.
-- Take AWR snapshots
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
-- Wait 5- 10 minutes
SQL> exec dbms_workload_repository.create_snapshot;
