ORACLE awr Overview and generate AWR reports

1.Overview of the Automatic Workload Repository

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection an D self-tuning purposes. This data is both in memory and stored in the database. The gathered data can be displayed in both reports and views.

The statistics collected and processed by AWR include:

• Object statistics that determine both access and usage statistics of database segments

• Time model statistics based on time usage for activities, displayed in the and views V$SYS_TIME_MODEL V$SESS_TIME_MODEL

• Some of the system and session statistics collected in the and views V$SYSSTAT V$SESSTAT

• SQL statements that is producing the highest load on the system, based on criteria such as elapsed time and CPU time

• ASH statistics, representing the history of recent sessions activity

Gathering database statistics using the AWR is enabled by default and is controlled by theSTATISTICS_LEVELInitialization parameter. TheSTATISTICS_LEVELParameter should is set to theTYPICALOrALLTo enable statistics gathering by the AWR. The default setting isTYPICAL. SettingSTATISTICS_LEVELToBASICDisables many Oracle Database features, including the AWR, and is not recommended. IfSTATISTICS_LEVELis set toBASIC, you can still manually capture AWR statistics using theDBMS_WORKLOAD_REPOSITORYPackage. However, because In-memory collection of many system Statistics-such as segments statistics and memory advisor information -will is disabled, the statistics captured in these snapshots May is not a complete. For information about theSTATISTICS_LEVELinitialization parameter, seeOracle Database Reference.

2. Setting the level of Statistics Collection

Oracle Database provides the initialization parameter STATISTICS_LEVEL , which controls all major statistics collections or advisories In the database. This parameter sets the statistics collection level for the database.

Depending on STATISTICS_LEVEL the setting of, certain advisories or statistics is collected, as follows:

• BASIC: No advisories or statistics is collected. Monitoring and many automatic features are disabled. Oracle does not recommend this setting because it disables important Oracle Database features.

• TYPICAL : The default value and ensures collection for all major statistics while providing best overall dat Abase performance. This setting should is adequate for most environments. (before generating the ARW report, check the parameter statistics_level, the default is typical)

• ALL: All of the advisories or statistics TYPICAL that is collected with the setting is included, plus timed operating system Statistics and row source execution statistics.

• The v$statistics_level view displays information about the status of the STATISTICS or advisories controlled by T He statistics_level parameter. See "V$statistics_level". This view allows you to view information about these three parameters.

Sql> descV$statistics_level; Name Type NullableDefaultComments--------------------        -------------- -------- ------- -------- Statistics_nameVARCHAR2( -) Y DESCRIPTIONVARCHAR2(4000) Y Session_statusVARCHAR2(8) Y System_statusVARCHAR2(8) Y Activation_levelVARCHAR2(7) Y Statistics_view_nameVARCHAR2( -) Y session_settableVARCHAR2(3) Y

3.Generating Automatic Workload Repository Reports

An AWR report shows data captured between the snapshots (or both points in time). The AWR reports is divided into multiple sections. The HTML report includes links the can is used to navigate quickly between sections. The content of the report contains the workload profiles of the system for the selected range of snapshots.

The primary interface for generating AWR reports is Oracle Enterprise Manager. Whenever possible, should generate AWR reports using Oracle Enterprise Manager, as described in Oracle Databa SE 2 day + Performance Tuningguide(generated awr on OEM interface). If Oracle Enterprise Manager is unavailable, you can generate AWR reports by running SQL scripts, as described in the Foll Owing sections:

3.1 Generating an AWR report (using SQL script to generate AWR)

The awrrpt.sql SQL script generates an HTML or text report, displays statistics for a range of snapshot IDs.

To generate a AWR report:

2. At the SQL prompt, enter:

   @ $ORACLE _home/rdbms/admin/awrrpt.sql

4. Specify whether you want an HTML or a text report:

   Enter value for Report_type:text

   In this example, a text, the is chosen.

6. Specify the number of days for which your want to list snapshot IDs.

   Enter value for Num_days:2

   A List of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days is displayed.

8. Specify a beginning and ending snapshot ID for the Workload Repository report:

   Enter value for Begin_snap:150enter value for end_snap:160

   In this example, the snapshot with a snapshot ID of $ selected as the beginning snapshot, and the snapshot with a SNA Pshot ID of selected as the ending snapshot.

10. Enter a report name, or accept the default report name:

    Enter value for report_name:using the report name awrrpt_1_150_160

    In this example, the default name is accepted and a AWR report named is awrrpt_1_150_160 generated.

generate AWR report in 3.2WINDOWS Environment:

In this directory @ $ORACLE _home/rdbms/admin/log in as Sysdba Sqlplus

I:\app\administrator\product\11.2.0\dbhome_1\rdbms\admin>sqlplus/as Sysdba;

Enter @awrrpt in the sqlplus

The other steps are as above.

Note: (The time between two snap IDs must be continuous, and no downtime is allowed)

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter the value of Begin_snap: 1
Begin Snapshot Id specified:1

Enter the value of End_snap: 18
End Snapshot Id specified:18

===================

3.3 Generating additional ARW reports:

• Generating an Oracle RAC AWR report

• Generating an AWR report on a specific Database Instance

• Generating an Oracle RAC-AWR report on specific Database Instances

• Generating an AWR report for a SQL Statement

• Generating an AWR report for a SQL Statement on a specific Database Instance

From: Transfer from Oracle Official document: http://docs.oracle.com/cd/E11882_01/server.112/e41573/autostat.htm#PFGRF94202

ORACLE awr Overview and generate AWR reports