AWR Report in Oracle 11g

AWR Report in Oracle 11g

Intro:

-oracle have  provided many performance gathering and reporting tools over the years

-to monitor the performance metrics "UTLBSTAT/UTLESTAT

-In oracle 8i The stactspack functionality was introduced

-In oracle 9i the functionality had been extended

-In oracle 10g statspack has evolved into AWR (Automatic Workload Repository)

How It Works:

-It resides in SYSAUX tablespace and by default, snapshots are generated once every 60mins and mainted for 7 days

Uses:

-depends on the output a series of statistics based on the differences b /w  snapshots that may be used to investigate performance and other issues.

Types:

-Awr reports can be generated by various SQL scripts to satisfy various requirements

-the report is availble on HTML and TEXT format

1)awrrpt.sql   = displays various statistics for a range of snapshot id

2)awrrpti.sql  = displays statistics for a range of snapshot id on specified database and instance

3)awrsqrpt.sql = display statistics of a particular SQL statement for a range of snapshot ids, run this report to debug the performance of a particular SQL statement

4)awrsqrpi.sql = displays statistics of a particular SQL statement for a range of snapshot Ids on a specified SQL

5)awrddrpt.sql = compares  detailed performance attributes and configuration settings b/w two selected time periods

6)awrddrpi.sql = compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.

To generate Snapshot manually:

used the script below

begin

DBMS_WORKLOAD_REPOSITORY.Create_snapshot()

end;

/

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.

Automation of AWR Reports:

-We can use the dbms_workload_repository.awr_report_text in a bash script and be using cron you can run it on a daily basis for automated reports.

Scenarios of AWR taking:

-The performance has slowly degraded over time due to increased user activity

-The performance of DB over time has been degraded due to changing or increased content

-The performance of DB has become slower after the patchset has been applied or app server  version changed

-The performance of DB has become slower after the database version changes or patchset has been applied

Steps to generate AWR:

In Linux:

$ORACLE_HOME/rdbms/admin/awrrpt.sql

In Windows:

C:\Users>sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.2.0 Production on Tue May 23 17:17:54 2017

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

SQL> exec dbms_workload_repository.create_snapshot

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/awrrpt.sql

Current Instance

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

DB Id    DB Name      Inst Num Instance

2804950687 DHONI               1 xe

Using 2804950687 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 without

specifying a number lists all completed snapshots.

Enter a value for num_days:

(Press "ENTER" if we need to view over all snapshot )

Listing all Completed Snapshots

Snap

Instance     DB Name        Snap Id    Snap Started    Level

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

xe           DHONI             1208 18 May 2017 09:45      1

1209 18 May 2017 10:30      1

1210 18 May 2017 11:30      1

1211 18 May 2017 12:30      1

1212 18 May 2017 13:30      1

1213 18 May 2017 14:30      1

1214 18 May 2017 15:30      1

Specify the Begin and End Snapshot Ids

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

Enter value for begin_snap: 1235

Begin Snapshot Id specified: 1235

Enter value for end_snap: 1236

End   Snapshot Id specified: 1236

(here we need to type the Snapshot id which is our DB had performance issue and took the snapshot which used took diagnose the issue )

Specify the Report Name

The default report file name is awrrpt_1_1235_1236.html.  To use this name,

press to continue, otherwise enter an alternative.

Enter value for report_name: awr_test_ana.html

(--------------)

(--------------)

(--------------)

End of Report

Report was written to awr_report_techpaste.com.html

SQL>exit

How to understand AWR report in Oracle11g?

Let we discuss about How to understand AWR report in oracle11g?
* We already know the steps to generate AWR reports , but after you did this how can you read it .
To understand AWR report:
Step-1:
To check "DB Time"

Snap Id Snap Time Sessions Cursors/Session

Begin Snap: 1235 23 May 2017 10:30 191 6.7

End Snap: 1236 23 May 2017  11:31    173 7.4

Elapsed: 59.23 (mins)

DB Time: 710.73 (mins) 

In above scenario " DB time " metric is higher than elapsed time so it meaning session are waiting for something
Note:if the Dbtime is higher than elapsed time so the session must be waiting for something
 STEP-2:
To View Instance Efficiency

 Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 100.00 Redo NoWait %: 100.00

Buffer Hit %: 98.67 In-memory Sort %: 100.00

Library Hit %: 98.60 Soft Parse %: 99.69

Execute to Parse %: 5.26 Latch Hit %: 99.31

Parse CPU to Parse Elapsd %: 12.78 %Non-Parse CPU: 99.10 

Note: As per the thumb rule, Instance Efficieny Percentages should be ideally above 90%.
 STEP-3:
To Review Shared Pool Statistics

 Shared Pool Statistics

Begin End

Memory Usage %: 83.49 80.93

% SQL with executions>1: 42.46 82.96

% Memory for SQL w/exec>1: 47.77 81.03 

In above the memory Usage of shared pool statistics has been shown here now it seems 83 %
If suppose its higher than 90 % the conflict in the shared pool

STEP-4:
To View Top 5 Events

 Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class

db file sequential read 4,076,086 28,532 7 66.9 User I/O

CPU time 11,214 26.3

Backup: sbtbackup 4 4,398 1,099,452 10.3 Administrative

log file sync 37,365 2,421 65 5.7 Commit

log file parallel write 37,928 1,371 36 3.2 System I/O 

Here, the significant wait is the db file sequential read which contributes to 67% of DB Time.
 STEP-5:
Then , SQL Statistics can be checked.

SQL Statistics

SQL ordered by Elapsed Time

SQL ordered by CPU Time

SQL ordered by Gets

SQL ordered by Reads 

SQL Statistics section would have commonly the above four sections.
Each section shows the list of SQLs based on the order of the respective metric.
For example, SQL ordered by Elapsed Time section shows the list of SQLs in the order of the Elapsed Time.
High resource consuming SQLs can be spotted out and meant for tuning.
Note: All the above four sections of SQL Statistics show the list of SQLs in descending order.
i.e, For ex: Highest elapsed time is shown as first.
 STEP-6:
Then comes the IO Stats section.
This shows the IO Statistics for each tablespaces in the database.

Note:As the thumb rule, the Av Rd(ms) [Average Reads in milliseconds] should not cross beyond 30, add myself(not greater that 30) which is considered to be IO bottleneck.

Tablespace IO Stats

ordered by IOs (Reads + Writes) desc 

Tablespace Reads     Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms)

TEMP        3,316,082 933         4.91      1.00              28,840 8 0 0.00

DATA1      520,120     146 16.06 1.21 185,846 52 902 13.00

DATA3      93,411       26 42.82 2.98 13,442 4 16 23.13

DATA2      98,171       28 91.97 7.97 5,333 2 325 34.89 

In the above example, the Av Rd(ms) is high in all tablespaces indicating the IO contention.
 STEP-7
To verfiy Advisory Statistics

This section shows the following:-

Buffer Pool Advisory

PGA Aggr Summary

PGA Aggr Target Stats

PGA Aggr Target Histogram

PGA Memory Advisory

Shared Pool Advisory

SGA Target Advisory

Streams Pool Advisory

Java Pool Advisory 

It is very commonly used to check the advisories for the most important SGA structures like shared pool, buffer cache etc and PGA.
 STEP-8
Then finally, init.ora Parameters is shown which shows the list of parameters set at instance level.

init.ora Parameters

All the above said sections except the DB Time can be checked from Statspack report also.
The statspack snapshots are not generated automatically as in AWR.It has to be generated during the problem period as follows:-
Take 2 snapshots between 60 minutes interval during the problem and generate the statspack report

exec statspack.snap

wait for 60 minutes

exec statspack.snap

Please run $ORACLE_HOME/rdbms/admin/spreport.sql

and specify BEGIN and END ID's of the snapshots taken during t

AWR VS STATSPACK Reports in Oracle 11g

AWR VS STATSPACK

*When we are facing performance issue in the database our first thought is to take AWR or statspack reports. but we do not know what the difference between AWR are and statspack

Here we can discuss them, and I will try to mention as much as I can

*Basically, as a dba we aware about AWR report but it did not exist on oracle 9i we forced to use statspack in the way of generating AWR report much easier than statspack report.

*AWR holds all the information and statistics that exist in STATPACK, include to that AWR hold Additional Information.

*In AWR we can find the information on Active Session History(ASH) which does not exist in Statspack.

*In statspack we should run the procedure to enable snapshot, we can use DBMS_JOB or schedule on crontab.

*In AWR A snapshot provides a sustained view of database statistics. A snapshot is a collection of performance statistics that are captured at a specific point in time, AWR snapshots are scheduled every 60 minutes by default.

*In statspack snapshot are purged must be scheduled manually.

*In AWR snapshot are purged automatically by MMON.

ASSM (Automatic Space Segment Management)

What is segments ?

- Segments  is a collection of extents that  contain all the data for a specific logical storage structure within a tablespace .

- Segment Space Management  is allow to  specif y how free and used space within a segment is to be managed

Types of segment space management   

*Manual

- Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows. This form of managing space within segments is called manual segment-space management.

- We need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects created in the tablespace.

*Auto

- Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows.

- As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

- Automatic segment-space management delivers better space utilization than manual segment-space management, and it is self-tuning in that it scales with increasing the number of users, as well as instances.