分享个ORACLE整理表碎片的脚本

最新推荐文章于 2024-04-15 00:05:16 发布

贤时间

最新推荐文章于 2024-04-15 00:05:16 发布

阅读量2.8k

点赞数 2

分类专栏： Oracle 文章标签： oracle 碎片整理

本文链接：https://blog.csdn.net/x6_9x/article/details/50589244

版权

Oracle 专栏收录该内容

59 篇文章 5 订阅

订阅专栏

由于业务数据库中不少表因为经常的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

贤时间

关注

2
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
分享个ORACLE整理表碎片的脚本

表和存储过程的初始化设置文件文件夹autoshell文件夹下shell编写autoshell文件夹下sql编写开始执行shell 由于业务数据库中不少表因为经常的insert、delete操作，时间一长容易操作表的HWM（高水位）比较高，在做全表扫描时消耗比较大，为提高数据库执行效率，该脚本对经常insert、delete的表进行监控，进行碎片整理。表和存储过程的初始化grant se
复制链接

扫一扫