AWR Report

v  AWR Report : Automatic workload Repository:

‘Statspack’ -as known as in the earlier releases, AWR was introduced with 10g. AWR is very similar to statspack elapsed time report.

AWR Report is a combination of

·          Background Processes

·          Set of Tables

·          Set of Reports

·          The snapshot that it take on the statistics at every one hour

·          The snapshot it takes or gathers on Top SQL at every one hour.

        The AWR elapsed-time report, contains valuable information regarding the health of the Oracle instance,  

·          The easy way to generate an AWR report is from the SQL*Plus command line.

SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sql

        The old-fashioned AWR report generation procedure has also been preserved from STATSPACK. The awrrpt.sql script in SQL*Plus can simply be run, and the parameters necessary to build the AWR report can be provided. In fact, the awrrpt.sql script calls the corresponding procedure from the dbms_workload_repository package and stores its output in the target report file.

o    STATSPACK: The only way to get a final STATSPACK report is to manually run the spreport.sql script in an ad-hoc SQL environment like SQL*Plus

·          The procedure of report generation in Oracle10g is quite different with AWR. The final AWR report can be built by using the PL/SQL API provided in the dbms_workload_repository package.

·          To create and using dbms_workload_repository package :

                    SELECT

                          output

                    FROM  

                    TABLE

                    (dbms_workload_repository.awr_report_text (dbid,inst Num,2900 (snapshortid),2911 ) );

v  Playing with packages and procedures for AWR report generation

·          Creating snap shot using dbms_workload_repository package.

BEGIN

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

END;

/

Dynamic view: DBA_HIST_SNAPSHOT   : A list of snapshot ids and database ids are available.

·          Dropping a range of snap shot ids:

Query the DBA_HIST_SNAPSHOT to get the information on the snapshot ids

BEGIN

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 102, high_snap_id => 122, dbid =>  8187786345);

END;

/

·          Modify Snap shot settings

If you want to modify the retention period as 43200 minutes (30 days), the interval between each snapshot is specified as 30 minutes, and the number of Top SQL to flush for each SQL criteria as 100 then use following:

BEGIN

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,

interval => 30, topnsql => 100, dbid =>  7123356265);

END;

/

The dbid is optional.

·          Extracting AWR Report and loading the AWR Data:

The awrextr.sql script extracts the AWR data for a range of snapshots from the database into a Data Pump export file. Once created, this dump file can be transported to another system where the extracted data can be loaded. To run the awrextr.sql script, you need to be connected to the database as the SYS user.

To extract AWR data at the SQL prompt, enter:

SQL> @$ORACLE_HOME/rdbms/admin/awrextr.sql`

Loading: Once the export dump file is transported to the target system, you can load the extracted AWR data using the awrload.sql script. The awrload.sql script will first create a staging schema where the snapshot data is transferred from the Data Pump file into the database. The data is then transferred from the staging schema into the appropriate AWR tables. To run the awrload.sql script, you need to be connected to the database as the SYS user.

To load AWR data at the SQL prompt, enter:

SQL> @$ORACLE_HOME/rdbms/admin/awrload.sql

·          Extra snapshot can be created and old existing snapshot can be removed.

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN

  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (

    low_snap_id  => 22,

    high_snap_id => 32);

END;

/

v  Different Scripts to generate AWR Report for different purposes:

·          awrrpt.sql

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

·          awrrpti.sql

The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.

·          awrsqrpt.sql

The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.

·          awrsqrpi.sql

The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.

·          awrddrpt.sql

The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.

·          awrddrpi.sql

The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.

o    Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL. If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics.

•     Baselines

·          A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing.

BEGIN

DBMS_WORKLOAD_REPOSITORY.create_baseline (

start_snap_id => 210,

end_snap_id   => 220,

baseline_name => 'batch baseline');

END;

/

·          The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted.

BEGIN

  DBMS_WORKLOAD_REPOSITORY.drop_baseline (

    baseline_name => 'batch baseline',

    cascade       => FALSE); -- Deletes associated snapshots if TRUE.

END;

/

•     The following workload repository views are available:

V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.

V$METRIC - Displays metric information.

V$METRICNAME - Displays the metrics associated with each metric group.

V$METRIC_HISTORY - Displays historical metrics.

V$METRICGROUP - Displays all metrics groups.

DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.

DBA_HIST_BASELINE - Displays baseline information.

DBA_HIST_DATABASE_INSTANCE - Displays database environment information.

DBA_HIST_SNAPSHOT - Displays snapshot information.

DBA_HIST_SQL_PLAN - Displays SQL execution plans.

DBA_HIST_WR_CONTROL - Displays AWR settings.