Skip to content

awr report – brief intro to the new oracle dba





awrrpt.sql

This chapter focuses on the skills required for reading and interpreting an Automated Workload Repository (AWR) report, which is very similar to the STATSPACK elapsed-time report.  The AWR elapsed-time report contains valuable information regarding the health of the Oracle instance, but considerable skill is required to fully understand and interpret each section.

While there is only enough room in this chapter to cover the highlights, this important chapter should give users a good idea about what to look for in an AWR report and how to use this data to identify performance problems.

Generating the AWR Report

The procedure for creating a standard report provided by the STATSPACK utility in previous Oracle releases has already been introduced.  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.

Two procedures that generate AWR reports are awr_report_text and awr_report_html .  These procedures generate the AWR report for the specified snapshot range in TEXT or HTML formats, respectively.  The following script shows one way of retrieving the AWR text report for the particular snapshot range:

SELECT

output

FROM

TABLE

(dbms_workload_repository.awr_report_text

(37933856,1,2900,2911 )

);

OUTPUT

——————————————————————–

WORKLOAD REPOSITORY report for

DB Name       DB Id    Instance  Inst Num Release     Cluster Host

———- ———– ——— ——– ———– ——- —–

DBDABR        37933856 dbdabr           1 10.1.0.2.0  NO      Host1

Snap Id      Snap Time      Sessions Curs/Sess

——— ——————- ——– ———

Begin Snap:      2900 19-Aug-04 11:00:29        18       5.2

End Snap:      2911 19-Aug-04 22:00:16        18       4.6

Elapsed:              659.78 (mins)

DB Time:               10.08 (mins)

Cache Sizes (end)

~~~~~~~~~~~~~~~~~

Buffer Cache:        48M      Std Block Size:         8K

Shared Pool Size:        56M          Log Buffer:       256K

Load Profile

~~~~                            Per Second       Per Transaction

——–       —————

Redo size:      1,766.20             18,526.31

Logical reads:         39.21                411.30

Block changes:         11.11                116.54

Physical reads:          0.38                  3.95

Physical writes:          0.38                  3.96

User calls:          0.06                  0.64

Parses:          2.04                 21.37

Hard parses:          0.14                  1.45

Sorts:          1.02                 10.72

Logons:          0.02                  0.21

Executes:          4.19                 43.91

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.

The next section outlines the evolution of the STATSPACK report, spreports, into the Oracle10g AWR report.

Automatic Workload Repository (AWR) in Oracle Database 10g

Oracle have provided many performance gathering and reporting tools over the years. Originally the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics. Oracle8i introduced the Statspack functionality which Oracle9i extended. In Oracle 10g statspack has evolved into the Automatic Workload Repository (AWR).

AWR Features

The AWR is used to collect performance statistics including:

  • Wait events used to identify performance problems.
  • Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
  • Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
  • Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
  • Object usage statistics.
  • Resource intensive SQL statements.

The repository is a source of information for several other Oracle 10g features including:

  • Automatic Database Diagnostic Monitor
  • SQL Tuning Advisor
  • Undo Advisor
  • Segment Advisor

Snapshots

By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using:

BEGIN

DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(

retention => 43200, — Minutes (= 30 Days). Current value retained if NULL.

interval => 30); — Minutes. Current value retained if NULL.

END;

/

The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view. Typically the retention period should capture at least one complete workload cycle. If you system has monthly archive and loads a 1 month retention time would be more beneficial that the default 7 days. An interval of "0" switches off snapshot collection, which in turn stops much of the self-tuning functionality, hence this is not recommended. 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.

Extra snapshots can be taken and existing snapshots can be removed using:

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

BEGIN

DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (

low_snap_id => 22,

high_snap_id => 32);

END;

/

Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.

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 like:

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;

/

Baseline information can be queried from the DBA_HIST_BASELINE view.

Workload Repository Views

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.

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

The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

Enterprise Manager

The automated workload repository administration tasks have been included in Enterprise Manager. The "Automatic Workload Repository" page is accessed from the main page by clicking on the "Administration" link, then the "Workload Repository" link under the "Workload" section. The page allows you to modify AWR settings or manage snapshots without using the PL/SQL APIs.

Form more information see:

  • Automatic Performance Statistics

.

SQL> @/opt/oracle/product/10.2.0/rdbms/admin/awrrpt.sql;

1172 19 Aug 2008 14:00 1

1173 19 Aug 2008 15:00 1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 1172

Begin Snapshot Id specified: 1172

Enter value for end_snap: 1173

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_1172_1173.txt. To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: awrrpt_august_19th_2pm_3pm.txt

VN:F [1.9.22_1171]
Rating: 7.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
awr report - brief intro to the new oracle dba , 7.0 out of 10 based on 1 rating

Post a Comment

You must be logged in to post a comment.