Question: I did not pay the fee
for the Oracle diagnostic pack and I would like to uninstall
AWR and use STATSPACK. How do I un-install
and/or disable AWR?
Answer: AWR is an extra-cost
package pre-installed into Oracle and AWR consists of
several components, a schema that holds the AWR tables and
indexes and scheduled jobs that populates the tables
periodically and purge older data. To use AWR you must
purchase the Oracle Diagnostic Pack and Oracle Performance
Pack. Otherwise, it is a good idea to disable or
remove the AWR, since even querying a DBA_HIST_* AWR table
can make an entry in
dba_features_usage_statistics.
Also see my notes on
disabling AWR automatic data purging.
Rampant author Bert Scalzo offers these ways to remove
the various AWR components from your database.
11g and onwards: Disable
performance and diagnostic packs with
control_management_pack_access
From 11g and beyond, the
control_management_pack_access parameter allows you to
control which diagnostic and tuning packs are licensed and
used. This parameter can be set as one of three values:
NONE - Neither pack is licensed or used.
DIAGNOSTIC - Only the DIAGNOSTIC pack is available.
DIAGNOSTIC+TUNING - Both packs are available. This is the
default value!
To disable AWR via access to the packs, set
control_management_pack_access=none;
Disable all of AWR:
Download
MOSC script dbms_awr.plb, compile this package, then execute
the PL/SQL package dbms_awr.disable_awr() [see MOSC note
436386.1]
Disable AWR data colection:
Set your init.ora parameter
STATISTICS_LEVEL = BASIC
Disable AWR snapshot
collections:
Execute the Oracle provided PL/SQL
package:
dbms_workload_repository.modify_snapshot_settings(interval=>0).
You can use OEM: Main Menu->Scheduler->Jobs, select the data
grid row for GATHER_STATS_JOB, choose the disable drop-down
action, then
finally press OK.
Remove AWR collection job:
Execute the Oracle provided PL/SQL
package: dbms_scheduler.disable('GATHER_STATS_JOB').
You can use OEM: Main Menu->Workload->Automatic Workload
Repository, select the "Edit" button and then select the
last radio group item labeled: Turn off Snapshot Collection,
finally press OK.
