關于oracle高水位（high water mark)的一些思考

原創 cqiwen 2022-04-24

4589

繼上次發現某數據庫表空間異常增長，然后通過一些手段找出問題之后（詳見文章：記一次對oracle數據庫表空間異常增長的分析和處理）。我發現很多人對高水位的理解也不是很清晰，因此本文深入分析一下表產生高水位的原因，高水位下空塊情況，以及為什么收縮表之后仍然在dba_tables中仍然能查到不少空塊的原因。

一、清除表的高水位線

1）通過exp/imp和expdp/impdp方式清除表的高水位線

實測通過exp/imp和expdp/impdp方式并不一定能清除表的高水位線。
這個大家可以自己做下實驗，我測試了幾種情況，有些表能降低高水位，有些表不能清除表的高水位。如下測試時沒有清除掉高水位：

實驗用到的表，我已經使用exp導出來了，歡迎大家下載測試！下載地址：high_wmk_1.dmp

SQL> select * from cat;

TABLE_NAME		       TABLE_TYPE
------------------------------ -----------
HIGH_WMK_1		       TABLE

SQL> analyze table HIGH_WMK_1 compute statistics;

Table analyzed.

SQL> select segment_name,partition_name,round(bytes/1024/1024,2) size_mb from user_segments where segment_name='HIGH_WMK_1';

SEGMENT_NAME           PARTITION_NAME			      SIZE_MB
------------------- -------------------     ----------
HIGH_WMK_1                                        88

SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='HIGH_WMK_1';

TABLE_NAME			 NUM_ROWS   BLOCKS    EMPTY_BLOCKS
-------------- ---------- ---------- ------------
HIGH_WMK_1				1	           222	    11042

exp導出：
$ exp test/test file=exp_high_wmk_1.dmp log=exp_high_wmk_1.log statistics=none tables=high_wmk_1

Export: Release 11.2.0.3.0 - Production on Mon Apr 25 07:48:28 2022

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 AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                     HIGH_WMK_1          1 rows exported
Export terminated successfully without warnings.

先進入test用戶下收縮原表高水位，查看收縮情況并刪除原表：
SQL> alter table high_wmk_1 enable row movement;

Table altered.

SQL> alter table high_wmk_1 shrink space;

Table altered.

SQL> analyze table HIGH_WMK_1 compute statistics;

Table analyzed.

SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='HIGH_WMK_1';

TABLE_NAME			 NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
HIGH_WMK_1				1	  21	       35

SQL> drop table HIGH_WMK_1 purge;

Table dropped.

然后使用imp重新導入：
$ imp test/test file=exp_high_wmk_1.dmp log=imp_high_wmk_1.log full=y

Import: Release 11.2.0.3.0 - Production on Mon Apr 25 07:56:03 2022

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 AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. importing TEST's objects into TEST
. . importing table                   "HIGH_WMK_1"          1 rows imported
Import terminated successfully without warnings.

$ sqlplus test/test

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 25 07:56:24 2022

Copyright (c) 1982, 2011, Oracle.  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

--收集統計信息
SQL> analyze table HIGH_WMK_1 compute statistics;

Table analyzed.

--查看空塊數量
SQL> select table_name,num_rows,blocks,empty_blocks from user_tables where table_name='HIGH_WMK_1';

TABLE_NAME			 NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ---------- ------------
HIGH_WMK_1				1	 222	    11042

SQL> select count(*) from HIGH_WMK_1;

  COUNT(*)
----------
	 1

可見，通過exp/imp方式并沒有清除表的高水位線。

2）通過移動表空間方式降低高水位
注：在操作之前，先對有高水位的表做一次exp導出，為之后的實驗做準備。

SQL> select num_rows, blocks, empty_blocks from dba_tables where table_name='CHONGZHI_LIST';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------

SQL> analyze table chongzhi_list compute statistics;

Table analyzed.

--查看當前表中是否有高水位
SQL> select num_rows, blocks, empty_blocks from dba_tables where table_name='CHONGZHI_LIST';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
      1722	  222	     19234

從查詢中可知，該表有19234個空塊，也就是肯定有高水位。

--查詢表實際有數據的塊共有多少個
SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)||'-'||dbms_rowid.rowid_relative_fno (rowid)) "used blocks" from CHONGZHI_LIST;

used blocks
-----------
	 40

SQL> alter table CHONGZHI_LIST enable row movement;

Table altered.

SQL> alter table CHONGZHI_LIST shrink space;

Table altered.

注：如果加上cascade數據，則會連同index占用的空間也一起收縮 ALTER TABLE user_name.table_name SHRINK SPACE CASCADE;

SQL> alter table CHONGZHI_LIST disable row movement;

Table altered.

SQL> analyze table chongzhi_list compute statistics;

Table analyzed.

SQL> select num_rows, blocks, empty_blocks from dba_tables where table_name='CHONGZHI_LIST';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
      1722	   41		39

收縮后，查詢到EMPTY_BLOCKS已經降下來了。

--查詢指定的表的數據實際占用了多少個塊
SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)||'-'||dbms_rowid.rowid_relative_fno (rowid)) "used blocks" from CHONGZHI_LIST;

used blocks
-----------
	 40