一、高水位线是什么？
Oracle的每一个段segment，都有一个容纳数据的上限，我们把这个上限称为"high water mark"——HWM高水位。
HWM用来说明在这个segment中未被使用的数据库的数量。HWM通常增长的幅度为一次5个数据块，原则上高水位线在日常的增删操作中只会上涨，不会下跌，即使将表中的数据全部删除，HWM还是为原值（truncate除外），由于这个特点，使HWM很象一个水库的历史最高水位，这也就是HWM的原始含义。
二、高水位线的影响：
-
全表扫描通常要读出HWM以下的所有的属于该表数据库块，即使该表中没有任何数据。
-
即使HWM以下有空闲的数据库块，键入在插入数据时使用了append关键字，则在插入时使用HWM以上的数据块，此时HWM会自动增大。
三、降低高水位线的方法
在ORACLE中，执行对表的删除操作不会降低该表的高水位线。而全表扫描将始终读取一个段(extent)中所有低于高水位线标记的块。如果在执行删除操作后不降低高水位线标记，则将导致查询语句的性能低下。
下面的方法都可以降低高水位线标记。
-
表移动
alter table table_name move [tablespace_name tablespace_name]; -
表收缩
alter table table_name shrink space;
注意，执行该指令之前必须允许表的行移动
alter table table_name enable row movement; -
表重建
create table t1 as select * from t;
drop table t;
alter table t1 rename to t; -
emp/imp
-
alter table table_name deallocate unused
注意：deallocate 仅适用于释放HWM高水位以上的空间，而无法释放高水位以下的空间；比如对表预分配的空间 -
truncate table_name
接下来讲一下日常中经常使用的shrink space的方式进行高水位线的降低。
四、环境数据模拟
1、创建分区表
SQL> create table t_user.t_par
2 (id number,
3 inc_datetime varchar2(19),
4 random_id number,
5 random_string varchar2(60)
6 )
7 partition by range (id)
8 (
9 partition p_01 values less than(200000),
10 partition p_02 values less than(400000),
11 partition p_03 values less than(600000),
12 partition p_04 values less than(800000),
13 partition p_05 values less than(1000000),
14 partition p_max values less than(maxvalue)
15 );
Table created.
SQL>
2、插入测试数据
SQL> insert into t_par
2 select rownum as id,
3 to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,
4 trunc(dbms_random.value(0, 100)) as random_id,
5 dbms_random.string('x', 20) random_string
6 from dual
7 connect by level <= 1500000;
1500000 rows created.
SQL>
3、查询数据分布
SQL> select count(*) from t_par partition(p_01);
COUNT(*)
----------
199999
SQL> select count(*) from t_par partition(p_02);
COUNT(*)
----------
200000
SQL> select count(*) from t_par partition(p_03);
COUNT(*)
----------
200000
SQL> select count(*) from t_par partition(p_04);
COUNT(*)
----------
200000
SQL> select count(*) from t_par partition(p_05);
COUNT(*)
----------
200000
SQL> select count(*) from t_par partition(p_max);
COUNT(*)
----------
500001
SQL>
4、收集统计信息
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'T_USER',tabname=>'T_PAR',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,method_opt=>'for all indexed columns',cascade=>true,force=>true,degree=>10); PL/SQL procedure successfully completed. SQL>
5、查看高水位
SQL> select table_name,partition_name,
2 ROUND ( (blocks * 8), 2) "高水位空间 k",
3 ROUND ( (num_rows * avg_row_len / 1024), 2) "真实使用空间 k",
4 ROUND ( (blocks * 10 / 100) * 8, 2) "预留空间(pctfree) k",
5 ROUND ( (blocks * 8 - (num_rows * avg_row_len / 1024) - blocks * 8 * 10 / 100),2) "浪费空间 k"
6 from dba_tab_partitions where lower(table_name)='t_par'
7 ORDER BY 5 DESC;
TABLE_NAME PARTITION_NAME 高水位空间 k 真实使用空间 k 预留空间(pctfree) k 浪费空间 k
------------------------------ ------------------------------ ----------------- -------------------- ----------------------- -------------
T_PAR P_MAX 32240 24414.11 3224 4601.89
T_PAR P_02 16112 9570.31 1611.2 4930.49
T_PAR P_04 16112 9570.31 1611.2 4930.49
T_PAR P_05 16112 9570.31 1611.2 4930.49
T_PAR P_01 16112 9570.26 1611.2 4930.54
T_PAR P_03 16112 9570.31 1611.2 4930.49
6 rows selected.
SQL>
五、模拟高水位
1、删除ID为偶数数据
SQL> delete from t_par where mod(id,2) = 0;
750000 rows deleted.
SQL>
2、查看高水位
SQL> select table_name,partition_name,
2 ROUND ( (blocks * 8), 2) "高水位空间 k",
3 ROUND ( (num_rows * avg_row_len / 1024), 2) "真实使用空间 k",
4 ROUND ( (blocks * 10 / 100) * 8, 2) "预留空间(pctfree) k",
5 ROUND ( (blocks * 8 - (num_rows * avg_row_len / 1024) - blocks * 8 * 10 / 100),2) "浪费空间 k"
6 from dba_tab_partitions where lower(table_name)='t_par'
7 ORDER BY 5 DESC;
TABLE_NAME PARTITION_NAME 高水位空间 k 真实使用空间 k 预留空间(pctfree) k 浪费空间 k
------------------------------ ------------------------------ ----------------- -------------------- ----------------------- -------------
T_PAR P_MAX 32240 24414.11 3224 4601.89
T_PAR P_02 16112 9570.31 1611.2 4930.49
T_PAR P_04 16112 9570.31 1611.2 4930.49
T_PAR P_05 16112 9570.31 1611.2 4930.49
T_PAR P_01 16112 9570.26 1611.2 4930.54
T_PAR P_03 16112 9570.31 1611.2 4930.49
6 rows selected.
SQL>
3、收集统计信息
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'T_USER',tabname=>'T_PAR',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,method_opt=>'for all indexed columns',cascade=>true,force=>true,degree=>10); PL/SQL procedure successfully completed. SQL>
4、查看高水位
SQL> select table_name,partition_name,
2 ROUND ( (blocks * 8), 2) "高水位空间 k",
3 ROUND ( (num_rows * avg_row_len / 1024), 2) "真实使用空间 k",
4 ROUND ( (blocks * 10 / 100) * 8, 2) "预留空间(pctfree) k",
5 ROUND ( (blocks * 8 - (num_rows * avg_row_len / 1024) - blocks * 8 * 10 / 100),2) "浪费空间 k"
6 from dba_tab_partitions where lower(table_name)='t_par'
7 ORDER BY 5 DESC;
TABLE_NAME PARTITION_NAME 高水位空间 k 真实使用空间 k 预留空间(pctfree) k 浪费空间 k
------------------------------ ------------------------------ ----------------- -------------------- ----------------------- -------------
T_PAR P_MAX 32240 12207.03 3224 16808.97
T_PAR P_02 16112 4785.16 1611.2 9715.64
T_PAR P_04 16112 4785.16 1611.2 9715.64
T_PAR P_05 16112 4785.16 1611.2 9715.64
T_PAR P_01 16112 4785.16 1611.2 9715.64
T_PAR P_03 16112 4785.16 1611.2 9715.64
6 rows selected.
SQL>
六、降低高水位
1、开启行移动
SQL> alter table t_par enable row movement;
Table altered.
SQL>
2、shrink高水位
SQL> alter table t_par modify partition p_01 shrink space cascade;
Table altered.
SQL>
3、收集统计信息
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'T_USER',tabname=>'T_PAR',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,method_opt=>'for all indexed columns',cascade=>true,force=>true,degree=>10); PL/SQL procedure successfully completed. SQL>
4、查看高水位
SQL> select table_name,partition_name,
2 ROUND ( (blocks * 8), 2) "高水位空间 k",
3 ROUND ( (num_rows * avg_row_len / 1024), 2) "真实使用空间 k",
4 ROUND ( (blocks * 10 / 100) * 8, 2) "预留空间(pctfree) k",
5 ROUND ( (blocks * 8 - (num_rows * avg_row_len / 1024) - blocks * 8 * 10 / 100),2) "浪费空间 k"
6 from dba_tab_partitions where lower(table_name)='t_par'
7 ORDER BY 5 DESC;
TABLE_NAME PARTITION_NAME 高水位空间 k 真实使用空间 k 预留空间(pctfree) k 浪费空间 k
------------------------------ ------------------------------ ----------------- -------------------- ----------------------- -------------
T_PAR P_MAX 32240 12207.03 3224 16808.97
T_PAR P_02 16112 4785.16 1611.2 9715.64
T_PAR P_03 16112 4785.16 1611.2 9715.64
T_PAR P_04 16112 4785.16 1611.2 9715.64
T_PAR P_05 16112 4785.16 1611.2 9715.64
T_PAR P_01 5368 4785.16 536.8 46.04
6 rows selected.
SQL>
这里可以看到分区表P_01高水位已经从16112降低到了5368.
5、剩余分区降低高水位
SQL> alter table t_par modify partition p_02 shrink space cascade;
Table altered.
SQL> alter table t_par modify partition p_03 shrink space cascade;
Table altered.
SQL> alter table t_par modify partition p_04 shrink space cascade;
Table altered.
SQL> alter table t_par modify partition p_05 shrink space cascade;
Table altered.
SQL> alter table t_par modify partition p_max shrink space cascade;
Table altered.
SQL>
6、收集统计信息
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'T_USER',tabname=>'T_PAR',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size,method_opt=>'for all indexed columns',cascade=>true,force=>true,degree=>10); PL/SQL procedure successfully completed. SQL>
7、查看高水位
SQL> select table_name,partition_name,
2 ROUND ( (blocks * 8), 2) "高水位空间 k",
3 ROUND ( (num_rows * avg_row_len / 1024), 2) "真实使用空间 k",
4 ROUND ( (blocks * 10 / 100) * 8, 2) "预留空间(pctfree) k",
5 ROUND ( (blocks * 8 - (num_rows * avg_row_len / 1024) - blocks * 8 * 10 / 100),2) "浪费空间 k"
6 from dba_tab_partitions where lower(table_name)='t_par'
7 ORDER BY 5 DESC;
TABLE_NAME PARTITION_NAME 高水位空间 k 真实使用空间 k 预留空间(pctfree) k 浪费空间 k
------------------------------ ------------------------------ ----------------- -------------------- ----------------------- -------------
T_PAR P_MAX 13704 12207.03 1370.4 126.57
T_PAR P_02 5376 4785.16 537.6 53.24
T_PAR P_03 5376 4785.16 537.6 53.24
T_PAR P_04 5376 4785.16 537.6 53.24
T_PAR P_05 5376 4785.16 537.6 53.24
T_PAR P_01 5368 4785.16 536.8 46.04
6 rows selected.
SQL>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
