For some time, Oracle’s solution in this area has been its built-in tool, Statspack.Oracle Database 10g offers a significant improvement: the Automatic Workload Repository (AWR). The AWR installs along with the database and captures not only statistics, but the derived metrics as well.
AWR retention settings and data gathering frequency
The AWR History is by default maintained for 7 days and the data is gathered in the AWR repository tables every hour by default.
The current snapshot retention settings and data gathering frequency can be determined by the query shown below. Note in this case the default settings of 7 days and 1 hour is displayed.
SQL> select to_char(snap_interval,’DD’),to_char(retention,’DD’) FROM dba_hist_wr_control;
TO_CHAR(SNAP_INTER TO_CHAR(RETENTION,
—————— ——————
+00000 01:00:00.0 +00007 00:00:00.0;
To change the settings –say, for snapshot intervals of 20 minutes and a retention period of two days –you would issue the following. The parameters are specified in minutes.
begin
dbms_workload_repository.modify_snapshot_settings (
interval => 20,
retention => 2*24*60
);
end;
AWR TABLES
Metadata (WRM$)
Historical data (WRH$)
AWR tables related to advisor functions (WRI$)
Oracle 11g New Features About Workload Capture and Workload Replay tables (WRR$)
Workload Repository Reports
Oracle provide two main scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows:
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
There are other scripts too, here is the full list:
REPORT NAME SQL Script
Automatic Workload Repository Report awrrpt.sql
Automatic Database Diagnostics Monitor Report addmrpt.sql
ASH Report ashrpt.sql
AWR Diff Periods Report awrddrpt.sql
AWR Single SQL Statement Report awrsqrpt.sql
AWR Global Report awrgrpt.sql
AWR Global Diff Report awrgdrpt.sql
Exporting and Importing AWR snapshot data
AWR data is stored in the WRH$ and DBA_HIST tables in the SYSAUX tablespace. There could be performance implications if these tables were to grow too large in size or if the retention was increased beyond the default of 7 days.
A good solution is to have a central repository and move statistical AWR data periodically to this central repository database using the Oracle supplied awrextr.sql and awrload.sql scripts which can be found in the $ORACLE_HOME/rdbms/admin directory.
— in source db
SQL> @?/rdbms/admin/awrextr.sql
— in target db
SQL>@?/rdbms/admin/awrload.sql
or
using oracle internal package
dbms_swrf_internal.AWR_EXTRACT
DBMS_SWRF_INTERNAL.AWR_LOAD
DBMS_SWRF_INTERNAL.MOVE_TO_AWR
DBMS_SWRF_INTERNAL.CLEAR_AWR_DBID
Clean AWR
exec dbms_swrf_internal.unregister_database();
dbms_workload_repository.DROP_SNAPSHOT_RANGE;
Disable Oracle AWR
If you would like to disable AWR from executing on an Oracle database, here are several ways to turn it off. If you are not using the AWR data, why pay the penalty for having it continually running and collecting unused data. These steps are listed in what I think are the easiest options first.
1,Set STATISTICS_LEVEL parameter to BASIC.
2,Run the CATNOAWR.sql script to drop the AWR Repository tables. The script calls procedure dbms_swrf_internal.remove_wr_control, which deletes a row relating to your database from the wrm$_wr_control table, and then drops all the AWR tables.
3,Execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval=>0).By setting the value of the interval as 0, we set the new interval between each snapshot collection as 110 years:
4,Download dbms_awr.plb from Metalink, compile this package and execute the PL/SQL package DBMS_AWR.DISABLE_AWR() [see Metalink note 436386.1].
5,This does not work for an existing database, but does for future databases: Create your own database creation scripts (do not utilize DBCA) and do not execute the CATAWRTB.sql script.
6,_awr_restrict_mode initialization parameter which is set to TRUE and turns off all AWR features in the repository database
Recreate the AWR
Oracle Support suggesting us to recreate the AWR using the below steps since our SYSAUX tablespace is keep growing:
alter system set sga_target=0 scope=spfile;
alter system set statistics_level = basic scope=both;
alter system set cluster_database=false;
shutdown immediate
startup restrict
— in 10g begin —
@?/rdbms/admin/catnoawr.sql
alter system flush shared_pool;
@?/rdbms/admin/catsvrm.sql –in the script had calls catawrtb.sql
— in 10g end —
— in 11g begin—
SQL> @?/rdbms/admin/catnoawr.sql
SQL> alter system flush shared_pool;
SQL> @?/rdbms/admin/catawr.sql
SQL> @?/rdbms/admin/utlrp.sql
sql> @?/rdbms/admin/execsvrm.sql
— in 11g end—
Then re-enable the AWR statistics gathering as required, by setting STATISTICS_LEVEL back to its original value, and restart the instance normally
Tip:
When SYSAUX tablespace is keep growing,you can check the V$SYSAUX_OCCUPANTS View to find out who/what is occupying space in SYSAUX.