There may be times when a DBA might desire to disable Oracle 10g’s and 11g’s AWR (Automatic Workload Repository). I am neither advising nor condoning such a practice, but merely elaborating on how such a thing could be done. One reason might be to avoid licensing issues, because AWR isn’t part of the standard or even enterprise database – as it requires the optional (extra cost) Oracle Enterprise Manager (OEM) Diagnostic pack. So even though your 10g/11g database automatically collects AWR data every sixty minutes and retains it for a week – you cannot legally use the Oracle supplied PL/SQL packages (i.e. DBMS_WORKLOAD_REPOSITORY), the OEM screens for AWR, ADMM and ASH, or even the AWR data dictionary views (i.e. DBA_HIST_*) if you’re not licensed. This has in fact outraged more than a few users – resulting in an “Open Letter to Oracle about AWR and Ash Licensing”.

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$*!

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:

1. 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].
2. Set your init.ora parameter STATISTICS_LEVEL = BASIC
3. Execute the Oracle provided PL/SQL package: dbms_workload_repository.modify_snapshot_settings(interval=>0)
4. Execute the Oracle provided PL/SQL package: dbms_scheduler.disable('GATHER_STATS_JOB')
5. You can use Toad for #3: Main Menu->Database->Monitor->ADDM/AWR Reports screen, choose the Snapshot Management tab, set the interval to all zeroes, and then press the green checkmark in upper left corner to commit the change.
6. You can use Toad for #4: Main Menu->Schema Browser, choose the Sched. Job tab and disable the GATHER_STATS_JOB job.
7. You can use OEM for #4: 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
8. You can use OEM for #5: Main Menu->Scheduler->Jobs, select the data grid row for GATHER_STATS_JOB, choose the disable drop-down action, then  finally press OK
9. 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]
10. Run the CATNOAWR.sql script to drop the AWR Repository tables [Note – Oracle upgrade process may undo this]

For those of you who might want to read additional thoughts about this, please take a look at Mike Ault’s blog – Disabling Land Mines. Or better yet, simply do a web-search on “disable oracle awr” – you’ll find a ton of useful info on this topic. It’s a very popular discussion about a very unpopular licensing issue. 

Bert Scalzo is a Database Domain Expert for Quest Software and a member of the TOAD team. He has worked extensively with TOAD's developers and designed many of its features. Mr. Scalzo has worked with Oracle databases for well over two decades, starting with version 4. His work history includes time at Oracle Education and Oracle Consulting, plus he holds several Oracle Masters certifications. Mr. Scalzo also has an extensive academic background - including a BS, MS and PhD in Computer Science, an MBA and several insurance industry designations. Mr. Scalzo is an accomplished speaker and has presented at numerous Oracle conferences and user groups - including OOW, ODTUG, IOUGA, OAUG, RMOUG, et al. His key areas of DBA interest are Data Modeling, Database Benchmarking, Database Tuning & Optimization, "Star Schema" Data Warehouses and Linux. Mr. Scalzo has written articles for Oracle’s Technology Network (OTN), Oracle Magazine, Oracle Informant, PC Week (eWeek), Dell PowerEdge Magazine, The Linux Journal, www.linux.com, and www.orafaq.com. Mr. Scalzo can be reached via email at bert.scalzo@quest.com or bert.scalzo@yahoo.com.