Newsletter
DBConcepts Logo Original

Oracle Automatic Workload Repository (AWR) – Part 1: Getting Started

Introduction to Oracle Automatic Workload Respository

In 2003 when Oracle Database 10.1 was released Oracle added Automatic Manageability Features for the first time.

Database administrators and internal database advisor tools need a lot of solid data to answer performance-related questions. Until then only external tools were available to record the data of an “Oracle black-box”

The Automatic Workload Repository (AWR) is built-in to every Oracle database collecting data automatically and inside the database.

Database engine and AWR share the same base-data. It will be used by the database itself even if you are not using it. It is just there.

Having such an in-depth access to the database using AWR is a good choice to give in-depth information of what is going on inside your database.

The easiest way to investigate performance-related issues is by using graphical representations of data. You should use tools like Oracle Grid Control or Oracle Enterprise Manager Console (EM Express) to analyse the data. It is just a few clicks and you are done.

However, sometimes you need advanced functionality, portability or might have no access to these web-interfaces.

This is the right time to take a closer look at the AWR Scripts which can be found in each Oracle Home.

Oracle AWR Licensing

Before I am showing you more details, we need to make sure you are not committing a license break. Even your database uses this data internally you are might not be allowed to use/query it too.

AWR is such a great feature built into the database you are only allowed to use it if you own “Diagnostic Pack” which is only available for “Enterprise Edition “.

The limitation even prohibits the usage of various sql-scripts like ORACLE_HOME/rdbms/admin/awr*.sql

Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/dblic/Licensing-Information.html#GUID-68A4128C-4F52-4441-8BC0-A66F5B3EEC35

 

Are you allowed to use Automatic Workload Repository?

You cannot tell it for sure without knowing exactly what Licenses were bought. Ask your company’s license administrator if Enterprise Edition and Diagnostic Pack was bought?

CAUTION: It is technically possible to install and use all options even you are not allowed to do so.

However, there are some small clues when you may not use it (do not use it for sure)

 

Your installed database edition is not Enterprise Edition

It is easy for unwary dbas to install enterprise edition even if the company paid for Standard Edition only. If you see your edition is something different than Enterprise Edition, it was very likely installed on purpose.

Screenshot 2023 06 21 at 12 52 11 Oracle Automatic Workload Repository AWR Part1 Getting Started DBConcepts

Parameter: control_management_pack_access is disabled

This Parameter was invented in 11g to users from causing an accidental unwanted license break. An all non-Enterprise Editions this Parameter is set to NONE by default.

Screenshot 2023 06 21 at 12 52 20 Oracle Automatic Workload Repository AWR Part1 Getting Started DBConcepts
If “use maybe ok” ask your license administrator for your safety. Just because if it is configured to work it might be a license break.
 
Pitfall: The value defaults to DIAGNOSTIC+TUNING on Enterprise Edition after Installation even if you have not bought this additional license.
Screenshot 2023 06 21 at 12 52 31 Oracle Automatic Workload Repository AWR Part1 Getting Started DBConcepts
Screenshot 2023 06 21 at 12 52 40 Oracle Automatic Workload Repository AWR Part1 Getting Started DBConcepts

Pitfall: In 10g it was even easier to install the wrong edition and there is no parameter control_management_pack_access. Be careful using it there.

Oracle Statspack

If you cannot afford to buy Diagnostic Pack I strongly recommend configuring and install the free and dusty alternative STATSPACK (I will write about this next)

Sure, it is not a full replacement to AWR but when “we are adjusting the sails” we need to know the winds direction, don’t we? Statspacks runs on instance level (I will explain this later) and still works on 19c, RAC and PDB.

If you miss the nice graphics you must write you own data-parser, use excel or Toad > DBAdmin > Statspack Browser

 

Gaphical User interface (CloudControl & EM Express)

Since the first AWR tools were invented it is possible to use a limited set of tasks over a Graphical User Interface.

Oracle Enterprise Manger Express is a very limited management homepage running inside your database. The very limited options you are seeing here will be restricted further depending on database version and licenses. In general, it is more a nice dashboard than administration interface. It is just a start page linking to Performance Hub Active Report (perfhubrpt.sql). If you need usable performance reports, you should better switch to CloudControl or AWR Scripts.

Oracle EM Express Start PAge

Image: EM Express: Performance Menu Options in 12c

Oracle EM Express

Performance Menu Options

Image EM Express: Performance Menu Options in 19c

EM Express Performance Menu Options in 19c

Yes, it is just one option here.

All those features available in 12c EM Express are still available in 19c but the quick links are remove. You can reach them implicitly when investigation a single issue or sql-statement.

 

Image: EM Express: Performance > Performance Hub

EM Express Performance Hub

Performance Hub > This is the only performance related view you can get on EM Express. It looks very similar to Performance Hub Active Report (perfhubrpt.sql) but you have to option to „move“ the timeframe slider and switch to „monitored SQL“ with just a click.

Performance Hub > Activity – See history of sessions limited to local instance. Depending on configuration you will see almost all sessions causing load on database.

Performance Hub > Monitored SQL – Use it to look at the current running database and see some performance numbers. You may even investigate SQL issues a little.

 

CloudControl

The CloudControl should be used by enterprise customers who want to monitor and store more than one database. If you own the licenses you get more clickable options than in EM Express.

You get links to a lot of tools, advisors and configuration wizards. There is no need to switch to SQLPlus or command line for the most tasks.

Image: CloudControl: Performance Menu Options > Performance Home

 

Oracle CloudControl Performance Menu Options

Performance-Home – default dashboard to see what is going on. Same as on EM Express but gives you cluster-wide views.

Performance Hub – Same as on EM Express. You could even get it on command line as shown in: Performance Hub Active Report (perfhubrpt.sql) All tools here help you to narrow down issues fast without really digging too much into details. Most of the time one single statement is causing the problem.

Cluster Cache Coherency – Cluster related performance indicators as tabular or graphical-view (still needs flash)

SQL – a lot of SQL related tools and options (see next screenshot)

AWR – some Automatic Workload Repository related tasks (see next screenshot)

Advisors Home – quick link to database advisors. Suggesting possible solutions to past issues found using AWR or ASH

Real-Time ADDM – Automatic Database Diagnostic Monitor. Suggests possible solutions to tasks currently running.

Search Sessions – find a specific active session

Blocking Sessions – analyse blocking tree and kill blocking sessions

Image: CloudControl: Performance Menu Options > Performance Hub

Oracle CloudControl Performance Menu Performance Hub

Performance Hub > ActiveSessionHistory Analytics – is called Activity in EM Express to see history of sessions. This page even works on cluster-wide databases not just on single instances. Depending on configuration you will see almost all sessions causing load on database.

Performance Hub > SQL Monitoring – dig into the performance of a specific sql statement done in the past. The option is named Monitored SQL in EM Express

Image: CloudControl: Performance Menu Options > SQL

CloudControl Performance Menu Options 1

SQL > SQL Tuning Advisor – SQL optimisation suggestions

SQL > SQL Performance Analyzer (SPA) Home – analyse change impacts (whatif)

SQL > SQL Performance Analyzer (SPA) Quick Check Setup – ad hoc check

SQL > SQL Access Advisor – data access plans

SQL > SQL Tuning Sets – for SQL tuning advisor

SQL > SQL Plan Control – SQL Profiles and SQL Plan Baselines (execution hints)

SQL > Optimizer Statistics – Statistics gathering auto tasks

SQL > Cloud Control SQL History – find top statements, enable tracing

SQL > Search SQL – find SQL by various conditions in cursor cache, AWR snapshots, AWR baselines, SQL tuning sets (STS)

SQL > Run SQL – run a single SQL-statement as you would do from SQLDeveloper

SQL > SQL Worksheet – like „run SQL“ but with some execution options

 

The SQL related tools are a lot more than you can get from EM Express. Everything you need to handle SQLs using Diagnostic and Tuning Pack.

The SQL related tools are a lot more than you can get from EM Express. Everything you need to handle SQLs using Diagnostic and Tuning Pack.

Image: CloudControl: Performance Menu Options: AWR

Oracle CloudControl Performance Menu Options AWR

AWR Reports – Automatic Workload Repository Reports. See AWR Scripts for example outputs.

AWR Configuration – Automatic Workload Repository Reports Configuration. I will do this on command line in Configuration

AWR Report Comparison – Compare Reports of different times, instances and/or databases. See AWR Scripts for example outputs.

All AWR tasks besides AWR-Warehouse can be accomplished on command line too. I will cover the AWR part later in AWR Scripts section.

The CloudControl has a lot more options than EM Express but you must use AWR Scripts if you:

… cannot access the EM Express / CloudControl homepage

… need more options than available in EM Express

… need to store AWR data externally

… want to compare AWR reports of different databases / instances

Configuration

If you continue with these steps, you should better already know if you are allowed by license to do so.

The Automatic Workload Repository is installed and available since Oracle 10.1. It comes preconfigured with best-practice-defaults and will be used by the database internally. This configuration is enough to get “some data” for the AWR reports I am showing you later in this article. I suggest doing small adjustments to before using it extensively.

 

Time and Space matters

By default, the so-called database “snapshots” are made every hour. It might be even a good idea to increase the interval to every 15 minutes to sharpen the focus. The snapshots are stored in the AWR for 8 days by default after which they are automatically purged.

Oracle estimates its space for an average single instance snapshotting every 1 hour and keeping it for 8 days round about 300MB. Use this as minimum. I have seen bigger values in practice.

Example: AWR Size of a bigger database

Screenshot 2023 06 21 at 12 54 30 Oracle Automatic Workload Repository AWR Part1 Getting Started DBConcepts
Columns explained
 
DBID – On a newly installed database the dbid of this database should be the only dbid.
 
Tip: If you see more than one dbid someone imported AWR data from another database or this database was renamed using the dbnewid utility.
 
SNAP_INTERVAL – interval of snapshots. means “snapshot creation runs every … ” format: +<days> <hours>:<minutes>:<seconds>.<milliseconds> default: 1 hour recommended: 15 minutes
 
Tip: We recommend an interval not bigger than 15 minutes to avoid too much aggregation.  If ranges are too big you cannot focus on important tasks.
 
RETENTION – preserve time of snapshots. means “delete snapshots after … ” format: +<days> <hours>:<minutes>:<seconds>.<milliseconds>. default: 8 days recommended: 7 days – 90 days
 
Tip: We recommend a retention of at least 7 days because that is typically time range when performance problems will be reported and investigated. In rare cases it might be needed to keep them even longer for performance forensics.
 
TOPNSQL – Number of top SQLs which metadata will be stored. We leave it at default. minimum: 30 maximum: 50000
 
Tip: Setting the value to 30 is good in most cases. You should focus on the top-scorer and fix them first. A higher value will need more space and performance.

modify AWR snapshot settings

It is possible to change these settings using CloudControl > Performance > AWR > AWR Configuration or you can modify the AWR settings using the Package DBMS_WORKLOAD_REPOSITORY like this:

Example: modify snapshot settings using dbms_workload_repository

Screenshot 2023 06 21 at 12 54 41 Oracle Automatic Workload Repository AWR Part1 Getting Started DBConcepts

A snapshot is a picture of a specific point-in-time. You get a lot of data which helps you to see what is going on, but you will not get everything.

Tip: We recommend setting the interval not smaller than 15 minutes. In most cases this is a good size. Lower intervals take up more performance and space.

Limit snapshot scope

The Parameter STATISTICS_LEVEL defines the granularity and scope of data to be collected on each snapshot.

Possible values are:

Table: possible statistics_level values

Screenshot 2023 06 21 at 12 54 50 Oracle Automatic Workload Repository AWR Part1 Getting Started DBConcepts

Example: show configured statistics_level parameter

Screenshot 2023 06 21 at 12 54 59 Oracle Automatic Workload Repository AWR Part1 Getting Started DBConcepts

Tip: We recommend leaving this parameter on its default value TYPICAL.

Example: change parameter statistics_level

Screenshot 2023 06 21 at 12 55 07 Oracle Automatic Workload Repository AWR Part1 Getting Started DBConcepts

dba_hist_snapshot (list AWR snapshots)

This is the base-table which contains the overview-information of all available snapshots. You will see a similar output later when using the AWR scripts.

Pitfall: All queries o DBA_HIST_* tables require Enterprise Edition and Diagnostic Pack License. Do not query if you do not own it.

 

Example: dba_hist_snapshot output

Screenshot 2023 06 21 at 12 55 17 Oracle Automatic Workload Repository AWR Part1 Getting Started DBConcepts

Columns explained

SNAP_LEVEL – Parameter STATISTICS_LEVEL when snap was taken. 1 = TYPICAL 2 = ALL

Tip: Do not mix it up with STATSPACK levels (1-19)

ERROR_COUNT – this is new in 19c. Reporting will still work with minor glitches or missing information.

SNAP_FLAG – this in new in 19c. It shows how data was collected.

Create AWR snapshot

If you cannot wait until the snapshots create themselves, you may create them manually:

Screenshot 2023 06 21 at 12 55 28 Oracle Automatic Workload Repository AWR Part1 Getting Started DBConcepts

DBConcepts

Weitere Beiträge

AOUG – Anwenderkonferenz 2024

Mit dem Motto „Driving Transformation“ findet dieses Jahr im Juni 2024 im Tech Gate (Donau-City-Straße 9, 1220 Wien) die Anwenderkonferenz der Austrian Oracle User Group

DBConcepts

Newsletter abonnieren

Wir freuen uns, dass wir Ihr Interesse für den Newsletter geweckt haben! Mit dem Versand dieser Zustimmung erhalten Sie regelmäßig alle aktuellen Informationen!

Vielen Dank für Ihr Interesse an unserem Unternehmen. Derzeit suchen wir niemanden für diese Stelle. Aber wir sind immer an talentierten Menschen interessiert und freuen uns von Ihnen zu hören! Schicken Sie uns einfach Ihren Lebenslauf und eine kurze Nachricht und schreiben Sie an welcher Stelle Sie interessiert sind: recruitment@dbconcepts.com. Wir freuen usn von Ihnen zu hören!