Automatic Workload Respository (AWR) 11gr2

Automatic Workload Respository (AWR) 11gr2

On the OTN forums i used to see a lot of thread about AWR basic questions. like "how to generate , how to analyze etc etc".

It is worth to write on AWR today.

FAQ's
1. What is AWR reports?
AWR = Automatic workload respository introduced in 10gr1 to tune oracle database. it is a advance version of old snapshot method.
Automatic Workload Repository (AWR) is a repository of historical performance data that includes cumulative statistics for the system, sessions, individual SQL statements, segments, and services. These statistics are the foundation of performance tuning. By automating the gathering of database statistics for problem detection and tuning, AWR serves as the foundation for database self-management.


2. How to use or generate AWR reports?
AWR reports will generate automatically by oracle database in every 1 hour interval and retained in the database for 8 days.

If you want to generate AWR report manually then use the following steps
There few scripts needs to be run on SQL prompt to generate AWR report manually.
all the scripts is located under $ORACLE_HOME/rdbms/admin directory


SQL> conn system/oracle@db01



Connected.


SQL> --to generate AWR report there is a awrrpt.sql script needs to be run.


SQL> @D:\app\Administrator\11.2.0\db\RDBMS\ADMIN\awrrpt.sql


If database is RAC then run the following scripts
SQL>@%ORACLE_HOME%/rdbms/admin/awrgrpt.sql

If you have multiple instance configured on the same host and want to generate AWR report for the specific instance then run the following scripts
SQL>@%ORACLE_HOME%/rdbms/admin/awrrpti.sql

Generating AWR report on RAC for specific database instance
SQL>@%ORACLE_HOME%/rdbms/admin/awrgrpti.sql


Generating AWR report on RAC database
SQL>@%ORACLE_HOME%/rdbms/admin/awrgdrpt.sql


Generating AWR report for specific instance
SQL>@%ORACLE_HOME/rdbms/admin/awrddrpi.sql

Generating AWR report for specific instance on RAC

SQL>@%ORACLE_HOME/rdbms/admin/awrgdrpi.sql


Generating AWR report for an SQL statement
SQL>@%ORACLE_HOME%/rdbms/admin/awrsqrpt.sql

Generating AWR report for an SQL statement for specific instance
SQL>@%ORACLE_HOME%/rdbms/admin/awrsqrpti.sql

NOTE: you can get the SQL ID from V$SQL database view.



Generating AWR report to compare periods reports
We can compare the two different periods reports to analyze database bottleneck.
there is a scripts needs to be run called awrddrpt.sql

SQL>@%ORACLE_HOME%/rdbms/admin/awrddrpt.sql


Generating AWR report to compare periods reports for RAC


List of all AWR scripts.
D:\app\Administrator\11.2.0\db\RDBMS\ADMIN>dir awr*.sql

Volume in drive D is OraHome
Volume Serial Number is 50E3-4204

Directory of D:\app\Administrator\11.2.0\db\RDBMS\ADMIN

01-12-2006 07:31 PM 1,189 awrblmig.sql
This script will migrate the baseline data on a pre-11g database to the 11g database.

23-05-2005 08:13 PM 21,618 awrddinp.sql

13-05-2009 07:08 PM 7,600 awrddrpi.sql
This script will compare period reports for specific instance.

27-05-2005 08:22 PM 2,069 awrddrpt.sql
This script will compare period reports.

24-03-2009 10:38 AM 11,490 awrextr.sql
This script extract AWR details from EXPORT DATA PUMP FILE, suppose if you want to export AWR details from PROD and import in TEST db, in that case you can use this scripts (you need to give DATA PUMP DIRECTORY AND IT WILL WORK ONLY WITH DATAPUMP Utilities)

13-03-2008 04:01 PM 17,051 awrgdinp.sql
13-05-2009 07:08 PM 7,551 awrgdrpi.sql
This script compare period report for RAC database on specific instance.

29-04-2009 05:53 PM 1,958 awrgdrpt.sql
This script compare period report for RAC database.

13-03-2008 04:01 PM 7,719 awrginp.sql
29-04-2009 05:53 PM 1,578 awrgrpt.sql
This script generate AWR report for RAC database.

13-03-2008 04:01 PM 6,526 awrgrpti.sql
This script generate AWR report for RAC database on specific instance.

01-09-2004 06:38 PM 50,507 awrinfo.sql
This is one of the most important scripts that DBA needs to run frequenctly and check the followings :
this script will give us the below information:
1. SYSAUX space occupied by AWR reports in the particular tablespace
2. SYSAUX occupants 
3. SYSAUX usage (unregistered schemas)
4. Size estimated for AWR snapshots
5. Space usage by AWR components
6. And many more info... just run and read it.
This scripts must be run with SYSDBA user.

05-01-2005 03:25 PM 2,542 awrinpnm.sql
03-03-2006 05:47 PM 8,901 awrinput.sql
15-07-2009 10:20 AM 10,736 awrload.sql
This script will LOAD awr report from exported dumpfile to the target system. once you execute the script , then it will create the AWR staging schema to load awr report after loading report will be transffered to SYS schema. (DATA PUMP DIRECTORY required)

24-10-2003 02:20 PM 2,069 awrrpt.sql
This script will generate AWR report

18-04-2005 01:00 PM 7,801 awrrpti.sql
This script will generate AWR report on specific instance

05-01-2005 03:25 PM 6,919 awrsqrpi.sql
This script will generate AWR report on specific instance for SQL statement.

05-01-2005 03:25 PM 1,528 awrsqrpt.sql
This script will genearate AWR report for SQL statement.

By default system create the snapshot every hour but if you want to create snapshot on a specific time then use the following procedure.

To create snapshot manually

SQL> conn sys/oracle@db01 as sysdba

Connected.


SQL> begin
2 DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
3 end;
4 /
PL/SQL procedure successfully completed.

If you want to drop snapshot manually
BEGIN

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22,
High_snap_id => 32, dbid => 3310949047);
END;
/

To modify default retention settings of snapshot.
BEGIN

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
interval => 30, topnsql => 100, dbid => 3310949047);
END;
/