由于业务数据库中不少表因为经常的insert、delete操作,时间一长容易操作表的HWM(高水位)比较高,在做全表扫描时消耗比较大,为提高数据库执行效率,该脚本对经常insert、delete的表进行监控,进行碎片整理。
表和存储过程的初始化
grant select on dba_segments to perfstat;
conn perfstat/perfstat123
CREATE TABLE "PERFSTAT"."TB_MONITOR"
( "TABLE_NAME" VARCHAR2(200) NOT NULL ENABLE,
"OWNER" VARCHAR2(20) NOT NULL ENABLE,
"SIZE_THRESHOLD" NUMBER,
"WASTE_THRESHOLD" NUMBER,
"SGM_SPACE_MANAGEMENT" VARCHAR2(6),
"CURRENT_SIZE" NUMBER,
"CURRENT_WASTE" NUMBER,
CONSTRAINT "PK_TAB_NAME" PRIMARY KEY ("TABLE_NAME","OWNER")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
);
grant select on "PERFSTAT"."TB_MONITOR" to public;
create public synonym TB_MONITOR for "PERFSTAT"."TB_MONITOR";
存储过程,在sysdba 下执行即可:
create or replace procedure perfstat.p_tb_monitor Authid Current_User is
/*
Introduce
================
This procedure is use for monitor size over 100M table which have fragement need to be rebuild.
can be use on 9iR2 or upper version,MSSM or ASSM both can be use.
---------------
*/
cursor c_check_tb is select table_name from perfstat.TB_MONITOR;
v_check_tb c_check_tb%rowtype;
acheck number;
cursor c_tab_mon_man is select table_name,owner,sgm_space_management from perfstat.TB_MONITOR
where SGM_SPACE_MANAGEMENT='MANUAL';
/*debug*/
--and rownum<5;
cursor c_tab_mon_aut is select table_name,owner,sgm_space_management from perfstat.TB_MONITOR
where SGM_SPACE_MANAGEMENT='AUTO';
/*debug*/
--and rownum<5;
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_owner varchar2(20); ----new variable
--- 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_check_tb;
loop
fetch c_check_tb into v_check_tb;
select count(*) INTO acheck
from dba_tab_columns b
where b.table_name= v_check_tb.table_name
and (b.DATA_TYPE like 'LONG%'
or b.DATA_TYPE like '%AQ%'
or b.DATA_TYPE like '%LOB%');
if acheck > 0 then
delete from perfstat.TB_MONITOR where table_name=v_check_tb.table_name ;
commit;
acheck:=0;
end if;
exit when c_check_tb%NOTFOUND;
end loop;
close c_check_tb;
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(v_tab_mon_man.owner, v_tab_mon_man.table_name,'TABLE',0,v_free_blocks);
DBMS_SPACE.UNUSED_SPACE(v_tab_mon_man.owner, 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 perfstat.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 and a.owner=v_tab_mon_man.owner;
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(v_tab_mon_aut.owner, 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(v_tab_mon_aut.owner, 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 perfstat.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 and a.owner=v_tab_mon_aut.owner;
commit;
end if;
exit when c_tab_mon_aut%NOTFOUND;
end loop;
close c_tab_mon_aut;
update perfstat.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=a.owner 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;
/
设置文件文件夹
在$HOME下建立monitor文件,再建立三个子文件夹autoshell、log、report
autoshell文件夹下shell编写
在其下建立tb_monitor_auto.sh并根据业务需求改下相关sql:
#!/bin/sh
#########################################################################
# DB TUNING MONITOR SCRIPT #
# MUST run the initial scrpit at the first time #
# to create procedure and table #
#########################################################################
# Path Define
work_path=$HOME/monitor
#log=${work_path}/log
#date_format_yyyymmdd
date_yyyymmdd=`date '+%Y%m%d'`
cd $work_path/log
rm -f *
### CHECH TABLE IF NEED TO BE REBUILD START ########################
sqlplus "/ as sysdba"<<EOF>/dev/null
set feedback off
set echo off
set heading off
set lines 300
set pagesize 50000
set trimspool on
col segment_name Format a32
col Owner Format a12
spool table_monitor_insert.temp
truncate table PERFSTAT.TB_MONITOR;
select 'insert into PERFSTAT.TB_MONITOR(owner,TABLE_NAME,CURRENT_SIZE) values( ' || '''' ||
Owner || ''',' || '''' || segment_name || '''' || ',' || sumsize || ');'
from (select Owner, segment_name, sum(bytes / 1024 / 1024) sumsize
from dba_segments
where segment_type = 'TABLE'
and Owner not in
('ANONYMOUS', 'AURORA$', 'AURORA', 'CTXSYS', 'DBSNMP', 'DIP',
'DMSYS', 'DVF', 'DVSYS', 'EXFSYS', 'HR', 'LBACSYS', 'MDDATA',
'MDSYS', 'MGMT_VIEW', 'ODM', 'ODM_MTR', 'OE', 'OLAPSYS',
'ORACLE_OCM', 'ORAWSM', 'ORDPLUGINS', 'ORDSYS', 'OSE',
'OUTLN', 'PERFSTAT', 'PM', 'QS', 'QS_ADM', 'QS_CB',
'QS_CBADM', 'QS_CS', 'QS_ES', 'QS_OS', 'QS_WS', 'REPADMIN',
'SCOTT', 'SH', 'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN',
'SYSTEM', 'TRACESVR', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WK_TEST',
'WKUSER', 'WMSYS', 'XDB', 'EUL4_ZHS', 'EUL4_US', 'SYSADMIN',
'APPLSYS')
and segment_name in
(select table_name
from dba_tables a
where Owner not in
('ANONYMOUS', 'AURORA$', 'AURORA', 'CTXSYS', 'DBSNMP',
'DIP', 'DMSYS', 'DVF', 'DVSYS', 'EXFSYS', 'HR',
'LBACSYS', 'MDDATA', 'MDSYS', 'MGMT_VIEW', 'ODM',
'ODM_MTR', 'OE', 'OLAPSYS', 'ORACLE_OCM', 'ORAWSM',
'ORDPLUGINS', 'ORDSYS', 'OSE', 'OUTLN', 'PERFSTAT', 'PM', 'QS',
'QS_ADM', 'QS_CB', 'QS_CBADM', 'QS_CS', 'QS_ES',
'QS_OS', 'QS_WS', 'REPADMIN', 'SCOTT', 'SH',
'SI_INFORMTN_SCHEMA', 'SYS', 'SYSMAN', 'SYSTEM',
'TRACESVR', 'TSMSYS', 'WKPROXY', 'WKSYS', 'WK_TEST',
'WKUSER', 'WMSYS', 'XDB', 'EUL4_ZHS', 'EUL4_US',
'SYSADMIN', 'APPLSYS')
and table_name not in
(select table_name from dba_part_tables))
Group by Owner, segment_name
having sum(bytes / 1024 / 1024) > 102
order by 3)
/
spool off
set feedback on
set heading on
!cat table_monitor_insert.temp |grep insert |grep -v SQL >table_monitor_insert.sql
@table_monitor_insert
commit
exit
EOF
sqlplus "/ as sysdba"<<EOF>/dev/null
update TB_MONITOR set WASTE_THRESHOLD=40
where CURRENT_SIZE > 2048;
commit;
update TB_MONITOR set WASTE_THRESHOLD=60
where CURRENT_SIZE between 512 and 2047;
commit;
update TB_MONITOR set WASTE_THRESHOLD=70
where CURRENT_SIZE <512 ;
commit;
update TB_MONITOR c set SGM_SPACE_MANAGEMENT=(select b.SEGMENT_SPACE_MANAGEMENT from dba_tables a,dba_tablespaces b
where a.tablespace_name=b.tablespace_name and a.owner=c.owner
and a.table_name=c.table_name);
commit;
select * from TB_MONITOR where SGM_SPACE_MANAGEMENT is null;
delete TB_MONITOR where SGM_SPACE_MANAGEMENT is null;
exit
EOF
sqlplus "/ as sysdba"<<EOF>/dev/null
set feedback on;
set pages 10000;
set line 250;
set echo on;
set heading on;
set trimspool on
col 属主 for a12;
col 表名 for a30;
col 表的大小阈值 for 9
col 浪费率的大小阈值 for 99
exec perfstat.p_tb_monitor;
spool tb_need_to_rebuild.txt
select table_name 表名,
owner 属主,
SIZE_THRESHOLD 表的大小阈值,
WASTE_THRESHOLD 浪费率的大小阈值,
SGM_SPACE_MANAGEMENT 段管理方式,
CURRENT_SIZE 表当前大小MB,
CURRENT_WASTE 碎片率
from 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))
order by 碎片率 desc;
spool off
exit
EOF
#check if need to send alert mail
cd $work_path/log
sqlplus -s "/ as sysdba"<<EOF>/dev/null
set feedback off
set pages 0
set head off
set echo off
----to construct sql for the tables which need to rebuild;
spool rebuild.tmp
@${work_path}/autoshell/tb_monitor_construct.sql
spool off
!echo "REM ================================= ">rebuild.sql;
!echo "REM SQL for move tables:">>rebuild.sql;
!echo "REM ================================= ">>rebuild.sql;
!cat rebuild.tmp |grep 'alter table' |grep -v SQL >>rebuild.sql;
!echo "REM ================================= ">>rebuild.sql;
!echo "REM SQL for rebuild indexes:">>rebuild.sql;
!echo "REM ================================= ">>rebuild.sql;
!cat rebuild.tmp |grep 'alter index' |grep -v SQL >>rebuild.sql;
!echo "REM ================================= ">>rebuild.sql;
!echo "REM SQL for gather Table statistics:">>rebuild.sql;
!echo "REM ================================= ">>rebuild.sql;
!cat rebuild.tmp |grep dbms_stats.gather_table_stats |grep -v SQL |grep -v select >>rebuild.sql;
!echo "REM ================================= ">>rebuild.sql;
!echo "REM SQL for gather Index statistics:">>rebuild.sql;
!echo "REM ================================= ">>rebuild.sql;
!cat rebuild.tmp |grep dbms_stats.gather_index_stats |grep -v SQL |grep -v select >>rebuild.sql;
exit
EOF
cp rebuild.sql $work_path/report/rebuild_${date_yyyymmdd}.sql
cp tb_need_to_rebuild.txt $work_path/report/tb_need_to_rebuild_${date_yyyymmdd}.txt
autoshell文件夹下sql编写
autoshell文件夹下建立tb_monitor_construct.sql文件,内容如下:
----construct sql for move tables:
set feedback off;
set pages 100;
set heading on;
col table_name for a30;
set trimspool on;
set line 500
col script_for_gather_stat for a250
select 'alter table ' || owner || '.' || table_name || ' move nologging parallel 8;'
from perfstat.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));
select 'alter table ' || owner || '.' || table_name || ' parallel 1 logging;'
from perfstat.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));
------表回收碎片后,需要重建索引,以下是手工构建重建的语句
------关于重建索引的并行度问题,如果在rebuild的时候指定并行度,那么,rebuild完成后最后将并行度改回来。
-----否则,将倾向于走全表扫
select 'alter index ' || owner || '.' || index_name || ' rebuild online nologging parallel 8;'
from dba_indexes a
where (a.table_name, a.owner) in
(SELECT table_name, owner
FROM 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)));
select 'alter index ' || owner || '.' || index_name || ' parallel 1 logging;'
from dba_indexes a
where (a.table_name, a.owner) in
(SELECT table_name, owner
FROM 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)));
--------表重建完毕后,需要收集一次统计信息
--可以采用如下的语句一次性生成分析语句
select 'exec dbms_stats.gather_table_stats(OWNNAME=>' || '''' ||
upper(owner) || ''',tabname=>' || '''' || upper(table_name) ||
''',cascade=>TRUE,estimate_percent=>10,degree=>4);' as script_for_gather_stat
from (select owner,
segment_name table_name,
sum(bytes) / 1024 / 1024 size_m
from dba_segments
where (owner, segment_name) in
(SELECT owner, table_name
FROM perfstat.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)))
group by segment_name, owner
order by size_m) a
;
--------表统计完毕后,需要统计相关索引信息
--可以采用如下的语句一次性生成分析语句
select 'exec dbms_stats.gather_index_stats(OWNNAME=>' || '''' ||
upper(owner) || ''',indname=>' || '''' || upper(index_name) ||
''',estimate_percent=>10,degree=>4);' as script_for_gather_inx_stat
from dba_indexes a
where (a.table_name, a.owner) in
(SELECT table_name, owner
FROM 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)));
开始执行shell
在 autoshell下执行tb_monitor_auto.sh,如果没有报错,会在report文件下生产一个txt文件和一个sql文件,执行sql文件即可,可以写成一个脚本,在后头执行:
sqlplus -s “/ as sysdba”<exesql.sh.log
@rebuild_20160126.sql
exit
EOF