AWR & Statspack Report

AWR Report

Snapshots

For AWR report By default snapshots of the relevant data are taken every hour and retained for 7 days. 

we can alter these using following (Current value retained if NULL)

BEGIN
  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
    retention => 14400, 
    interval  => 30);
END;
/

Creating snapshot manually

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

Dropping existing snapshot

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
    low_snap_id  => 22, 
    high_snap_id => 32);
END;
/

Baselines

A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => 100, 
    end_snap_id   => 120,
    baseline_name => 'test baseline');
END;
/

The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted. if cascade TRUE then Deletes associated snapshots.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.drop_baseline (
    baseline_name => 'test baseline',
    cascade       => FALSE);
END;
/

AWR Related Views

The following workload repository views are available:

V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.
V$METRIC - Displays metric information.
V$METRICNAME - Displays the metrics associated with each metric group.
V$METRIC_HISTORY - Displays historical metrics.
V$METRICGROUP - Displays all metrics groups.
DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.
DBA_HIST_BASELINE - Displays baseline information.
DBA_HIST_DATABASE_INSTANCE - Displays database environment information.
DBA_HIST_SNAPSHOT - Displays snapshot information.
DBA_HIST_SQL_PLAN - Displays SQL execution plans.
DBA_HIST_WR_CONTROL - Displays AWR settings.

Statspack Report

Configure statspack 

cd <$ORACLE_HOME>/rdbms/admin
sqlplus / as sysdba
sql> @spcreate.sql

The variables are: 
perfstat_password -> for the password 
default_tablespace -> for the default tablespace (SYSAUX)
temporary_tablespace -> for the temporary tablespace (TEMP)

If there any errors then  de-install and install again

SQL> @spdrop   
SQL> @spcreate

Gathering data - Taking a snapshot 

For statspack report snapshots did not taken automatically and We have to take this manually.

SQL> connect perfstat/perfstat_password 
SQL> exec statspack.snap;

Find the snapshot

SQL> select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT;

Generate SP Report

cd <$ORACLE_HOME>/rdbms/admin
sqlplus / as sysdba
SQL> @spreport.sql

you  have to provide at the prompt: BEGIN_SNAP, END_SNAP, REPORT_NAME (we can use a default value as well)

then sp report will generate the same name that you given (or else default report name)