In 8i, oracle has introduced a primitive version of statspack report and enhanced the capabilities of statspack report in version 9i. AWR is a successor of statspack and it was introduced in 10g version of oracle.
In order to generate statspack report first we need to install statspack and then we need to gather performance statisics by executing the following package :
SQL>exec dbms_statspack.snap();
SQL>exec dbms_statspack.snap(); -- 2pm
SQL>exec dbms_statspack.snap(); -- 4pm
By considering 2 snap id's, we can generate the statspack report.
In 10g,by default oracle gathers performance statistics per one hour and stores in sysaux tablespace with the help of MMON(Memory Monitor) background process.
If at all if the instance is down between 2 snap id's, we cannot generate AWR report.
We can modify the default frequency and the retension with the help of the package
dbms_workload_repository
SQL>exec dbms_workload_repository.modify_snapshot_settings(Retention=>432000,interval=>120);
Once the retention period expires, by default oracle purges the snapshots. We can also retain a specific set of snap ids by creating a 'base line';
SQL>exec dbms_workload_repository.create_baseline (start_snap_id=>200,end_snap_id=>230,baseline_name=>'April302012snaps');
We can also drop the baseline by executing the following package
SQL>exec dbms_workload_repository.drop_baseline(baseline_name=>'April302012snaps',cascade=>True);
Once we create a baseline, oracle is not going to purge the snapids that are associated with the baseline.
cascade=True means oracle automatically deletes the snapids that are associated with the baseline.
We can also create a snapshot manually by executing the following package
SQL>exec dbms_workload_repository.create_snapshot;
To know the list of snapids, execute the following command
SQL>select * from dba_hist_snapshots;
