Oracle Performance : Statspack Replacement in 10g : Automatic Workload Repository (AWR)

Resolving The Problem

EMail from Perf ENG
=================

After emailing with Oracle support, it turns out that Oracle changes the behaviour with statspack.
It requires more SGA.

All the following can be done in Enterprise Manager.

The Replacement for statspack is Automatic Workload Repository (AWR). Actually so far, I like it better than statspack only because it can generate html, is fast, and not crashing.

The following is basic 101, please refer to docs for more details.
In AWR, you can log on as a user with DBA priviledge.

1. Create Snap Shot
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END


2. Drop Snapshot
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 1, high_snap_id => 200);
END;

3. Modifying Snapshot Settings
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200, interval => 30);
END;

Change STATISTICS_LEVEL
SQL>ALTER SYSTEM SET statistics_level=TYPICAL SCOPE=BOTH

AWR Report
Workload Repository Reports

--- awrrpt.sql: generates statistics for a range of snapshot.
--- awrrpti.sql: generates for a specified database and instance.

SQL>@ORACLE_HOME/rdbms/admin/awrrpt.sql

It outputs both HTML and TEXT format