In this Document
Goal
Solution
This document is being delivered to you via Oracle Support's
Rapid Visibility (RaV)
process, and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.4
Information in this document applies to any platform.
Goal
To briefly explain on how to interpret the AWR report.
This Note shows you some basic checks to be done from AWR to identify the problem.
Older Database versions like 8i,9i have Statspack report only.
From 10g onwards, AWR is available along with Statspack.
Solution
Automatic Workload Repository (AWR) : The Automatic Workload
Repository (AWR) provides information to different manageabilities
components. AWR consists of two components: in-memory statistics
accessible through V$ dynamic views, and AWR snapshots saved in the
database that represent the persistent and historical portion.
AWR snapshots can be generated at will using the following
syntax:
EXECUTE dbms_workload_repository.create_snapshot();
By default in 10g, the AWR snapshots are generated automatically
on hourly basis.
If you are facing any performance problem in the database and you have license for AWR,
then AWR reports
can be generated for the problem period.
If there is no proper license for AWR available then statspack report can be generated.
The AWR/Statspack report should be taken for the interval not more than 60 minutes during
problem.
Please dont
take AWR / Statspack report for duration of like five or six as that would not
be reliable.
The AWR report can be taken in both html/text format.
The first thing to be checked in AWR report is the following:-
Snap IdSnap Time Sessions Cursors/Session
Begin Snap: 112 11-Jun-09 00:00:57 191 6.7
End Snap: 11311-Jun-09 01:00:11 1737.4
Elapsed: 59.23 (mins)
DB Time: 710.73 (mins)
Check the "DB Time" metric. If it is wayoff with the elapsed time, then it indicates that
the sessions are waiting for something.
Here in this example, the Elapsed Time is around 60 minutes while the DB Time is around
700 minutes. This means that 700 minutes of time is spent by the sessions on waiting.
Next thing to be looked is the following:-
InstanceEfficiencyPercentages(Target100%)
BufferNowait%: 100.00 RedoNoWait%: 100.00
BufferHit%: 98.67 In-memorySort%: 100.00
LibraryHit%: 98.60 SoftParse%: 99.69
ExecutetoParse%: 5.26 LatchHit%: 99.31
ParseCPUtoParseElapsd%:12.78 %Non-ParseCPU: 99.10
As per the thumb rule, Instance Efficieny Percentages should be ideally above 90%.
Then comes the Shared Pool Statistics.
SharedPoolStatistics
Begin End
MemoryUsage%:85.49 80.93
%SQLwithexecutions>1: 42.46 82.96
%MemoryforSQLw/exec>1: 47.7781.03
The memory usage statistics of shared pool is shown.
Idealy this should be lesser. If it is very high like beyond 90, this shows the contention
in the shared pool.
Next thing to be looked after is the Top 5 Timed Events table.
This shows the most significant waits contributing to the DB Time.
Top5TimedEvents
Event WaitsTime(s)AvgWait(ms)%TotalCallTimeWaitClass
dbfilesequentialread4,076,08628,532 7 66.9UserI/O
CPUtime11,21426.3
Backup:sbtbackup44,3981,099,45210.3Administrative
logfilesync37,3652,421655.7Commit
logfileparallelwrite37,9281,371363.2SystemI/O
Here, the significant wait is the db file sequential read which contributes to 67% of DB Time.
Commonly, most of the non-problematic databases would have "CPU Time" wait as the Top.
Then , SQL Statistics can be checked.
SQLStatistics
SQLorderedbyElapsedTime
SQLorderedbyCPUTime
SQLorderedbyGets
SQLorderedbyReads
SQL Statistics section would have commonly the above four sections.
Each section shows the list of SQLs based on the
order of the respective metric.
For example, SQL ordered by Elapsed Time section shows the list of SQLs in the order
of
the Elapsed Time. High resource consuming SQLs can be spotted out and meant for
tuning.
Note: All the above four sections
of SQL Statistics show the list of SQLs in descending order.
i.e, For ex: Highest elapsed time is shown as first.
Then
comes the IO Stats section.
This shows the IO Statistics for each tablespaces in the database.
As the thumb rule, the Av Rd(ms) [Average Reads in milliseconds] should not cross beyond 30,
which is considered to be IO bottleneck.
TablespaceIOStats
orderedbyIOs(Reads+Writes)desc
Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av
Buf Wt(ms)
TEMP 3,316,082 933 4.91 1.00 28,840 8 0 0.00
DAT1520,120 146 16.06 1.21 185,846 52 902 13.00
DAT3 93,411 26 42.82 2.98 13,442 4 16 23.13
DAT2 98,171 28 91.97 7.97 5,333 2 325 34.89
In the above example, the Av Rd(ms) is high in all tablespaces indicating the IO contention.
Then , Advisory Statistics can be checked.
This section shows the following:-
BufferPoolAdvisory
PGAAggrSummary
PGAAggrTargetStats
PGAAggrTargetHistogram
PGAMemoryAdvisory
SharedPoolAdvisory
SGATargetAdvisory
StreamsPoolAdvisory
JavaPoolAdvisory
It is very commonly used to check the advisories for the most important SGA structures like
shared pool, buffer cache etc and PGA.
Then finally, init.ora Parameters is shown which shows the list of parameters set at instance
level.
init.oraParameters
All the above said sections except the DB Time can be checked from Statspack report also.
The statspack snapshots are
not generated automatically as in AWR.
It has to be generated during the problem period as follows:-
Take 2 snapshots between 60 minutes interval during the problem and generate the statspack report
exec statspack.snap
wait for 60 minutes
exec statspack.snap
Please run $ORACLE_HOME/rdbms/admin/spreport.sql
and specify BEGIN and END ID's of the snapshots taken during the problem.
The above said sections are the most common checks can be performed from user level.
Further intensive checking can be done through Oracle Support.
Help us improve our service. Please email us your comments for this document.
Goal
Solution
This document is being delivered to you via Oracle Support's
Rapid Visibility (RaV)
process, and therefore has not been subject to an independent technical review.
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.4
Information in this document applies to any platform.
Goal
To briefly explain on how to interpret the AWR report.
This Note shows you some basic checks to be done from AWR to identify the problem.
Older Database versions like 8i,9i have Statspack report only.
From 10g onwards, AWR is available along with Statspack.
Solution
Automatic Workload Repository (AWR) : The Automatic Workload
Repository (AWR) provides information to different manageabilities
components. AWR consists of two components: in-memory statistics
accessible through V$ dynamic views, and AWR snapshots saved in the
database that represent the persistent and historical portion.
AWR snapshots can be generated at will using the following
syntax:
EXECUTE dbms_workload_repository.create_snapshot();
By default in 10g, the AWR snapshots are generated automatically
on hourly basis.
If you are facing any performance problem in the database and you have license for AWR,
then AWR reports
can be generated for the problem period.
If there is no proper license for AWR available then statspack report can be generated.
The AWR/Statspack report should be taken for the interval not more than 60 minutes during
problem.
Please dont
take AWR / Statspack report for duration of like five or six as that would not
be reliable.
The AWR report can be taken in both html/text format.
The first thing to be checked in AWR report is the following:-
Snap IdSnap Time Sessions Cursors/Session
Begin Snap: 112 11-Jun-09 00:00:57 191 6.7
End Snap: 11311-Jun-09 01:00:11 1737.4
Elapsed: 59.23 (mins)
DB Time: 710.73 (mins)
Check the "DB Time" metric. If it is wayoff with the elapsed time, then it indicates that
the sessions are waiting for something.
Here in this example, the Elapsed Time is around 60 minutes while the DB Time is around
700 minutes. This means that 700 minutes of time is spent by the sessions on waiting.
Next thing to be looked is the following:-
InstanceEfficiencyPercentages(Target100%)
BufferNowait%: 100.00 RedoNoWait%: 100.00
BufferHit%: 98.67 In-memorySort%: 100.00
LibraryHit%: 98.60 SoftParse%: 99.69
ExecutetoParse%: 5.26 LatchHit%: 99.31
ParseCPUtoParseElapsd%:12.78 %Non-ParseCPU: 99.10
As per the thumb rule, Instance Efficieny Percentages should be ideally above 90%.
Then comes the Shared Pool Statistics.
SharedPoolStatistics
Begin End
MemoryUsage%:85.49 80.93
%SQLwithexecutions>1: 42.46 82.96
%MemoryforSQLw/exec>1: 47.7781.03
The memory usage statistics of shared pool is shown.
Idealy this should be lesser. If it is very high like beyond 90, this shows the contention
in the shared pool.
Next thing to be looked after is the Top 5 Timed Events table.
This shows the most significant waits contributing to the DB Time.
Top5TimedEvents
Event WaitsTime(s)AvgWait(ms)%TotalCallTimeWaitClass
dbfilesequentialread4,076,08628,532 7 66.9UserI/O
CPUtime11,21426.3
Backup:sbtbackup44,3981,099,45210.3Administrative
logfilesync37,3652,421655.7Commit
logfileparallelwrite37,9281,371363.2SystemI/O
Here, the significant wait is the db file sequential read which contributes to 67% of DB Time.
Commonly, most of the non-problematic databases would have "CPU Time" wait as the Top.
Then , SQL Statistics can be checked.
SQLStatistics
SQLorderedbyElapsedTime
SQLorderedbyCPUTime
SQLorderedbyGets
SQLorderedbyReads
SQL Statistics section would have commonly the above four sections.
Each section shows the list of SQLs based on the
order of the respective metric.
For example, SQL ordered by Elapsed Time section shows the list of SQLs in the order
of
the Elapsed Time. High resource consuming SQLs can be spotted out and meant for
tuning.
Note: All the above four sections
of SQL Statistics show the list of SQLs in descending order.
i.e, For ex: Highest elapsed time is shown as first.
Then
comes the IO Stats section.
This shows the IO Statistics for each tablespaces in the database.
As the thumb rule, the Av Rd(ms) [Average Reads in milliseconds] should not cross beyond 30,
which is considered to be IO bottleneck.
TablespaceIOStats
orderedbyIOs(Reads+Writes)desc
Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av
Buf Wt(ms)
TEMP 3,316,082 933 4.91 1.00 28,840 8 0 0.00
DAT1520,120 146 16.06 1.21 185,846 52 902 13.00
DAT3 93,411 26 42.82 2.98 13,442 4 16 23.13
DAT2 98,171 28 91.97 7.97 5,333 2 325 34.89
In the above example, the Av Rd(ms) is high in all tablespaces indicating the IO contention.
Then , Advisory Statistics can be checked.
This section shows the following:-
BufferPoolAdvisory
PGAAggrSummary
PGAAggrTargetStats
PGAAggrTargetHistogram
PGAMemoryAdvisory
SharedPoolAdvisory
SGATargetAdvisory
StreamsPoolAdvisory
JavaPoolAdvisory
It is very commonly used to check the advisories for the most important SGA structures like
shared pool, buffer cache etc and PGA.
Then finally, init.ora Parameters is shown which shows the list of parameters set at instance
level.
init.oraParameters
All the above said sections except the DB Time can be checked from Statspack report also.
The statspack snapshots are
not generated automatically as in AWR.
It has to be generated during the problem period as follows:-
Take 2 snapshots between 60 minutes interval during the problem and generate the statspack report
exec statspack.snap
wait for 60 minutes
exec statspack.snap
Please run $ORACLE_HOME/rdbms/admin/spreport.sql
and specify BEGIN and END ID's of the snapshots taken during the problem.
The above said sections are the most common checks can be performed from user level.
Further intensive checking can be done through Oracle Support.
Help us improve our service. Please email us your comments for this document.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11780477/viewspace-705098/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11780477/viewspace-705098/