表的浪费率监控脚本

(一) 背景说明:
由于业务数据库中不少表因为经常的insert、delete操作,时间一长容易操作表的HWM(高水位)比较高,在做全表扫描时消耗比较大,为提高数据库执行效率,该脚本对经常insert、delete的表进行监控,对浪费率大于设定阈值的表进行邮件告警。

(二) 脚本部署:
(1)环境:HP-UX B.11.11+Oracle 9.2.0.6
(2)主机空间要求:必须保证空间有大于50M。以防止输出临时文件时候无法输出,导致变量传递失败。
(3)其他:部署在oracle用户下,oracle有sendmail或者mailx权限以便发送邮件。
(4)部署方法:
$ su – oracle
(假定脚本部署目录为/oracle/tb_monitor,脚本名称为tb_monitor.sh)
oracle@ah_db01:/oracle/tb_monitor:>mkdir bin –脚本存放目录
oracle@ah_db01:/oracle/tb_monitor:>mkdir mail_result –发送邮件的邮件内容路径

oracle@ah_db01:/oracle/tb_monitor/bin > chmod +x tb_monitor.sh — 给脚本赋予执行权限

部署crontab,每天凌晨3点30运行
oracle@ah_db01:/oracle/tb_monitor/bin > crontab -l
30 3 * * * /oracle/tb_monitor/bin/tb_monitor.sh
(三) 脚本初始化:
(1) 在业务数据库PUBUSER用户(一个公用的用户)下执行init_tb_monitor.sql(注意以下所有初始化脚本在执行前一定要将脚本中的业务用户——”业务TEST”改为本省的业务用户)创建需要的用于监控的列表:TB_MONITOR。

TABLE_NAME:需要监控的表的名称。
SIZE_THRESHOLD:表的大小阈值
WASTE_THRESHOLD:浪费率的大小阈值,默认值为70%。
SGM_SPACE_MANAGEMENT:段管理方式,分为MANUAL和AUTO
CURRENT_SIZE:表当前的大小。
CURRENT_WASTE:表当前的浪费率。

(2)创建用于监控的procedure:
执行init_procedure.sql,”业务TEST”用户也同样需要修改成本省的业务用户。

(2) 部署脚本到bin目录下,脚本中的告警邮件接收者列表可以根据实际情况增加,用逗号分割:DBALIST=”jimmyhe1981@gmail.com,hejianmin@aspire-tech.com”

(四) 日常维护说明:
在正常情况下:
(1)脚本执行完成后发送邮件的内容在/oracle/tb_monitor/mail_result,如果有表的浪费率超过设置的阈值,会发送邮件到之前配置的DBALIST,正常情况下,这个路径存放自备份以来所有的sendmail内容。一天一个文件,文件名如all2mail_20071116.mail。该路径需要定期清理。
(2)如果有其他的表需要监控,可以对pubuser用户的TB_MONITOR表进行修改(insert新的记录)。
(3)SIZE_THRESHOLD和WASTE_THRESHOLD可以根据实际情况自行调节

(五) 收到告警邮件后的处理:
对于超过浪费率的表,DBA进行move tablespace and rebuild index的操作(注以下操作仅适用于小的堆表,可瞬间完成,不包含分区表)。可结合statspack和autotrace查看consistent gets的变换。
Alter table TABLE_NAME move tablespace TABLESPACE_NAME;
Alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME.

相关文章

4条评论

  1. 一个小问题
    procedure pubuser.p_tb_monitor
    中的 exit when c_tab_mon_aut%NOTFOUND;代码应该放在fetch下面,不能放在最后面,这样会导致多循环一下。
    另外,我有一张现在已经是700M的表,但记录 只有4万多
    表只有11个字段,varchar2最长150,表空间 assm, 表自动扩展2M
    这张表很频繁的insert,和delete,但是用了那个过程算出来浪费费只有0.2,我觉得不太可能
    dbms_output.put_line(V_FS1_BLOCKS) =0;
    dbms_output.put_line(V_FS2_BLOCKS) =4
    dbms_output.put_line(V_FS3_BLOCKS)=20
    dbms_output.put_line(V_FS4_BLOCKS)=118
    dbms_output.put_line(V_TOTAL_BLOCKS) =90112
    破碎率=0.2

    –不知道大师可否指点一下,为什么为这样子?

  2. 是否能通过下面这个查询来分析表破碎的情况?
    select dbms_rowid.ROWID_BLOCK_NUMBER(rowid),count(*) from test group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid);

  3. re cityvigil:谢谢指出。4万多的数据,700多M,如果设置了较高的pctfree等,在这样的情况下算出来较少的碎片率也是有可能的。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据