How to check whether the current database in using Oracle options

最新推荐文章于 2024-09-30 17:19:22 发布

ciyangliln703681

最新推荐文章于 2024-09-30 17:19:22 发布

阅读量78

点赞数

文章标签：数据库

1.How to check whether the current database in using Oracle Diagnostics Pack and Oracle Tuning Pack ?

10g Enterprise Edition
============================
If the DB version is 10g Enterprise Edition, please using following sql to check whether the Diagnostics Pack and Tuning Pack are in use or not :

SELECT NAME, currently_used, detected_usages, last_usage_date, last_sample_date
FROM dba_feature_usage_statistics
WHERE NAME IN ('Automatic Workload Repository','SQL Access Advisor','SQL Tuning Advisor','SQL Tuning Set')
AND currently_used = 'TRUE';

Note: If the column "currently_used" show "TRUE" ,It means the feature of pack is in use. "FALSE" measn not in use.

11g Enterprise Edition
===========================
If the DB version is 11.2.0.1, you can refer to following note to check whether the Packs are in use or not.

Database Options/Management Packs Usage Reporting for Oracle Database 11g Release 2 ( Doc ID 1317265.1 )

From the output , we can know whether the following packs are in use or not from "Used" column.

2.If they are in use,how to disable it?

Answer:
+AWR: Disabling and Uninstalling AWR ( Doc ID 1909073.1 )
+ADDM: In 10g, ADDM can be disabled by following parameter: ( Doc ID 562932.1 )
ALTER SYSTEM set "_addm_auto_enable"=false;
+ASH: ALTER SYSTEM set "_ash_enable"=false;

refer to :
Oracle Diagnostic Pack and its related Views. ( Doc ID 1165604.1 )
+SQL Tuning Advisor:
---- Disable automatic SQL tuning advisor
exec DBMS_AUTO_TASK_ADMIN.DISABLE( client_name => 'sql tuning advisor', operation => NULL, window_name => NULL);
+SQL PROFILE:
Refer to : Automatic SQL Tuning and SQL Profiles ( Doc ID 271196.1 )
Disabling the Management Packs in Enterprise Manager:
10.2.0.5 dbconsole
Setup/Management Pack Access (down left hand side)
There is an option to "grant access" or "remove access"
Refer to :
Understanding the Management Packs in Enterprise Manager (dbconsole, grid control and cloud control) ( Doc ID 1566956.1 ) To BottomTo Bottom
Disabling and Uninstalling AWR ( Doc ID 1909073.1 )
In 10g, ADDM can be disabled by following parameter: ( Doc ID 562932.1 )
Automatic SQL Tuning and SQL Profiles ( Doc ID 271196.1 )
Oracle Diagnostic Pack and its related Views. ( Doc ID 1165604.1 )