Understand each field of AWR [ID 884046.1]

Source: Internet
Author: User

Understand each field of AWR [ID 884046.1]




In this document


Oracle Server-Enterprise Edition-version: to 10.1 to 11.2
Information in this document applies to any platform.


Understanding various sections of AWR and meaning of each of them, to help narrowing the problem.


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 shocould 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, shocould be read for a high level view.

5) Shared Pool statistics: This summarizes changes to the Shared Pool During the snapshot

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. 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 caching ing, 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
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 threads des top 5 wait events and other wait events that occurred
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 participating components in Database
Processing. This gives information about background process Timing also which is not supported 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: Between des SQL statements that took significant execution
Time during processing.

B) SQL ordered by CPU time: des SQL statements that consumed significant CPU time
During its processing.

C) SQL ordered by gets: These sqls generated med a high number of logical reads while
Retrieving data.

D) SQL ordered by reads: These sqls saved med a high number of physical disk reads while
Retrieving data.

E) SQL ordered by parse cballs: 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 shoshould 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 shoshould 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 cocould be. Tally this with the top 5 wait events.

Segments by logical reads: includes top segments which experienced high number
Logical reads.

Segments by physical reads: Primary des top segments which experienced high number of disks
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 partitioned ced by increasing initrans Storage
Parameter of the table.

24) dictionary cache stats: This section exposes details about how the data dictionary cache is

25) Library cache activity: supported des 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 wowould tell us the difference in the respective pools at the start and end of report. this cocould 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
The Snapshot period.

There wocould be more sections in case of RAC setups to provide details.




Blog: http://blog.csdn.net/tianlesoftware

Online Resources: http://tianlesoftware.download.csdn.net

Video: http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx

Dba1 group: 62697716 (full); dba2 group: 62697977 (full)

Dba3 group: 62697850 super DBA group: 63306533;

Chat group: 40132017

-- Add the group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, the application is rejected.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.