（一） 背景说明：
由于业务数据库中不少表因为经常的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。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
--You MUST replace table owner "业务TEST" to your own 业务用户 and owner must be capital!! create table pubuser.TB_MONITOR ( TABLE_NAME VARCHAR2(200) not null, SIZE_THRESHOLD NUMBER, WASTE_THRESHOLD NUMBER, SGM_SPACE_MANAGEMENT VARCHAR2(6), CURRENT_SIZE NUMBER, CURRENT_WASTE NUMBER ); alter table pubuser.TB_MONITOR add constraint PK_TAB_NAME primary key (TABLE_NAME); grant select any dictionary to pubuser; delete from pubuser.TB_MONITOR; commit; prompt Loading pubuser.TB_MONITOR... insert into pubuser.TB_MONITOR (TABLE_NAME, SIZE_THRESHOLD, WASTE_THRESHOLD, SGM_SPACE_MANAGEMENT, CURRENT_SIZE, CURRENT_WASTE) values ('MYTABLE_1', 20, 70, null, null, null); insert into pubuser.TB_MONITOR (TABLE_NAME, SIZE_THRESHOLD, WASTE_THRESHOLD, SGM_SPACE_MANAGEMENT, CURRENT_SIZE, CURRENT_WASTE) values ('MYTABLE_2', 20, 70, null, null, null); insert into pubuser.TB_MONITOR (TABLE_NAME, SIZE_THRESHOLD, WASTE_THRESHOLD, SGM_SPACE_MANAGEMENT, CURRENT_SIZE, CURRENT_WASTE) values ('MYTABLE_3', null, 70, null, null, null); insert into pubuser.TB_MONITOR (TABLE_NAME, SIZE_THRESHOLD, WASTE_THRESHOLD, SGM_SPACE_MANAGEMENT, CURRENT_SIZE, CURRENT_WASTE) values ('MYTABLE_4', null, 70, null, null, null); insert into pubuser.TB_MONITOR (TABLE_NAME, SIZE_THRESHOLD, WASTE_THRESHOLD, SGM_SPACE_MANAGEMENT, CURRENT_SIZE, CURRENT_WASTE) values ('MYTABLE_5', null, 70, null, null, null); insert into pubuser.TB_MONITOR (TABLE_NAME, SIZE_THRESHOLD, WASTE_THRESHOLD, SGM_SPACE_MANAGEMENT, CURRENT_SIZE, CURRENT_WASTE) values ('MYTABLE_6', null, 70, null, null, null); insert into pubuser.TB_MONITOR (TABLE_NAME, SIZE_THRESHOLD, WASTE_THRESHOLD, SGM_SPACE_MANAGEMENT, CURRENT_SIZE, CURRENT_WASTE) values ('MYTABLE_7', null, 70, null, null, null); insert into pubuser.TB_MONITOR (TABLE_NAME, SIZE_THRESHOLD, WASTE_THRESHOLD, SGM_SPACE_MANAGEMENT, CURRENT_SIZE, CURRENT_WASTE) values ('MYTABLE_8', null, 70, null, null, null); insert into pubuser.TB_MONITOR (TABLE_NAME, SIZE_THRESHOLD, WASTE_THRESHOLD, SGM_SPACE_MANAGEMENT, CURRENT_SIZE, CURRENT_WASTE) values ('MYTABLE_9', 10, 70, null, null, null); insert into pubuser.TB_MONITOR (TABLE_NAME, SIZE_THRESHOLD, WASTE_THRESHOLD, SGM_SPACE_MANAGEMENT, CURRENT_SIZE, CURRENT_WASTE) values ('MYTABLE_10', 20, 70, null, null, null); insert into pubuser.TB_MONITOR (TABLE_NAME, SIZE_THRESHOLD, WASTE_THRESHOLD, SGM_SPACE_MANAGEMENT, CURRENT_SIZE, CURRENT_WASTE) values ('MYTABLE_11', 10, 70, null, null, null); insert into pubuser.TB_MONITOR (TABLE_NAME, SIZE_THRESHOLD, WASTE_THRESHOLD, SGM_SPACE_MANAGEMENT, CURRENT_SIZE, CURRENT_WASTE) values ('MYTABLE_12', null, 70, null, null, null); insert into pubuser.TB_MONITOR (TABLE_NAME, SIZE_THRESHOLD, WASTE_THRESHOLD, SGM_SPACE_MANAGEMENT, CURRENT_SIZE, CURRENT_WASTE) values ('MYTABLE_13', null, 70, null, null, null); commit; update pubuser.TB_MONITOR a set SGM_SPACE_MANAGEMENT=(select SEGMENT_SPACE_MANAGEMENT from dba_tablespaces b,dba_tables c where a.table_name=c.table_name and c.tablespace_name=b.tablespace_name and c.owner='业务TEST'); commit; |
|
1 2 3 4 5 6 7 8 9 |
SQL> desc pubuser.TB_MONITOR Name Null? Type ----------------------------------------- -------- ---------------------------- TABLE_NAME NOT NULL VARCHAR2(200) SIZE_THRESHOLD NUMBER WASTE_THRESHOLD NUMBER SGM_SPACE_MANAGEMENT VARCHAR2(6) CURRENT_SIZE NUMBER CURRENT_WASTE NUMBER |
TABLE_NAME：需要监控的表的名称。
SIZE_THRESHOLD：表的大小阈值
WASTE_THRESHOLD：浪费率的大小阈值，默认值为70%。
SGM_SPACE_MANAGEMENT：段管理方式，分为MANUAL和AUTO
CURRENT_SIZE：表当前的大小。
CURRENT_WASTE：表当前的浪费率。
（2）创建用于监控的procedure：
执行init_procedure.sql，”业务TEST”用户也同样需要修改成本省的业务用户。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 |
create or replace procedure pubuser.p_tb_monitor Authid Current_User is /* Introduce ================ This procedure is use for monitor misc table which is need to be rebuild. can be use on 9iR2 or upper version,MSSM or ASSM both can be use. Attention!!!! You MUST replace table owner 业务TEST to your own 业务用户 owner and owner must be capital!! Written by hejianmin@aspire-tech.com --------------- */ cursor c_tab_mon_man is select table_name,sgm_space_management from pubuser.tb_monitor where SGM_SPACE_MANAGEMENT='MANUAL'; cursor c_tab_mon_aut is select table_name,sgm_space_management from pubuser.tb_monitor where SGM_SPACE_MANAGEMENT='AUTO'; v_tab_mon_man c_tab_mon_man%rowtype; v_tab_mon_aut c_tab_mon_aut%rowtype; a number; v_tab_name varchar2(200); v_seg_mng varchar2(200); v_free_blocks number; V_TOTAL_BLOCKS NUMBER; V_TOTAL_BYTES NUMBER; V_UNUSED_BLOCKS NUMBER; V_UNUSED_BYTES NUMBER; V_LAST_USED_EXTENT_FILE_ID NUMBER; V_LAST_USED_EXTENT_BLOCK_ID NUMBER; V_LAST_USED_BLOCK NUMBER; V_UNFORMATTED_BLOCKS NUMBER; V_UNFORMATTED_BYTES NUMBER; V_FS1_BLOCKS NUMBER; V_FS1_BYTES NUMBER; V_FS2_BLOCKS NUMBER; V_FS2_BYTES NUMBER; V_FS3_BLOCKS NUMBER; V_FS3_BYTES NUMBER; V_FS4_BLOCKS NUMBER; V_FS4_BYTES NUMBER; V_FULL_BLOCKS NUMBER; V_FULL_BYTES NUMBER; --err_num varchar2(2000); begin open c_tab_mon_man; loop fetch c_tab_mon_man into v_tab_mon_man; if v_tab_mon_man.sgm_space_management='MANUAL' then DBMS_SPACE.FREE_BLOCKS('业务TEST', v_tab_mon_man.table_name,'TABLE',0,v_free_blocks); DBMS_SPACE.UNUSED_SPACE('业务TEST', v_tab_mon_man.table_name,'TABLE',V_TOTAL_BLOCKS, V_TOTAL_BYTES,V_UNUSED_BLOCKS,V_UNUSED_BLOCKS,V_LAST_USED_EXTENT_FILE_ID,V_LAST_USED_EXTENT_BLOCK_ID, V_LAST_USED_BLOCK); update pubuser.tb_monitor a set CURRENT_WASTE=round((v_free_blocks/(V_TOTAL_BLOCKS)*100),2) where SGM_SPACE_MANAGEMENT='MANUAL' and a.TABLE_NAME=v_tab_mon_man.table_name; commit; end if; exit when c_tab_mon_man%NOTFOUND; end loop; close c_tab_mon_man; open c_tab_mon_aut; loop fetch c_tab_mon_aut into v_tab_mon_aut; if v_tab_mon_aut.sgm_space_management='AUTO' then DBMS_SPACE.UNUSED_SPACE('业务TEST', v_tab_mon_aut.table_name,'TABLE',V_TOTAL_BLOCKS, V_TOTAL_BYTES,V_UNUSED_BLOCKS,V_UNUSED_BLOCKS,V_LAST_USED_EXTENT_FILE_ID,V_LAST_USED_EXTENT_BLOCK_ID, V_LAST_USED_BLOCK); DBMS_SPACE.SPACE_USAGE('业务TEST', v_tab_mon_aut.table_name,'TABLE',V_UNFORMATTED_BLOCKS, V_UNFORMATTED_BYTES,V_FS1_BLOCKS,V_FS1_BYTES,V_FS2_BLOCKS,V_FS2_BYTES,V_FS3_BLOCKS,V_FS3_BYTES, V_FS4_BLOCKS,V_FS4_BYTES,V_FULL_BLOCKS,V_FULL_BYTES); update pubuser.tb_monitor a set CURRENT_WASTE=round(((V_FS1_BLOCKS+V_FS2_BLOCKS+V_FS3_BLOCKS+V_FS4_BLOCKS)/V_TOTAL_BLOCKS)*100,2) where SGM_SPACE_MANAGEMENT='AUTO' and a.TABLE_NAME=v_tab_mon_aut.table_name; commit; end if; exit when c_tab_mon_aut%NOTFOUND; end loop; close c_tab_mon_aut; update pubuser.tb_monitor a set CURRENT_SIZE=(select sum(bytes)/1024/1024 as size_m from dba_segments where segment_name=A.TABLE_NAME and owner='业务TEST' and segment_type LIKE 'TABLE%'); COMMIT; /* exception when others then err_num := SQLCODE; if err_num = 1 then dbms_output.put_line(SQLERRM(err_num)); end if; */ end; / |
（2） 部署脚本到bin目录下，脚本中的告警邮件接收者列表可以根据实际情况增加，用逗号分割：DBALIST=”jimmyhe1981@gmail.com,hejianmin@aspire-tech.com”
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
#!/bin/sh ######################################################################### # DB TUNING MONITOR SCRIPT # # MUST run the initial scrpit at the first time # # to create procedure and table # # 2008-03-14 writen by hejianmin@aspire-tech.com # ######################################################################### . /oracle/.profile # Path Define work_path=/oracle/tb_monitor log=${work_path}/log mail_result=${work_path}/mail_result cd $work_path # SENDMAIL Define DBALIST="jimmyhe1981@gmail.com,hejianmin@aspire-tech.com" #Define ORACLE_SID orasid=$ORACLE_SID #date_format_yyyymmdd date_yyyymmdd=`date '+%Y%m%d'` ############## CHECH TABLE IF NEED TO BE REBUILD START ##################################################### sqlplus "/ as sysdba"<<EOF>/dev/null set feedback on; set pages 10000; set line 200; set echo on; set heading on; col table_name for a30; exec pubuser.p_tb_monitor; spool tb_need_to_rebuild.txt SELECT * FROM PUBUSER.TB_MONITOR WHERE SGM_SPACE_MANAGEMENT IS NOT NULL AND ( (SIZE_THRESHOLD IS NOT NULL AND SIZE_THRESHOLD<CURRENT_SIZE AND WASTE_THRESHOLD<CURRENT_WASTE) or (SIZE_THRESHOLD is null and WASTE_THRESHOLD<CURRENT_WASTE) ); spool off exit EOF #check if need to send alert mail sqlplus -s "/ as sysdba"<<EOF>/dev/null set feedback off set pages 0 set head off set echo off spool mail_flag.tmp SELECT count(*) FROM PUBUSER.TB_MONITOR WHERE SGM_SPACE_MANAGEMENT IS NOT NULL AND ( (SIZE_THRESHOLD IS NOT NULL AND SIZE_THRESHOLD<CURRENT_SIZE AND WASTE_THRESHOLD<CURRENT_WASTE) or (SIZE_THRESHOLD is null and WASTE_THRESHOLD<CURRENT_WASTE) ); spool off exit EOF read mail_flag<mail_flag.tmp #clear tmp file rm *.tmp ############## CHECH TABLE IF NEED TO BE REBUILD END ##################################################### ############## Send the result to dba START ############################################################## echo "================= $orasid DB TUNING MONITOR REPORT =================">all2mail_${date_yyyymmdd}.mail cat tb_need_to_rebuild.txt>>all2mail_${date_yyyymmdd}.mail echo "============================ REPORT END ============================">>all2mail_${date_yyyymmdd}.mail rm tb_need_to_rebuild.txt if [ $mail_flag -ge 1 ] then mailx -s "MISC DB TUNING MONITOR REPORT(SOME TABLE NEED TO REBUILD!!)" -r "dba@MISC_DB" $DBALIST < all2mail_${date_yyyymmdd}.mail else echo "empty" fi mv all2mail_${date_yyyymmdd}.mail ${mail_result} |
（四） 日常维护说明：
在正常情况下：
（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条评论
几年来困惑的问题 ，终于在这边找到了，谢谢!
一个小问题
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
–不知道大师可否指点一下，为什么为这样子？
是否能通过下面这个查询来分析表破碎的情况？
select dbms_rowid.ROWID_BLOCK_NUMBER(rowid),count(*) from test group by dbms_rowid.ROWID_BLOCK_NUMBER(rowid);
re cityvigil：谢谢指出。4万多的数据，700多M，如果设置了较高的pctfree等，在这样的情况下算出来较少的碎片率也是有可能的。