INSIDE THE AWR REPORT: HOW TO ANALYZE : Part 1.
1.
Fundamentals Concepts
A
Database operation is one or more SQL or PL/SQL statements executed by one or
more sessions. Those operations can generate a lot of problems related to the
performance. Problems can arise at any time. A proactive DBA watches for
problems and corrects them before they are noticed by users. To quickly
identify the problem the DBA will:
-
Monitor the
current state of the database instance and compare it to the previous state
-
Examine AWR or
Statpack reports and instance files carefully.
In
this article, we are talking about AWR.
From
10g, Oracle Database automatically collects and stores workload information in
the Automatic Workload Repository (AWR). The AWR can be used to identify:
-
SQLs or Modules
with heavy loads or potential performances issues
-
Symptoms of
those heavy loads (e.g. Logical I/O (buffer gets), Physical I/O, Contention,
Waits)
-
SQLs that could
be using sub-optimal execution plans (e.g. buffer gets, segments statistics
-
Numbers of
executions
-
Parsing issues
-
General
performance issues (e.g. System capacity (I/O, Memory, CPU), System/Database
Configuration)
-
SGA (Shared
pool/buffer cache) and PGA sizing advice.
AWR
stores and reports statistics for each snapshot (identified by snapshot IDs).
This is the minimum granularity of time. An AWR report can only report the
total of each statistic between to completed snapshots. So, the choice of
snapshot is critical.
2.
More about AWR
2.1.
What means AWR?
AWR:
Automatic Workload Repository is a collection of persistent system performance
statistics on Oracle Database, owner by SYS user and resides on SYSAUX
tablespace. AWR is the infrastructure that provides services to Oracle Database
components to collect, maintain and use statistics for problem detection and
self-tuning purposes.
The
AWR infrastructure consists of two major parts: An In-memory statistics
collection facility and AWR snapshots.
-
An In-memory
statistic is used to collect statistics. These statistics are stored in memory
for performance reasons. Statistics stored in memory are accessible through
dynamic performance views V$.
-
AWR snapshots
represent the persistent portion of the facility, that are accessible through
data dictionary DBA_ views and Enterprise Manager. By default, snapshots are
generated on every 60 minutes and maintained for 7 days. Each snapshot has a
unique ID known as “snap_id”.
Statistics
are stored in persistent storage for several reasons:
-
The statistics
need to survive instance crashes
-
Historical data
for base line comparisons is needed for certain types of analysis
-
When old
statistics are replaced by the new ones due to memory shortage, the replaced
data can be stored for later use.
AWR
is not used for real-time performance monitoring like the V$ tables. It is used
for historical analysis of performance. AWR complements, but does not replace
real-time monitoring.
2.2.
Workload Repository
As mentioned earlier, the Workload repository is a
collection of persistent system performance statistics. A snapshot is a set of
performance statistics captured at certain time and used for computing the rate
of change of a statistic. By default, snapshots are generated every 60 minutes.
You can adjust this frequency by changing the snapshot interval parameter.
In RAC (Real Application Cluster) environment, each
snapshot spans all nodes in a cluster and snapshots for data in each node share
the same snap_id, differentiated by their instance IDs. You can also take a
manual snapshots by using Enterprise Manager; taking manual snaphots are
expected to be used when you want to capture the system behavior at two
specific point not time that do not coincide with the automatic schedule.
The ASH (Active Session History) data is captured
first to memory at one-second intervals for on those sessions that are
currently active. Then the ASH data is reduced by a factor of the storing to
disk a random sample of the in-memory data. The ASH data is heavily used by
ADDM (Automatic Database Diagnostic Monitor) to identify root causes of
performance issues.
The advisor reports produced by ADDM, the segment
advisor and other advisors are also stored in AWR for later viewing.
Fig.2 : AWR Repository
2.3.
AWR Snapshot Settings
These
should be decided before capturing and using AWR data. The Snapshot interval,
retention and the number od SQLs captured for each “Top” criteria can be
controlled by using the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS
procedure. This has 4 arguments: Interval, Retention, TopnSQL and DBid. If NULL
value is specified for retention interval or topnsql, the existing values are
preserved.
Automatic
Statistics gathering by AWR is disable if the STATISTICS_LEVEL initialization
parameter is set to BASIC. If STATISTICS_LEVEL=BASIC, AWR statistics can still
captured manually. However, as the collection of many statistics (in-memory)
will be disabled, The AWR reports will omit a lot of information such as
segments statistics or memory advice.
2.4.
Baselines
AWR
baselines are a series of snapshots that are retained for comparison with
subsequent snapshots. The subsequent snapshots may be when performance problems
have occurred, or they may be used to compare changes in workload. They can be
created by using the DBMS_WORKLOAD_REPOSITORY.create_baseline procedure. They
are retained indefinitely and not purged from the AWR.
3.
How to generate AWR Report
An
AWR report can be generated from SQL*Plus by running the AWR SQL scripts stored
under $ORACLE_HOME/rdbms/admin, or through Oracle Enterprise Manager Database
Control or Cloud Control.
3.1.
Using SQL*Plus scripts
The
main scripts available are:
-
awrrpt.sql: Produces the AWR
report for the current (local) database and instance.
-
awrrpti.sql: Produces the AWR
report for a specified database and instance.
-
awrgrpt.sql: (11g Release 2
and above) - Produces the Global AWR report for all available instances in an
Oracle Real Application Clusters (RAC) environment. It does this for the
current database.
-
awrgrpti.sql: (11g Release 2
and above) - Produces the Global AWR report for available instances in an
Oracle Real Application Clusters (RAC) environment. It does this for a
specified database and instances (either one instance, a comma delimited list
of some instances or all instances can be chosen).
-
spawrrac.sql: (10g to 11g
Release 1) - This is a global AWR report (in text format) for all instances in
an Oracle Real Application Clusters (RAC) environment. It does this for a
specified database.
.All
the scripts are interactive. The below arguments must be provided:
-
Report Type (html or
text)
The HTML format is recommended. It is
clearer, easier to read and has complete SQL statements (they are not
truncated).
-
Database and Instance
If running for a specified instance (e.g.
awrrpti.sql) then the script will list the "Instances in this Workload
Repository schema" and prompt for the database id (dbid) and instance
number (inst_num). The current (local) instance will be highlighted with an
asterix (*) at the left hand side of the row. Choose an instance. This should
be the database and instance on which the performance issue has been observed.
If running for specified instances on a RAC environment (e.g. awrgrpti.sql)
then the instance can be a comma separated list of instance numbers or
"ALL".
-
Begin and End Snapshots
It is advisable to think about the
snapshots required before running these scripts. It is usually best to produce
AWR reports for every snapshot during the period of the performance issue. If a
performance issue is long running (straddles more than 2 snapshots) then it is
very useful to produce a report for the whole period of the performance issue,
program or process. Ideally for snapshots covering the period from just before
the program/process starts until just after program/process completes. If a
series of successive programs or processes is being reported on (e.g. Upgrade)
then produce a separate report for the period that each successive program is
running (if possible).
-
Report Name
Specify
the report name or enter null for it to default to
awrrpt_<instance_number>_<begin_snap>_<end_snap>.html
or
awrrpt_rac_<begin_snap>_<end_snap>.html
For the RAC reports
(awrrptg.sql/awrrptgi.sql).
E.G
:
SQL>
@awrgrpt.sql
Specify the
Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports
can be generated in the following formats.
Please enter the
name of the
format at the prompt. Default value is
'html'.
'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report
Enter value
for report_type:
old 1: select 'Type Specified:
',lower(nvl('&&report_type','html')) report_type from dual
new 1: select 'Type Specified:
',lower(nvl('','html')) report_type from dual
Type
Specified: html
old 1: select '&&report_type'
report_type_def from dual
new 1: select 'html' report_type_def from dual
old 1: select '&&view_loc' view_loc_def
from dual
new 1: select 'AWR_PDB' view_loc_def from dual
Current
Database
~~~~~~~~~~~~~~~~
DB Id DB Name Container Name
--------------
-------------- --------------
3622587404
ISLDB isldb
Instances
in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id
Inst Num DB Name Instance Host
------------
---------- --------- ---------- ------
*
3622587404 1 ISLDB isldb1 entdbrac08.m
*
3622587404 2 ISLDB isldb2 entdbrac09.m
Using
3622587404 for database Id
Using
instances ALL (default 'ALL')
Specify the
number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering
the number of days (n) will result in the most recent
(n) days of
snapshots being listed. Pressing <return>
without
specifying
a number lists all completed snapshots.
Enter value
for num_days: 1
Listing the
last day's Completed Snapshots
DB
Name Snap Id Snap Started Snap Level
------------ ----------
------------------ ----------
ISLDB 1936
14 Dec 2017 00:00 1
1937 14 Dec 2017 01:00 1
1938 14 Dec 2017 02:00 1
1939 14 Dec 2017 03:00 1
1940 14 Dec 2017 04:00 1
1941 14 Dec 2017 05:00 1
1942 14 Dec 2017 06:00 1
1943 14 Dec 2017 07:00 1
1944 14 Dec 2017 08:00 1
1945 14 Dec 2017 09:00 1
1946 14 Dec 2017 10:00 1
1947 14 Dec 2017 11:00 1
1948 14 Dec 2017 12:00 1
1949 14 Dec 2017 13:00 1
1950 14 Dec 2017 14:00 1
1951 14 Dec 2017 15:00 1
Specify the
Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value
for begin_snap: 1945
Begin
Snapshot Id specified: 1945
Enter value
for end_snap: 1946
End Snapshot Id specified: 1946
Specify the
Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default
report file name is awrrpt_rac_1945_1946.html.
To use this name,
press
<return> to continue, otherwise enter an alternative.
Enter value
for report_name:
Using the
report name awrrpt_rac_1945_1946.html
.....
.....
.....
End of
Report
</body></html>
Report written
to awrrpt_rac_1945_1946.html
SQL>
When the script is completed, you can open the AWR
report with your Internet Navigator.
3.2.
Using Enterprise Manager Grid Control
-
On Performance
Page, Go to AWR --> AWR Report :
-
Choose the Begin
and End Snaphot and then click on Generate Report Button.
4.
Tips before analyze AWR Report
4.1.
Collect multiple AWR Reports
It’s always suggested to have a least two AWR reports,
one for good time (when database was performing well), second when performance
is poor. This way we can easily compare good and bad report to find out the
culprit.
4.2.
Stick to Particular time
Always take the report during the time when the
Database is performing slow. We have to have a specific tike like Database was
slow today at 10AM and continued till 02PM. Here, we can get a report for these
four hours.
4.3.
Split Large AWR Report into smaller reports
Instead of having one report for a long time like one
report for 4 hours, it’s better to have four reports each for one hour. This
will help to isolate the problem.
4.4.
A few Knowledges
-
What is DB Time)
DB
Time is amount of elapsed time spent performing database user-level calls. DB
Time is a total time in Database calls by foreground sessions. It includes CPU
time, I/O time and non-idle wait time. Total DB Time is a sum of DB time for
all active sessions. DB Time is different to Response Time. DB Time is measured
cumulatively from the time that the instance was started.
-
What is DB CPU
DB
CPU is amount of CPU time spent on database user-level calls. This time
includes processes on the run queue. Maximum
-
Sequence load elapsed time
It’s
the amount of elapsed time spent getting the next sequence number from the data
dictionary. If a sequence is cached, this is the amount of time spend
replenishing the cache when it runs out. No time if the sequence number is
found in the cache.
-
Parse Time elapsed
It’s
the amount elapsed time spent parsing SQL statement. It includes both soft and
hard parse time.
-
Hard Parse elapsed Time
It’s
the amount elapsed time spent hard-parsing SQL statement.
-
SQL execute elapsed Time
It’s
the amount elapsed time SQL statements are executing. If it’s the SELECT
statement, this amount also includes the amount of time spent performing
fetched of query results
-
Connection Management call elapsed time
It’s
the amount elapsed time spent performing session connect and disconnect calls.
-
Failed parse elapsed time
It’s
the amount of time spent performing SQL parses that ultimately fail with some
parse error.
-
Hard parse (sharing criteria) elapsed time
It’s
the amount of time spent performing SQL hard parses when the hard parse
resulted from not being able to share an existing cursor in the SQL cache.
-
Hard parse (bind mismatch) elapsed time
It’s
the amount of time spent performing SQL hard parses when the hard parse
resulted from bind type or bind mismatch with an existing cursor in the cache.
-
PL/SQL execution elapsed time
It’s
the amount of time spent running the PL/SQL interpreter.
-
PL/SQL compilation elapsed time
It’s
the amount of time spent running the PL/SQL compiler.
-
Inbound
PL/SQL RPC elapsed time
Time
inbound PL/SQL remote procedure calls (RPCs) have spent executing. It includes
all time spent recursively executing SQL and Java, and therefore, is not easily
related to PL/SQL execution elapsed time.
-
Java
execution elapsed time
Amount
of elapsed time spent running the Java VM. This does not include time spent
recursively executing or parsing SQL statements or time spent recursively
executing PL/SQL.
-
Repeated
bind elapsed time
Elapsed
time spent on rebinding
-
Background
CPU time
Amount
of CPU time (in microseconds) consumed by database background processes
-
Background
elapsed time
Total
time spent in the database by background sessions (CPU time and non-idle wait
time)
-
RMAN CPU
time (backup/restore)
CPU
time spent by RMAN backup and restore operations
