Linna Kozlovsky

25. August 2020 from Christian Weingerl

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.

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

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)

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.

Example: Installed Database Version is not Enterprise Edition

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.

Table: database edition and control_management_pack_access

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.

Example: control_management_pack_access does not contain the word DIAGNOSTIC

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

Within the last years I saw DBAs using these hidden parameters to disable the usage of AWR in 10g.

Example: hidden_parameters in 10g to disable AWR

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.

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

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.

Image: EM Express: Start Page

Image: EM Express: Performance Menu Options in 12c

Image: 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

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.

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

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

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

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.

Image: 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.

AWR-Warehouse – Setup Wizard to get AWR-Warehouse up and running. This is useful if you want to store AWR data outside the database for analysis purpose.

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

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.

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

Depending on your workload this can cause a lot of data being stored in SYS Schema in SYSAUX tablespace. There might be needs to store performance-data even longer. It helps to prove statements like “it was faster 6 month ago “. High workload, smaller snapshot intervals and longer retentions can lead to a huge amount of data which might strain your database.

A good configuration is needed to get the information you need without let your database fill-up with performance-data.

This table displays the current AWR configuration.

Example: dba_hist_wr_control content

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: + ::. 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: + ::.. 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.

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

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.

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

Example: show configured statistics_level parameter

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

Example: change parameter statistics_level

Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/STATISTICS_LEVEL.html#GUID-16B23F95-8644-407A-A6C8-E85CADFA61FF

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

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.

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

If you notice that your dog has started with signs of an upset stomach, remove all food and water to allow his gastrointestinal tract to calm down If he

TAKE DEEP BREATHS : NHS says, "Take a deep breath to briefly shut down the ejaculatory reflex (an automatic reflex of the body during which ejaculation occurs)" This will eventually delay the process

Amounts do not accumulate toward the calendar year benefit maximum. There is a 12-month waiting period to get these services, unless you had prior coverage. Please contact Member Services at (888) 271-4880 for more information on how to get an exception. There is a three-month waiting period to get these services, unless you had prior coverage. Please contact Member Services at (888) 271-4880 for more information on how to get an exception. If precious metals are used, the member will be charged at the dentist's cost.

In the case of Dental HMO, porcelain on molar teeth has an additional charge of $75. Preventive and diagnostic services do not apply to this plan's calendar year benefit maximum. There is a six-month waiting period for these services, unless you had prior coverage.