一、什么是水线(High Water Mark)?
所有的oracle段(segments,在此,为了理解方便,建议把segment作为表的一个同义词) 都有一个在段内容纳数据的上限,我们把这个上限称为"high water mark"或HWM。 这个HWM是一个标记,用来说明已经有多少没有使用的数据块分配给这个segment。HWM通常增长的幅度为一次5个数据块,原则上HWM只会增大,不会缩小,即使将表中的数据全部删除,HWM还是为原值,由于这个特点,使HWM很象一个水库的历史最高水位,这也就是HWM的原始含义,当然不能说一个水库没水了,就说该水库的历史最高水位为0。但是如果我们在表上使用了truncate命令,则该表的HWM会被重新置为0。
二、HWM数据库的操作有如下影响:
a) 全表扫描通常要读出直到HWM标记的所有的属于该表数据库块,即使该表中没有任何数据。
b) 即使HWM以下有空闲的数据库块,键入在插入数据时使用了append关键字,则在插入时使用HWM以上的数据块,此时HWM会自动增大。
三、查找哪些表需要降低高水位线
想要确定哪些表需要降低高水位线,要基于有正确的统计信息的,统计信息的错误也将导致结果的错误。
NUM_ROWS:表中现有的行数
NEED:行的平均长度*行数/1024/1024/0.9=表需要的空间,单位为MB,其中0.9为1-pctfree得到的。一般情况下创建表pctfree默认都是10%
TRUE:占用空间
RECOVER_MB:可恢复的空间
where条件中,1定位表空间;2筛选出能压缩至少100M的表,也可根据需求增加减少;3显示前11行。
四、如何降低高水位线
降低高水位线的方式一共有7种,在这里我们一一列举出来。
a) truncate 表
b) 开启对表的shrink
c) CTAS
d) 表移动
e) exp/imp
f) expdp/impdp
g) 在线重定义
接下来将用实验的方式将各方法的操作以及效果列举出来。
oracle数据库版本如下
实验环境模拟
1、truncate 表
优点:速度快
缺点:表中数据全无
建议:当需要delete全表的时候,确定表中数据无用,可以使用这种方式进行清理。切记,谨慎。
接下来的六种方式,均采用清理表中部分数据,与开始对全表进行delete略有不同。
2、开启表的shrink
优点:对于表中已清除部分数据依然可用,
缺点:暂不详
3、CTAS
4、使用table move的方式
优点:降低了高水位线
缺点:因为表的移动,需要将索引进行重建
5、exp/imp
6、expdp/impdp
expdp/impdp这种方式也可以降低高水位线,方法类似exp/imp就不在做介绍。
7、在线重定义
优点:降低高水位线的同时,它是这七种办法中唯一不需要停业务的方式。
建议:生产中大多数时候是没办法停止的,即便有停机时间,还是有其他好多操作要进行,在线重定义提供了很大的帮助。然而在线重定义的作用还远远不止于此。
-------END-------
所有的oracle段(segments,在此,为了理解方便,建议把segment作为表的一个同义词) 都有一个在段内容纳数据的上限,我们把这个上限称为"high water mark"或HWM。 这个HWM是一个标记,用来说明已经有多少没有使用的数据块分配给这个segment。HWM通常增长的幅度为一次5个数据块,原则上HWM只会增大,不会缩小,即使将表中的数据全部删除,HWM还是为原值,由于这个特点,使HWM很象一个水库的历史最高水位,这也就是HWM的原始含义,当然不能说一个水库没水了,就说该水库的历史最高水位为0。但是如果我们在表上使用了truncate命令,则该表的HWM会被重新置为0。
二、HWM数据库的操作有如下影响:
a) 全表扫描通常要读出直到HWM标记的所有的属于该表数据库块,即使该表中没有任何数据。
b) 即使HWM以下有空闲的数据库块,键入在插入数据时使用了append关键字,则在插入时使用HWM以上的数据块,此时HWM会自动增大。
三、查找哪些表需要降低高水位线
想要确定哪些表需要降低高水位线,要基于有正确的统计信息的,统计信息的错误也将导致结果的错误。
- SYS@prod > SELECT NUM_ROWS,
- 2 AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9 NEED,
- 3 BLOCKS * 8 / 1024 TRUE,
- 4 (BLOCKS * 8 / 1024 - AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9) RECOVER_MB,
- 5 TABLE_NAME
- 6 FROM dba_tables
- 7 WHERE tablespace_name = 'VASTTBS'
- 8 AND BLOCKS * 8 / 1024 - AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9 > 100
- 9 AND rownum < 11
- 10 order by RECOVER_MB desc;
NEED:行的平均长度*行数/1024/1024/0.9=表需要的空间,单位为MB,其中0.9为1-pctfree得到的。一般情况下创建表pctfree默认都是10%
TRUE:占用空间
RECOVER_MB:可恢复的空间
where条件中,1定位表空间;2筛选出能压缩至少100M的表,也可根据需求增加减少;3显示前11行。
四、如何降低高水位线
降低高水位线的方式一共有7种,在这里我们一一列举出来。
a) truncate 表
b) 开启对表的shrink
c) CTAS
d) 表移动
e) exp/imp
f) expdp/impdp
g) 在线重定义
接下来将用实验的方式将各方法的操作以及效果列举出来。
oracle数据库版本如下
- SYS@prod > select * from v$version;
- BANNER
- --------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- PL/SQL Release 11.2.0.3.0 - Production
- CORE 11.2.0.3.0 Production
- TNS for Linux: Version 11.2.0.3.0 - Production
- NLSRTL Version 11.2.0.3.0 - Production
实验环境模拟
- --创建一张表
- VAST@prod > create table ote as select * from dba_objects;
- Table created.
- --创建索引
- VAST@prod > create index ind_ote_id on ote(object_id);
- Index created.
- --创建同义词
- VAST@prod > create or replace public synonym ote for vast.ote;
- Synonym created.
- --收集统计信息
- VAST@prod > ANALYZE TABLE OTE COMPUTE STATISTICS; --ESTIMATE/COMPUTE
- Table analyzed.
- --段的信息
- VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
- BYTES/1048576 BLOCKS EXTENTS
- ------------- ---------- ----------
- 9 1152 24
- --删除表中数据
- VAST@prod > delete from ote;
- 75355 rows deleted.
- VAST@prod > commit;
- Commit complete.
- --段的信息
- VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
- BYTES/1048576 BLOCKS EXTENTS
- ------------- ---------- ----------
- 9 1152 24
- --分析表块数,空闲块数,行数
- VAST@prod > SELECT blocks, empty_blocks, num_rows
- 2 FROM user_tables
- 3 WHERE table_name = 'OTE';
- BLOCKS EMPTY_BLOCKS NUM_ROWS
- ---------- ------------ ----------
- 1099 53 75355
- --BLOCKS + EMPTY_BLOCKS (1099+53=1152)DBA_SEGMENTS.BLOCKS 表示分配给这个表的所有的数据库块的数目。
- --USER_TABLES.BLOCKS表示已经使用过的数据库块的数目。USER_TABLES.EMPTY_BLOCKS则表示剩余块的数目。
- --插入数据
- VAST@prod > insert into ote select * from dba_objects where object_id<20000;
- 19677 rows created.
- VAST@prod > commit;
- Commit complete.
- --段的信息
- VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
- BYTES/1048576 BLOCKS EXTENTS
- ------------- ---------- ----------
- 9 1152 24
- --可见即便插入了数据,但是分给OTE段的块数目没有改变,这是因为之前删除了一部分数据,oracle在插入的时候,找到了这些可以被覆盖的块,
- --将这些数据插入到这些块中,故没有分配新的块给OTE段
- --分析表块数,空闲块数,行数
- VAST@prod > SELECT blocks, empty_blocks, num_rows
- 2 FROM user_tables
- 3 WHERE table_name = 'OTE';
- BLOCKS EMPTY_BLOCKS NUM_ROWS
- ---------- ------------ ----------
- 1099 53 75355
- --使用hint
- --使用append将会把新插入的数据插到高水位线之后,省去了查找可覆盖块的时间,这种方式也可提高插入的速度,但是会提升高水位线的高度。
- VAST@prod > insert /*+append*/ into ote select * from dba_objects where object_id<20000;
- 19677 rows created.
- VAST@prod > commit;
- Commit complete.
- --段的信息
- VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
- BYTES/1048576 BLOCKS EXTENTS
- ------------- ---------- ----------
- 11 1408 26
- --可见这次没有查找可覆盖的块,而是直接将数据插在了高水位线之后的空闲块中。
- --收集统计信息
- VAST@prod > ANALYZE TABLE OTE COMPUTE STATISTICS; --ESTIMATE/COMPUTE
- Table analyzed.
- --分析表块数,空闲块数,行数
- VAST@prod > SELECT blocks, empty_blocks, num_rows
- 2 FROM user_tables
- 3 WHERE table_name = 'OTE';
- BLOCKS EMPTY_BLOCKS NUM_ROWS
- ---------- ------------ ----------
- 1370 38 39354
- --对表的信息查看时,建议先进行统计信息的收集,不然会得到错误的信息。
1、truncate 表
- --截断表
- VAST@prod > truncate table ote;
- Table truncated.
- --收集统计信息
- VAST@prod > analyze table ote compute statistics; --estimate/compute
- Table analyzed.
- --段的信息
- VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
- BYTES/1048576 BLOCKS EXTENTS
- ------------- ---------- ----------
- .0625 8 1
- --此时可见OTE的段只剩下8个块了,因为表还是存在的,段头还是记录了一些基本信息的。
- --分析表块数,空闲块数,行数
- VAST@prod > SELECT blocks, empty_blocks, num_rows
- 2 FROM user_tables
- 3 WHERE table_name = 'OTE';
- BLOCKS EMPTY_BLOCKS NUM_ROWS
- ---------- ------------ ----------
- 0 8 0
- --检查索引以及同义词是否可用
- VAST@prod > col name for a15;
- VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
- 2 union all
- 3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
-
- TYPE NAME STATUS
- ------- --------------- --------
- INDEX IND_OTE_ID VALID
- SYNONYM IND_OTE_ID VALID
- --可见此种方式执行过后,原索引以及同义词还是可用的。
缺点:表中数据全无
建议:当需要delete全表的时候,确定表中数据无用,可以使用这种方式进行清理。切记,谨慎。
接下来的六种方式,均采用清理表中部分数据,与开始对全表进行delete略有不同。
2、开启表的shrink
- --开启行迁移
- VAST@prod > alter table ote enable row movement;
- Table altered.
- --查看表的状态
- VAST@prod > select table_name,
- 2 tablespace_name,
- 3 ROW_MOVEMENT,
- 4 read_only,
- 5 segment_created
- 6 from user_tables
- 7 where table_name = 'OTE';
- TABLE_NAME TABLESPACE_NAME ROW_MOVE REA SEG
- ------------------------------ ------------------------------ -------- --- ---
- OTE VASTTBS ENABLED NO YES
- --开启shrink
- VAST@prod > alter table ote shrink space;
- Table altered.
- --关闭行迁移
- VAST@prod > alter table ote disable row movement;
- Table altered.
- --查看表的状态
- VAST@prod > select table_name,
- 2 tablespace_name,
- 3 ROW_MOVEMENT,
- 4 read_only,
- 5 segment_created
- 6 from user_tables
- 7 where table_name = 'OTE';
- TABLE_NAME TABLESPACE_NAME ROW_MOVE REA SEG
- ------------------------------ ------------------------------ -------- --- ---
- OTE VASTTBS DISABLED NO YES
- --收集统计信息
- VAST@prod > analyze table ote compute statistics;
- Table analyzed.
- --段的信息
- VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
- BYTES/1048576 BLOCKS EXTENTS
- ------------- ---------- ----------
- 3.375 432 19
- --分析表块数,空闲块数,行数
- VAST@prod > SELECT blocks, empty_blocks, num_rows
- 2 FROM user_tables
- 3 WHERE table_name = 'OTE';
-
- BLOCKS EMPTY_BLOCKS NUM_ROWS
- ---------- ------------ ----------
- 413 19 29678
- --检查索引以及同义词是否可用
- VAST@prod > col name for a15;
- VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
- 2 union all
- 3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
-
- TYPE NAME STATUS
- ------- --------------- --------
- INDEX IND_OTE_ID VALID
- SYNONYM IND_OTE_ID VALID
缺点:暂不详
3、CTAS
- --创建一张新表
- VAST@prod > create table ote1 as select * from ote;
- Table created.
- --收集统计信息
- VAST@prod > analyze table ote1 compute statistics;
- Table analyzed.
- --段的信息
- VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE1';
- BYTES/1048576 BLOCKS EXTENTS
- ------------- ---------- ----------
- 4 512 19
- --分析表块数,空闲块数,行数
- VAST@prod > SELECT blocks, empty_blocks, num_rows
- 2 FROM user_tables
- 3 WHERE table_name = 'OTE1';
- BLOCKS EMPTY_BLOCKS NUM_ROWS
- ---------- ------------ ----------
- 429 83 29678
- --检查索引以及同义词是否可用
- 原索引以及同义词依然可用,但是在同义词以及索引是指向原OTE表。
- 建议:对于这种情况,可以在业务可以停止的时候进行,在ote1表新建对应索引,对应同义词等。然后rename OTE表,再将OTE1 rename为OTE。又或者将CTAS后将原表truncate,再将数据导入。
4、使用table move的方式
- --使用table move的方式
- VAST@prod > alter table ote move tablespace vasttbs;
- Table altered.
- --收集统计信息
- VAST@prod > analyze table ote compute statistics;
- Table analyzed.
- --段的信息
- VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
- BYTES/1048576 BLOCKS EXTENTS
- ------------- ---------- ----------
- 4 512 19
- --分析表块数,空闲块数,行数
- VAST@prod > SELECT blocks, empty_blocks, num_rows
- 2 FROM user_tables
- 3 WHERE table_name = 'OTE';
- BLOCKS EMPTY_BLOCKS NUM_ROWS
- ---------- ------------ ----------
- 429 83 29678
- --检查索引以及同义词是否可用
- VAST@prod > col name for a15;
- VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
- 2 union all
- 3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
- TYPE NAME STATUS
- ------- --------------- --------
- INDEX IND_OTE_ID UNUSABLE
- SYNONYM IND_OTE_ID VALID
- 发现这时,索引已经失效,对索引进行在线重建。
- --在线重建索引
- VAST@prod > alter index ind_ote_id rebuild online;
- Index altered.
- --检查索引以及同义词是否可用
- VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
- 2 union all
- 3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
- TYPE NAME STATUS
- ------- --------------- --------
- INDEX IND_OTE_ID VALID
- SYNONYM IND_OTE_ID VALID
缺点:因为表的移动,需要将索引进行重建
5、exp/imp
- --exp/imp降低高水位线
- [oracle@vedeu ~]$ exp vast/vast file=/home/oracle/ote.dmp tables=ote log=ote.log
- Export: Release 11.2.0.3.0 - Production on Mon Jan 23 11:54:49 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- Export done in US7ASCII character set and AL16UTF16 NCHAR character set
- server uses AL32UTF8 character set (possible charset conversion)
- About to export specified tables via Conventional Path ...
- . . exporting table OTE 29678 rows exported
- EXP-00091: Exporting questionable statistics.
- EXP-00091: Exporting questionable statistics.
- Export terminated successfully with warnings.
- --删除ote表
- VAST@prod > drop table ote purge;
- Table dropped.
- --导入表
- [oracle@vedeu ~]$ imp vast/vast file=/home/oracle/ote.dmp full=y
- Import: Release 11.2.0.3.0 - Production on Mon Jan 23 11:56:23 2017
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- Export file created by EXPORT:V11.02.00 via conventional path
- import done in US7ASCII character set and AL16UTF16 NCHAR character set
- import server uses AL32UTF8 character set (possible charset conversion)
- . importing VAST's objects into VAST
- . importing VAST's objects into VAST
- . . importing table "OTE" 29678 rows imported
- Import terminated successfully without warnings.
- --收集统计信息
- VAST@prod > analyze table ote compute statistics;
- Table analyzed.
- --段的信息
- VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
- BYTES/1048576 BLOCKS EXTENTS
- ------------- ---------- ----------
- 4 512 19
- --分析表块数,空闲块数,行数
- VAST@prod > SELECT blocks, empty_blocks, num_rows
- 2 FROM user_tables
- 3 WHERE table_name = 'OTE';
- BLOCKS EMPTY_BLOCKS NUM_ROWS
- ---------- ------------ ----------
- 496 16 29678
- --检查索引以及同义词是否可用
- VAST@prod > col name for a15;
- VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
- 2 union all
- 3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
- TYPE NAME STATUS
- ------- --------------- --------
- INDEX IND_OTE_ID VALID
- SYNONYM IND_OTE_ID VALID
6、expdp/impdp
expdp/impdp这种方式也可以降低高水位线,方法类似exp/imp就不在做介绍。
7、在线重定义
- --在线重定义
- 在线重定义同时也是我比较喜欢的一种方式,因为它可以不停业务,就完成降低高水位线。
- --设定主键
- VAST@prod > alter table ote add constraint pk_ote_id primary key (object_id);
- Table altered.
- --检查是否可以在线重定义
- VAST@prod > exec dbms_redefinition.can_redef_table( 'VAST','OTE',dbms_redefinition.cons_use_pk);
- PL/SQL procedure successfully completed.
- --定义中间表
- VAST@prod > create table ote_interim as select * from ote where 1=0;
- Table created
- --开始在线重定义
- VAST@prod > exec dbms_redefinition.start_redef_table('VAST','OTE','OTE_INTERIM',
- col_mapping => 'OWNER OWNER,
- OBJECT_NAME OBJECT_NAME,
- SUBOBJECT_NAME SUBOBJECT_NAME,
- OBJECT_ID OBJECT_ID,DATA_OBJECT_ID DATA_OBJECT_ID,
- OBJECT_TYPE OBJECT_TYPE,
- CREATED CREATED,
- LAST_DDL_TIME LAST_DDL_TIME,
- TIMESTAMP TIMESTAMP,
- STATUS STATUS,
- TEMPORARY TEMPORARY,
- GENERATED GENERATED,
- SECONDARY SECONDARY,
- NAMESPACE NAMESPACE,
- EDITION_NAME EDITION_NAME',
- options_flag => dbms_redefinition.cons_use_pk);
- PL/SQL procedure successfully completed.
- --处理索引以及约束对象
- VAST@prod > declare
- 2 error_count number;
- 3 begin
- 4 error_count := 0;
- 5 dbms_redefinition.copy_table_dependents(uname => 'VAST',orig_table => 'OTE',
- 6 int_table => 'OTE_INTERIM',
- 7 copy_indexes => dbms_redefinition.cons_orig_params,
- 8 num_errors => error_count);
- 9 dbms_output.put_line(to_char(error_count));
- 10 end;
- 11 /
- PL/SQL procedure successfully completed.
- --结束在线重定义
- VAST@prod > exec dbms_redefinition.finish_redef_table('VAST','OTE','OTE_INTERIM');
- PL/SQL procedure successfully completed.
- --收集统计信息
- VAST@prod > analyze table ote compute statistics;
- Table analyzed.
- --段的信息
- VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
- BYTES/1048576 BLOCKS EXTENTS
- ------------- ---------- ----------
- 4 512 19
- --分析表块数,空闲块数,行数
- VAST@prod > SELECT blocks, empty_blocks, num_rows
- 2 FROM user_tables
- 3 WHERE table_name = 'OTE';
- BLOCKS EMPTY_BLOCKS NUM_ROWS
- ---------- ------------ ----------
- 429 83 29678
- --检查索引以及同义词是否可用
- col name for a15;
- VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
- 2 union all
- 3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
- TYPE NAME STATUS
- ------- --------------- --------
- INDEX IND_OTE_ID VALID
- SYNONYM IND_OTE_ID VALID
建议:生产中大多数时候是没办法停止的,即便有停机时间,还是有其他好多操作要进行,在线重定义提供了很大的帮助。然而在线重定义的作用还远远不止于此。
-------END-------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30820196/viewspace-2132866/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30820196/viewspace-2132866/