您當前位置：首頁 > 互聯網 > 用Delete與Truncate清除表數據對高水位的影響

用Delete與Truncate清除表數據對高水位的影響

來源：程序員人生發布時間：2014-10-08 08:00:01 閱讀次數：2271次

眾所周知，oracle段都有一個在段內容納數據塊的上限，我們把這個上限稱為"High Water Mark"（HWM）。這個HWM是一個標記，用來說明已經有多少沒有使用的數據塊分配給這個segment。原則上HWM只會增大，不會縮小，即使將表中的數據都刪除，HWM還是為原值。HWM就像一個水庫的歷史最高水位，這也是為何會稱之為“高水位”的緣故。實際環境中隨著我們表中數據的不斷增長，表的高水位也被不斷的推高。當高水位達到一定程度之后，會對該表上的SQL查詢效率產生負面影響，因此需要采取有效措施降低高水位。下面做個測試，來比較下如何刪除數據才能有效降低高水位。注意，我的測試環境為11.2.0.3，其他版本的測試結果可能略有不同。

--創建測試環境

SQL> conn / as sysdba

SQL> create tablespace zlm datafile '/u01/app/oracle/oradata/zlm11g/zlm01.dbf' size 50m;

Tablespace created.

SQL> create table zlm.zlm1 as select * from dba_objects;

Table created.

SQL> set lin 130 pages 130

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

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

ZLM1

--分析表的統計信息

SQL> analyze table zlm.zlm1 estimate statistics;

Table analyzed.

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

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

ZLM1 77341 1101 51 100 27-SEP-14

SQL> select count(*) from zlm.zlm1;

COUNT(*)

----------

75541

可以看到，用estimate分析的表的行數會不準確，差了1800條記錄，我們用compute來分析表

SQL> analyze table zlm.zlm1 compute statistics;

Table analyzed.

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

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

ZLM1 75541 1101 51 100 27-SEP-14

也可以用dbms_stats包來收集表的統計信息

SQL> exec dbms_stats.gather_table_stats('ZLM','ZLM1')

PL/SQL procedure successfully completed.

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED

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

ZLM1 75541 1101 51 97 27-SEP-14

這里我們發現，兩者除了在AVG_ROW_LEN字段上的值略有不同（前者是100，后者是97）外，其他基本一致

對于普通表而言，用dbms_stats包和用analyze來收集統計信息區別不大，但這兩種方法還是有各自應用場景的：

1、對于分區表，建議使用DBMS_STATS，而不是使用Analyze語句
a) 可以并行進行，對多個用戶，多個Table
b) 可以得到整個分區表的數據和單個分區的數據
c) 可以在不同級別上Compute Statistics：單個分區，子分區，全表，所有分區
d) 可以倒出統計信息
e) 可以用戶自動收集統計信息
2、DBMS_STATS的缺點
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，這兩個仍舊需要使用Analyze語句
c) DBMS_STATS 默認不對索引進行Analyze，因為默認Cascade是False，需要手工指定為True
3、對于oracle 9里面的External Table，不能使用Analyze，只能使用DBMS_STATS

SQL> select header_file,header_block,bytes,blocks,extents from dba_segments where segment_name like 'ZLM%';

HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS

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

6 130 9437184 1152 24

這里我們發現，在dba_tables中占用的塊為1101+52=1152，其中1152就是我們的高水位，EMPTY_BLOCKS表示高水位以下未被使用的空塊，

我們可以通過show_space()存儲過程來驗證一下：

SQL> exec show_space('ZLM1','ZLM')

Total Blocks............................1152

Total Bytes.............................9437184

Unused Blocks...........................51

Unused Bytes............................417792

Last Used Ext FileId....................6

Last Used Ext BlockId...................1152

Last Used Block.........................77

PL/SQL procedure successfully completed.

注意，這里77+51=128，正好是最后分配的一個extent的大小

--查看測試表ZLM1占用extent和block的情況

SQL> select block_id,extent_id,bytes,blocks from dba_extents where segment_name like 'ZLM%';

BLOCK_ID EXTENT_ID BYTES BLOCKS

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

128 0 65536 8

136 1 65536 8

144 2 65536 8

152 3 65536 8

160 4 65536 8

168 5 65536 8

176 6 65536 8

184 7 65536 8

192 8 65536 8

200 9 65536 8

208 10 65536 8

216 11 65536 8

224 12 65536 8

232 13 65536 8

240 14 65536 8

248 15 65536 8

256 16 1048576 128 --從256塊起，開始分配1M的空間作為1個extent

384 17 1048576 128

512 18 1048576 128

640 19 1048576 128

768 20 1048576 128

896 21 1048576 128

1024 22 1048576 128

1152 23 1048576 128

從block_id字段的值可以發現，當在表中插入數據后，分配給表的block從128開始一直到1152，并且，開始的1-16個extent會以8個塊（8*8=64K）為分配單位，而到了第17個extent之后，則以128個塊（8*128=1M）為分配單位，目前ZLM1表共分配了24個extent

--查看測試表ZLM1的block分配情況

SQL> col segment_name for a10

SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name like 'ZLM%';

SEGMENT_NA HEADER_FILE HEADER_BLOCK BLOCKS BYTES EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

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

ZLM1 6 130 1152 9437184 24 1 2147483645 TABLE

這里可以發現一個規律，dba_segments中的header_block的值總是要比dba_extents查出來值的多2個塊

--創建測試表ZLM2并查看block分配情況

SQL> create table zlm.zlm2 as select * from zlm.zlm1;

Table created.

SQL> select segment_name,header_file,header_block,blocks,bytes,extents,min_extents,max_extents,segment_type from dba_segments where segment_name like 'ZLM%';

SEGMENT_NA HEADER_FILE HEADER_BLOCK BLOCKS BYTES EXTENTS MIN_EXTENTS MAX_EXTENTS SEGMENT_TYPE

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

ZLM2 6 1282 1152 9437184 24 1 2147483645 TABLE

ZLM1 6 130 1152 9437184 24 1 2147483645 TABLE

第2個表從1282個塊開始分配，為什么是1282呢？1282=130+1152，也就是從測試表ZLM1之后的block開始分配

--delete測試表ZLM1中的數據

SQL> delete from zlm.zlm1;

75541 rows deleted.

SQL> commit;

Commit complete.

SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'ZLM%';

TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED