AWR Reports

Overview

Ideally, dbcollect collects Oracle AWR reports in HTML format as HTML can be processed (parsed) reliably. It can also generate Statspack reports. This is how dbcollect decides which reports to produce.

Short answer

If you are licensed for Diagnostics Pack but you get error E021 (No AWR or Statspack detected), use the --license-ok option:

# Generate AWR reports even if AWR usage is not detected:
dbcollect --license-ok

Use the --license-ok flag ONLY if you are sure you are correctly licensed!

Diagnostics Pack

By default, dbcollect tries to generate AWR Reports for each detected and running database instance on the host.

However, creating AWR reports require the Oracle Diagnostics Pack license. (see Oracle doc id 1490798.1).

This page explains how dbcollect deals with this requirement.

Diagnostics Pack is a licensed option on top of Oracle Database Enterprise Edition. It is required to run AWR reports. You can run AWR reports without the license, however, this will be registered in the database and is a licensing violation that can have consequences when customers are audited. Therefore, dbcollect has some safeguards against accidental usage.

Detecting Diagnostics Pack license

It is impossible to detect contract details using automated tools on a host. Therefore, dbcollect checks for previous usage of AWR Reports instead. See Tracking Database Feature Usage for a detailed explanation and example script.

Currently - the detection is per database - so it is possible to have one database running AWR reports without problems and another one not due to missing AWR usage. This is intentional - a database without prior usage could later be migrated to another host that is not properly licensed and therefore cause issues.

If prior AWR Usage is detected, then either the system is licensed with Diagnostics Pack, or (worst case scenario) the system is in license violation anyway. In both cases, dbcollect will proceed creating AWR reports (unless disabled via CLI parameters)

How detection works

  1. Check if AWR Reports is a feature that has been used before (by checking dba_feature_usage_statistics). If so, use AWR reports
  2. If AWR usage is not detected but the --license-ok flag has been specified, use AWR reports
  3. If AWR usage is not detected and no --license-ok flag is specified, check if STATSPACK is available. If so, use Statspack
  4. If AWR usage is not detected, no --license-ok has been specified and no statspack reports are available, abort with an error

No prior AWR usage

If no prior AWR usage is detected, then dbcollect will NOT automatically generate AWR reports, but issue an error instead, unless the --license-ok flag is used.

AWR performance

Sometimes, generating AWR reports can be very slow. This article lists some potential remedies.

Solution 1

Reference: Oracle Support Note 2929370.1

alter session set "_push_join_predicate"=false;
-- OR
alter session set "_optimizer_push_pred_cost_based" = false;

Solution 2

Reference: Oracle Support Note 2148489.1

EXEC DBMS_STATS.DELETE_SYSTEM_STATS('NOWORKLOAD');
EXEC DBMS_STATS.GATHER_SYSTEM_STATS(GATHERING_MODE => 'NOWORKLOAD');

Solution 3

Reference: Oracle Support Note 2148489.1

Clean up old AWR snapshots as per the support note. Then run the next SQL snippet

CONNECT / AS SYSDBA
ALTER TABLE WRH$_SEG_STAT_OBJ MOVE;
ALTER INDEX WRH$_SEG_STAT_OBJ_PK REBUILD;
ALTER INDEX WRH$_SEG_STAT_OBJ_INDEX REBUILD;
4) Gather Dictionary and System Statistics:
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
--To gather the fixed objects stats, use the following:
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;