Steps for creating Automatic Workload Repository (AWR) reports in Oracle 10g

Step1:
----------

Run the following SQL and note down the original settings, ie, Snapshot Interval and Retential Interval:

Select extract( day from snap_interval) *24*60+extract( hour from snap_interval) *60+extract( minute from snap_interval ) "Snapshot Interval", extract( day from retention) *24*60+ extract( hour from retention) *60+ extract( minute from retention ) "Retention Interval"
from dba_hist_wr_control;

Now run the following PL/SQL block in your SQL session. This will reduce the snapshot interval to 10 mins and retention days to 30 days.

BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 10); -- Minutes. Current value retained if NULL.
END;
/

NOTE:
> It is important to follow Step1 BEFORE you start the testing.
> You can keep the snapshot interval according to the duration of your test. Say if the duration is 5 mins then you can keep the interval as 5 mins.
> Please revert back to the original settings.


Step2:
----------

Copy awrrpti.sql and awrinput.sql to some temp dir. You can find these files @<ORACLE_HOME>/rdbms/admin/l

Step3:
----------

Set the following attributes in awrrpti.sql file:

define report_type='html';
define begin_snap = <snap_id> -- for begin and end IDs values see below
define end_snap = <snap_id>
define report_name = 'C:\awrrpt_1_3541_3543.html';

For begin_snap and end_snap values, please run the following query:

select * from dba_hist_snapshot where dbin=<dbid> order by snap_id desc;

begin_snap = snap_id for which End_Interval_Time = Start time of the testing.
End_snap = snap_id for which Begin_Interval_Time = End time of the testing.

You can get the dbid by using the following query:

select distinct
(case when cd.dbid = wr.dbid and
cd.name = wr.db_name and
ci.instance_number = wr.instance_number and
ci.instance_name = wr.instance_name
then '* '
else ' '
end) || wr.dbid dbbid
, wr.instance_number instt_num
, wr.db_name dbb_name
, wr.instance_name instt_name
, wr.host_name host
from dba_hist_database_instance wr, v$database cd, v$instance ci;

Step4:
----------

Now run the awrrpti.sql in your sql session, it will prompt you to enter the "dbid". Select the relevant "dbid" from the list and then enter the corresponding inst_num and press enter. The system will generate the report at the location specified in awrrpt.sql.

References:
-------------------
You can refer to Solution# 15753 for creating and dropping snapshots.