In this blog, we will see what controls Automatic Workload Repository in Oracle. For those who arrived to this blog and new, you will need to know more about Controlling Diagnostic and Tuning Pack Usage in below link.
CONTROL_MANAGEMENT_PACK_ACCESS parameter allows you to control which packs are utilized.
This parameter takes below values
- NONE – Neither pack is available.
- DIAGNOSTIC – To enable only Diagnostic pack.
- DIAGNOSTIC+TUNING – Both packs are available. This is the default.
How to enable/disable:
This parameter is dynamic; So you can change to any of the above value at any time.
Example:
Below commands disables the Diagnostic and Tuning pack
ALTER SYSTEM SET control_management_pack_access='NONE' SCOPE=BOTH;
How to check:
To check what is set in your database,
SQL> SHOW PARAMETER control_management_pack_access NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_management_pack_access string DIAGNOSTIC+TUNING
Important Points to remember:
The diagnostic pack includes AWR and ADDM while diagnostic and tuning includes sql tuning advisor and sql access advisor.
Diagnostic and Tuning Packs are extra cost, licensed features.
Statspack is another tool for database comes without licenses and those who do not want to use these licensed feature, you can use Statspack.
In order to access AWR views, Dianostic pack license is required.
If you wish to know whether this feature has been used earlier or not, you can use the below query.
column name format a30 SQL> SELECT name, detected_usages, currently_used, TO_CHAR(last_sample_date,'DD-MON-YYYY:HH24:MI') last_sample FROM dba_feature_usage_statistics WHERE name = 'AWR Report' ;
