oracle 设置自动采样,如何禁用Oracle AWR自动采样功能

在Oracle 10g中，AWR采样缺省部署于数据库中，那么如何禁用这个功能呢？

在Metalink Note： 436386.1中，Oracle提供了一个增强脚本，称为： dbmsnoawr.plb

通过这个脚本可以启用和禁用AWR采样功能，从原则上，为未购买该部分功能授权的用户提供了一个退出途径。

在Oracle Database 11g中，可以通过参数CONTROL_MANAGEMENT_PACK_ACCESS 控制组件包的访问。

该脚本的内容如下：

Rem

Rem dbmsnoawr.sql

Rem

Rem Copyright (c) 2006, Oracle. All rights reserved.

Rem

Rem    NAME

Rem      dbmsnoawr.sql - Declaration of the DBMS_AWR package

Rem

Rem    DESCRIPTION

Rem      Utilities for disabling and getting status of AWR

Rem

Rem    NOTES

Rem

Rem    MODIFIED   (MM/DD/YY)

Rem    gwood       04/13/07 - created

Rem

create or replace package dbms_awr as

--    PACKAGE dbms_awr

--        This package allows users to disable AWR functionality in a Oracle 10g+ database.

--        The use of this package is not resticted by licencing of the Diagnostic Pack.

--        Additionally this package contains two functions that can be used to determine

--        if AWR is currently enabled.

--

--    PROCEDURE dbms_awr.disable_awr

--    PURPOSE: turns off collections into Automatic Workload Repository

--    PARAMETERS: none

procedure disable_awr;

--    PROCEDURE dbms_awr.enable_awr

--    PURPOSE: turns on collections into Automatic Workload Repository. The capture interval

--        is set to the default of 60 minutes.

--    PARAMETERS: none

procedure enable_awr;

--    FUNCTION dbms_awr.awr_enabled

--    PURPOSE: Returns TRUE if Automatic Workload Repository is performing periodic capture.

--             Returns FALSE if Automatic Workload Repository periodic capture is disabled.

--    PARAMETERS: none

function awr_enabled return boolean;

--    FUNCTION dbms_awr.awr_status

--    PURPOSE: Returns 'ENABLED' if Automatic Workload Repository is performing periodic capture.

--             Returns 'DISABLED' if Automatic Workload Repository periodic capture is disabled.

--    PARAMETERS: none

function awr_status return varchar2;

end dbms_awr;

/

create or replace package body dbms_awr wrapped

a000000

b2

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

b

27b 1de

XeDco+SpVfG9KEZ2ikXc00yhW88wg2P3AK7bfHRAWE7VX0b1S25KKJCp5VrehjNR9oaXoWT1

GGfYVnyl/lLyux308Fmhfp1y9pjrQyux50RY8xmHmiSG2bFbFs2Upn6MLYcfsqsW+joOTKYe

4TyFpXVqzVWS+Tjt8bcmSiai64IVcdOB3Q7Y6kQ8PGwqXqAiy9sFQKD0X6RC/ePGAQzUKwvS

3L8/hKgjdK9Fgw8bb7v1HTq22OJlAv+R/DYCSK57rPmAkyx/XLuXcPo3hcYs8fvUUAO33szW

gy5zNau9U7xiyAOExBz9Vh0U7EaMRl6rLr6UXpk/0tk3BW0W/GVo3XfdSzUpGN5aKa1xF2Yh

trcMV3KuK/FfIpy0bNDxSQ3LFuOsB8i5xzhj/dCqMxT4dO2awc0hnP3XeLhxWDvcEuqdkR9O

u+Z6US/LtRISXt2I8zFq6/aDSuOXTor9KQ1jYA==

/该脚本包含两个过程和两个函数，通过disable_awr / enable_awr 就可以禁用或者启用AWR采样功能：

SQL> @D:\\dbmsnoawr.plb

Package created.

Package body created.

SQL> desc dbms_awr

FUNCTION AWR_ENABLED RETURNS BOOLEAN

FUNCTION AWR_STATUS RETURNS VARCHAR2

PROCEDURE DISABLE_AWR

PROCEDURE ENABLE_AWR

SQL> exec dbms_awr.disable_awr

PL/SQL procedure successfully completed.

SQL> select dbms_awr.awr_status from dual;

AWR_STATUS

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

DISABLED

SQL> exec dbms_awr.enable_awr

PL/SQL procedure successfully completed.

SQL> select dbms_awr.awr_status from dual;

AWR_STATUS

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

ENABLED

这个脚本本质上非常简单，就是通过将快照采样价格设置为0和60来控制禁用和启用的。

PROCEDURE DISABLE_AWR

IS

BEGIN

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 0);

END;

PROCEDURE ENABLE_AWR

IS

BEGIN

DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 60);

END;记录供参考。