eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 如何删除日志组成员( DROP LOGFILE MEMBER ) | Blog首页 | Oracle数据恢复:kcbz_check_objd_typ_3 错误处理 »

如何禁用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;
记录供参考。



历史上的今天...
    >> 2011-03-14文章:
    >> 2010-03-14文章:
    >> 2009-03-14文章:
    >> 2008-03-14文章:
           我的搬家历程
    >> 2005-03-14文章:

无觅

By eygle on 2012-03-14 08:56 | Comments (0) | FAQ | 2970 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com