Awr Report 1w5r34

AWR Report

Introduction 

Statspack was introduced in Oracle 8.1.7



Below are the steps to run statspack report: 1. cd $ORACLE_HOME/rdbms// 2. Start the Statspack install script, spcreate.sql, as follows: > sqlplus "/ as sysdba" @spcreate.sql 3. Enter a for the PERFSTAT when prompted. 4. Enter the default tablespace (tools) for the PERFSTAT when prompted. 5. Enter the temporary tablespace (temp) for the PERFSTAT when prompted.



AWR (Automatic Workload Repository) came out in Oracle10g and is the next evolution of statspack utility.



Both provide a top-down look at performance statistics.

What is AWR 

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. 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 V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views.



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



SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and U time.



ASH (Active Session History) statistics, representing the history of recent sessions activity.

STATISTICS_LEVEL parameter STATISTICS_LEVEL parameter specifies the level of 

collection for database and operating system statistics. 

STATISTICS_LEVEL = TYPICAL : ensures collection of all major statistics required for database selfmanagement functionality and provides best overall performance.



STATISTICS_LEVEL = ALL : additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.



STATISTICS_LEVEL = BASIC : disables the collection of many of the important statistics required by Oracle Database features and functionality.



The V$STATISTICS_LEVEL view displays information about the status of the statistics or advisories controlled by the STATISTICS_LEVEL parameter.

CONTROL_MANAGEMENT_PACK_A CCESS parameter 

CONTROL_MANAGEMENT_PACK_ACCESS specifies which of the Server Manageability Packs should be active.



The following packs are available:





The DIAGNOSTIC pack includes AWR, ADDM, and so on.



The TUNING pack includes SQL Tuning Advisor, SQLAccess Advisor, and so on.



A license for DIAGNOSTIC is required for enabling the TUNING pack.

Default value : DIAGNOSTIC + TUNING

SNAPSHOTS 

Snapshots are sets of historical data for specific time periods that are used for performance comparisons.



By default, Oracle Database automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 8 days.



AWR compares the difference between snapshots to determine which SQL statements to capture based on the effect on the system load. This reduces the number of SQL statements that must be captured over time.



DBMS_WORKLOAD_REPOSITORY procedures are used to manually create, drop, and modify the snapshots.



Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.



Create a Snapshot:



Modify a Snapshot:



Drop a Snapshot:

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.



Types of Baselines:



Fixed Baseline - corresponds to a fixed, contiguous time period in the past that you specify.



Moving Window Baseline - corresponds to all AWR data that exists within the AWR retention period. Oracle Database automatically maintains a system-defined moving window baseline. The default window size is the current AWR retention period, which by default is 8 days. Useful when using adaptive thresholds.



Baseline Template – used to create baselines for a contiguous time period. There are two types of baseline templates: single and repeating.



Review the existing baselines in the DBA_HIST_BASELINE view.



Create a Baseline:



Drop a Baseline:



Display Baseline Metrics: The SELECT_BASELINE_METRICS function enables you to display the summary statistics for metric values in a baseline period.

AWR report generation – a look at the scripts 1. awrextr.sql - extracts the AWR data for a range of snapshots from the database into a Data Pump export file. 2.

awrload.sql – loads the extracted data in target database. To run the awrload.sql script, you need to be connected to the database as the SYS .

3.

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

4.

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

5.

awrsqrpt.sql - 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.

6.

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

7.

awrddrpi.sql - 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.



Steps to create an AWR report:

1. to the database and from the sql command prompt enter the below command: SQL> @?/rdbms//awrrpt.sql Current Instance -------------------------------DB Id

DB Name

Inst Num Instance

----------- ------------ -------- -----------929422019 DWDEVL2

1 dwdevl2

2. Specify the Report Type: Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: html Type Specified:

html

3. Specify the number of days of snapshots to choose from Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots. Enter value for num_days: 1 Listing the last day's Completed Snapshots Snap Instance

DB Name

Snap Id

Snap Started

Level

------------ ------------ --------- ------------------ ----dwdevl2

DWDEVL2

72214 09 Dec 2014 00:00

1

72215 09 Dec 2014 01:00

1

72216 09 Dec 2014 02:01

1

72217 09 Dec 2014 03:00

1

4. Specify the Begin and End Snapshot Ids Enter value for begin_snap: 72215 Begin Snapshot Id specified: 72215 Enter value for end_snap: 72216 End

Snapshot Id specified: 72216

5. Specify the Report Name: The default report file name is awrrpt_1_72215_72216.html. name,

To use this

press to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrrpt_1_72215_72216.html

6. Use the below command to mail the awr report to any specific email id: uuencode reportname reportname | mailx [email protected]

7. Sample AWR report: awrrpt_1_72215_72216.html

How to analyze the AWR report 

Load Profile:



Redo size: The amount of redo generated during this report.



Logical Reads: Calculated as (Consistent Gets + DB Block Gets = Logical Reads).



Block changes: The number of blocks modified during the sample interval.



Physical Reads: The number of requests for a block that caused a physical I/O operation.



Physical Writes: Number of physical writes performed.



Calls: Number of queries generated.



Parses: The total of all parses; both hard and soft.



Hard Parses: The parses requiring a completely new parse of the SQL statement. These consume both latches and shared pool area.



Soft Parses: Soft parses are not listed but derived by subtracting the hard parses from parses. A soft parse reuses a previous hard parse; hence it consumes far fewer resources.



Sorts, Logons, Executes and Transactions: All self-explanatory.

a section for monitoring the throughput.



Instance Efficiency:



The Instance Efficiency Percentage report section contains ratios or calculations that may provide information regarding different structures and operations in the Oracle instance.



Database tuning must never be driven by hit ratios.



Hit ratios only provide additional information to help the DBA understand how the instance is operating.

Instance Efficiency Percentages (Target 100%) Buffer Nowait %:

100.00

Redo NoWait %:

100.00

Buffer Hit %:

99.05

In-memory Sort %:

99.55

Library Hit %:

99.90

Soft Parse %:

99.88

Execute to Parse %:

54.56

Latch Hit %:

99.89

% Non-Parse U:

99.00

Parse U to Parse Elapsd %:

100.00



Top 5 Timed Events:



This report is critical because it shows those database events that might constitute the bottleneck for the system.



The causes of these top wait events should be investigated in order to minimize database wait time as much as possible. These top wait events are also available in the next report section called Wait Events. Wai Event

ts

U time

Time(s)

Avg Wait(ms)

% Total Call Time

4

Wait Class 80.3

control file parallel write

1, 2 0 1

2

1

34.3

System I/O

db file sequential read

6, 7 6 2

1

0

20.5

I/O

db file parallel write

1 0 8

0

4

7.8

System I/O

log file parallel write

7 4

0

4

5.8

System I/O

Further more, we can check: 

SQL statistics – List of Sql statements ran during the timeframe.



I/O statistics – includes the tablespace and file i/o stats.



Wait statistics – various wait events as present in Top 5 wait events list.