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

一、什么是水线(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';
    
25. 
    
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';
    
44. 
    
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';
    
53. 
    
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博客 ” ，链接：http://blog.itpub.net/30820196/viewspace-2132866/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30820196/viewspace-2132866/