Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 11.2.0.2 - Release: 10.1 to 11.2
Information in this document applies to any platform.
Goal:
Understanding various sections of AWR and meaning of each of them , to help narrowing the problem.
Solution:
AWR report is broken into multiple parts.
1)Instance information:
This provides information the instance name , number,snapshot ids,total time the report was taken for and the database time during this elapsed time.
Elapsed time= end snapshot time - start snapshot time
Database time= Work done by database during this much elapsed time( CPU and I/o both add to Database time).If this is lesser than the elapsed time by a great margin, then database is idle.Database time does not include time spend by the background processes.
2)Cache Sizes: This shows the size of each SGA region after AMM has changed them. This information
can be compared to the original init.ora parameters at the end of the AWR report.
3)Load Profile: This important section shows important rates expressed in units of per second and transactions per second.This is very important for understanding how is the instance behaving.This has to be compared to base line report to understand the expected load on the machine and the delta during bad times.
4)Instance Efficiency Percentages(Target 100%): This section talks about how close are the vital ratios like buffer cache hit, library cache hit,parses etc.These can be taken as indicators ,but should not be a cause of worry if they are low.As the ratios cold be low or high based in database activities, and not due to real performance problem.Hence these are not stand alone statistics, should be read for a high level view .
5)Shared Pool Statistics: This summarizes changes to the shared pool during the snapshot
period.
6)Top 5 Timed Events:This is the section which is most relevant for analysis.This section shows what % of database time was the wait event seen for.Till 9i, this was the way to backtrack what was the total database time for the report , as there was no Database time column in 9i.
7)RAC Statistics:This part is seen only incase of cluster instance.This provides important indication on the average time take for block transfer, block receiving , messages ., which can point to performance problems in the Cluster instead of database.
8)Wait Class : This Depicts which wait class was the area of contention and where we need to focus.Was that network, concurrency, cluster, i/o Application, configuration etc.
9)Wait Events Statistics Section: This section shows a breakdown of the main wait events in the database including foreground and background database wait events as well as time model, operating system, service, and wait classes statistics.
10)Wait Events: This AWR report section provides more detailed wait event information for foreground user processes which includes Top 5 wait events and many other wait events that occurred during the snapshot interval.
11)Background Wait Events: This section is relevant to the background process wait events.
12)Time Model Statistics: Time mode statistics report how database-processing time is spent. This section contains detailed timing information on particular components participating in database processing.This gives information about background process timing also which is not included in database time.
13)Operating System Statistics: This section is important from OS server contention point of view.This section shows the main external resources including I/O, CPU, memory, and network usage.
14)Service Statistics: The service statistics section gives information services and their load in terms of CPU seconds, i/o seconds, number of buffer reads etc.
15)SQL Section: This section displays top SQL, ordered by important SQL execution metrics.
a)SQL Ordered by Elapsed Time: Includes SQL statements that took significant execution
time during processing.
b)SQL Ordered by CPU Time: Includes SQL statements that consumed significant CPU time
during its processing.
c)SQL Ordered by Gets: These SQLs performed a high number of logical reads while
retrieving data.
d)SQL Ordered by Reads: These SQLs performed a high number of physical disk reads while
retrieving data.
e)SQL Ordered by Parse Calls: These SQLs experienced a high number of reparsing operations.
f)SQL Ordered by Sharable Memory: Includes SQL statements cursors which consumed a large
amount of SGA shared pool memory.
g)SQL Ordered by Version Count: These SQLs have a large number of versions in shared pool
for some reason.
16)Instance Activity Stats: This section contains statistical information describing how the database operated during the snapshot period.
17)I/O Section: This section shows the all important I/O activity.This provides time it took to make 1 i/o say Av Rd(ms), and i/o per second say Av Rd/s.This should be compared to the baseline to see if the rate of i/o has always been like this or there is a diversion now.
18)Advisory Section: This section show details of the advisories for the buffer, shared pool, PGA and Java pool.
19)Buffer Wait Statistics: This important section shows buffer cache waits statistics.
20)Enqueue Activity: This important section shows how enqueue operates in the database. Enqueues are special internal structures which provide concurrent access to various database resources.
21)Undo Segment Summary: This section gives a summary about how undo segments are used by the database. Undo Segment Stats: This section shows detailed history information about undo segment activity.
22)Latch Activity: This section shows details about latch statistics. Latches are a lightweight
serialization mechanism that is used to single-thread access to internal Oracle structures.The latch should be checked by its sleeps.The sleepiest Latch is the latch that is under contention , and not the latch with high requests.Hence run through the sleep breakdown part of this section to arrive at the latch under highest contention.
23)Segment Section: This portion is important to make a guess in which segment and which segment type the contention could be.Tally this with the top 5 wait events.
Segments by Logical Reads: Includes top segments which experienced high number of
logical reads.
Segments by Physical Reads: Includes top segments which experienced high number of disk
physical reads.
Segments by Buffer Busy Waits: These segments have the largest number of buffer waits
caused by their data blocks.
Segments by Row Lock Waits: Includes segments that had a large number of row locks on
their data.
Segments by ITL Waits: Includes segments that had a large contention for Interested
Transaction List (ITL). The contention for ITL can be reduced by increasing INITRANS storage
parameter of the table.
24)Dictionary Cache Stats: This section exposes details about how the data dictionary cache is
operating.
25)Library Cache Activity: Includes library cache statistics which are needed in case you see library cache in top 5 wait events.You might want to see if the reload/invalidations are causing the contention or there is some other issue with library cache.
26)SGA Memory Summary:This would tell us the difference in the respective pools at the start and end of report.This could be an indicator of setting minimum value for each, when sga)target is being used..
27)init.ora Parameters: This section shows the original init.ora parameters for the instance during the snapshot period.
There would be more Sections in case of RAC setups to provide details.
