More about AWR

More about AWR

AWR is a new feature of Oracle 10g version. Its full name is Automatic Workload Repository-Automatic load information library. AWR compares the statistics collected by snapshot twice, to generate report data. The generated report consists of multiple parts.

Set AWR sampling retention policy and collection frequency

By default, historical sampling data is retained for 7 days and collected every hour by default.

The following statement can be used to query the current snapshot retention policy and collection frequency. The following shows 7 days and 1 hour

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;

We can change the settings in the following way to set the snapshot collection interval to retained the historical data every 20 minutes for two days. The parameters are specified for minutes.

Begin
Dbms_workload_repository.modify_snapshot_settings (
Interval => 20,
Retention => 2*24*60
);
End;

AWR-related tables

Metadata (WRM $)
Historical data (WRH $)
Recommended Methods for reporting AWR (WRI $)
AWR-related information about the new features of Oracle 11g (WRR $)

Workload Repository Reports

Oracle provides two major scripts to generate AWR reports (awrrpt. SQL and awrrpti. SQL ). their formats are very similar to those of statspack reports. They provide HTML and text formats. the two reports are output in the same format but awrrpti. SQL allows you to select a single instance:
@ $ ORACLE_HOME/rdbms/admin/awrrpt. SQL
@ $ ORACLE_HOME/rdbms/admin/awrrpti. SQL

The following are commonly used scripts:
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

Export and Import AWR snapshot data

AWR data is stored in WRH $ and DBA_HIST tables in the SYSAUX tablespace. If the table grows very large, the data storage duration will affect the database performance. Therefore, by default, only seven days of historical data are retained.

A better solution is to transfer AWR data to another data storage database center. Oracle provides two scripts awrextr. SQL and awrload. SQL scripts to migrate AWR data
In another database. You can find these two scripts in the $ ORACLE_HOME/rdbms/admin directory.

-In source db
SQL> @? /Rdbms/admin/awrextr. SQL

-In target db
SQL> @? /Rdbms/admin/awrload. SQL

Or
Use 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

Clear AWR

Exec dbms_swrf_internal.unregister_database ();

Dbms_workload_repository.DROP_SNAPSHOT_RANGE;

Disable Oracle AWR

If you want to disable AWR, you can use the following method to disable AWR. The detailed operation steps are listed below.

1. Set STATISTICS_LEVEL to BASIC.
2. Run the CATNOAWR. SQL script to delete the relevant awr table. This script runs the Stored procedure dbms_swrf_internal.remove_wr_control and deletes wrm $ _ wr_control-related data and all AWR-related tables.
3. Run DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (interval => 0 ):
4. Download The dbms_awr.plb script from Metalink and run DBMS_AWR.DISABLE_AWR (). [for the script, see Metalink note 436386.1].
5. If the database is created manually, instead of using DBCA, you do not need to execute CATAWRTB. SQL.
6. Set _ awr_restrict_mode to TRUE.

Rebuilding AWR

We recommend that you rebuild the AWR and store the data in the SYSAUX tablespace:

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 callcatawrtb. 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.