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)
