Automatic Workload Repository

Creating Snapshots
BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;

Dropping Snapshots
BEGIN
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22,
high_snap_id => 32, dbid => 3310949047);
END;

Modifying Snapshot Settings
BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
interval => 30, topnsql => 100, dbid => 3310949047);
END;

Run utlsyxsz.sql for sizing recommendations for the awr(in rdbms\admin).
http://www.ioug.org/performancetuning.pdf

SQL> @utlsyxsz.sql

This script estimates the space required for the SYSAUX tablespace.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
......

Run awrinfo.sql for awr components size.
SQL> @awrinfo.sql

http://forums.oracle.com/forums/thread.jspa?threadID=680095

Transport Awr Data to Test Environment
Run @$ORACLE_HOME/rdbms/admin/awrextr.sql in prod environment, move result into test environment and run @$ORACLE_HOME/rdbms/admin/awrload.sql to load into test environment.
After transporting awr data check dba_hist_snapshot for new awr data.Transported data has DBID of prod instance, so we can't use transported data in EM of test instance.
Reports for transported awr data can be produced via awrrpti.sql, awrsqrpi.sql , awrddrpi.sql or dbms_workload_repository package.

Generating AWR Reports

SQL> select t.snap_id,t.dbid,t.begin_interval_time from dba_hist_snapshot t order by t.snap_id desc;

SNAP_ID DBID BEGIN_INTERVAL_TIME
---------- ---------- -------------------------------------------------
9832 1055334377 06/04/2009 13:00:55,018
9831 1055334377 06/04/2009 12:01:04,018
.....
SQL> spool awr_rep_htm_03_04_09.html
SQL> select * from table(dbms_workload_repository.awr_report_html(1055334377 ,1,9795,9832 ));
......
SQL> spool off

Compare Period Report

SQL> spool awr_prod_01_apr_03_apr.html
SQL> select * from table(dbms_workload_repository.awr_diff_report_html(1055334377 ,1,9699 ,9722 ,1055334377,1,9747,9770));
SQL> spool off

Generating ASH Reports
Run ashrpt.sql or ashrpti.sql for reports.(in rdbms\admin)

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/autostat.htm#i27008