oracle调优诊断包,控制诊断和调优包的使用

Parameter CONTROL_MANAGEMENT_PACK_ACCESS (Oracle 11g onwards)

From 11g, the CONTROL_MANAGEMENT_PACK_ACCESS parameter allows you to control which packs are utilized.  This parameter can be set as one of three values:

Values:

NONE - Neither pack is available.

DIAGNOSTIC - Only the DIAGNOSTIC pack is available.

DIAGNOSTIC+TUNING - Both packs are available. This is the default.

The diagnostic pack includes AWR and ADDM while diagnostic and tuning includes sql tuning advisor and sql access advisor.

To check what is set in current environment, you can run the following:

SQL v11202>  SHOW PARAMETER control_management_pack_access

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_management_pack_access       string      DIAGNOSTIC+TUNING

This parameter is dynamic; so to change the parameter from default to none, the following command can be used:

SQL> ALTER SYSTEM SET control_management_pack_access=NONE;

To change the parameter back, run following:

SQL> alter system set control_management_pack_access="DIAGNOSTIC+TUNING";

Remember, the Diagnostic and Tuning Packs are extra cost, licensed features. If a license has not been acquired, then there are the options to use Statspack, a third-party tool, or to build home-grown monitoring procedures.