AWR Packages

AWR PACKAGES

AWR Features

The AWR is used to collect performance statistics including:

·         Wait for events to identify performance problems.

·         Time model statistics.

·         Active Session History (ASH).

·         Statistics from the V$SYSSTAT and V$SESSTAT views.

·         Object usage statistics.

·         SQL statements that are resource-intensive.

·         Automatic Database Diagnostic Monitor

·         SQL Tuning Advisor

·         Undo Advisor

·         Segment Advisor

Workload Repository Views

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

Workload Repository Reports

Oracle provide two 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

 

 

Oracle built in Packages for AWR

DBMS_AW

Defines the interfaces of the OLAP option package. It is also identified as the interface to the Express server routines.

DBMS_AWRHUB

AWR Server Host Utilities

Procedures cannot be replicated by LogMiner via regular DML/DDL apply (replication of SYS schema is not supported) nor using 'Procedural replication'

DBMS_AWRHUB_SERVER

AWR Server Host Utilities

DBMS_AWRHUB_SOURCE

AWR Source Host Utilities

DBMS_AWR_ANALYTICS

Creates support log based replication (proj 17779) used for analytic functions. Reads existing AWR data accessible through views and tables generating no UNDO/REDO and can be replicated. 

DBMS_AWR_PROTECTED

A fascinating object. It is wrapped, has both a specification and a body, but yet contains no visible internal objects or arguments.

           The following is pure speculation but I expect that the package head is defining constants and/or data types and the package body contains an initialization section. This speculation is based on the fact that there are two known dependencies.

DBMS_AWR_REPORT_LAYOUT

ASH and AWR Reporting

DBMS_AWR_WAREHOUSE_SERVER

Utilities used to configure an AWR Warehouse Database.

DBMS_AWR_WAREHOUSE_SOURCE

Utilities used to configure a source database to utilize an AWR Warehouse Database.

Sub programs for AWR

ASH_REPORT_HTML Function

Displays the ASH report in HTML

ASH_REPORT_TEXT Function

Displays the ASH report in text

AWR_DIFF_REPORT_HTML Function

Displays the AWR Diff-Diff report in HTML

AWR_DIFF_REPORT_TEXT Function

Displays the AWR Diff-Diff report in text

AWR_REPORT_HTML Function

Displays the AWR report in HTML

AWR_REPORT_TEXT Function

Displays the AWR report in text

AWR_SQL_REPORT_HTML Function

Displays the AWR SQL Report in HTML format

AWR_SQL_REPORT_TEXT Function

Displays the AWR SQL Report in text format

CREATE_BASELINE Function and Procedure

Creates a single baseline

CREATE_SNAPSHOT Function and Procedure

Creates a manual snapshot immediately

DROP_BASELINE Procedure

Drops a range of snapshots

DROP_SNAPSHOT_RANGE Procedure

Activates service

MODIFY_SNAPSHOT_SETTINGS Procedures

Modifies the snapshot settings.