MODULE 4
AWR Report analysis:
How to read an AWR Report.
AWR
The AWR collects and stores database statistics relating to problem detection and tuning. AWR is
a replacement for the statspackutility which helps gather database performance statistics. AWR
generates snapshots of key performance data, such as system and session statistics, segment-
usage statistics, time-model statistics, high-load statistics and stores it in the sysauxtablespace.
AWR provides statistics in two formats:
Temporary - in memory collection of statistics in the SGA, accessible via the V$ views
Persistent - type of performance data in the form of regular AWR snapshots which you access via
the DBA_ views
The MMON process is responsible for collecting the statistics in the SGA and saving them to the
sysauxtablespaces.
AWR
Main Steps to Analyze AWR Report:
Database Details
Host Configuration
Snap Shot Detail
Load Profile
Instance Efficiency Percentages
Top 5 Timed Foreground Events
Time Model Statistics Operating System Statistics - Detail
SQL Ordered by Elapsed Time
SQL Ordered by CUP Time
Recommendations before getting an AWR Report:
Collect Multiple AWR Reports Check AWR current snapshot setting
Stick to Particular Time SQL-->
Split Large AWR Report into select
Smaller Reports (30 mins) extract(day from snap_interval) *24*60+
extract(hour from snap_interval) *60+
extract(minute from snap_interval) snapshot_interval,
extract(day from retention) *24*60+
extract(hour from retention) *60+
extract(minute from retention) retention_interval,
topnsql
from dba_hist_wr_control;
How to Run? Change snapshot interval to 30 mins:
@?/rdbms/admin/[Link]; SQL-->
@?/rdbms/admin/[Link] EXEC
@?/rdbms/admin/[Link]; dbms_workload_repository.modify_snapshot_settings
@?/rdbms/admin/[Link]; (interval=>30);
Database
Details:
• RAC,
• DB Version,
• OS,
• CPUs,
• Memory
AWR Report
1. Sum of the time taken by all sessions in 2. DB TIME/ELAPSED = 1774.69/29.69 = 59.7
the database during the 'Elapsed' time. So database load (average active sessions) = 2.18
DB Time=Session time spent in database.
DB Time=CPU Time + Non IDLE wait time.
DB Time=DB CPU + wait for CPU + wait time 3. ELAPSED TIME
The time duration in which this AWR
No background process report has been generated.
Load Profile
physical reads,
physical writes,
hard parse to parse ratio and
executes to transaction ratio.
Block Changes:
Number of blocks modified during the
sample interval.
More Redo Means:
INSERT, UPDATE and DELETE
Parses Hard Parses + Soft Parses
User Calls
Like "parse", "fetch", "execute", "close"
Average active sessions, DB CPU, logical and physical reads, user calls, executions, parses, hard parses, logons, rollbacks,
transactions.
As per the thumb rule, Instance Efficiency Percentages % Non-Parse CPU
should be ideally above 90%. -Used for execution but here we are using other than
execution which is good
Buffer Nowait%:
data buffers were accessed directly without any wait time. Latch Hit%:
latches are acquired without having to wait.
Execute to Parse%:
Shows how often parsed SQL statements are reused Redo NoWait%:
without re-parsing. Shows whether the redo log buffer has sufficient size.
Parse CPU to Parse Elapsd%:
Gives the ratio of CPU time spent to parse SQL statements.
1. This section is critical because it shows those 1. DB FILE SEQUENTIAL READ
database events that might constitute the DB FILE SCATTERED READ
bottleneck for the system. Here, first of all LOG FILE SYNC
check for wait class if wait class is User I/O, enq TX- row lock contention
System I/O, Others etc this could be fine but Buffer Busy Wait
if wait class has value "Concurrency" then Concurrency, wait class
there could be some serious problem direct path read temp or direct path write temp
2. Waits and Wait Avg(ms) are high then there is
a issue
Time Model Statistics
Operating System Statistics
AWR Report
WHAT NEXT?