AWR & Statspack Report

Performance Tuning

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)