Tuesday, September 27, 2011

How to read AWR reports

To generate AWR, read my post

http://bash-dba.blogspot.com/search/label/AWR?updated-max=2011-09-27T12%3A36%3A00-04%3A00&max-results=20

OK let start analyzing AWR

This is the example of AWR report. I have tried here to discuss the finding of sample AWR report I have generated.


Report Header

This section is self explanatory which provides database name, id, instance if RAC , platform information and snap interval. (database workload time duration in review).
This report is for instance number 2 of my RAC environment. So if you need to do the analysis on RAC environment, you need to do it separately of all the instances in the RAC to see if all the instances are balanced the way they should be.



DB NameDB IdInstanceInst numStartup TimeReleaseRAC
TestRAC3626203793TestRac2217-Aug-11 19:0811.1.0.6.0YES


Host NamePlatformCPUsCoresSocketsMemory (GB)
TestRACLinux 64-bit for AMD88231.44



Snap IdSnap TimeSessionsCursors/Session
Begin Snap:2856627-Sep-11 01:00:211304.8
End Snap:2856727-Sep-11 02:00:431354.5
Elapsed:
60.35 (mins)

DB Time:
15.07 (mins)




BeginEnd

Buffer Cache:5,888M5,888MStd Block Size:8K
Shared Pool Size:8,704M8,704MLog Buffer:138,328K



Load Profile
This section provides the snapshot of the database workload occurred during the snapshot interval.



Per SecondPer TransactionPer ExecPer Call
DB Time(s):0.30.10.000.00
DB CPU(s):0.30.10.000.00
Redo size:48,933.619,916.2

Logical reads:1,124.4457.7

Block changes:195.979.7

Physical reads:80.532.8

Physical writes:4.31.8

User calls:141.457.6

Parses:123.250.2

Hard parses:2.20.9

W/A MB processed:1,940,807.0789,918.9

Logons:4.31.7

Executes:127.651.9

Rollbacks:0.00.0

Transactions:2.5






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 above 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:
Number 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

Instance Efficiency Percentages (Target 100%)

Instance efficiency should be close to 100 %

Buffer Nowait %:99.99Redo NoWait %:100.00
Buffer Hit %:93.06In-memory Sort %:100.00
Library Hit %:98.67Soft Parse %:98.20
Execute to Parse %:3.40Latch Hit %:99.98
Parse CPU to Parse Elapsd %:0.01% Non-Parse CPU:96.21

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.

Shared Pool Statistics




BeginEnd
Memory Usage %:73.8675.42
% SQL with executions>1:92.6193.44
% Memory for SQL w/exec>1:94.3394.98




Memory Usage % is the shared pool usage. So here we have use 73.86 per cent of our shared pool and out of that almost 94 percent is being re-used. if Memory Usage % is too large like 90 % it could mean that your shared pool is tool small and if the percent is in 50 for example then this could mean that you shared pool is too large


Top 5 Timed Foreground Events


EventWaitsTime(s)Avg wait (ms)% DB timeWait Class
DB CPU
1,019
112.73
log file sync25,6424324.73Commit
db file scattered read3,06440134.43User I/O
library cache pin136,2672702.98Concurrency
db file sequential read7,6082432.71User I/O


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



  • buffer type waits

so what's going on in your memory
latch: cache buffer chains:
check hot objects
free buffer waits:
insufficient buffers, process holding buffers too long or i/o subsystem is over loaded. Also check you db writes may be getting clogged up.
buffer busy waits:
see what is causing them further along in report. most of the time its data block related.
gc buffer busy:
its in the RAC environment. caused may be because of not enough memory on your nodes,overloaded interconnect. Also look RAC specific section of the report latch:
cache buffers lru chain – Freelist issues, hot blocks latch: cache buffer handles – Freelist issues, hot blocks
buffer busy - See what is causing them further along in report
no free buffers – Insufficient buffers, dbwr contention


  • Log Type Waits
log file parallel write – Look for log file contention
log buffer space – Look at increasing log buffer size
log file switch (checkpoint incomplete) – May indicate excessive db files or slow IO subsystem
log file switch (archiving needed) – Indicates archive files are written too slowly
log file switch completion – May need more log files per
log file sync – Could indicate excessive commits


  • GC Events
gccr multi block request – Full table or index scans
gc current multi block request – Full table or index scans
gccr block 2-way – Blocks are busy in another instance, check for block level contention or hot blocks
gccr block 3-way – Blocks are busy in another instance, check for block level contention or hot blocks
gccr block busy – Blocks are busy in another instance, check for block level contention or hot blocks
gccr block congested – cr block congestion, check for hot blocks or busy interconnect
gccr block lost – Indicates interconnect issues and contention
gc current block 2-way – Blocks are busy in another instance, check for block level contention or hot blocks
gc current block 3-way – Blocks are busy in another instance, check for block level contention or hot blocks
gc current block busy – Block is already involved in GC operation, shows hot blocks or congestion
gc current block congested – current block congestion, check for hot blocks or busy interconnect
gc current block lost - Indicates interconnect issues and contention


  • Undo Events
undo segment extension – If excessive, tune undo
latch: In memory undo latch – If excessive could be bug, check for your version, may have to turn off in memory undo
wait for a undo record – Usually only during recovery of large transactions, look at turning off parallel undo recovery.
  • What Next?
Determine wait events of concern
Drill down to specific sections of report for deeper analysis
Use custom scripts, ADDM and Ash to investigate issues


15 comments:

  1. very interesting site......here are the steps to generate AWR report may be useful to the readers:

    http://chandu208.blogspot.com/2011/04/steps-to-generate-awr-report.html

    ReplyDelete
  2. Here are the Steps-to-generate AWR report :

    http://chandu208.blogspot.com/2011/04/steps-to-generate-awr-report.html

    ReplyDelete
  3. Good artical! Please post a deeper analysis if possbible.

    ReplyDelete
  4. Very well explained, I have also written 10 important points in analyzing AWR report http://www.dbas-oracle.com/2013/05/10-steps-to-analyze-awr-report-in-oracle.html

    ReplyDelete
  5. Bilal and Umesh Good Work

    Thanks

    ReplyDelete
  6. Nice post... Thanks

    ReplyDelete
  7. previously i am confusing the analaysis of AWR report. now i got some clarity on this.. thank nice article..

    ReplyDelete
    Replies
    1. hi,

      please provide the steps to analyze the awr report.tell me some tips.

      Delete
  8. Good post!

    Rajesh Gheware
    http://rajeshg.info

    ReplyDelete