AWR(Automatic Workload Repositor

 

AWR(Automatic Workload Repository)

 

The AWR provides a set of tables into which snapshots of system statistics are stored. Generally these snapshots are taken on an hourly basis and include wait interface statistics, top SQL, memory, and I/O information that is cumulative in nature up to the time of the capture.

The AWR report process takes the cumulative data from two snapshots and subtracts the earlier snapshot’s cumulative data from the later snapshot and then generates a delta report showing the statistics and information relevant for the time period requested. AWR is a more advanced version of the old Statspack reports that has been automated and made integral to Oracle’s automated tuning processes for the Oracle Database.

The main sections in an AWR report include:

Report Summary: This gives an overall summary of the instance during the snapshot period, and it contains important aggregate summary information.

Cache Sizes (end): This shows the size of each SGA region after AMM has changed them. This information can be compared to the original init.ora parameters at the end of the AWR report.

Load Profile: This important section shows important rates expressed in units of per second and transactions per second.

Instance Efficiency Percentages: With a target of 100%, these are high-level ratios for activity in the SGA.

Shared Pool Statistics: This is a good summary of changes to the shared pool during the snapshot period.

Top 5 Timed Events: This is the most important section in the AWR report. It shows the top wait events and can quickly show the overall database bottleneck.

Wait Events Statistics Section: This section shows a breakdown of the main wait events in the database including foreground and background database wait events as well as time model, operating system, service, and wait classes statistics.

Wait Events: This AWR report section provides more detailed wait event information for foreground user processes which includes Top 5 wait events and many other wait events that occurred during the snapshot interval.

Background Wait Events: This section is relevant to the background process wait events.

Time Model Statistics: Time mode statistics report how database-processing time is spent. This section contains detailed timing information on particular components participating in database processing.

Operating System Statistics: The stress on the Oracle server is important, and this section shows the main external resources including I/O, CPU, memory, and network usage.

Service Statistics: The service statistics section gives information about how particular services configured in the database are operating.

SQL Section: This section displays top SQL, ordered by important SQL execution metrics.

SQL Ordered by Elapsed Time: Includes SQL statements that took significant execution time during processing.

SQL Ordered by CPU Time: Includes SQL statements that consumed significant CPU time during its processing.

SQL Ordered by Gets: These SQLs performed a high number of logical reads while retrieving data.

SQL Ordered by Reads: These SQLs performed a high number of physical disk reads while retrieving data.

SQL Ordered by Parse Calls: These SQLs experienced a high number of reparsing operations.

SQL Ordered by Sharable Memory: Includes SQL statements cursors which consumed a large amount of SGA shared pool memory.

SQL Ordered by Version Count: These SQLs have a large number of versions in shared pool for some reason.

Instance Activity Stats: This section contains statistical information describing how the database operated during the snapshot period.

Instance Activity Stats (Absolute Values): This section contains statistics that have absolute values not derived from end and start snapshots.

Instance Activity Stats (Thread Activity): This report section reports a log switch activity statistic.

I/O Section: This section shows the all important I/O activity for the instance and shows I/O activity by tablespace, data file, and includes buffer pool statistics.

Tablespace IO Stats

File IO Stats

Buffer Pool Statistics

Advisory Section: This section show details of the advisories for the buffer, shared pool, PGA and Java pool.

Buffer Pool Advisory

PGA Aggr Summary: PGA Aggr Target Stats; PGA Aggr Target Histogram; and PGA Memory Advisory.

Shared Pool Advisory

Java Pool Advisory

Buffer Wait Statistics: This important section shows buffer cache waits statistics.

Enqueue Activity: This important section shows how enqueue operates in the database. Enqueues are special internal structures which provide concurrent access to various database resources.

Undo Segment Summary: This section gives a summary about how undo segments are used by the database.

Undo Segment Stats: This section shows detailed history information about undo segment activity.

Latch Activity: This section shows details about latch statistics. Latches are a lightweight serialization mechanism that is used to single-thread access to internal Oracle structures.

Latch Sleep Breakdown

Latch Miss Sources

Parent Latch Statistics

Child Latch Statistics

Segment Section: This report section provides details about hot segments using the following criteria:

Segments by Logical Reads: Includes top segments which experienced high number of logical reads.

Segments by Physical Reads: Includes top segments which experienced high number of disk physical reads.

Segments by Buffer Busy Waits: These segments have the largest number of buffer waits caused by their data blocks.

Segments by Row Lock Waits: Includes segments that had a large number of row locks on their data.

Segments by ITL Waits: Includes segments that had a large contention for Interested Transaction List (ITL). The contention for ITL can be reduced by increasing INITRANS storage parameter of the table.

Dictionary Cache Stats: This section exposes details about how the data dictionary cache is operating.

Library Cache Activity: Includes library cache statistics describing how shared library objects are managed by Oracle.

SGA Memory Summary: This section provides summary information about various SGA regions.

init.ora Parameters: This section shows the original init.ora parameters for the instance during the snapshot period.

 

 

Example AWR report:

 

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

 

Specify the Report Type

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

AWR reports can be generated in the following formats.      Please enter the

name of the format at the prompt.  Default value is 'html'.

 

'html'               HTML format (default)

'text'                Text format

'active-html'     Includes Performance Hub active report

 

Enter value for report_type: text

 

Type Specified:  text

 

Current Instance

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

DB Id         DB Name       Inst Num        Instance          Container Name

-------------- -------------- -------------- -------------- --------------

 3038906043   LIVEDB                                     1 livedb            livedb

 

Instances in this Workload Repository schema

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

  DB Id      Inst Num  DB Name      Instance              Host

------------ ---------- ---------    ----------   ------

* 3038906043 1         LIVEDB              livedb           vignesh.loca

 

Using 3038906043 for database Id

Using          1 for instance number

 

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 <return> without

specifying a number lists all completed snapshots.

Enter value for num_days: 5

 

Listing the last 5 days of Completed Snapshots

Instance     DB Name   Snap Id         Snap Started   Snap Level

------------ ------------ ---------- ------------------ ----------

 

livedb       LIVEDB                37  17 Sep 2021 00:30             1

                                                 58  17 Sep 2021 21:30             1

                                                 59  17 Sep 2021 22:30             1

                                                 60  17 Sep 2021 23:30             1

                                                 61  18 Sep 2021 00:43             1

                                                 62  18 Sep 2021 01:30             1

                                                 83  18 Sep 2021 22:30             1

                                                 89  19 Sep 2021 04:30             1

                                                 90  19 Sep 2021 05:30             1

                                                 91  20 Sep 2021 13:02             1

                                                 99  20 Sep 2021 21:30             1

                                                100  20 Sep 2021 22:30            1

                                                101  20 Sep 2021 23:30            1

                                                108  21 Sep 2021 06:30            1

                                                109  21 Sep 2021 07:30            1

 

 

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 100

Begin Snapshot Id specified: 100

 

Enter value for end_snap: 105

 

Specify the Report Name

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

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

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

 

Enter value for report_name:

 

Using the report name awrrpt_1_100_105.txt

 

WARNING: Since the DB Time is less than one second, there was

             minimal foreground activity in the snapshot period.

             Some of the percentage values will be invalid.

 

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.

Analyze AWR Report in Oracle

 

Analyze AWR Report in Oracle

 

First we have get AWR report in our database after that we have analyse the report

We can easily solve issues like Slow database, high wait events, Slow query and many more. Though It's a lengthy report but Analyzing or Reading relevant part of AWR Report can help to troubleshoot issues in easy and fast manner.

AWR stands for Automatically workload repository, Though there could be many types of database performance issues, but when whole database is slow, then there are two possibilities.

1. Issue with Database Machine. OS Watcher is the best tool to start.

2. If Database performance issue, Then AWR Report is the place to look at.

In case if a particular query is not performing well, i would suggest to look at execution plan of the query, stats of underlying table etc. In this case AWR won't help much.

 

Recommendations before getting an AWR Report.

1. Take Multiple AWR Reports: It's always good to have two AWR Reports, one for good time (when database was performing well), second when performance is poor. This way Remote DBA can easily compare good and bad report to find out the culprit.

2. Stick to Particular Time: "Database is performing slow" will not help anymore to resolve performance issues. We have to have a specific time like Database was slow yesterday at 1 Pm and continue till 4Pm. Here, DBA will get a report for these three hours.

3. Split Large AWR Report into Smaller Reports: Instead of having one report for long time like one report for 4hrs. it's is better to have four reports each for one hour. This will help to isolate the problem.

In case of RAC env. generate one report for each instance. Once, you have generated AWR report. Now, it's time of analyze the report. Since, AWR report is a huge report and area to look into AWR is also depends on problem to problem. Here, I am listing most common area for a DBA to look into which will give a clear picture of the issue.

Steps to Analyze AWR Report

1. Report Header

Database Details:

After getting an AWR Report This is first and Top part of the report. In this part cross check for database and instance and and database version with the Database having performance issue.This report also show RAC=YES if it's an RAC database.

Host Configuration:

This will give you host name, OS platform CUP, socket and RAM etc. Important thing to notice is number of cores into the system. In this example there are 1 CUP's in Cores.

Snap Shot Detail:

This are the detail about snap shot taken in the database, when the Snap start time and end time. Difference between them is as "Elapsed". Here is a new term "DB Time"

DB Time= session time spent in database.
DB Time= CPU Time + Non IDLE wait time.
You can find, DB time is very large as compared to Elapse time, which is not a concern. Check if you have taken a report for the time having performance problem. If yes fine, other wise take a report for performance problem time.

 

2. Load Profile:

Here are few important stats for a DBA to look into. Fist is "DB CPU(s)" per second. Before that let's understand how DB CUP's work.

Next stat to look at are Parses and Hard parses. If the ratio of hard parse to parse is high, this means Database is performing more hard parse. So, needs to look at parameters like cursor_sharing and application level for bind variables etc.

DB Time(s):
Its the amount of time oracle has spent performing database user calls. Note it does not include background processes.
DB CPU(s):
Its the amount of CPU time spent on user calls. Same as DB time it does not include background process. The value is in microseconds
Redo size:
 For example, the table below shows that an average transaction generates about 19,000 of redo data along with around 48,000 redo per second.
Logical reads:
Consistent Gets+ DB blocks Gets = Logical reads
Block Changes:
The number of block modified during the sample interval
Physical reads:
No of block request causing I/O operation
Physical writes:
Number of physical writes performed
User calls:
Number of user 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:
No of sorts performed
Logons:
No of logons during the interval
Executes:
No of SQL Executes
Transactions:
No of transactions per second

The load profile provides an at-a-glance look at some specific operational statistics. You can compare these statistics with a baseline snapshot report to determine if database activity is different. Values for these statistics are presented in two formats. The first is the value per second (for example, how much redo was generated per second) and the second is the value per transaction (for example, 1,024 bytes of redo were generated per transaction).
Statistics presented in the load profile include such things as:
Redo size - An indication of the amount of DML activity the database is experiencing.

Logical and physical reads - A measure of how many IO's (Physical and logical) that the database is performing.

User calls - Indicates how many user calls have occurred during the snapshot period. This value can give you some indication if usage has increased.

Parses and hard parses - Provides an indication of the efficiency of SQL re-usage.

Sorts - This number gives you an indication of how much sorting is occurring in the database.

Logons - Indicates how many logons occurred during the snapshot period.

Executes - Indicates how many SQL statements were executed during the snapshot period.

Transactions - Indicates how many transactions occurred during the snapshot period.
Additionally, the load profile section provides the percentage of blocks that were changed per read, the percentage of recursive calls that occurred, the percentage of transactions that were rolled back and the number of rows sorted per sort operation.

 

3. Instance Efficiency Percentages:

In these statistics, you have to look at "% Non-Parse CPU". If this value is near 100% means most of the CPU resources are used into operations other than parsing, which is good for database health.

Execute to Parse % and Parse CPU to Parse Elapsd %:

If the the value are low like in the above case of 3.40 and 0.01 means that there could be a parsing problem. You may need to look at bind variable issues or shared pool sizing issue.

Redo NoWait%:

Usually this stats is 99 or greater

In-memory Sort %:

This can tell you how efficient is you sort_area_size, hash_area_size or pga_aggrigate_target are. If you dont have adequate sizes of sort,hash and pga parameters, then you in-memory sort per cent will go down

Soft parse %:

with 98.20 % for the soft parse meaning that about 1.72 % (100 -soft parse) is happening for hard parsing. You might want to look at you bind variables issues.

Latch Hit %:
should be close to 100.

% Non-Parse CPU:

Most of our statements were already parsed so we weren't doing a lot of re parsing. Re parsing is high on CPU and should be avoided.

4. Top 10 Timed Foreground Events:

This is another most important stats to consider while looking at AWR Report for any database performance related issue. This has a list of top 10 foreground wait events.

Here, first of all check for wait class if wait class is  User I/O , System I/O,  Others etc this could be fine but if wait class has value "Concurrency" then there could be some serious problem. Next to look at is Time (s) which show how many times DB was waiting in this class and then Avg Wait (ms). If Time(s) are high but  Avg Wait (ms) is low then you can ignore this. If both are high or Avg Wait (ms) is high then this has to further investigate.

In the above screen shot, most of the resource are taken by DB CPU = 64% DB time. Taking resource by DB CUP is a normal situation.

Let's take an example,  In which event is "log file switch (checkpoint incomplete) " which has high waits, huge Time (s) and large values in Avg Wait (ms) and wait class is configuration. So, here you have to investigate and resolve log file switch (checkpoint incomplete).

This section provides insight into what events the Oracle database is spending most of it's time on (see wait events). Each wait event is listed, along with the number of waits, the time waited (in seconds), the average wait per event (in microseconds) and the associated wait class.

its critical to look into this section. If you turn off the statistic parameter, then the Time(s) wont appear. Wait analysis should be done with respect to Time(s) as there could be million of waits but if that happens for a second or so then who cares. Therefore, time is very important component.

So you have several different types of waits. So you may see the different waits on your AWR report. So lets discuss the most common waits.

df file type waits:

db file sequential read:

Is the wait that comes from the physical side of the database. it related to memory starvation and non selective index use. sequential read is an index read followed by table read because it is doing index lookups which tells exactly which block to go to

db file scattered read:

caused due to full table scans may be because of insufficient indexes or un-avilablity of updated statistics

direct Path writes:

You wont see them unless you are doing some appends or data loads

direct Path reads:

could happen if you are doing a lot of parallel query activity

db file parallel writes / read: 

if you are doing a lot of partition activity then expect to see that wait even. it could be a table or index partition

db file single write:

if you see this event than probably you have a lot of data files in your database.

direct path read temp or direct path write temp:

this wait event shows Temp file activity (sort,hashes,temp tables, bitmap)
check pga parameter or sort area or hash area parameters. You might want to increase them
Host CPU, Instance CPU and Memory Statistics are self explanatory.

5. Time Model Statistics:

This is a detailed explanations of system resource consumptions. Stats are order by Time (s) and % of DB Time.

A noticeable result Sum of all  % of DB time is > 100%. why is this ?
Because this is cumulative time i.e. In this case SQL execute elapsed time is taking 89% of DB time, which includes it sub parts like parse time elapsed, hard parse elapsed time etc. So, if you find Hard parse time elapsed is taking more %. So investigate further so on and so forth.
DBA has to look for stat which is taking abnormal % of DB time.

6. Operating System Statistics - Detail:

This is the information related to OS, what is the load status on System shown here.

This report shows, system is 62 and 70% idle at time of report taken, So, there is no resource crunch at system level. But if, you found very high busy, user or sys % and indeed this will led to low idle %. Investigate what is causing this. OS Watcher is the tool which can help in this direction.
Next, very crucial part of AWR report for a DBA is SQL Statistics. Which has all sql query details executed during report time interval.

We will explore few of them, To understand, how to analyzed these reports. Let's start with

7.  SQL Statistics:

In this report, SQL queries are listed on the basis of CPU taken by the query i.e. queries causing high load on the system. The top few queries could be the candidate query for optimization.

stat, look for queries using highest CPU Times, If a query shows executions 0, this doesn't means query is not executing. It might be same case as in SQL queries ordered by Elapsed time. The query is still executing and you have taken the snapshot.

However, There are so many other stats in AWR Report which a DBA needs to consider, I have listed only ten of them but these are the most commonly used stats for any performance related information.