Description
Introduced in Oracle 10g and having evolved from the Statspack functionality of Oracle 8i/9i, the Automatic Workload Repository report or AWR report (as it is more commonly known as) is a vital tool to collect historical performance statistics of an Oracle Database by DBAs and System Performance engineers.
What is AWR
üAWR stands for Automated Workload Repository.
üIt is a built-in repository that exists in every Oracle Database.
üOracle have provided many performance gathering and reporting tools over the years.
üOriginally, the UTLBSTAT/UTLESTAT scripts were used to monitor performance metrics.
üAt regular intervals, the Oracle Database makes a snapshot of all of its vital statistics.
üThe workload information is stored in the AWR.
üAWR was first introduced with Oracle 10g.
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.
ØActive Session History (ASH) statistics.
ØObject usage statistics.
ØResource intensive SQL statements.
Step 1: 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 the following procedure.
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;
/
Step 2: 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.
üWe create a 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 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;
/
Step 3: Workload Repository Reports
üOracle provides two scripts to produce workload repository reports.
1)awrrpt.sql
2)awrrpti.sql)
üThe reports can be generated by running the above scripts in sqlplus.
@$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 opened in a browser or text editor.
Step 4: Workload Repository Reports Usage
üIf you are new to the AWR reports, the first thing you should probably do is run the ADDM report.
üThe ADDM report provides root cause analysis of the parts of the system consuming the most time.
üWhen looking at an AWR report, a good place to start is the “Top 5 Timed Foreground Events” section.
üThis gives you an indication of the bottlenecks in the system during this sample period.
üOnce you’ve identified the top events, drill down to see what SQL and PL/SQL are consuming the majority of those resources.
üOn the “Main Report” section, click the “SQL Statistics” link.
üOn the “SQL Statistics” section, click the link that most closely relates to the wait event you identified in the “Top 5 Timed Foreground Events” section.
üIn our case, the “DB CPU” was the top event.
üYou will be then presented with the SQL and PL/SQL that are using most of the specified resource during the sample period.
üYou can then attempt to tune these areas to reduce the impact.
SQL Developer and AWR Reports
üIf you are using SQL Developer 4 onward, you can view AWR reports directly from SQL Developer.
üIf it is not already showing, open the DBA pane “View > DBA,” expand the connection of interest, then expand the “Performance” node.
üThe AWR reports are available from the “AWR” node.
Summary:
AWR contains vast amounts of database performance data. Enterprise Manager displays a comprehensive view and analysis of that data. Standard reports can provide additional insights – Use the right report for the right problem. Active reports are a useful new tool for the Oracle database professional.
Queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
