AWR is used to collect performance statistics between two snapshots with below details:

- Wait events

- Database Load Profile

- Instance Specific percentages

- Shared pool statistics

- Report header

- Resource intensive SQL statements

Workload Repository Views:

- V$ACTIVE_SESSION_HISTORY – Displays ASH sampled every second

- DBA_HIST_ACTIVE_SESS_HISTORY – Displays the history contents of the ASH

- DBA_HIST_SNAPSHOT – Displays snapshot information

- DBA_HIST_SQL_PLAN – Displays SQL execution plans.

How to generate AWR report:

- For single instance: @$ORACLE_HOME/rdbms/admin/awrrpt.sql

- For RAC instance: @$ORACLE_HOME/rdbms/admin/awrrpti.sql

Reading AWR reports

Report Header: It provides database name, id, instance in RAC, Platform information and snap interval.
DB Name DB Id Instance Instnum Startup Time Release RAC
TestRAC 3626203793 TestRac2 2 17-Aug-11 19:08 11.1.0.6.0 YES
Host Name Platform CPUs Cores Sockets Memory (GB)
TestRAC Linux 64-bit for AMD 8 8 2

44

Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 28566 27-Sep-11 01:00:21 130 4.8
End Snap: 28567 27-Sep-11 02:00:43 135 4.5
Elapsed: 60.35 (mins)
DB Time: 15.07 (mins)

Begin End
Buffer Cache: 5,888M 5,888M Std Block Size: 8K
Shared Pool Size: 8,704M 8,704M Log Buffer: 138,328K

Load Profile: It provides the snapshot of the database workload occurred during the snapshot interval.

- DB time(s): Amount of time oracle has spent performing database user calls.

- DB CPU(s): Amount of CPU time spent on user calls.

- Redo Size: shows the size of redo generated and no. of redos generated per second.

- Physical Reads: Number of block requested causing I/O operation

- Physical Writes: Number of physical writes performed

- User Calls: Number of user queries generated

- Sorts: No. of sorts performed

- Transactions: No. of transactions per second

Instance Efficiency Percentages (Target 100%): It should be close to 100%

- Execute to Parsed % and Parse CPU to Parse Elapsed %: If the value is low, that means that there could be a parsing problem. You may look into bind variables or shared pool sizing

- Redo NoWait %: Usually this is 99 or greater

- In Memory Sort%: If down that means you need to check SORT_AREA_SIZE, HASH_AREA_SIZE or PGA_AGGREGATE_TARGET.

- % Non-Parse CPU: Re-parsing is high on CPU and should be avoided.

Shared Pool Statistics:

- Memory Usage %:It is the shared pool usage. If it is too high then your SGA size is small and if it is too low that means your SGA is too big. Good range is 65% to 85%.

Top 5 Timed Foreground Events:Critical place to look first in AWR report

File Type Wait Events:

- DB File Sequential Read:Non selective index use. Rebuild indexes

- Db File scattered Read: Caused due to full table scan or missing indexes. Create indexes.

Buffer Type Wait Events:

- Free Buffer Waits: Insufficient buffers, process holding buffers too long. Check if DBWR may be getting clogged up.

- Buffer Busy Waits: Most of the time its data block related

- GC Buffer Busy(RAC): Caused because of not enough memory on your nodes

Log Type Wait Events:

- Log buffer space: Look at increasing log buffer size

- Log File Sync: Could indicate excessive commits

GC Wait events:

- GC CR Multi Block Request: Full table or index scans

- GC CR Current Block Busy: Shows block congestion. Increase network bandwidth

- GC CR Block Lost: Indicates interconnect issues

Undo Events:

- Undo Segment Extension:If excessive, Tune undo.

- Wait for undo record: Look at tuning off parallel undo recovery.