转:Reading Oracle AWR report sections
<p class="MsoNormal"><font face="Times New Roman" size="6">Reading OracleAWR report sections</font><font face="Times New Roman" size="6" color="#000000"><br>
</font><i><font size="2"><br>
Oracle Tips by Burleson Consulting</font></i></p>
<p class="Heading1"><i><font size="4">See here for
<a href="http://www.dba-oracle.com/t_reading_statspack_report.htm" target="_blank" target="_blank">
more notes on reading a STATSPACK/AWR report</a>. Also try
<a href="http://www.statspackanalyser.com/" target="_blank" target="_blank">www.statspackanalyser.com</a>
for a sample AWR analysis.</font></i></p>
<p class="Heading1"><b><font size="4">Reading the AWR Report</font></b></p>
<p class="BodyText">This section contains detailed guidance for
evaluating each section of an AWR report. An AWR report is very
similar to the STATSPACK report from Oracle9i, and it contains vital
elapsed-time information on what happened during particular snapshot
range. The data in an AWR or STATSPACK report is the delta, or
changes, between the accumulated metrics within each snapshot.</p>
<p class="BodyText">The main sections in an AWR report include:</p>
<font size="1">
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Report Summary</span></font></b></span><b><font color="#0000F0"><span style="font-size: 12.0pt">:</span></font></b><span style="font-size: 12.0pt">
This gives an overall summary of the instance during the snapshot
period, and it contains important aggregate summary information.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Cache Sizes (end):</span></font></b></span><span style="font-size: 12.0pt">
This shows the size of each SGA region after AMM has changed them.
This information can be compared to the original
<span class="BodyTextItalicsChar1Char">init.ora</span></span><span class="BodyTextItalicsChar1Char"></span><span style="font-size: 12.0pt">
parameters
at the end of the AWR report.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Load Profile:</span></font></b></span><span style="font-size: 12.0pt">
This <b><font color="#F00000">important section</font></b> shows important rates expressed in units of
per second and transactions per second.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Instance Efficiency Percentages:</span></font></b></span><span style="font-size: 12.0pt">
With a target of 100%, these are high-level ratios for activity in
the SGA.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Shared Pool Statistics:</span></font></b></span><span style="font-size: 12.0pt">
This is a good summary of changes to the shared pool during the
snapshot period.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Top 5 Timed Events:</span></font></b></span><span style="font-size: 12.0pt">
This is <b><font color="#F00000">the most important section</font></b> in the AWR report. It shows the
top wait events and can quickly show the overall database
bottleneck.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Wait Events Statistics Section:</span></font></b></span><span style="font-size: 12.0pt">
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. </span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Wait Events:</span></font></b></span><span style="font-size: 12.0pt">
This AWR report section provides more detailed wait event info</span><span style="font-size: 12.0pt">rmation
for foreground user processes which includes Top 5 wait events and
many other wait events that occurred during the snapshot interval.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Background Wait Events:</span></font></b></span><span style="font-size: 12.0pt">
This section is relevant to the background process wait events.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Time Model Statistics:</span></font></b></span><span style="font-size: 12.0pt">
Time mode statistics report how database-processing time is spent.
This section contains detailed timing information on particular
components participating in database processing.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Operating System Statistics:</span></font></b></span><span style="font-size: 12.0pt">
The stress on the Oracle server is important, and this section shows
the main external resources including I/O, CPU, memory, and network
usage. </span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Service Statistics:</span></font></b></span><span style="font-size: 12.0pt">
The service statistics section gives information about how
particular services configured in the database are operating.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">SQL Section:</span></font></b></span><span style="font-size: 12.0pt">
This section displays top SQL, ordered by important SQL execution
metrics.</span></p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span><span class="TextListHeaderChar">
<span style="font-size: 13.0pt">SQL Ordered by Elapsed Time:</span></span>
Includes SQL statements that took significant execution time during
processing. </p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span><span class="TextListHeaderChar">
<span style="font-size: 13.0pt">SQL Ordered by CPU Time:</span></span>
Includes SQL statements that consumed significant CPU time during
its processing. </p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span><span class="TextListHeaderChar">
<span style="font-size: 13.0pt">SQL Ordered by Gets:</span></span>
These SQLs performed a high number of logical reads while retrieving
data.</p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span><span class="TextListHeaderChar">
<span style="font-size: 13.0pt">SQL Ordered by Reads:</span></span>
These SQLs performed a high number of physical disk reads while
retrieving data.</p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span><span class="TextListHeaderChar">
<span style="font-size: 13.0pt">SQL Ordered by Parse Calls:</span></span>
These SQLs experienced a high number of reparsing operations.</p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span><span class="TextListHeaderChar">
<span style="font-size: 13.0pt">SQL Ordered by Sharable Memory:</span></span>
Includes SQL statements cursors which consumed a large amount of SGA
shared pool memory.</p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span><span class="TextListHeaderChar">
<span style="font-size: 13.0pt">SQL Ordered by Version Count:</span></span>
These SQLs have a large number of versions in shared pool for some
reason.</p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Instance Activity Stats:</span></font></b></span><span style="font-size: 12.0pt">
This section contains statistical information describing how the
database operated during the snapshot period.</span></p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span><span class="TextListHeaderChar">
<span style="font-size: 13.0pt">Instance Activity Stats</span></span>
(Absolute Values): This section contains statistics that have
absolute values not derived from end and start snapshots.</p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span><span class="TextListHeaderChar">
<span style="font-size: 13.0pt">Instance Activity Stats</span></span>
(Thread Activity): This report section reports a log switch activity
statistic.</p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">I/O Section</span></font></b></span><b><font color="#0000F0"><span style="font-size: 12.0pt">:</span></font></b><span style="font-size: 12.0pt">
This section shows the all important I/O activity for the instance
and shows I/O activity by tablespace, data file, and includes buffer
pool statistics.</span></p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>Tablespace IO Stats </p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>File IO Stats </p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>Buffer Pool Statistics </p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Advisory Section</span></font></b></span><b><font color="#0000F0"><span style="font-size: 12.0pt">:</span></font></b><span style="font-size: 12.0pt">
This section show details of the advisories for the buffer, shared
pool, PGA and Java pool.</span></p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>Buffer Pool Advisory </p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>PGA Aggr Summary: PGA Aggr Target Stats; PGA Aggr
Target Histogram; and PGA Memory Advisory. </p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>Shared Pool Advisory </p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>Java Pool Advisory </p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Buffer Wait Statistics:</span></font></b></span><span style="font-size: 12.0pt">
This <b><font color="#F00000">important section</font></b> shows buffer cache waits statistics.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Enqueue Activity:</span></font></b></span><span style="font-size: 12.0pt"><b><font color="#0000F0"> </font></b>
This <b><font color="#F00000">important section</font></b> shows how enqueue operates in the database.
Enqueues are special internal structures which provide concurrent
access to various database resources.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Undo Segment Summary:</span></font></b></span><span style="font-size: 12.0pt">
This section gives a summary about how undo segments are used by the
database.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Undo Segment Stats:</span></font></b></span><span style="font-size: 12.0pt">
This section shows detailed history information about undo segment
activity.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Latch Activity:</span></font></b></span><span style="font-size: 12.0pt">
This section shows details about latch statistics. Latches are a
lightweight serialization mechanism that is used to single-thread
access to internal Oracle structures.</span></p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>Latch Sleep Breakdown</p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>Latch Miss Sources</p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>Parent Latch Statistics</p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span>Child Latch Statistics</p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Segment Section:</span></font></b></span><span style="font-size: 12.0pt">
This report section provides details about hot segments using the
following criteria:</span></p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span><span class="TextListHeaderChar">
<span style="font-size: 13.0pt">Segments by Logical Reads:</span></span>
Includes top segments which experienced high number of logical
reads. </p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span><span class="TextListHeaderChar">
<span style="font-size: 13.0pt">Segments by Physical Reads:</span></span>
Includes top segments which experienced high number of disk physical
reads. </p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span><span class="TextListHeaderChar">
<span style="font-size: 13.0pt">Segments by Buffer Busy Waits:</span></span>
These segments have the largest number of buffer waits caused by
their data blocks.</p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span><span class="TextListHeaderChar">
<span style="font-size: 13.0pt">Segments by Row Lock Waits:</span></span>
Includes segments that had a large number of row locks on their
data.</p>
<p class="BulletsIndented" style="text-indent: -.25in; margin-left: .65in">
<span style="font-style: normal; font-variant: normal; font-weight: normal; font-size: 7.0pt; font-family: Times New Roman">
</span><span class="TextListHeaderChar">
<span style="font-size: 13.0pt">Segments by ITL Waits:</span></span>
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.</p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Dictionary Cache Stats:</span></font></b></span><span style="font-size: 12.0pt">
This section exposes details about how the data dictionary cache is
operating.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">Library Cache Activity:</span></font></b></span><span style="font-size: 12.0pt">
Includes library cache statistics describing how shared library
objects are managed by Oracle.</span></p>
<p class="TextList"><span class="TextListHeaderChar">
<b><font color="#0000F0"><span style="font-size: 12.0pt">SGA Memory Summary:</span></font></b></span><span style="font-size: 12.0pt"><b><font color="#0000F0"> </font></b>
This section provides summary information about various SGA regions.</span></p>
<p class="TextList"><span class="TextListHeaderChar"><i>
<b><font color="#0000F0"><span style="font-size: 12.0pt">init.ora</span></font></b></i><b><font color="#0000F0"><span style="font-size: 12.0pt">
Parameters</span></font></b></span><b><font color="#0000F0"><span style="font-size: 12.0pt">:</span></font></b><span style="font-size: 12.0pt">
This section shows the original <i>init.ora</i> </span>
<span class="BodyTextItalicsChar1Char">
<span style="font-size: 12.0pt"> </span></span><span style="font-size: 12.0pt">parameters for the instance during
the snapshot period.</span></p></font>
页:
[1]