Oracle 降低高水位線的方法

發布時間：2020-08-11 10:11:19 來源：ITPUB博客閱讀：409 作者：zhjd1985 欄目：關系型數據庫

Oracle 降低高水位線的方法

高水位（HIGH WARTER MARK，HWM）好比水庫中儲水的水位，用于描述數據庫中段的擴展方式。高水位對全表掃描方式有著至關重要的影響。當使用DELETE刪除表記錄時，高水位并不會下降，隨之導致的是全表掃描的實際開銷并沒有任何減少。

例如，首先新建一張空表，大小占用64K，然后插入數據直到表大小變為50G，此時使用DELETE刪除所有的數據并且提交，這個時候查詢表的大小的時候依然是50G，這就是因為表的高水位沒有釋放的緣故，而在這時如果使用“SELECT * FROM TABLE_NAME;”語句來查詢數據的話，那么查詢過程就會很慢，因為Oracle要執行全表掃描，從高水位下所有的塊都得去掃描，直到50G的所有塊全部掃描完畢。曾遇到一個同事使用DELETE刪除了一個很大的分區表，然后執行SELECT查詢很久都沒有結果，以為是數據庫HANG住了，其實這個問題是由于高水位的緣故。所以，表執行了TRUNCATE操作，再次SELECT的時候就可以很快返回結果了。

釋放表的高水位通常有如下幾種辦法：

（1）對表進行MOVE操作：ALTER TABLE TABLE_NAME MOVE;。若表上存在索引，則記得重建索引。

（2）對表進行SHRINK SPACE操作：ALTER TABLE TABLE_NAME SHRINK SPACE;，注意，在執行該指令之前必須開啟行移動：ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;。該方法的優點是：在碎片整理結束后，表上相關的索引仍然有效，缺點是會產生大量的UNDO和REDO。

（3）復制要保留的數據到臨時表T，DROP原表，然后RENAME臨時表T為原表。

（4）exp/imp或expdp/impdp重構表。

（5）若表中沒有數據則直接使用TRUNCATE來釋放高水位。

如何找出系統中哪些表擁有高水位呢？這里給出兩種辦法，①比較表的行數和表的大小關系。如果行數為0，而表的當前占用大小減去初始化時的大小（INITIAL_EXTENT）后依然很大，那么說明該表有高水位。②行數和塊數的比率，即查看一個塊可以存儲多少行數據。如果一個塊存儲的行數少于5行甚至更少，那么說明有高水位。注意，這兩種方法都不是十分準確，需要再對查詢結果進行篩選。需要注意的是，在查詢表的高水位時，首先需要分析表，以得到最準確的統計信息。

下面給出用于查詢高水位的幾個SQL語句：

Segment Space and the High Water Mark

To manage space, Oracle Database tracks the state of blocks in the segment. The high water mark (HWM) is the point in a segment beyond which data blocks are unformatted and have never been used.

MSSM uses free lists to manage segment space. At table creation, no blocks in the segment are formatted. When a session first inserts rows into the table, the database searches the free list for usable blocks. If the database finds no usable blocks, then it preformats a group of blocks, places them on the free list, and begins inserting data into the blocks. In MSSM, a full table scan reads allblocks below the HWM.

ASSM does not use free lists and so must manage space differently. When a session first inserts data into a table, the database formats a single bitmap block instead of preformatting a group of blocks as in MSSM. The bitmap tracks the state of blocks in the segment, taking the place of the free list. The database uses the bitmap to find free blocks and then formats each block before filling it with data. ASSM spread out inserts among blocks to avoid concurrency issues.

Every data block in an ASSM segment is in one of the following states:

Above the HWM

These blocks are unformatted and have never been used.
Below the HWM

These blocks are in one of the following states:
- Allocated, but currently unformatted and unused
- Formatted and contain data
- Formatted and empty because the data was deleted

Figure 12-23 depicts an ASSM segment as a horizontal series of blocks. At table creation, the HWM is at the beginning of the segment on the left. Because no data has been inserted yet, all blocks in the segment are unformatted and never used.

Figure 12-23 HWM at Table Creation

Description of "Figure 12-23 HWM at Table Creation"

段空間和高水位標記

oracle數據庫通過跟蹤段中的塊狀態來管理空間。高水位標記(HWM)是段中的一個點，超過該點的數據塊是未格式化和未使用過的。

MSSM使用空閑列表來管理段空間。在創建表時，段中的塊并未被格式化。當一個會話初次向表中插入行時，數據庫將搜索空閑列表來查找可用的塊。如果數據庫未找到可用的塊，那么它會預格式化一組塊，并將它們放置在空閑列表中，并開始將數據插入到塊中。在MSSM中，全表掃描會讀取HWM之下的所有塊。

ASSM不使用空閑列表，所以必須以不同的方式管理空間。當會話初次向表中插入數據時，數據庫只格式化一個單一位圖塊，而不像在MSSM中那樣預格式化一組塊。位圖取代了空閑列表，用于跟蹤在段中的塊的狀態。數據庫使用位圖來查找可用的塊，然后在往塊寫入數據之前將其格式化。ASSM將插入操作分散到多個塊，以避免并發問題。

在一個ASSM段中的每個數據塊處于以下狀態之一：

l 在HWM之上

這些塊是未格式化的，且從未使用過。

l 在HWM之下

這些塊處于以下狀態之一：

u 已分配，但當前未格式化且未使用

u 已格式化且包含數據

u 已格式化且為空，因為數據已被刪除

圖12-23將一個ASSM段描述為一系列水平的塊。在創建表時，HWM在左側段的開頭。因為還未插入數據，段中的所有塊都還是未格式化且從未使用過。

圖將12-23在創建表時的HWM

Suppose that a transaction inserts rows into the segment. The database must allocate a group of blocks to hold the rows. The allocated blocks fall below the HWM. The database formats a bitmap block in this group to hold the metadata, but does not preformat the remaining blocks in the group.

In Figure 12-24, the blocks below the HWM are allocated, whereas blocks above the HWM are neither allocated or formatted. As inserts occur, the database can write to any block with available space. The low high water mark (low HWM) marks the point below which all blocks are known to be formatted because they either contain data or formerly contained data.

Figure 12-24 HWM and Low HWM

Description of "Figure 12-24 HWM and Low HWM"

In Figure 12-25, the database chooses a block between the HWM and low HWM and writes to it. The database could have just as easily chosen any other block between the HWM and low HWM, or any block below the low HWM that had available space. In Figure 12-25, the blocks to either side of the newly filled block are unformatted.

Figure 12-25 HWM and Low HWM

Description of "Figure 12-25 HWM and Low HWM"

The low HWM is important in a full table scan. Because blocks below the HWM are formatted only when used, some blocks could be unformatted, as in Figure 12-25. For this reason, the database reads the bitmap block to obtain the location of the low HWM. The database reads all blocks up to the low HWM because they are known to be formatted, and then carefully reads only the formatted blocks between the low HWM and the HWM.

Assume that a new transaction inserts rows into the table, but the bitmap indicates that insufficient free space exists under the HWM. In Figure 12-26, the database advances the HWM to the right, allocating a new group of unformatted blocks.

Figure 12-26 Advancing HWM and Low HWM

Description of "Figure 12-26 Advancing HWM and Low HWM"

When the blocks between the HWM and low HWM are full, the HWM advances to the right and the low HWM advances to the location of the old HWM. As the database inserts data over time, the HWM continues to advance to the right, with the low HWM always trailing behind it. Unless you manually rebuild, truncate, or shrink the object, the HWM never retreats.

See Also:

Oracle Database Administrator's Guide to learn how to shrink segments online
Oracle Database SQL Language Reference for TRUNCATE TABLE syntax and semantics

move不但可以重置水位線（HWM），解決松散表帶來的 IO 浪費，還可以解決表中的行遷移問題。
  move表的話需要雙倍的空間，否則無法成功。move表可以通過重新安排數據文件的空間達到收縮數據文件的目的。
move表時，會產生exclusive lock 鎖，此時只能對表進行 select 操作。
  move表之后，如果表上有索引，記得重建。
2）shrink表只對ASSM管理的表有效，相關命令有：
-----alter table TABLE_NAME shrink space; 整理碎片并回收空間
-----alter table TABLE_NAME shrink space compact;   只整理碎片，不回收空間
-----alter table TABLE_NAME shrink space cascate; 整理碎片回收空間，并連同表的級聯對象一起整理(比如索引)
能在線進行，不影響表上的DML操作，當然，并發的DML操作在shrink結束的時刻會出現短暫的block；
shrink的另外一個優點是在碎片整理結束后，表上相關的index仍然enable。
3）move的操作速度遠遠快于shrink 操作，不是一般的快，不是一個數量級的，而且shrink 會產生大量的undo 和redo 操作。
4）truncate是DDL操作，相當于刪表后重建。
5）還有其他的方法，如導出后再重新導入。

準備工作，創建一張表：
CREATE TABLE TEST2
(
ID NUMBER(10),
NAME VARCHAR2(32)
);

1、移動表：
SQL> begin
2 for i in 1..10000 loop
3 insert into test2 values(i,'bbb');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2 LXM 244 12

SQL> delete test2;
100000 rows deleted.

SQL> alter table test2 move;
Table altered.

SQL> analyze table test2 compute statistics;
Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';

TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2 LXM 0 8

2、收縮表：
SQL> begin
2 for i in 1..100000 loop
3 insert into test2 values(i,'kkk');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.

SQL> analyze table test2 compute statistics;
Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2 LXM 244 12

SQL> delete test2;
100000 rows deleted.

SQL> alter table test2 shrink space;
alter table test2 shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table test2 enable row movement;
Table altered.

SQL> alter table test2 shrink space;
Table altered.

SQL> analyze table test2 compute statistics;
Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2 LXM 1 7

3、truncate表
SQL> begin
2 for i in 1..100000 loop
3 insert into test2 values(i,'kkk');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.

SQL> analyze table test2 compute statistics;
Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2 LXM 244 12

SQL> truncate table test2;
Table truncated.

SQL> analyze table test2 compute statistics;
Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2 LXM 0 8

4、新建臨時表，然后rename
SQL> begin
2 for i in 1..100000 loop
3 insert into test2 values(i,'kkk');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.

SQL> delete test2;
100000 rows deleted.

SQL> analyze table test2 compute statistics;
Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2 LXM 244 12

SQL> create table test3 as select * from test2;
Table created.

SQL> drop table test2;
Table dropped.

SQL> alter table test3 rename to test2;
Table altered.

SQL> analyze table test2 compute statistics;
Table analyzed.

SQL> select TABLE_NAME,TABLESPACE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST2';
TABLE_NAME TABLESPACE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ---------- ------------
TEST2 LXM 0 0

==================================================
空表移動無須重建索引：
SQL> begin
2 for i in 1..10000 loop
3 insert into test2 values(i,'bbb');
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.

SQL> select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME STATUS INITIAL_EXTENT MAX_EXTENTS
------------------------------ --------------------------- ------------------------------ -------- -------------- -----------
IND_TEST2 NORMAL TEST2 VALID 65536 2147483645

SQL> delete test2 where id=1;
1 row deleted.
SQL> alter table test2 move;
Table altered.

SQL> select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME STATUS INITIAL_EXTENT MAX_EXTENTS
------------------------------ --------------------------- ------------------------------ -------- -------------- -----------
IND_TEST2 NORMAL TEST2 UNUSABLE 65536 2147483645

SQL> alter index ind_test2 rebuild;
Index altered.

SQL> select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME STATUS INITIAL_EXTENT MAX_EXTENTS
------------------------------ --------------------------- ------------------------------ -------- -------------- -----------
IND_TEST2 NORMAL TEST2 VALID 65536 2147483645

SQL> delete test2;
9999 rows deleted.
SQL> alter table test2 move;
Table altered.

SQL> select index_name,index_type,table_name,status,initial_extent,max_extents from user_indexes;
INDEX_NAME INDEX_TYPE TABLE_NAME STATUS INITIAL_EXTENT MAX_EXTENTS
------------------------------ --------------------------- ------------------------------ -------- -------------- -----------
IND_TEST2 NORMAL TEST2 VALID 65536 2147483645

高水位是記錄段里能容納數據的上限，高水位存在段里

全表掃先讀段頭塊，而后在段頭塊里面找到HWM

下面用實驗由內而外來理解Oracle的HWM

[sql] view plain copy

print?

--t表有一條數據
hr@ORCL> select * from t;
ID NAME
---------- ----------
1 AAAAA
--找t段的段頭塊
hr@ORCL> select header_file,header_block from dba_segments where segment_name='T' and owner='HR';
HEADER_FILE HEADER_BLOCK
----------- ------------
4 387
--另開一個session，dump段頭塊
sys@ORCL> alter session set tracefile_identifier='sys_dump_t_01';
Session altered.
sys@ORCL> alter system dump datafile 4 block 387;
System altered.

dump的部分trc內容摘入

[sql] view plain copy

print?

Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 1 #blocks: 8
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x01000189 ext#: 0 blk#: 8 ext size: 8 --Highwater就是高水位，0x01000189這個是HWM的地址
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x01000189 ext#: 0 blk#: 8 ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 5
mapblk 0x00000000 offset: 0
Level 1 BMB for High HWM block: 0x01000181
Level 1 BMB for Low HWM block: 0x01000181
--------------------------------------------------------
Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x01000182
Last Level 1 BMB: 0x01000181
Last Level II BMB: 0x01000182
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 52713 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01000181 length: 8
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x01000181 Data dba: 0x01000184
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x01000182
d dump data blocks tsn: 4 file#: 4 minblk 387 maxblk 387

[sql] view plain copy

print?

--對t表做一次全表掃
hr@ORCL> set autot traceonly
hr@ORCL> select * from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T | 1 | 20 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets --全表掃讀了6個塊
0 physical reads
0 redo size
469 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

這6個塊是如何算出來的呢？

[sql] view plain copy

print?

hr@ORCL> select file_id,block_id,blocks from dba_extents where segment_name='T';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
4 385 8

這t段一共用了8個塊，分別是385 386 387 388 389 390 391 392 393
Highwater:: 0x01000189 即：4號文件的393號塊
這個可由下面dbms_utility包算出

[sql] view plain copy

print?

sys@ORCL> select to_number('01000189','xxxxxxxx') from dual;
TO_NUMBER('01000189','XXXXXXXX')
--------------------------------
16777609
sys@ORCL> select dbms_utility.data_block_address_file(16777609) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16777609)
----------------------------------------------
4
sys@ORCL> select dbms_utility.data_block_address_block(16777609) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16777609)
-----------------------------------------------
393

讀了一次段頭塊：4號文件387號塊
讀了高水位之下的388 389 390 391 392 等5個塊
這樣一共就讀了6個塊
注：
385是FIRST LEVEL BITMAP BLOCK
386是SECOND LEVEL BITMAP BLOCK

接著分析我們所dump的內容：

[sql] view plain copy

print?

Low HighWater Mark :
Highwater:: 0x01000189 ext#: 0 blk#: 8 ext size: 8

接下來談談highwater mark 和 low highwater mark

low high water mark與high water mark 之間可能存在formated block也可以可能存在unformatted block

Oracle 降低高水位線的方法

先來理清dba_tables里面的字段blocks的含義

dba_tables.blocks記錄的是分析得到的 formatted block 的總數
而 low hwm 和 high hwm之間可能同時存在 formatted block 和 unfomatted block
所以準確地說 blocks 不能代表 low hwm 或high hwm
如果 low hwm 和 high hwm之間正好沒有formatted block時，dba_tables.blocks和low hwm下的blocks一致

那么什么是Oracle中未格式化的塊呢？

未格式化，意思就是這個塊，已經是屬于這個段了，但是還保留著原來的樣子沒動
格式化就是把塊中的數據清除掉，并把塊頭改為這個對象的
MSSM表空間中的段，只有一個高水位，高水位下的塊都是格式化了的
但是ASSM表空間中的段，有兩個高水位：低高水位和高高水位
即上文trc里的:Highwater:: 0x01000189和Low HighWater Mark Highwater:: 0x01000189
低高水位下的塊全部是格式化了的
但是低高水位和高高水位之間的塊，則可能是格式化了的，也可能是沒有

現在的t的高高水位是file 4，block 393；其低高水位是file 4，block 393

我們現在再來看一下t現在data_object_id是多少：

[sql] view plain copy

print?

hr@ORCL> select object_id,data_object_id from dba_objects where object_name='T';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
52713 52714

這里很明顯t的data_object_id大于object_id
也就是說，在t上曾經發生過move或truncate操作
注意，對于truncate操作而言，truncate后其data_object_id不一定就是在原先的data_object_id上加1

[sql] view plain copy

print?

sys@ORCL> select to_char('52714','XXXXXXXX') from dual;
TO_CHAR('
---------
CDEA

換句話說，t中現在在其低高水位和其高高水位之間的block，只要這個block上記錄的data_object_id不等于CDEA
我們可以通過dump里面的Block header dump部分中的seg/obj來判斷其data_object_id是否與段編號相等
那么這個block 就是一個未格式化的塊
也就是說，可以通過data_object_id來確定塊是在HWM和LHWM的位置

那么Oracle為什么要增加低高水位設置？出于什么目的？全表掃描時，是到低高水位，還是到高高水位？

Oracle設計有一個原則，就是把一個大操作分散到很多小操作中，以保證某個大操作不會花費太長時間
無論是延遲提交，還是什么，都體現了這種思想,這和Linux的理念有異曲同工之妙哦
而低高水位線與高高水位線結合，正是這種思想的體現
可以不用一次性格式化所有新分配的塊，留一部分給下次操作來完成

全表掃描時，通常都是讀至低高水位線，然后根據位圖去讀低高與高高之間格式化過的塊，避開未格式化的塊

如何查看HWM？如何知道HWM下有多少空閑的空間？

最實用的方法就是dump segment_header，速度快，而且對應用沒有影響
而且，trc里面的"#blocks in seg. hdr's freelists:"可以告訴我們HWM下有多少空閑塊
這里需要注意，如果dba_segments.freelist_groups > 1，那么freelist不再segment header中
比如，freelist_group = 3 ,則你便要分別dump header后面的3個塊，來看每個group的freelist的個數

那么如何降低HWM呢？

① expdp/impdp
② 10G及以后的版本可以采用shrink，需要注意的是，表所在表空間須為自動段空間管理
alter table tab_name enable row movement;
alter table tab_name shrink space;
③ 使用move，不過在Move期間，會影響到DML語句，如UPDATE，也需要考慮空間問題
總之move會鎖表而且是TM 另外還會影響index，所以，之后記得rebuild index
alter table move tab_name; 在當前表空間中move
alter table move tab_name tablespace tbs_name; 將其move到其他表空間
④ CTAS 然后rename，rebuild index
⑤ 在線重定義

等等.......

高水位（High Water Mark）的概念及高水位問題的解決

說到HWM,我們首先要簡要的談談ORACLE的邏輯存儲管理.我們知道,ORACLE在邏輯存儲上分4個粒度:表空間,段,區和塊.

(1)塊:是粒度最小的存儲單位,現在標準的塊大小是8K,ORACLE每一次I/O操作也是按塊來操作的,也就是說當ORACLE從數據文件讀數據時,是讀取多少個塊,而不是多少行.

(2) 區:由一系列相鄰的塊而組成,這也是ORACLE空間分配的基本單位,舉個例子來說,當我們創建一個表PM_USER時,首先ORACLE會分配一區的空間給這個表,隨著不斷的INSERT數據到PM_USER,原來的這個區容不下插入的數據時,ORACLE是以區為單位進行擴展的,也就是說再分配多少個區給PM_USER,而不是多少個塊.

(3) 段:是由一系列的區所組成,一般來說,當創建一個對象時(表,索引),就會分配一個段給這個對象.所以從某種意義上來說,段就是某種特定的數據.如 CREATE TABLE PM_USER,這個段就是數據段,而CREATE INDEX ON PM_USER(NAME),ORACLE同樣會分配一個段給這個索引,但這是一個索引段了.查詢段的信息可以通過數據字典: SELECT * FROM USER_SEGMENTS來獲得,

(4)表空間:包含段,區及塊.表空間的數據物理上儲存在其所在的數據文件中.一個數據庫至少要有一個表空間.

OK,我們現在回到HWM上來,那么,什么是高水位標記呢?這就跟ORACLE的段空間管理相關了.

(一)ORACLE用HWM來界定一個段中使用的塊和未使用的塊.

舉個例子來說,當我們創建一個表:PT_SCHE_DETAIL時,ORACLE就會為這個對象分配一個段.在這個段中,即使我們未插入任何記錄,也至少有一個區被分配,第一個區的第一個塊就稱為段頭(SEGMENT HEADE),段頭中就儲存了一些信息,基中HWM的信息就存儲在此.此時,因為第一個區的第一塊用于存儲段頭的一些信息,雖然沒有存儲任何實際的記錄, 但也算是被使用,此時HWM是位于第2個塊.當我們不斷插入數據到PM_USER后,第1個塊已經放不下后面新插入的數據,此時,ORACLE將高水位之上的塊用于存儲新增數據,同時,HWM本身也向上移.也就是說,當我們不斷插入數據時,HWM會往不斷上移,這樣,在HWM之下的,就表示使用過的塊,HWM之上的就表示已分配但從未使用過的塊.

(二)HWM在插入數據時,當現有空間不足而進行空間的擴展時會向上移,但刪除數據時不會往下移.

這就好比是水庫的水位,當漲水時,水位往上移,當水退出后,最高水位的痕跡還是清淅可見.
考慮讓我們看一個段，如一張表，其中填滿了塊，如圖 1 所示。在正常操作過程中，刪除了一些行，如圖 2 所示。現有就有了許多浪費的空間：(I) 在表的上一個末端和現有的塊之間，以及 (II) 在塊內部，其中還有一些沒有刪除的行。

圖1：分配給該表的塊。用灰色正方形表示行

ORACLE 不會釋放空間以供其他對象使用，有一條簡單的理由：由于空間是為新插入的行保留的，并且要適應現有行的增長。被占用的最高空間稱為最高使用標記 (HWM)，如圖 2 所示。

圖2：行后面的塊已經刪除了；HWM 仍保持不變

(三)HWM的信息存儲在段頭當中.

HWM本身的信息是儲存在段頭.在段空間是手工管理方式時,ORACLE是通過FREELIST(一個單向鏈表)來管理段內的空間分配.在段空間是自動管理方式時(ASSM),ORACLE是通過BITMAP來管理段內的空間分配.

(四)ORACLE的全表掃描是讀取高水位標記(HWM)以下的所有塊.

所以問題就產生了.當用戶發出一個全表掃描時，ORACLE 始終必須從段一直掃描到 HWM，即使它什么也沒有發現。該任務延長了全表掃描的時間。

(五)當用直接路徑插入行時 — 例如，通過直接加載插入（用 APPEND 提示插入）或通過 SQL*LOADER 直接路徑 — 數據塊直接置于 HWM 之上。它下面的空間就浪費掉了。

我們來分析這兩個問題,后者只是帶來空間的浪費,但前者不僅是空間的浪費,而且會帶來嚴重的性能問題.我們來看看下面的例子:

(A)我們先來搭建測試的環境,第一步先創建一個段空間為手工管理的表空間:

CREATE TABLESPACE "RAINNY"
LOGGING
DATAFILE 'D:ORACLE_HOMEORADATARAINNYRAINNY.ORA' SIZE 5M
AUTOEXTEND
ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT MANUAL;

(B)創建一個表,注意,此表的第二個字段我故意設成是CHAR(100),以讓此表在插入1千萬條記錄后,空間有足夠大:

CREATE TABLE TEST_TAB(C1 NUMBER(10),C2 CHAR(100)) TABLESPACE RAINNY;

插入記錄

DECLARE
I NUMBER(10);

BEGIN
    FOR I IN 1..10000000 LOOP
        INSERT INTO TEST_TAB VALUES(I,'TESTSTRING');
    END LOOP;
    COMMIT;

END;

(C)我們來查詢一下,看在插入一千萬條記錄后所訪問的塊數和查詢所用時間:

SQL> SET TIMING ON

SQL> SET AUTOTRACE TRACEONLY

SQL> SELECT COUNT(*) FROM TEST_TAB;

ELAPSED: 00:01:03.05

EXECUTION PLAN

------------------------------------------------------------

0 SELECT STATEMENT OPTIMIZER=CHOOSE (COST=15056 CARD=1)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'TEST_TAB' (COST=15056 CARD=10000

000)

STATISTICS

----------------------------------------------------------

0 RECURSIVE CALLS

0 DB BLOCK GETS

156310 CONSISTENT GETS

154239 PHYSICAL READS

0 REDO SIZE

379 BYTES SENT VIA SQL*NET TO CLIENT

503 BYTES RECEIVED VIA SQL*NET FROM CLIENT

2 SQL*NET ROUNDTRIPS TO/FROM CLIENT

0 SORTS (MEMORY)

0 SORTS (DISK)

1 ROWS PROCESSED

我們來看上面的執行計劃,這句SQL總供耗時是:1分3秒.訪問方式是采用全表掃描方式(FTS),邏輯讀了156310個BLOCK,物理讀了154239個BLOCK.
我們來分析一下這個表:

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'TEST',
TABNAME=> 'TEST_TAB',
PARTNAME=> NULL);END;

發現這個表目前使用的BLOCK有: 156532,未使用的BLOCK(EMPTY_BLOCKS)為:0,總行數為(NUM_ROWS):1000 0000

(D)接下來我們把此表的記錄用DELETE方式刪掉,然后再來看看SELECT COUNT(*) FROM TEST_TAB所花的時間:

DELETE FROM TEST_TAB;
COMMIT;

SQL> SELECT COUNT(*) FROM TEST_TAB;

ELAPSED: 00:01:04.03

EXECUTION PLAN
----------------------------------------------------------
0 SELECT STATEMENT OPTIMIZER=CHOOSE (COST=15056 CARD=1)

1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST_TAB' (COST=15056 CARD=1)

STATISTICS
----------------------------------------------------------
0      RECURSIVE CALLS
0      DB BLOCK GETS
156310 CONSISTENT GETS
155565 PHYSICAL READS
0      REDO SIZE
378    BYTES SENT VIA SQL*NET TO CLIENT
503    BYTES RECEIVED VIA SQL*NET FROM CLIENT
2      SQL*NET ROUNDTRIPS TO/FROM CLIENT
0      SORTS (MEMORY)
0      SORTS (DISK)
1      ROWS PROCESSED

大家來看,在DELETE表后,此時表中已沒有一條記錄,為什么SELECT COUNT(*) FROM TEST_TAB花的時間為1分4秒, 反而比有記錄稍微長點,這是為什么呢?而且大家看,其邏輯讀了156310個 BLOCK,跟之前有一千萬行記錄時差不多,ORACLE怎么會這么笨啊?

我們在DELETE表后再次分析表,看看有什么變化:
這時, TEST_TAB表目前使用的BLOCK是: 156532,未使用的BLOCK(EMPTY_BLOCKS)為:0,總行數為(NUM_ROWS)已變成:0

為什么表目前使的BLOCK數還是156532呢?

問題的根源就在于ORACLE的HWM.也就是說,在新增記錄時,HWM會慢慢往上移,但是在刪除記錄后,HWM卻不會往下移,也就是說,DELETE一千萬條記錄后,此表的HWM根本沒移動,還在原來的那個位置,所以,HWM以下的塊數同樣也是一樣的.ORACLE的全表掃描是讀取ORACLE高水位標記下的所有BLOCK,也就是說,不管HWM下的BLOCK現在實際有沒有存放數據,ORACLE都會一一讀取,這樣,大家可想而知,在我們DELETE表后,ORACLE讀了大量的空塊,耗去了大量的時間.

我們再來看DELETE表后段空間實際使用的狀況:

SQL> EXEC SHOW_SPACE('TEST_TAB','TEST');

TOTAL BLOCKS............................164352 --總共164352塊
TOTAL BYTES.............................1346371584
UNUSED BLOCKS...........................7168 --有7168塊沒有用過,也就是在HWM上面的塊數
UNUSED BYTES............................58720256
LAST USED EXT FILEID....................9
LAST USED EXT BLOCKID...................158856-- BLOCK ID 是針對數據文件來編號的，表示最后使用的一個EXTENT的第一個BLOCK的編號
LAST USED BLOCK.........................1024 -- 在最后使用的一個EXTENT 中一共用了1024塊

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED

總共用了164352塊，除了一個SEGMENT HEADER,實際總共用了164351個塊，有7168塊從來沒有使用過。LAST USED BLOCK表示在最后一個使用的EXTENT 中使用的BLOCK, 結合　LAST USED EXT BLOCK ID可以計算　HWM 位置 :

LAST USED EXT BLOCK ID + LAST USED BLOCK -1 = HWM 所在的數據文件的BLOCK編號

代入得出: 158856+1024-1=159879,這個就是HWM所有的BLOCK編號

HWM所在的塊:TOTAL BLOCKS- UNUSED BLOCKS=164352-7168=157184,也就是說,HWM在第157184個塊,其BLOCKID是159879

(E)結下來,我們再做幾個試驗:

第一步:執行ALTER TABLE TEST_TAB DEALLOCATE UNUSED;

我們看看段空間的使用狀況:

SQL> EXEC SHOW_SPACE('TEST_TAB','TEST');

TOTAL BLOCKS............................157184
TOTAL BYTES.............................1287651328
UNUSED BLOCKS...........................0
UNUSED BYTES............................0
LAST USED EXT FILEID....................9
LAST USED EXT BLOCKID...................158856
LAST USED BLOCK.........................1024

此時我們再代入上面的公式,算出HWM的位置: 157184-0=157184 HWM所在的BLOCK ID是158856+1024-1=159879,跟剛剛的沒有變化,也就是說執行ALTER TABLE TEST_TAB DEALLOCATE UNUSED后,段的高水位標記的位置沒有改變,但是大家看看UNUSED BLOCKS變為0了,總的塊數減少到157184,這證明,DEALLOCATE UNUSED為釋放HWM上面的未使用空間,但是并不會釋放HWM下面的自由空間,也不會移動HWM的位置.

第二步:我們再來看看執行ALTER TABLE TEST_TAB MOVE后段空間的使用狀況:

SQL> EXEC SHOW_SPACE('TEST_TAB','TEST');

TOTAL BLOCKS............................8

TOTAL BYTES.............................65536

UNUSED BLOCKS...........................5

UNUSED BYTES............................40960

LAST USED EXT FILEID....................9

LAST USED EXT BLOCKID...................2632

LAST USED BLOCK.........................3

此時,總共用到的塊數已變為8, 我們再代入上面的公式,算出HWM的位置: 8-5=3 HWM所在的BLOCK ID是2632+3-1=2634,

OK, 我們發現,此時HWM的位置已經發生變化,現在HWM的位置是在第3個BLOCK,其BLOCK ID是2634,所有數據文件的ID是9(這個沒有發生變化,數據文件還是原來的那個數據文件,只是釋放了原來的自由空間),最后使用的塊數也變為3,也就是說已經使用了3塊,HWM就是在最后一個使用的塊上,即第3個塊上.大家可能會覺得奇怪,為什么釋放空間后,未使用的塊還有5個啊?也就是說HWM之上還是有5個已分配但從未使用的塊.答案就跟HWM移動的規律有關.當我們在插入數據時,ORACLE首先在HWM之下的塊當中定位自由空間(通過自由列表FREELIST),如果FREELIST當中沒有自由塊了,ORACLE就開始往上擴展,而HWM也跟著往上移,每5塊移動一次.我們來看 ORACLE的說明:

The high water mark is:
-Recorded in the segment header block
-Set to the beginning of the segment on the creation
-Incremented in five-block increments as rows are inserted
-Reset by the truncate command
-Never reset by the delete command
-Space above the high-water-mark can be reclaimed at the table level by using the following command:
ALTER TABLE DEALLOCATE UNUSED…

我們再來看看:SELECT COUNT(*) FROM TEST_TAB所花的時間:

SQL> SELECT COUNT(*) FROM TEST_TAB;

ELAPSED: 00:00:00.00

EXECUTION PLAN
----------------------------------------------------------

0 SELECT STATEMENT OPTIMIZER=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'TEST_TAB'

STATISTICS
----------------------------------------------------------
0 RECURSIVE CALLS
0 DB BLOCK GETS

3     CONSISTENT GETS
0     PHYSICAL READS
0     REDO SIZE
378   BYTES SENT VIA SQL*NET TO CLIENT
503   BYTES RECEIVED VIA SQL*NET FROM CLIENT
2     SQL*NET ROUNDTRIPS TO/FROM CLIENT
0     SORTS (MEMORY)
0     SORTS (DISK)
1     ROWS PROCESSED

很快,不到1秒.

我們最后再來對表作一次分析, 此時這個表目前使用的BLOCK為: 0,未使用的BLOCK(EMPTY_BLOCKS)為:0,總行數為(NUM_ROWS):0
從中我們也可以發現,分析表和SHOW_SPACE顯示的數據有點不一致.那么哪個是準的呢?其實這兩個都是準的,只不過計算的方法有點不同.事實上,當你創建了一個對象如表以后,不管你有沒有插入數據,它都會占用一些塊,ORACLE也會給它分配必要的空間.同樣,用ALTER TABLE MOVE釋放自由空間后,還是保留了一些空間給這個表.
最后,我們再來執行TRUNCATE命令,截斷這個表,看看段空間的使用狀況:

TRUNCATE TABLE TEST_TAB;

SQL> EXEC SHOW_SPACE('TEST_TAB','TEST');

TOTAL BLOCKS............................8
TOTAL BYTES.............................65536
UNUSED BLOCKS...........................5
UNUSED BYTES............................40960
LAST USED EXT FILEID....................9
LAST USED EXT BLOCKID...................2632
LAST USED BLOCK.........................3

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED

SQL>

我們發現TRUNCATE后和MOVE沒有什么變化.

為了,最終驗證一下我上面的觀點,我再DROP一下表,然后新建這個表,看看這時在沒有插入任何數據之前,是否ORACLE確實有給這個對象分配必要的空間:

DROP TABLE TEST_TAB;

CREATE TABLE TEST_TAB(C1 NUMBER(10),C2 CHAR(100)) TABLESPACE RAINNY;

SQL> EXEC SHOW_SPACE('TEST_TAB','TEST');

大家看,即使我沒有插入任何一行記錄,ORACLE還是給它分配了8個塊.當然這個跟建表語句的INITIAL 參數及MINEXTENTS參數有關:請看TEST_TAB的存儲參數:

S TORAGE
(
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
);

也就是說,在這個對象創建以后,ORACLE至少給它分配一個區,初始大小是64K,一個標準塊的大小是8K,剛好是8個BLOCK.

總結:

在9I中:

(1)如果MINEXTENT 可以使ALTER TABLE TABLENAME DEALLOCATE UNUSED將HWM以上所有沒使用的空間釋放
(2)如果MINEXTENT >HWM 則釋放MINEXTENTS 以上的空間。如果要釋放HWM以上的空間則使用KEEP 0。
ALTER TABLE TABLESNAME DEALLOCATE UNUSED KEEP 0;
(3) TRUNCATE TABLE DROP STORAGE(缺省值)命令可以將MINEXTENT 之上的空間完全釋放(交還給操作系統),并且重置HWM。
(4)如果僅是要移動HWM,而不想讓表長時間鎖住,可以用TRUNCATE TABLE REUSE STORAGE,僅將HWM重置。
(5)ALTER TABLE MOVE會將HWM移動,但在MOVE時需要雙倍的表空間,而且如果表上有索引的話,需要重構索引
(6)DELETE表不會重置HWM,也不會釋放自由的空間(也就是說DELETE空出來的空間只能給對象本身將來的INSERT/UPDATE使用,不能給其它的對象使用)

在ORACLE 10G:

可以使用ALTER TABLE TEST_TAB SHRINK SPACE命令來聯機移動HWM,
如果要同時壓縮表的索引,可以發布:ALTER TABLE TEST_TAB SHRINK SPACE CASCADE
注意:在使用此命令時需要先使行可遷移row movement(具體見例子)。
與使用ALTER TABLE MOVE 不同的是執行此命令后并不需要重構索引。

Oracle 官方說明

Shrinking Database Segments Online
You use online segment shrink to reclaim fragmented free space below the high water mark in an Oracle Database segment. The benefits of segment shrink are these:
    * Compaction of data leads to better cache utilization, which in turn leads to better online transaction processing (OLTP) performance.
    * The compacted data requires fewer blocks to be scanned in full table scans, which in turns leads to better decision support system (DSS) performance.
Segment shrink is an online, in-place operation. DML operations and queries can be issued during the data movement phase of segment shrink. Concurrent DML operation are blocked for a short time at the end of the shrink operation, when the space is deallocated. Indexes are maintained during the shrink operation and remain usable after the operation is complete. Segment shrink does not require extra disk space to be allocated.
Segment shrink reclaims unused space both above and below the high water mark. In contrast, space deallocation reclaims unused space only above the high water mark. In shrink operations, by default, the database compacts the segment, adjusts the high water mark, and releases the reclaimed space.
Segment shrink requires that rows be moved to new locations. Therefore, you must first enable row movement in the object you want to shrink and disable any rowid-based triggers defined on the object.
Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM). Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:
    * IOT mapping tables
    * Tables with rowid based materialized views
    * Tables with function-based indexes

操作的過程：

SQL> create table demo as select * from dba_source;

Table created.
Elapsed: 00:00:05.83

SQL> select count(*) from demo;

COUNT(*)
----------
210992
Elapsed: 00:00:01.06

SQL> insert into demo select * from demo;
210992 rows created.
Elapsed: 00:00:59.83

SQL> commit;
Commit complete.

//得到一個40萬條記錄的表，下面來查看這個表空間分布情況。

SQL> exec show_space('demo','auto');

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.07

SQL> set serveroutput on

SQL> exec show_space('demo','auto');

Total Blocks............................9216
Total Bytes.............................75497472
Unused Blocks...........................768
Unused Bytes............................6291456
Last Used Ext FileId....................4
Last Used Ext BlockId...................8328
Last Used Block.........................256

一共有9216個數據塊，HWM在9216-768=8448這個塊.
也可以通過查看extents得到HWM=8*16+128*63+256=8192+256=8448

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01

SQL> delete from demo where rownum<220000;

219999 rows deleted.
Elapsed: 00:00:40.99

SQL> commit;

Commit complete.
Elapsed: 00:00:00.01

SQL> exec show_space('demo','auto');

PL/SQL procedure successfully completed.

//刪除操作后表的HWM沒有變化，還是在第8448塊這個位置。
Elapsed: 00:00:00.00

SQL> alter table demo shrink space;
alter table demo shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

//先要enable row movement才能shrink
Elapsed: 00:00:00.09

SQL> alter table demo enable row movement;

Table altered.
Elapsed: 00:00:00.10

SQL> alter table demo shrink space;

Table altered.
Elapsed: 00:01:35.51

SQL> exec show_space('demo','auto');

Total Blocks............................3656
Total Bytes.............................29949952
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................3720
Last Used Block.........................72

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02

//可以看到HWM降到了3656這個塊上面!

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

最近學習了一陣子Oracle, 感覺Oracle真的是博大精深, 包括Oralce內存結構,性能調整,數據備份等都不簡單, 這些對開發也很重要, 下面把做的Oracle高水位線的一些實驗貼出來, 方便以后Review:
高水位線實驗:
-- 創建test3表
SQL> create table test3 as
2 select * from dba_objects where 1 = 2;
Table created
-- 查看表中分配塊，區大小
SQL> SELECT segment_name, segment_type, blocks -- 分配數據塊數, extents -- 分配區塊數
2    FROM dba_segments
3   WHERE segment_name = 'TEST3'
4 ;
SEGMENT_NAME                                                                     SEGMENT_TYPE           BLOCKS    EXTENTS
-------------------------------------------------------------------------------- ------------------ ---------- ----------
TEST3                                                                            TABLE                       8          1
TEST3                                                                            TABLE                       8          1
-- 分析表TEST3表
SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS;
Table analyzed
-- 查詢TEST3表高水位線
SQL> SELECT blocks -- 高水位線(占用TEST3表數據塊數), empty_blocks -- TEST3表空閑塊數, num_rows
2    FROM user_tables
3   WHERE table_name = 'TEST3';
    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
         0            7          0
-- 因為未向TEST3表中插入任何數據，因此此表的高水位線為0，現向TEST3表中插入數據再觀察
SQL> insert into test3
2 select * from dba_objects;
50361 rows inserted
SQL> commit;
Commit complete
-- 重新分析表
SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS;
Table analyzed
-- 再次查看表中分配塊，區大小
SQL> SELECT segment_name, segment_type, blocks, extents
2    FROM dba_segments
3   WHERE segment_name = 'TEST3'
4 ;
SEGMENT_NAME                                                                     SEGMENT_TYPE           BLOCKS    EXTENTS
-------------------------------------------------------------------------------- ------------------ ---------- ----------
TEST3                                                                            TABLE                       8          1
TEST3                                                                            TABLE                     768         21
此時看到BLOCKS數已增長到768, 也就是Oracle分配給TEST3表768個數據塊，21個區
-- 再次查看TEST3表高水位線
SQL> SELECT blocks, empty_blocks, num_rows
2    FROM user_tables
3   WHERE table_name = 'TEST3';
    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
       689           78      50361
已增長到689個塊，還有78個空閑塊，689 + 78 = 767, 比分配的少1個數據塊，是因為這一個數據塊是用作segment header
-- 現將TEST3表delete，在查看高水位線
SQL> delete from test3;
50361 rows deleted
SQL> commit;
Commit complete
SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS;
Table analyzed
SQL>
SQL> SELECT blocks, empty_blocks, num_rows
2    FROM user_tables
3   WHERE table_name = 'TEST3';
    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
       689           78          0
發現此表高水位線并未減少，證明delete只是刪除表中數據塊的記錄，但并不會使表中的高水位線下降，在進行全表掃描時會Oracle會掃描表中高水位線下的所有數據塊，
因此數據雖然被刪除了，但查詢時有可能還是很慢。所以在進行大表刪除時應使用truncate語句，看下面實驗:
SQL> truncate table test3;
Table truncated
SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS;
Table analyzed
SQL>
SQL> SELECT blocks, empty_blocks, num_rows
2    FROM user_tables
3   WHERE table_name = 'TEST3';
    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
         0            7          0
現在表中高水位下降到0了，一點心得，記錄下來。

----------------------------------------------------------------------------------------------------------------

4. 修正ORACLE 表的高水位線

　　在ORACLE 中，執行對表的刪除操作不會降低該表的高水位線。而全表掃描將始終讀取一個段(extent) 中所有低于高水位線標記的塊。如果在執行刪除操作后不降低高水位線標記，則將導致查詢語句的性能低下。

下面的方法都可以降低高水位線標記。

1. 執行表重建指令 alter table table_name move;

在線轉移表空間ALTER TABLE ... MOVE TABLESPACE ..

當你創建了一個對象如表以后, 不管你有沒有插入數據 , 它都會占用一些塊 ,ORACLE 也會給它分配必要的空間 . 同樣 , 用 ALTER TABLE MOVE 釋放自由空間后 , 還是保留了一些空間給這個表 .

ALTER TABLE ... MOVE 后面不跟參數也行，不跟參數表還是在原來的表空間， M ove后記住重建索引 . 如果以后還要繼續向這個表增加數據，沒有必要move ，只是釋放出來的空間，只能這個表用，其他的表或者 segment 無法使用該空間。

2. 執行alter table table_name shrink space;

注意，此命令為Oracle 10g 新增功能，再執行該指令之前必須允許行移動 alter table table_name enable row movement;

3. 復制要保留的數據到臨時表t ， drop 原表，然后 rename 臨時表 t 為原表

4. 用邏輯導入導出: E mp/ I mp

5. A lter table table_name deallocate unused

注：這證明,DEALLOCATE UNUSED 為釋放 HWM 上面的未使用空間 , 但是并不會釋放 HWM 下面的自由空間 , 也不會移動 HWM 的位置 .

6. 盡量使用 truncate .

注意：

在9I 中 :

1. 如果是 INEXTENT ，可以使ALTER TABLE TABLENAME DEALLOCATE UNUSED 將 HWM 以上所有沒使用的空間釋放

2. 如果MINEXTENT >HWM 則釋放 MINEXTENTS 以上的空間。如果要釋放 HWM 以上的空間則使用 KEEP 0 。

ALTER TABLE TABLESNAME DEALLOCATE UNUSED KEEP 0;

3. TRUNCATE TABLE DROP STORAGE (缺省值 ) 命令可以將MINEXTENT 之上的空間完全釋放 (交還給操作系統 ), 并且重置 HWM 。

4. 如果僅是要移動HWM, 而不想讓表長時間鎖住 , 可以用 TRUNCATE TABLE REUSE STORAGE, 僅將 HWM 重置。

5. ALTER TABLE MOVE會將 HWM 移動 , 但在 MOVE 時需要雙倍的表空間 , 而且如果表上有索引的話 , 需要重構索引

6. DELETE表不會重置 HWM, 也不會釋放自由的空間 ( 也就是說 DELETE 空出來的空間只能給對象本身將來的 INSERT/UPDATE 使用 , 不能給其它的對象使用 )

在ORACLE 10G:

1. 可以使用ALTER TABLE TEST_TAB SHRINK SPACE 命令來聯機移動 HWM,

2. 如果要同時壓縮表的索引, 可以發布 :ALTER TABLE TEST_TAB SHRINK SPACE CASCADE

5. HWM 特點：

1. ORACLE用 HWM 來界定一個段中使用的塊和未使用的塊 .

舉個例子來說, 當我們創建一個表時 ,ORACLE 就會為這個對象分配一個段 . 在這個段中 , 即使我們未插入任何記錄 , 也至少有一個區被分配 , 第一個區的第一個塊就稱為段頭 (SEGMENT HEADE), 段頭中就儲存了一些信息 , 基中 HWM 的信息就存儲在此 . 此時 , 因為第一個區的第一塊用于存儲段頭的一些信息 , 雖然沒有存儲任何實際的記錄 , 但也算是被使用 , 此時 HWM 是位于第 2 個塊 . 當我們不斷插入數據到表后, 第 1 個塊已經放不下后面新插入的數據 , 此時 ,ORACLE 將高水位之上的塊用于存儲新增數據 , 同時 ,HWM 本身也向上移 . 也就是說 , 當我們不斷插入數據時 ,HWM 會往不斷上移 , 這樣 , 在 HWM 之下的 , 就表示使用過的塊 ,HWM 之上的就表示已分配但從未使用過的塊 .

2. HWM在插入數據時 , 當現有空間不足而進行空間的擴展時會向上移 , 但刪除數據時不會往下移 .

這就好比是水庫的水位, 當漲水時 , 水位往上移 , 當水退出后 , 最高水位的痕跡還是清淅可見 .

ORACLE 不會釋放空間以供其他對象使用，有一條簡單的理由：由于空間是為新插入的行保留的，并且要適應現有行的增長。被占用的最高空間稱為最高使用標記 (HWM) ，

3. HWM的信息存儲在段頭當中 .

HWM本身的信息是儲存在段頭 . 在段空間是手工管理方式時 ,ORACLE 是通過 FREELIST( 一個單向鏈表 ) 來管理段內的空間分配 . 在段空間是自動管理方式時 (ASSM),ORACLE 是通過 BITMAP 來管理段內的空間分配 .

4. ORACLE的全表掃描是讀取高水位標記 (HWM) 以下的所有塊 .

所以問題就產生了. 當用戶發出一個全表掃描時， ORACLE 始終必須從段一直掃描到 HWM ，即使它什么也沒有發現。該任務延長了全表掃描的時間。

5. 當用直接路徑插入行時，即使HWM 以下有空閑的數據庫塊，鍵入在插入數據時使用了 append關鍵字，則在插入時使用 HWM 以上的數據塊，此時 HWM 會自動增大。

一、什么是水線(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會自動增大。

三、如何知道一個表的HWM？

a) 首先對表進行分析:

ANALYZE TABLE <tablename> ESTIMATE/COMPUTE STATISTICS;

b) SELECT blocks, empty_blocks, num_rows

FROM user_tables

WHERE table_name = <tablename>;

說明：

BLOCKS 列代表該表中曾經使用過得數據庫塊的數目，即水線。

EMPTY_BLOCKS 代表分配給該表，但是在水線以上的數據庫塊，即從來沒有使用的數據塊。

讓我們以一個有28672行的BIG_EMP1表為例進行說明：

1) SQL> SELECT segment_name, segment_type, blocks

FROM dba_segments

WHERE segment_name='BIG_EMP1';

SEGMENT_NAME SEGMENT_TYPE BLOCKS

----------------- -------------- ---------

BIG_EMP1 TABLE 1024

1 row selected.

2) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;

Statement processed.

3) SQL> SELECT table_name,num_rows,blocks,empty_blocks

FROM user_tables

WHERE table_name='BIG_EMP1';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS

---------- -------- ------- -------------

BIG_EMP1 28672 700 323

1 row selected.

注意：

BLOCKS + EMPTY_BLOCKS (700+323=1023)比DBA_SEGMENTS.BLOCKS少1個數據庫塊，這是因為有一個數據庫塊被保留用作segment header。DBA_SEGMENTS.BLOCKS 表示分配給這個表的所有的數據庫塊的數目。USER_TABLES.BLOCKS表示已經使用過的數據庫塊的數目。

4) SQL> SELECT COUNT (DISTINCT

DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||

DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"

FROM big_emp1;

Used

----------

700

1 row selected.

5) SQL> delete from big_emp1;

28672 rows processed.

6) SQL> commit;

Statement processed.

7) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;

Statement processed.

8) SQL> SELECT table_name,num_rows,blocks,empty_blocks

FROM user_tables

WHERE table_name='BIG_EMP1';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS

--------- -------- ------- ----------

BIG_EMP1 0 700 323

1 row selected.

9) SQL> SELECT COUNT (DISTINCT

DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)||

DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "Used"

FROM big_emp1;

Used

----------

0 ----這表名沒有任何數據庫塊容納數據，即表中無數據

1 row selected.

10) SQL> TRUNCATE TABLE big_emp1;

Statement processed.

11) SQL> ANALYZE TABLE big_emp1 ESTIMATE STATISTICS;

Statement processed.

12) SQL> SELECT table_name,num_rows,blocks,empty_blocks

2> FROM user_tables

3> WHERE table_name='BIG_EMP1';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS

---------- -------- -------- ------------

BIG_EMP1 0 0 511

1 row selected.

13) SQL> SELECT segment_name,segment_type,blocks

FROM dba_segments

WHERE segment_name='BIG_EMP1';

SEGMENT_NAME SEGMENT_TYPE BLOCKS

------------ ------------- ------

BIG_EMP1 TABLE 512

1 row selected.

注意:

TRUNCATE命令回收了由delete命令產生的空閑空間，注意該表分配的空間由原先的1024塊降為512塊。

為了保留由delete命令產生的空閑空間，可以使用TRUNCATE TABLE big_emp1 REUSE STORAGE.

用此命令后，該表還會是原先的1024塊。

四、Oracle表段中的高水位線HWM

在Oracle數據的存儲中，可以把存儲空間想象為一個水庫，數據想象為水庫中的水。水庫中的水的位置有一條線叫做水位線，在Oracle中，這條線被稱為高水位線（High-warter mark, HWM）。在數據庫表剛建立的時候，由于沒有任何數據，所以這個時候水位線是空的，也就是說HWM為最低值。當插入了數據以后，高水位線就會上漲，但是這里也有一個特性，就是如果你采用delete語句刪除數據的話，數據雖然被刪除了，但是高水位線卻沒有降低，還是你剛才刪除數據以前那么高的水位。也就是說，這條高水位線在日常的增刪操作中只會上漲，不會下跌。

下面我們來談一下Oracle中Select語句的特性。Select語句會對表中的數據進行一次掃描，但是究竟掃描多少數據存儲塊呢，這個并不是說數據庫中有多少數據，Oracle就掃描這么大的數據塊，而是Oracle會掃描高水位線以下的數據塊。現在來想象一下，如果剛才是一張剛剛建立的空表，你進行了一次Select操作，那么由于高水位線HWM在最低的0位置上，所以沒有數據塊需要被掃描，掃描時間會極短。而如果這個時候你首先插入了一千萬條數據，然后再用delete語句刪除這一千萬條數據。由于插入了一千萬條數據，所以這個時候的高水位線就在一千萬條數據這里。后來刪除這一千萬條數據的時候，由于delete語句不影響高水位線，所以高水位線依然在一千萬條數據這里。這個時候再一次用select語句進行掃描，雖然這個時候表中沒有數據，但是由于掃描是按照高水位線來的，所以需要把一千萬條數據的存儲空間都要掃描一次，也就是說這次掃描所需要的時間和掃描一千萬條數據所需要的時間是一樣多的。所以有時候有人總是經常說，怎么我的表中沒有幾條數據，但是還是這么慢呢，這個時候其實奧秘就是這里的高水位線了。

那有沒有辦法讓高水位線下降呢，其實有一種比較簡單的方法，那就是采用TRUNCATE語句進行刪除數據。采用TRUNCATE語句刪除一個表的數據的時候，類似于重新建立了表，不僅把數據都刪除了，還把HWM給清空恢復為0。所以如果需要把表清空，在有可能利用TRUNCATE語句來刪除數據的時候就利用TRUNCATE語句來刪除表，特別是那種數據量有可能很大的臨時存儲表。

在手動段空間管理（Manual Segment Space Management）中，段中只有一個HWM，但是在Oracle9iRelease1才添加的自動段空間管理（Automatic Segment Space Management）中，又有了一個低HWM的概念出來。為什么有了HWM還又有一個低HWM呢，這個是因為自動段空間管理的特性造成的。在手段段空間管理中，當數據插入以后，如果是插入到新的數據塊中，數據塊就會被自動格式化等待數據訪問。而在自動段空間管理中，數據插入到新的數據塊以后，數據塊并沒有被格式化，而是在第一次在第一次訪問這個數據塊的時候才格式化這個塊。所以我們又需要一條水位線，用來標示已經被格式化的塊。這條水位線就叫做低HWM。一般來說，低HWM肯定是低于等于HWM的。

五、修正ORACLE表的高水位線

在ORACLE中，執行對表的刪除操作不會降低該表的高水位線。而全表掃描將始終讀取一個段(extent)中所有低于高水位線標記的塊。如果在執行刪除操作后不降低高水位線標記，則將導致查詢語句的性能低下。下面的方法都可以降低高水位線標記。

1.執行表重建指令 alter table table_name move;

(在線轉移表空間ALTER TABLE 。。。 MOVE TABLESPACE 。。。ALTER TABLE 。。。 MOVE 后面不跟參數也行，不跟參數表還是在原來的表空間，move后記住重建索引。如果以后還要繼續向這個表增加數據，沒有必要move，只是釋放出來的空間，只能這個表用，其他的表或者segment無法使用該空間)

2.執行alter table table_name shrink space; 注意，此命令為Oracle 10g新增功能，再執行該指令之前必須允許行移動alter table table_name enable row movement;

3.復制要保留的數據到臨時表t，drop原表，然后rename臨時表t為原表

4.emp/imp

5.alter table table_name deallocate unused

6.盡量truncate吧

About Me

...............................................................................................................................

● 本文作者：小麥苗，只專注于數據庫的技術，更注重技術的運用

● 本文在itpub（http://blog.itpub.net/26736162）、博客園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麥苗云盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 數據庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599 微信群：私聊

● 聯系我請加QQ好友(646634621)，注明添加緣由

● 于 2017-05-09 09:00 ~ 2017-05-30 22:00 在魔都完成

● 文章內容來源于小麥苗的學習筆記，部分整理自網絡，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的數據庫技術。