0 Replies Last post: Feb 10, 2012 12:08 AM by Community Admin  
Novice 44 posts since
Mar 30, 2009
Currently Being Moderated

Feb 10, 2012 12:19 AM

Oracle Recreate Automatic Workload Repository

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


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>alter system flush shared_pool;



-- On 11g it is required to also run:


SQL>shutdown immediate;


-- 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

OLAP Analytic Workspace                  VALID

OLAP Catalog                                    VALID

Oracle Data Mining                             VALID

Oracle Database Catalog Views           VALID

Oracle Database Java Packages          VALID

Oracle Database Packages and Types VALID

Oracle Expression Filter                      VALID

Oracle Multimedia                               VALID

Oracle OLAP API                               VALID

Oracle Rule Manager                          VALID

Oracle Text                                        VALID

Oracle Workspace Manager                VALID

Oracle XDK                                        VALID

Oracle XML Database                         VALID

Spatial                                               VALID



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;

More Like This

  • Retrieving data ...