Question:
How do I disable AWR and disable the Oracle tuning pack in
Oracle?
Answer: (courtesy of Bert Scalzo) I
want to elaborate that last bummer issue one more time just
to make sure you saw it. If you query the DBA_HIST_* data
dictionary views, you better have purchased the OEM
Diagnostic pack! For those of you who prefer to directly
access the SYS data dictionary tables - that means don't
even select from tables with names like WRM$*, WRH$* or WRI$*!
The tuning pack screens cannot be disable from OEM, but you
can disable the underlying data structures.
So assuming that you prefer to disable AWR so as not to
accidentally (or purposefully) violate your Oracle licensing
agreement, here are some ways to disable AWR for a given
database (you'll need to do one of these to every database
you manage):
Many Ways to Disable AWR:
- Download Meta-Link script dbms_awr.plb, compile this
package, then execute the PL/SQL package
dbms_awr.disable_awr() [see metalink note
436386.1].
- Set your init.ora parameter STATISTICS_LEVEL = BASIC
- Execute the Oracle provided PL/SQL package:
dbms_workload_repository.modify_snapshot_settings(interval=>0)
Execute the Oracle provided PL/SQL package:
dbms_scheduler.disable('GATHER_STATS_JOB')
- 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
- OEM Main Menu->Scheduler->Jobs, select the data
grid row for GATHER_STATS_JOB, choose the disable
drop-down action, then finally press OK
- Create your own database creation scripts (i.e. do
not use DBCA) and make sure not to run the CATAWRTB.sql
script [Note - Oracle upgrade process may undo this]
- Run the CATNOAWR.sql script to drop the AWR
Repository tables [Note - Oracle upgrade process may
undo this]
