INSIDE THE AWR REPORT, HOW TO ANALYZE Part 1 updated.

 

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.

Oracle have provided many performances gathering and reporting tools over the years. Oracle8i introduced the Statspack functionality which Oracle9i extended. 

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 report is a comparison report of two snapshots taken at the different timestamps. 

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, maintained for 7 days and then purged using snapshot FIFO (First In First Out) algorithm. 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.

Fig1: AWR Overview

 

2.2. AWR Snapshots and Statistics

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.

Fig.2: AWR Repository

Gathering Database statistics using AWR is enabled using the STATISTICS_LEVEL parameter.

This parameter should be set to TYPICAL (By default) or ALL. Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:

·        Automatic Workload Repository (AWR) Snapshots

·        Automatic Database Diagnostic Monitor (ADDM)

·        All server-generated alerts

·        …

·        …

When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.

2.3. Snapshots management

As mentioned earlier, Snapshots are sets of historical data for specific time periods that are used for performance comparisons by ADDM. By default, Oracle Database automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 8 days. 

 

Creating Snapshots:

To manually create snapshots in order to capture statistics at times different than those of the automatically generated snapshots. Use the below script

 

 BEGIN

  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();

END;

 

Query the below SQL and go at the end to view the newly created snapshot

 

 

SQL> select * from DBA_HIST_SNAPSHOT

 

 

Dropping Snapshots:

To view a list of the snapshot Ids along with database Ids, check the DBA_HIST_SNAPSHOT view

 

BEGIN

  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 21196, high_snap_id => 21199, dbid => 1545380912);

END;

 

Modify Snapshots Interval:

We have noticed the default snapshot interval is one hour (60 minutes) but sometimes you will need to reduce it according to your database workload.

Before reducing it, you need to check the current interval using the below script:

 

SQL> select extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_interval,extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_interval from dba_hist_wr_control;

 

SNAPSHOT_INTERVAL RETENTION_INTERVAL

----------------- ------------------

               60              11520

SQL>

 

Then modify the snapshot interval

 

SQL> exec dbms_workload_repository.modify_snapshot_settings(retention=>43200, interval=>15, dbid=> 1545380912);

 

PL/SQL procedure successfully completed.

 

SQL> select extract(day from snap_interval) *24*60+extract(hour from snap_interval) *60+extract(minute from snap_interval) snapshot_interval,extract(day from retention) *24*60+extract(hour from retention) *60+extract(minute from retention) retention_interval from dba_hist_wr_control;

 

SNAPSHOT_INTERVAL RETENTION_INTERVAL

----------------- ------------------

               15              43200

 

 

 

 

2.4. AWR Baselines

A baseline contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.

 

Creating a Baseline:

Based on the snapshots we created above, we will create the baseline.

·        Get the snap id to create the baseline on using DBA_HIST_SNAPSHOT

·        Create the base line as below 

 

BEGIN
DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 21196,end_snap_id => 21199, baseline_name => high baseline',dbid => 1545380912);
END;

·        View the baseline create using the script below

SQL> select dbid,baseline_id,baseline_name from DBA_HIST_BASELINE;

 

Renaming a Baseline:

·        Review the existing baselines in the DBA_HIST_BASELINE view as mentioned above to determine the baseline that you want to rename.

·        Use the script below the rename the baseline

 

BEGIN 

 DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (old_baseline_name => 'high baseline', new_baseline_name => 'low peak baseline', dbid => 1545380912);

END;

 

Dropping the Baseline:

BEGIN

  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'low peak baseline', cascade => FALSE, dbid => 1545380912);

END;

 

 

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

As mentioned, there are a lot of AWR related scripts stored in $ORACLE_HOME/rdbms/admin directory. I will be focused to the most important ones.

Note: You need a valid diagnostic pack license before you can use those scripts.

 

I will group the scripts according to their field application.

 

 AWR Performance Report Scripts

 

Script	Description
awrrpt.sql	AWR reports for current single instance database
awrrpti.sql	AWR report for another dbid or instance (even RAC)
awrgrpt.sql	AWR Global report for current RAC (from 11gR2 and above)
awrgrpti.sql	AWR Global report for RAC another dbid, another instance (from 11gR2 and above)

 

 

AWR Performance Report Comparison Scripts

 

Script	Description
awrddrpt.sql	AWR DateDiff report for current single instance database
awrddrpi.sql	AWR DateDiff report for another dbid, another instance
awrgdrpt.sql	AWR dateDiff report for Global RAC View on current RAC
awrgdrpi.sql	AWR dateDiff report for Global RAC View for another dbid, another instance

 

AWR SQL Report Scripts

Script	Description
awrsqrpt.sql	AWR SQL statement report for current single instance database
awrsqrpi.sql	AWR SQL statement report for another dbid or instance

 

 

 

 

 

 

 

 

 

AWR Data Movement Scripts

Script	Description
awrextr.sql	export awr data using datapump
awrload.sql	import awr data using datapump

 

 

AWR Additional Scripts

Script	Description
awrinfo.sql	general AWR information
perfhubrpt.sql	Performance Hub Active Report
awrinpnm.sql	AWR input name
awrinput.sql	Get inputs for AWR reports
awrddinp.sql	Get inputs for AWR diff report (awrddrpi.sql)
awrgdinp.sql	Get inputs for AWR  global diff reports (awrgdrpi.sql)

 

 

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

 

Current Database

~~~~~~~~~~~~~~~~

 

   DB Id    DB Name

----------- ------------

  158881526 ORCLDB

 

 

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:

 

Type Specified:  html

 

 

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

   DB Id      INSTT_NUM DB Name      INSTT_NAME       Host

------------ ---------- ------------ -----------     ---------------------------

* 158881526         1      ORCLDB       ORCLDB1        dbserver.localdomain.com

* 158881526         2      ORCLDB      ORCLDB2        dbserver.localdomain.com

* 158881526         3      ORCLDB      ORCLDB3        dbserver.localdomain.com

 

Using  158881526 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

 

                                           Snap

DB Name        Snap Id    Snap Started    Level

------------ --------- ------------------ -----

ORCLDB          39352 30 Jun 2021 10:41      1

                 39353 30 Jun 2021 12:00      1

                 39354 30 Jun 2021 13:00      1

                 39355 30 Jun 2021 14:00      1

                 39356 30 Jun 2021 15:00      1

                 39357 30 Jun 2021 16:00      1

                 39358 30 Jun 2021 17:00      1

                 39359 30 Jun 2021 18:00      1

                 39360 30 Jun 2021 19:00      1

                 39361 30 Jun 2021 20:00      1

                 39362 30 Jun 2021 21:00      1

 

 

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 39352

Begin Snapshot Id specified: 39352

 

Enter value for end_snap: 39354

End   Snapshot Id specified: 39354

 

 

 

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_rac_39352_39354.html.  To use this name,

press <return> to continue, otherwise enter an alternative.

 

Enter value for report_name:

 

Using the report name awrrpt_rac_39352_39354.html

…

…

…

</pre>

<br /><a class="awr" href="#top">Back to Top</a><p />

<p />

<p />

<p />

<p />

<p />

End of Report

</body></html>

Report written to awrrpt_rac_39352_39354.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 analyzing 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

 

NEXT