利用DBMS_SPACE包对Oracle 表碎片进行监控与清理

最新推荐文章于 2023-08-03 21:54:42 发布

kuifeng.dong

最新推荐文章于 2023-08-03 21:54:42 发布

阅读量583

点赞数

利用DBMS_SPACE包对Oracle 表碎片进行监控与清理

----转 http://blog.163.com/ansel_zlh/blog/static/103727172009384549323/

如果经常进行频繁的insert与delete操作，将会使表的HWM（高水位）变高，这样在作全表扫的操作时，将花费更多的成本。一般而言，当实际含有行数据的数据块只占HWM的50%时，我们一般认为表中数据块存在较大的浪费，出现这样的情况，一般建议对表进行重建。

对于查询某张表的空间使用情况，Oracle提供了一个包DBMS_SPACE来完成。关于该包的使用方法可以参见其说明。这里主要提供了一种快速布署的方法，来对数据库中的相关表进行监控，对浪费空间比较大的表进行表的重建，统计信息收集。其思路是，首先对数据库中所有用户的表作调查，确定哪些表需要监控，创建一张表来维护监控信息，碎片情况的收集通过Procedure来完成，如果有需要重建的表，则生成相关的语句，并将语句以邮件的方式发出。相关的脚本在HP-UX上运行，如果是其它平台，可作相应的修改。

一．在Oracle用户下创建相关的目录

mkdir /oracle/utils/tb_monitor

mkdir /oracle/utils/tb_monitor/log

mkdir /oracle/utils/tb_monitor/mail_result

二．创建TB_MONITOR表，由于这里是通过perfstat用户来进行监控，所以还需要显示的进行授权，方能在后面的procedure中查询dba_segments数据字典

TB_MONITOR表结构说明如下：

TABLE_NAME：需要监控的表的名称。

OWNER：表所属用户。

SIZE_THRESHOLD：表的大小阈值

WASTE_THRESHOLD：浪费率的大小阈值，默认值为70%。

SGM_SPACE_MANAGEMENT：段管理方式，分为MANUAL和AUTO

CURRENT_SIZE：表当前的大小。

CURRENT_WASTE：表当前的浪费率。

脚本如下：

grant select on dba_segments to perfstat;

conn perfstat/perfstat

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

);

完成表的创建后，赋予Public的查询权限，创建公共同义词。以方便查询：

grant select on "PERFSTAT"."TB_MONITOR" to public;

create public synonym TB_MONITOR for "PERFSTAT"."TB_MONITOR";

三．确定需要监控的用户

查看监控会涉及到哪些用户，并筛选需要监控的表

set pagesize 20

select username from dba_users

where username not in

('SYS','SYSTEM','OUTLN','CTXSYS','DBSNMP','PERFSTAT','WMSYS','MDSYS','ORDSYS','ORDPLUGINS');

可以按照下面的语句进行筛选需要监控的表，生成插入的sql语句，并将这些需要监控的表插入监控表中，这里没有监控分区表。根据情况再加入一些筛选的条件。

另外，监控的范围是1M---20G大小的普通表。

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

--select Owner,segment_name,sum(bytes/1024/1024) from dba_segments where segment_name in (

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_name in

(

select table_name from all_tables

where

Owner not in ('SYS','SYSTEM','OUTLN','CTXSYS','DBSNMP','PERFSTAT','WMSYS','MDSYS','ORDSYS','ORDPLUGINS')

and table_name not in (select table_name from dba_part_tables)

and table_name not like '%BAK%' AND

table_name not like '%BACK%' AND

table_name not like '%BOBO%' AND

table_name not like '%TMP%' AND

table_name not like '%OLD%' AND

table_name not like '%UPDATE%' AND

table_name not like '%TEST%' AND

table_name not like '%LIMITED_SERVICE%' and

table_name not like 'LX%' and

table_name not like '%HJM%' and

table_name not like 'MIGRATION%' and

table_name not like 'SP_PUNISH_INTERFACE_%' and

table_name not like 'TEMP_%' and

table_name not like '%_09%' and

table_name not like '%_08%' and

table_name not like '%_07%' and

table_name not like '%_06%' and

table_name not like '%_05%' and

table_name not like '%_04%' and

table_name not like '%_03%' and

table_name not like '%_0%' and

table_name not like '%_1%' and

table_name not like '%_2%' and

table_name not like '%_3%' and

table_name not like '%LOG' and

table_name not like 'SYL_%' and

table_name not like '%YY%' and

table_name not like '%XX%' and

table_name not like '%XXX%' and

table_name not like '%TEMP%' and

table_name not like '%PLAN_TABLE%'

)

Group by Owner,segment_name

having sum(bytes/1024/1024) between 1.01 and 20000 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

!rm table_monitor_insert.temp

!mailx -s "table_monitor_insert.sql" -r "DBA" "z**@a***re-tech.com" <table_monitor_insert.sql

@table_monitor_insert

commit

!rm table_monitor_insert.sql

生成的sql语句类似如下：

insert into PERFSTAT.TB_MONITOR(owner,TABLE_NAME,CURRENT_SIZE) values( 'MOCSACCT','MISC_PREFIX',2);

insert into PERFSTAT.TB_MONITOR(owner,TABLE_NAME,CURRENT_SIZE) values( 'MOCSACCT','LOCAL_SERVICE',2);

insert into PERFSTAT.TB_MONITOR(owner,TABLE_NAME,CURRENT_SIZE) values( 'MOCSACCT','LOCAL_WAP_SERVICE',2);

insert into PERFSTAT.TB_MONITOR(owner,TABLE_NAME,CURRENT_SIZE) values( 'MOCSACCT','CARDPAYSERVORDERSTAT',2);

insert into PERFSTAT.TB_MONITOR(owner,TABLE_NAME,CURRENT_SIZE) values( 'MOCSACCT','ACCTCOMPAREFILE',2);

insert into PERFSTAT.TB_MONITOR(owner,TABLE_NAME,CURRENT_SIZE) values( 'MOCSCARD','CARDPAYSERVORDERSTAT',2);

insert into PERFSTAT.TB_MONITOR(owner,TABLE_NAME,CURRENT_SIZE) values( 'MOCSACCT','DATASTATBYAREAANDBRAND',2);

insert into PERFSTAT.TB_MONITOR(owner,TABLE_NAME,CURRENT_SIZE) values( 'MOCSACCT','T_ACCTINFO_HIS_PIGEONHOLE',2);

insert into PERFSTAT.TB_MONITOR(owner,TABLE_NAME,CURRENT_SIZE) values( 'MOCSACCT','TOTALDATASTATBYAREAANDBRAND',2);

数据插入到监控到以后，作一番检查和更新：

更新空间的浪费阀值为70%：

update TB_MONITOR set WASTE_THRESHOLD=70;

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;

四．创建监控的procedure：

create or replace procedure perfstat.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.

Written by xiaohe@*****-tech.com

Modified by zlh@******-tech.com

---------------

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_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;

执行上述procedure创建脚本，如果需要手工编译过程，执行以下命令：

alter procedure perfstat.p_tb_monitor compile;

查看编译错误：

show error

五．创建构建碎片整理的sql语句的脚本：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;'

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)

(SIZE_THRESHOLD is null and WASTE_THRESHOLD<CURRENT_WASTE)

);

---after the sql to move tables be constructed,we need to construct sql for rebuild the indexes:

------表Move后，需要重建索引，以下是手工构建重建的语句：

------关于重建索引的并行度问题，如果在rebuild的时候指定并行度，那么，rebuild完成后最后将并行度改回来。

------alter index test.IDX_T99 noparallel;

-----否则，将倾向于走全表扫

select 'alter index '||owner||'.'||index_name||' rebuild;' 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)

(SIZE_THRESHOLD is null and WASTE_THRESHOLD<CURRENT_WASTE)

)

);

--------now, we construct sql for gather statistics for the tables:

--------the percent is up to the size of table such as:

--------表重建完毕后，需要收集一次统计信息

----不同的表采用不同的分析采样：

--if size_m<=500 then estimate_percent=>100

--if size_m between 500 and 1024 then estimate_percent=>50

--if size_m between 1024 and 5120 then estimate_percent=>10

--if size_m between 5120 and 10240 then estimate_percent=>3

--if size_m between 10240 and 20480 then estimate_percent=>1

--可以采用如下的语句一次性生成分析语句

select 'exec dbms_stats.gather_table_stats(OWNNAME=>'||''''||upper(owner)||''',tabname=>'||''''||

upper(table_name)||''',cascade=>TRUE,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 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)

(SIZE_THRESHOLD is null and WASTE_THRESHOLD<CURRENT_WASTE)

)

group by segment_name,owner

order by size_m) a

where a.size_m<=500---------------------<500M

union all

select 'exec dbms_stats.gather_table_stats(OWNNAME=>'||''''||upper(owner)||''',tabname=>'||''''||

upper(table_name)||''',cascade=>TRUE,estimate_percent=>50,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 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)

(SIZE_THRESHOLD is null and WASTE_THRESHOLD<CURRENT_WASTE)

)

group by segment_name,owner

order by size_m) a

where a.size_m between 500 and 1024 --------500M-1G

union all

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 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)

(SIZE_THRESHOLD is null and WASTE_THRESHOLD<CURRENT_WASTE)

)

group by segment_name,owner

order by size_m) a

where a.size_m between 1024 and 5120-----------1G-5G

union all

select 'exec dbms_stats.gather_table_stats(OWNNAME=>'||''''||upper(owner)||''',tabname=>'||''''||

upper(table_name)||''',cascade=>TRUE,estimate_percent=>3,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 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)

(SIZE_THRESHOLD is null and WASTE_THRESHOLD<CURRENT_WASTE)

)

group by segment_name,owner

order by size_m) a

where a.size_m between 5120 and 10240-----------5G-10G

union all

select 'exec dbms_stats.gather_table_stats(OWNNAME=>'||''''||upper(owner)||''',tabname=>'||''''||

upper(table_name)||''',cascade=>TRUE,estimate_percent=>1,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 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)

(SIZE_THRESHOLD is null and WASTE_THRESHOLD<CURRENT_WASTE)

)

group by segment_name,owner

order by size_m) a

where a.size_m >10240;

六．创建shell脚本来调用上述procedure进行监控

#!/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 xiaohe@*****-tech.com #

# 2009-03-12 modify by zlh@*****-tech.com #

#########################################################################

. ~oracle/.profile #如果执行过程中有问题，可以单独export ORACLE_HOME

# Path Define

work_path=/oracle/utils/tb_monitor

log=${work_path}/log

mail_result=${work_path}/mail_result

cd $work_path

# SENDMAIL Define

DBALIST="zhaolehuan@aspire-tech.com,liurui@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 250;

set echo on;

set heading on;

set trimspool on

col owner for a12;

col table_name for a30;

col SIZE_THRESHOLD for 999999

col WASTE_THRESHOLD for 999

exec perfstat.p_tb_monitor;

spool tb_need_to_rebuild.txt

SELECT * 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)

(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 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)

(SIZE_THRESHOLD is null and WASTE_THRESHOLD<CURRENT_WASTE)

);

spool off

----to construct sql for the tables which need to rebuild;

spool rebuild.tmp

@tb_monitor_construct.sql

spool off

!echo "SQL for move tables:">rebuild.sql;

!echo "================================= ">>rebuild.sql;

!cat rebuild.tmp |grep move |grep -v SQL >>rebuild.sql;

!echo "================================= ">>rebuild.sql;

!echo "SQL for rebuild indexes:">>rebuild.sql;

!echo "================================= ">>rebuild.sql;

!cat rebuild.tmp |grep rebuild |grep -v SQL >>rebuild.sql;

!echo "================================= ">>rebuild.sql;

!echo "SQL for gather statistics:">>rebuild.sql;

!echo "================================= ">>rebuild.sql;

!cat rebuild.tmp |grep dbms_stats |grep -v SQL |grep -v select >>rebuild.sql;

exit

EOF

read mail_flag<mail_flag.tmp

#clear tmp file

rm -f *.tmp

############## CHECH TABLE IF NEED TO BE REBUILD END ################

############## Send the result to dba START ##########################

echo "================= $orasid DB TUNING MONITOR REPORT =================">mail_${date_yyyymmdd}.mail

cat tb_need_to_rebuild.txt>>mail_${date_yyyymmdd}.mail

echo "============ FOLLOWING SQL TO MOVING,REBUIDING,ANALYZING=========">>mail_${date_yyyymmdd}.mail

cat rebuild.sql>>mail_${date_yyyymmdd}.mail

echo "============================ REPORT END ==========================">>mail_${date_yyyymmdd}.mail

rm -f tb_need_to_rebuild.txt

rm -f rebuild.sql

if [ $mail_flag -ge 1 ]

then mailx -s "${orasid} TUNING MONITOR REPORT(SOME TABLE NEED TO REBUILD!!)" -r "dba@${orasid}" $DBALIST < mail_${date_yyyymmdd}.mail

else echo "empty"

mv -f mail_${date_yyyymmdd}.mail ${mail_result}

首次执行过程后。对浪费空间为0的表进行清理，不需要进行监控

delete from TB_MONITOR where CURRENT_WASTE=0;

七．定时监控：

为了能自动执行表碎片监控的脚本，将脚本加到crontab列表中：

#########TB_monitor if result to space waste############

30 6 * * 1 /oracle/utils/tb_monitor/tb_monitor.sh

kuifeng.dong

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
利用DBMS_SPACE包对Oracle 表碎片进行监控与清理

利用DBMS_SPACE包对Oracle 表碎片进行监控与清理 ----转 http://blog.163.com/ansel_zlh/blog/static/103727172009384549323/如果经常进行频繁的insert与delete操作，将会使表的HWM（高水位）变高，这样在作全表扫的操作时，将花费更多的成本。一般而言，当实际含有行数据的数据块只占H
复制链接

扫一扫