Practice 4 - Managing Automatic Workload Repository (AWR)

P a g e |1

Practice 4

Managing Automatic Workload Repository (AWR)
Practice Target
In this practice, you will perform the following:
•

Manage AWR settings

•

Manually create and delete AWR snapshots

•

Check out the space consumed by AWR snapshots

Oracle Database Performance Tuning, a course by Ahmed Baraka


Practice 4 - Managing Automatic Workload Repository (AWR)

P a g e |2

Managing AWR Settings
In this section of the practice, you will view and modify AWR settings.
1.

In Oracle VirtualBox, make sure srv1 is up and running.

2.

Start Putty, login to srv1 as oracle.

3.

Invoke SQL*Plus and login to the database as system.

sqlplus system/oracle
4.

Show the value of the parameter STATISTICS_LEVEL
This parameter must be set to TYPICAL or ALL, in order to have the AWR enabled.

show parameter STATISTICS_LEVEL
5.

Set the parameter STATISTICS_LEVEL to ALL
Setting this parameter to ALL makes the database to gather the timed operating system statistics
and plan execution statistics.

ALTER SYSTEM SET STATISTICS_LEVEL=ALL SCOPE=BOTH;
6.

Run the following query to display the AWR settings.
By default, the INTERVAL equals to 1 hour and the RETENTION equals to 8 days.


col SNAP_INTERVAL format a20
col RETENTION format a20
col TOPNSQL format a10
SELECT SNAP_INTERVAL, RETENTION, TOPNSQL FROM DBA_HIST_WR_CONTROL;
7.

Modify the INTERVAL setting to 30 minutes.
Reducing the INTERVAL value leads to consuming more disk space by the AWR snapshots but it
allows more accurate performance analysis. In real life scenario, it is recommended to reduce the
default value of the AWR interval to 30 minutes or 15 minutes.

BEGIN
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 30);
END;
/

Oracle Database Performance Tuning, a course by Ahmed Baraka


Practice 4 - Managing Automatic Workload Repository (AWR)

Managing AWR Snapshots
In this section of the practice, you will view, create, and delete AWR snapshots.
8.

Execute the following query to display the existing AWR snapshots.

col BEGIN_INTERVAL_TIME format a28
col END_INTERVAL_TIME format a28
SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME, SNAP_LEVEL

FROM DBA_HIST_SNAPSHOT
ORDER BY SNAP_ID;
9.

Manually create a heavyweight AWR snapshot.
If FLUSH_LEVEL is not specified, it defaults to TYPICAL for a non-CDB database.

exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(FLUSH_LEVEL=>'ALL')
10.

Display information about the created snapshot.
Heavyweight AWR snapshot is described as a snapshot of level 2.

SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME, SNAP_LEVEL
FROM DBA_HIST_SNAPSHOT
ORDER BY SNAP_ID DESC FETCH FIRST 1 ROWS ONLY;
11.

Delete the manually created snapshot.
Obtain the SNAP_ID value from the output of the preceding query.

DEFINE v_snapID = &Enter_SnapID
begin
DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
LOW_SNAP_ID => &v_snapID,
HIGH_SNAP_ID => &v_snapID);
end;
/

Oracle Database Performance Tuning, a course by Ahmed Baraka


P a g e |3


Practice 4 - Managing Automatic Workload Repository (AWR)

P a g e |4

Managing Space Consumed by AWR Snapshots
In this section of the practice, you will view the space consumed by the AWR snapshots.
12.

Submit the following query to display the space consumed by AWR snapshots.
Unfortunately, there is no standard procedure to move the AWR snapshots out of the SYSAUX
tablespace.
Note: Space consumed by AWR snapshots depend on the INTERVAL and RETENTION setting
values of the AWR.

col OCCUPANT_NAME format a20
col MOVE_PROCEDURE format a20
SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTES/1024 MB, MOVE_PROCEDURE
FROM V$SYSAUX_OCCUPANTS
WHERE OCCUPANT_NAME = 'SM/AWR';
13.

Run the following script as sysdba to produce a report about the space consumed by the AWR
snapshots.

conn / as sysdba
@ ?/rdbms/admin/awrinfo.sql

14.

View the generated report.
Observe the estimate size of the AWR per week.

host vi awrinfo.txt
In the following steps, we will reduce the space consumed by the optimizer statistics history to reduce
the SYSAUX tablespace current and future size.
15.

Submit the following query to display the space consumed by the Optimizer backup statistics.

conn system/oracle
SELECT OCCUPANT_NAME, SPACE_USAGE_KBYTES/1024 MB, MOVE_PROCEDURE
FROM V$SYSAUX_OCCUPANTS
WHERE OCCUPANT_NAME = 'SM/OPTSTAT';
16.

Display the optimizer statistics retention period.
By default, it is set to 31 days. There is no practical need to keep the optimizer statistics for this
long period. We will change it to 7 days so that the space used by the remaining days are
purged.

SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;
17.

Decrease the retention period of the optimizer statistics history to 7 days.

exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(7);


Oracle Database Performance Tuning, a course by Ahmed Baraka


Practice 4 - Managing Automatic Workload Repository (AWR)

18.

P a g e |5

Purge the optimizer statistics that do not comply with the new retention period.
When you pass NULL to the PURGE_STATS procedure, the statistics that are older than the current
retention period will be deleted.
Note: do not run the procedure below in a production system if the size occupied by the
optimizer statistics is in gigabytes. It may take very long time to finish. Delete the statistics in
chunks instead.

exec DBMS_STATS.PURGE_STATS(NULL)

Note:
It is recommended not to shut down the srv1 for now till you implement the next practice on it.

Oracle Database Performance Tuning, a course by Ahmed Baraka


Practice 4 - Managing Automatic Workload Repository (AWR)

P a g e |6

Summary
•


For managing the AWR, you can perform the following tasks:
o

Modify the AWR settings: SNAP_INTERVAL, RETENTION, and TOPNSQL

o

Retrieve information about AWR snapshots from DBA_HIST_SNAPSHOT

o

Manually create an AWR snapshot

o

Delete a range of AWR snapshots

•

Space consumed by AWR snapshots are determined by the AWR settings

•

To allow more space in the SYSAUX tablespace for the AWR snapshots, reduce the retention
period for the Optimizer statistics history.

Oracle Database Performance Tuning, a course by Ahmed Baraka