Overview of oracle AWR report

 Awr report is automatic workload repository

It’s introduced in oracle database 10G

This report stores data in sysaux tablespace

AWR report background process is MMON(Manageability monitor) and MMNL.

AWR report collects, process and maintenance performance statists. It can’t give any recommendations.

It’s retain the stats for 8-days by default.

How to generate AER report

cd $ORACLE_HOME/rdbms/admin

Report Name	SQL Scripts
AWR Report	awrrpt.sql
Automatic DB Diagnostics Monitor report	addmrpt.sql
ASH Report	ashrpt.sql
AWR Diff periods Report	awrddrpt.sql
AWR Single SQL Statement Report	awrsqrpt.sql
AWR Global Report	awrgrpt.sql
AWR Global Diff Report	awrgdrpt.sql

 

AWR report Analysis

We can’t analysis the all details in AWR report because It’s contains much data

I will be cover top main steps to analysis awr report

Top 10 steps to analyses AWR report

Database details

Host configurations

Snap shot details

Load profile

Instance efficiency percentage.

Top 5-Timed forceground events

Time model statistics

SQL ordered by elapsed time

SQL ordered by CPU time

 

Database details

We can see the database and OS details like DB version, name,RAC/Non-RAC

Hostname, total number of CPU,Memory

What is DB Time?

DB time is session time spend in database

DB-Time=CPU Time +Non- Ideal wait time

Example:

I executed my query in 1:00AM it took to completed 1:5PM. 5-min is my DB time.

Query is waiting for CPU and other IDEL times all will be consider for DB Time

DB-Time=CPU Time +Non- Ideal wait time.

Background process will not consider for DB time.

What is ELAPSED Time?

The time duration which AWR report generated.

It’s used to find the average active session

DB Time/Elapsed=29.99/10.04=2.87

Database load (average active session)=2.87

Total 2 session are active during AWR report

 

Load profile

Load report is average active session, DB CPU,Logical and physical read, execution time,parses,hard parses, logons, rollback transactions

We need to chech physical reads, Physical writes, hard parses to parse ratio and execution to execution ratio.

 

Redo Size:

The read size is means performing DML operations like Insrt,update and delete

If reado size is more we need to check DML activity’s.

Block changes:

Number of blocks modified during interval.

If blocks are changed means it’s doing lot of physical reads

If blocks getting changed means lot of DML operation going on.

Parses:

Pares means both hard parses and soft parses

Parse=Hard parse + soft parse

Hard Parse:

The process requiring a completely new parse of the SQL statement.

Those are consume the shared pool area.

If hard parse is increased need to check below in AWR report

Library cache in top-5 wait events

Check CPU

User calls:

The number of call from a user process the database like “Parse,Frtch,Ececute,Close”

 

Instance Efficiency Percentages:

The thump rule is always 100% or above 90% our DB is good.

 

Buffer nowait:

Data buffer were accessed directly without any wait time.

Library Hit:

Shows % times sql and plsql founded in shared pool.

Execute to Parse

Shows how often parsed SQL statements are reused without re-processing.

When this number is low parsing is consuming CPU and latching.

Parse CPU to Parse Elapsed

Gives this ratio CPU times spent to parse SQL statement.

If its low means it could be a parsing problem and we need to check shared pool sizing issue.

Redo Nowait

It’s shows whether the redo log buffer has stuffiest size

Non-Parse CPU

Used for execution. Oracle utilize the CPU mostly execution process but not for the parsing.

If its value is above 90 our database good it avoid the high CPU performance.

In Memory sort

Its showing % time sorting operation happened in memory then in the disk (temporary table space)

Latch Hit  

Latches are acquired without having wait.

 

Top 10 Foreground events:

 It is the very important part of the AWR report

In this session mainly need to check any concurrency. If there is any concurrency our system is not good

If total waits and average wait is high we need to check what is causing the issue

There are so many wait events there in this   report captured few wait events only so I will write another new post for oracle wait events.

 

Reference:

How to Identify Tuning opportunity

Important views and terminologies in performance Tuning

Oracle ASH report Overview

Overview of Oracle SQL-Tuning Adviser

Performance tuning basic OS analysis