I must thank my fellow DBA Franky Weber Faust for his publication in his blog.
Today let’s talk a little about a fantastic feature we have in Oracle Database that helps us in performance analysis: the AWR.
What is AWR?
AWR stands for Automatic Workload Repository, ie it is a repository of information about the database workload. The AWR framework collects, processes and maintains performance statistics to enable problem detection and is also the basis for Oracle’s automatic tuning tasks.
These statistics are collected through regular snapshots and stored in the AWR for a set period of time, they are snapshot-based and can be used to compile a report. The values captured by the snapshot represent the changes in each statistic collected during the period.
The statistics collected by the AWR snapshot are stored both in memory and in tables in the SYSAUX tablespace and can be queried through a series of views.
Snapshots are controlled by the background process MMON (Manageability Monitor) and executed by the Manageability Monitor Light (MMNL) according to the defined execution interval. The default interval is 60 minutes, so every 60 minutes a snapshot will run. These snapshots are retained for 8 days by default. These settings can be viewed through the DBA_HIST_WR_CONTROL view:
SQL> select SNAP_INTERVAL,retention from DBA_HIST_WR_CONTROL;
SNAP_INTERVAL RETENTION
———————– ————————
+00000 01:00:00.0 +00008 00:00:00.0
We can change these settings with the DBMS_WORKLOAD_REPOSITORY package and the MODIFY_SNAPSHOT_SETTINGS procedure:
SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 21600, interval => 30);
PL/SQL procedure successfully completed.
The retention and interval parameters are set in minutes. When you choose to increase retention settings, always take into account the space required to store more snapshots during a longer retention period.
Let’s see if the change worked:
SQL> select SNAP_INTERVAL,retention from DBA_HIST_WR_CONTROL;
SNAP_INTERVAL RETENTION
———————– ————————
+00000 00:30:00.0 +00015 00:00:00.0
To view the snapshots, see the DBA_HIST_SNAPSHOT view:
In this example, my snapshots are not hourly, because my VM is not always on, but we’ll see at the end of the article how the snapshots will be when I finish writing.
We have the option of running manual snapshots using the CREATE_SNAPSHOT procedure:
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
PL/SQL procedure successfully completed.
We can also delete them manually with the DROP_SNAPSHOT_RANGE procedure. Let’s look at the snapshots and remove a range:
SQL> select * from DBA_HIST_SNAPSHOT order by SNAP_ID;
SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 16, high_snap_id => 20);
PL/SQL procedure successfully completed.
We can see that all have been removed:
SQL> select * from DBA_HIST_SNAPSHOT order by SNAP_ID;
no rows selected
If you want to stop collecting AWR information you can change the parameter statistics_level to BASIC, but you can still trigger the snapshots manually with the CREATE_SNAPSHOT procedure. The default is TYPICAL, but if you want more information you can also change it to ALL. These additional statistics are operating system times and execution plans. We usually set up for ALL when we are deploying a new application and need to do debugging and tuning in the code.
Generating an AWR report is easy, but you need at least 2 snapshots. These snapshots must be within the same window of operation of the database, that is, there could have been a shutdown between one snapshot and another.
To generate the report we can run the awrrpt.sql script or through tools like Enterprise Manager or SQL Developer.Let’s see some examples:
SQL> select * from DBA_HIST_SNAPSHOT order by SNAP_ID;
SNAP_ID DBID INSTANCE_NUMBER STARTUP_TIME BEGIN_INTERVAL_TIME END_INTERVAL_TIME FLUSH_ELAPSED SNAP_LEVEL ERROR_COUNT SNAP_FLAG SNAP_TIMEZONE CON_ID
———- ———- ————— —————————— —————————— —————————— —————————— ———- ———– ———- —————————— ———-
21 1937271711 1 13-JUL-16 07.51.44.000 PM 13-JUL-16 08.14.00.718 PM 13-JUL-16 08.30.40.094 PM +00000 00:00:01.9 1 0 0 -0 03:00:00 1
22 1937271711 1 13-JUL-16 07.51.44.000 PM 13-JUL-16 08.30.40.094 PM 13-JUL-16 09.00.42.247 PM +00000 00:00:00.8 1 0 0 -0 03:00:00 1
SQL> @?/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
———– ———— ——– ————
1937271711 LOREDB 1 loredb
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt. Default value is ‘html’.
‘html’ HTML format (default)
‘text’ Text format
‘active-html’ Includes Performance Hub active report
Enter value for report_type:
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
———— ——– ———— ———— ————
* 1937271711 1 LOREDB loredb loredata-srv
.localdomain
Using 1937271711 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day’s Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
———— ———— ——— —————— —–
loredb LOREDB 21 13 Jul 2016 20:30 1
22 13 Jul 2016 21:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 21
Begin Snapshot Id specified: 21
Enter value for end_snap: 22
End Snapshot Id specified: 22
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_21_22.html. To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: awr_report_sqlplus
Using the report name awr_report_sqlplus
Just open the file generated in a browser.
In SQL Developer we can generate the report following the screens below:
AWR-1
AWR-2
AWR-3
Last but not least, it is worth remembering the impact on licensing when we use AWR. To have rights to query or generate AWR reports, you must have licensing for Oracle Database Enterprise Edition plus the option Diagnostic Pack. If you use Oracle Database Standard Edition, Standard Edition One, or Standard Edition Two, you should know that you are infringing the license.
