用Delete與Truncate清除表數(shù)據(jù)對高水位的影響
來源：程序員人生 發(fā)布時間：2014-10-08 08:00:01 閱讀次數(shù)：2273次
眾所周知，oracle段都有一個在段內(nèi)容納數(shù)據(jù)塊的上限，我們把這個上限稱為"High Water Mark"（HWM）。這個HWM是一個標(biāo)記，用來說明已經(jīng)有多少沒有使用的數(shù)據(jù)塊分配給這個segment。原則上HWM只會增大，不會縮小，即使將表中的數(shù)據(jù)都刪除，HWM還是為原值。HWM就像一個水庫的歷史最高水位，這也是為何會稱之為“高水位”的緣故。實(shí)際環(huán)境中隨著我們表中數(shù)據(jù)的不斷增長，表的高水位也被不斷的推高。當(dāng)高水位達(dá)到一定程度之后，會對該表上的SQL查詢效率產(chǎn)生負(fù)面影響，因此需要采取有效措施降低高水位。下面做個測試，來比較下如何刪除數(shù)據(jù)才能有效降低高水位。注意，我的測試環(huán)境為11.2.0.3，其他版本的測試結(jié)果可能略有不同。
--創(chuàng)建測試環(huán)境
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
--分析表的統(tǒng)計信息
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分析的表的行數(shù)會不準(zhǔn)確，差了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包來收集表的統(tǒng)計信息
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
這里我們發(fā)現(xiàn)，兩者除了在AVG_ROW_LEN字段上的值略有不同（前者是100，后者是97）外，其他基本一致
對于普通表而言，用dbms_stats包和用analyze來收集統(tǒng)計信息區(qū)別不大，但這兩種方法還是有各自應(yīng)用場景的：
1、對于分區(qū)表，建議使用DBMS_STATS，而不是使用Analyze語句
a) 可以并行進(jìn)行，對多個用戶，多個Table
b) 可以得到整個分區(qū)表的數(shù)據(jù)和單個分區(qū)的數(shù)據(jù)
c) 可以在不同級別上Compute Statistics：單個分區(qū)，子分區(qū)，全表，所有分區(qū)
d) 可以倒出統(tǒng)計信息
e) 可以用戶自動收集統(tǒng)計信息
2、DBMS_STATS的缺點(diǎn)
a) 不能Validate Structure
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，這兩個仍舊需要使用Analyze語句
c) DBMS_STATS 默認(rèn)不對索引進(jìn)行Analyze，因為默認(rèn)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
這里我們發(fā)現(xiàn)，在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字段的值可以發(fā)現(xiàn)，當(dāng)在表中插入數(shù)據(jù)后，分配給表的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
這里可以發(fā)現(xiàn)一個規(guī)律，dba_segments中的header_block的值總是要比dba_extents查出來值的多2個塊
--創(chuàng)建測試表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中的數(shù)據(jù)
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
------------------------------ ---------- ---------- ------------ ----------- ------------------
ZLM1 75541 1101 51 100 27-SEP-14
--分析測試表ZLM1和ZLM2并查看各自數(shù)據(jù)塊占用情況
SQL> analyze table zlm.zlm1 compute statistics;
Table analyzed.
SQL> analyze table zlm.zlm2 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
生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈
------分隔線----------------------------
------分隔線----------------------------
