ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > oracle 高水位线及如何有效的降低高水位线

oracle 高水位线及如何有效的降低高水位线

原创 Oracle 作者：dbLjy2015 时间：2017-01-24 10:19:52 0 删除 编辑
一、什么是水线(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会自动增大。


三、查找哪些表需要降低高水位线
想要确定哪些表需要降低高水位线，要基于有正确的统计信息的，统计信息的错误也将导致结果的错误。

  1. SYS@prod > SELECT NUM_ROWS,
  2.   2 AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9 NEED,
  3.   3 BLOCKS * 8 / 1024 TRUE,
  4.   4 (BLOCKS * 8 / 1024 - AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9) RECOVER_MB,
  5.   5 TABLE_NAME
  6.   6 FROM dba_tables
  7.   7 WHERE tablespace_name = 'VASTTBS'
  8.   8 AND BLOCKS * 8 / 1024 - AVG_ROW_LEN * NUM_ROWS / 1024 / 1024 / 0.9 > 100
  9.   9 AND rownum < 11
  10.  10 order by RECOVER_MB desc;
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. SYS@prod > select * from v$version;
  2. BANNER
  3. --------------------------------------------------------------------------------
  4. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  5. PL/SQL Release 11.2.0.3.0 - Production
  6. CORE 11.2.0.3.0 Production
  7. TNS for Linux: Version 11.2.0.3.0 - Production
  8. NLSRTL Version 11.2.0.3.0 - Production

实验环境模拟
  1. --创建一张表
  2. VAST@prod > create table ote as select * from dba_objects;
  3. Table created.
  4. --创建索引
  5. VAST@prod > create index ind_ote_id on ote(object_id);
  6. Index created.
  7. --创建同义词
  8. VAST@prod > create or replace public synonym ote for vast.ote;
  9. Synonym created.
  10. --收集统计信息
  11. VAST@prod > ANALYZE TABLE OTE COMPUTE STATISTICS; --ESTIMATE/COMPUTE
  12. Table analyzed.
  13. --段的信息
  14. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  15. BYTES/1048576 BLOCKS EXTENTS
  16. ------------- ---------- ----------
  17.             9 1152 24
  18. --删除表中数据
  19. VAST@prod > delete from ote;
  20. 75355 rows deleted.
  21. VAST@prod > commit;
  22. Commit complete.
  23. --段的信息
  24. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  25. BYTES/1048576 BLOCKS EXTENTS
  26. ------------- ---------- ----------
  27.             9 1152 24
  28. --分析表块数，空闲块数，行数
  29. VAST@prod > SELECT blocks, empty_blocks, num_rows
  30.   2 FROM user_tables
  31.   3 WHERE table_name = 'OTE';
  32.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  33. ---------- ------------ ----------
  34.       1099           53      75355
  35. --BLOCKS + EMPTY_BLOCKS (1099+53=1152)DBA_SEGMENTS.BLOCKS 表示分配给这个表的所有的数据库块的数目。
  36. --USER_TABLES.BLOCKS表示已经使用过的数据库块的数目。USER_TABLES.EMPTY_BLOCKS则表示剩余块的数目。
  37. --插入数据
  38. VAST@prod > insert into ote select * from dba_objects where object_id<20000;
  39. 19677 rows created.
  40. VAST@prod > commit;
  41. Commit complete.
  42. --段的信息
  43. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  44. BYTES/1048576 BLOCKS     EXTENTS
  45. ------------- ---------- ----------
  46.             9       1152         24
  47. --可见即便插入了数据，但是分给OTE段的块数目没有改变，这是因为之前删除了一部分数据，oracle在插入的时候，找到了这些可以被覆盖的块，
  48. --将这些数据插入到这些块中，故没有分配新的块给OTE段
  49. --分析表块数，空闲块数，行数
  50. VAST@prod > SELECT blocks, empty_blocks, num_rows
  51.   2 FROM user_tables
  52.   3 WHERE table_name = 'OTE';
  53.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  54. ---------- ------------ ----------
  55.       1099           53      75355
  56. --使用hint
  57. --使用append将会把新插入的数据插到高水位线之后，省去了查找可覆盖块的时间，这种方式也可提高插入的速度，但是会提升高水位线的高度。
  58. VAST@prod > insert /*+append*/ into ote select * from dba_objects where object_id<20000;
  59. 19677 rows created.
  60. VAST@prod > commit;
  61. Commit complete.
  62. --段的信息
  63. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  64. BYTES/1048576 BLOCKS     EXTENTS
  65. ------------- ---------- ----------
  66.            11       1408         26
  67. --可见这次没有查找可覆盖的块，而是直接将数据插在了高水位线之后的空闲块中。        
  68. --收集统计信息
  69. VAST@prod > ANALYZE TABLE OTE COMPUTE STATISTICS; --ESTIMATE/COMPUTE
  70. Table analyzed.
  71. --分析表块数，空闲块数，行数
  72. VAST@prod > SELECT blocks, empty_blocks, num_rows
  73.   2 FROM user_tables
  74.   3 WHERE table_name = 'OTE';
  75.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  76. ---------- ------------ ----------
  77.       1370           38      39354
  78. --对表的信息查看时，建议先进行统计信息的收集，不然会得到错误的信息。

1、truncate 表
  1. --截断表
  2. VAST@prod > truncate table ote;
  3. Table truncated.
  4. --收集统计信息
  5. VAST@prod > analyze table ote compute statistics; --estimate/compute
  6. Table analyzed.
  7. --段的信息
  8. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  9. BYTES/1048576 BLOCKS     EXTENTS
  10. ------------- ---------- ----------
  11.         .0625          8          1
  12. --此时可见OTE的段只剩下8个块了，因为表还是存在的，段头还是记录了一些基本信息的。        
  13. --分析表块数，空闲块数，行数
  14. VAST@prod > SELECT blocks, empty_blocks, num_rows
  15.   2 FROM user_tables
  16.   3 WHERE table_name = 'OTE';
  17.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  18. ---------- ------------ ----------
  19.          0            8          0
  20. --检查索引以及同义词是否可用
  21. VAST@prod > col name for a15;
  22. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
  23.   2 union all
  24.   3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';

  26. TYPE    NAME            STATUS
  27. ------- --------------- --------
  28. INDEX   IND_OTE_ID      VALID
  29. SYNONYM IND_OTE_ID      VALID        
  30. --可见此种方式执行过后，原索引以及同义词还是可用的。
优点：速度快
缺点：表中数据全无
建议：当需要delete全表的时候，确定表中数据无用，可以使用这种方式进行清理。切记，谨慎。


接下来的六种方式，均采用清理表中部分数据，与开始对全表进行delete略有不同。


2、开启表的shrink
  1. --开启行迁移
  2. VAST@prod > alter table ote enable row movement;
  3. Table altered.
  4. --查看表的状态
  5. VAST@prod > select table_name,
  6.   2 tablespace_name,
  7.   3 ROW_MOVEMENT,
  8.   4 read_only,
  9.   5 segment_created
  10.   6 from user_tables
  11.   7 where table_name = 'OTE';
  12. TABLE_NAME                     TABLESPACE_NAME                ROW_MOVE REA SEG
  13. ------------------------------ ------------------------------ -------- --- ---
  14. OTE                            VASTTBS                        ENABLED  NO  YES
  15. --开启shrink
  16. VAST@prod > alter table ote shrink space;
  17. Table altered.
  18. --关闭行迁移
  19. VAST@prod > alter table ote disable row movement;
  20. Table altered.
  21. --查看表的状态
  22. VAST@prod > select table_name,
  23.   2 tablespace_name,
  24.   3 ROW_MOVEMENT,
  25.   4 read_only,
  26.   5 segment_created
  27.   6 from user_tables
  28.   7 where table_name = 'OTE';
  29. TABLE_NAME                     TABLESPACE_NAME                ROW_MOVE REA SEG
  30. ------------------------------ ------------------------------ -------- --- ---
  31. OTE                            VASTTBS                        DISABLED NO YES
  32. --收集统计信息
  33. VAST@prod > analyze table ote compute statistics;
  34. Table analyzed.
  35. --段的信息
  36. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  37. BYTES/1048576 BLOCKS     EXTENTS
  38. ------------- ---------- ----------
  39.         3.375        432         19
  40. --分析表块数，空闲块数，行数
  41. VAST@prod > SELECT blocks, empty_blocks, num_rows
  42.   2 FROM user_tables
  43.   3 WHERE table_name = 'OTE';

  45.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  46. ---------- ------------ ----------
  47.        413           19      29678
  48. --检查索引以及同义词是否可用
  49. VAST@prod > col name for a15;
  50. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
  51.   2 union all
  52.   3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';

  54. TYPE    NAME            STATUS
  55. ------- --------------- --------
  56. INDEX   IND_OTE_ID      VALID
  57. SYNONYM IND_OTE_ID      VALID
优点：对于表中已清除部分数据依然可用，
缺点：暂不详

3、CTAS
  1. --创建一张新表
  2. VAST@prod > create table ote1 as select * from ote;
  3. Table created.
  4. --收集统计信息
  5. VAST@prod > analyze table ote1 compute statistics;
  6. Table analyzed.
  7. --段的信息
  8. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE1';
  9. BYTES/1048576 BLOCKS EXTENTS
  10. ------------- ---------- ----------
  11.             4        512         19
  12. --分析表块数，空闲块数，行数
  13. VAST@prod > SELECT blocks, empty_blocks, num_rows
  14.   2 FROM user_tables
  15.   3 WHERE table_name = 'OTE1';
  16.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  17. ---------- ------------ ----------
  18.        429           83      29678
  19. --检查索引以及同义词是否可用
  1. 原索引以及同义词依然可用，但是在同义词以及索引是指向原OTE表。
  2. 建议：对于这种情况，可以在业务可以停止的时候进行，在ote1表新建对应索引，对应同义词等。然后rename OTE表，再将OTE1 rename为OTE。又或者将CTAS后将原表truncate，再将数据导入。

4、使用table move的方式
  1. --使用table move的方式
  2. VAST@prod > alter table ote move tablespace vasttbs;
  3. Table altered.
  4. --收集统计信息
  5. VAST@prod > analyze table ote compute statistics;
  6. Table analyzed.
  7. --段的信息
  8. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  9. BYTES/1048576 BLOCKS     EXTENTS
  10. ------------- ---------- ----------
  11.             4        512         19
  12. --分析表块数，空闲块数，行数
  13. VAST@prod > SELECT blocks, empty_blocks, num_rows
  14.   2 FROM user_tables
  15.   3 WHERE table_name = 'OTE';
  16.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  17. ---------- ------------ ----------
  18.        429           83      29678
  19. --检查索引以及同义词是否可用
  20. VAST@prod > col name for a15;
  21. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
  22.   2 union all
  23.   3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
  24. TYPE    NAME            STATUS
  25. ------- --------------- --------
  26. INDEX   IND_OTE_ID      UNUSABLE
  27. SYNONYM IND_OTE_ID      VALID
  28. 发现这时,索引已经失效，对索引进行在线重建。
  29. --在线重建索引
  30. VAST@prod > alter index ind_ote_id rebuild online;
  31. Index altered.
  32. --检查索引以及同义词是否可用
  33. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
  34.   2 union all
  35.   3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
  36. TYPE    NAME            STATUS
  37. ------- --------------- --------
  38. INDEX   IND_OTE_ID      VALID
  39. SYNONYM IND_OTE_ID      VALID
优点：降低了高水位线
缺点：因为表的移动，需要将索引进行重建

5、exp/imp
  1. --exp/imp降低高水位线
  2. [oracle@vedeu ~]$ exp vast/vast file=/home/oracle/ote.dmp tables=ote log=ote.log
  3. Export: Release 11.2.0.3.0 - Production on Mon Jan 23 11:54:49 2017
  4. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
  5. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  6. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  7. Export done in US7ASCII character set and AL16UTF16 NCHAR character set
  8. server uses AL32UTF8 character set (possible charset conversion)
  9. About to export specified tables via Conventional Path ...
  10. . . exporting table OTE 29678 rows exported
  11. EXP-00091: Exporting questionable statistics.
  12. EXP-00091: Exporting questionable statistics.
  13. Export terminated successfully with warnings.
  14. --删除ote表
  15. VAST@prod > drop table ote purge;
  16. Table dropped.
  17. --导入表
  18. [oracle@vedeu ~]$ imp vast/vast file=/home/oracle/ote.dmp full=y
  19. Import: Release 11.2.0.3.0 - Production on Mon Jan 23 11:56:23 2017
  20. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
  21. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  22. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  24. Export file created by EXPORT:V11.02.00 via conventional path
  25. import done in US7ASCII character set and AL16UTF16 NCHAR character set
  26. import server uses AL32UTF8 character set (possible charset conversion)
  27. . importing VAST's objects into VAST
  28. . importing VAST's objects into VAST
  29. . . importing table "OTE" 29678 rows imported
  30. Import terminated successfully without warnings.
  31. --收集统计信息
  32. VAST@prod > analyze table ote compute statistics;
  33. Table analyzed.
  34. --段的信息
  35. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  36. BYTES/1048576 BLOCKS     EXTENTS
  37. ------------- ---------- ----------
  38.            4         512         19
  39. --分析表块数，空闲块数，行数
  40. VAST@prod > SELECT blocks, empty_blocks, num_rows
  41. 2 FROM user_tables
  42. 3 WHERE table_name = 'OTE';
  43. BLOCKS EMPTY_BLOCKS NUM_ROWS
  44. ---------- ------------ ----------
  45.        496           16      29678
  46. --检查索引以及同义词是否可用
  47. VAST@prod > col name for a15;
  48. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
  49. 2 union all
  50. 3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
  52. TYPE    NAME            STATUS
  53. ------- --------------- --------
  54. INDEX   IND_OTE_ID      VALID
  55. SYNONYM IND_OTE_ID      VALID

6、expdp/impdp
    expdp/impdp这种方式也可以降低高水位线，方法类似exp/imp就不在做介绍。
7、在线重定义
  1. --在线重定义
  2. 在线重定义同时也是我比较喜欢的一种方式，因为它可以不停业务，就完成降低高水位线。
  3. --设定主键
  4. VAST@prod > alter table ote add constraint pk_ote_id primary key (object_id);
  5. Table altered.
  6. --检查是否可以在线重定义
  7. VAST@prod > exec dbms_redefinition.can_redef_table( 'VAST','OTE',dbms_redefinition.cons_use_pk);
  8. PL/SQL procedure successfully completed.
  9. --定义中间表
  10. VAST@prod > create table ote_interim as select * from ote where 1=0;
  11. Table created
  12. --开始在线重定义
  13. VAST@prod > exec dbms_redefinition.start_redef_table('VAST','OTE','OTE_INTERIM',
  14.                                                    col_mapping => 'OWNER OWNER,
  15.                                                                    OBJECT_NAME OBJECT_NAME,
  16.                                                                    SUBOBJECT_NAME SUBOBJECT_NAME,
  17.                                                                    OBJECT_ID OBJECT_ID,DATA_OBJECT_ID DATA_OBJECT_ID,
  18.                                                                    OBJECT_TYPE OBJECT_TYPE,
  19.                                                                    CREATED CREATED,
  20.                                                                    LAST_DDL_TIME LAST_DDL_TIME,
  21.                                                                    TIMESTAMP TIMESTAMP,
  22.                                                                    STATUS STATUS,
  23.                                                                    TEMPORARY TEMPORARY,
  24.                                                                    GENERATED GENERATED,
  25.                                                                    SECONDARY SECONDARY,
  26.                                                                    NAMESPACE NAMESPACE,
  27.                                                                    EDITION_NAME EDITION_NAME',
  28.                                                     options_flag => dbms_redefinition.cons_use_pk);
  29. PL/SQL procedure successfully completed.
  30. --处理索引以及约束对象
  31. VAST@prod > declare
  32.   2 error_count number;
  33.   3 begin
  34.   4 error_count := 0;
  35.   5 dbms_redefinition.copy_table_dependents(uname => 'VAST',orig_table => 'OTE',
  36.   6 int_table => 'OTE_INTERIM',
  37.   7 copy_indexes => dbms_redefinition.cons_orig_params,
  38.   8 num_errors => error_count);
  39.   9 dbms_output.put_line(to_char(error_count));
  40.  10 end;
  41.  11 /
  42. PL/SQL procedure successfully completed.
  43. --结束在线重定义
  44. VAST@prod > exec dbms_redefinition.finish_redef_table('VAST','OTE','OTE_INTERIM');
  45. PL/SQL procedure successfully completed.
  46. --收集统计信息
  47. VAST@prod > analyze table ote compute statistics;
  48. Table analyzed.
  49. --段的信息
  50. VAST@prod > select bytes/1048576,blocks,extents from dba_segments where segment_name='OTE';
  51. BYTES/1048576 BLOCKS     EXTENTS
  52. ------------- ---------- ----------
  53.             4        512         19
  54. --分析表块数，空闲块数，行数
  55. VAST@prod > SELECT blocks, empty_blocks, num_rows
  56.   2 FROM user_tables
  57.   3 WHERE table_name = 'OTE';
  58.     BLOCKS EMPTY_BLOCKS NUM_ROWS
  59. ---------- ------------ ----------
  60.        429           83      29678
  61. --检查索引以及同义词是否可用
  62. col name for a15;
  63. VAST@prod > select 'INDEX' type,index_name name,status from dba_indexes where index_name='IND_OTE_ID'
  64.   2 union all
  65.   3 select 'SYNONYM',object_name,status from user_objects where object_name='IND_OTE_ID';
  66. TYPE    NAME            STATUS
  67. ------- --------------- --------
  68. INDEX   IND_OTE_ID      VALID
  69. SYNONYM IND_OTE_ID      VALID
优点：降低高水位线的同时，它是这七种办法中唯一不需要停业务的方式。
建议：生产中大多数时候是没办法停止的，即便有停机时间，还是有其他好多操作要进行，在线重定义提供了很大的帮助。然而在线重定义的作用还远远不止于此。


-------END-------

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30820196/viewspace-2132866/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： RESIZE DATAFILE与ORA-03297
下一篇： Linux terminal 终端常用快捷键
请登录后发表评论 登录
全部评论
dbLjy2015

注册时间：2016-01-27

  • 博文量
    24
  • 访问量
    25556

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593