INSIDE THE AWR REPORT : Part 1

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