AWR Generating & Setting
How to display AWR snapshot settings
Oracle database is gathering statistics periodically
(snapshots), these statistics can be used for analyzing database performance.
These statistics are kept in the Automatic Workload Repository (AWR).
Snapshot Interval: How often to automatically take snapshots.
SELECT 'Snapshot Interval' "Interval"
, EXTRACT ( DAY FROM snap_interval ) days
, EXTRACT ( HOUR FROM snap_interval ) hours
, EXTRACT ( MINUTE FROM snap_interval ) minutes
FROM dba_hist_wr_control
UNION ALL
SELECT 'Retention Interval' "Interval"
, EXTRACT ( DAY FROM retention ) days
, EXTRACT ( HOUR FROM retention ) hours
, EXTRACT ( MINUTE FROM retention ) minutes
FROM dba_hist_wr_control;
|
Adjust the AWR retention and snapshot intervals according to your needs.
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings
( interval => 60, -- minutes
retention => 20160 -- minutes (14 days)
);
END;
/
|
The
STATISTICS_LEVEL parameter should be set to the TYPICAL or ALL to enable statistics gathering by the AWR. The default setting is TYPICAL. Setting STATISTICS_LEVEL to BASIC disables many Oracle Database features, including the AWR, and is not recommended.
Here’s a list of AWR related main scripts from oracle 11g, 12c.
Located at $ORACLE_HOME/rdbms/admin
Generic
awrextr.sql - Extracts the AWR data into a Data Pump export file.
awrload.sql - Load the extracted AWR data
awrinfo.sql - Output general AWR information
NON RAC
awrrpt.sql - Basic AWR report (generates an HTML or text report that displays statistics for a range of snapshot Ids.)
awrddrpt.sql - Period diff on current instance (generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods)
RAC
awrgrpt.sql - AWR Global Report (RAC)
awrgdrpt.sql - AWR Global Diff Report (RAC)
awrinfo.sql - Script to output general AWR information
Generating an AWR report from the SQL*Plus command line:
1. Creating Snapshot
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
|
The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.
To generate an HTML or text report for a range of snapshot Ids, run the awrrpt.sql script at the SQL prompt:
SQL> @?/rdbms/admin/awrrpt.sql
|
First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: text
|
Specify the number of days for which you want to list snapshot Ids.
Enter value for num_days: 2
|
After the list displays, you are prompted for the beginning and ending snapshot Id for the workload repository report.
Enter value for begin_snap: 95
Enter value for end_snap: 97
|
Next, accept the default report name or enter a report name. The default name is accepted in the following example:
Enter value for report_name:
Using the report name awrrpt_1_95_97
The workload repository report is generated.
|
Default location of output file (awrrpt_1_95_97.txt or awrrpt_1_95_97.html) is located at the directory you where before entered SQL*Plus
Generating an AWR Compare report (Non-RAC) from the SQL*Plus command line:
1. Generate AWR Reports
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/awrddrpt.sql
|
2. First, you need to specify whether you want an HTML or a text report.
Enter value for report_type: html
|
Enter value for num_days: 1
|
Enter value for begin_snap: 22665
Enter value for end_snap: 22666
|
5. Specify the number of days for which you want to list snapshot Ids for the Second Pair
Enter value for num_days2: 1
|
Enter value for begin_snap2: 22667
Enter value for end_snap2: 22668
|
The default report file name is awrdiff_1_22665_1_22667.html
Report written to awrdiff_1_22665_1_22667.html
|
Comments
Post a Comment