【實驗】關於HWM（高水位）的學習與測試
深入瞭解oracle的高水位(HWM)
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在插入資料時,當現有空間不足而進行空間的擴充套件時會向上移,但刪除資料時不會往下移.
這就好比是水庫的水位,當漲水時,水位往上移,當水退出後,最高水位的痕跡還是清淅可見.
考慮讓看一個段，如一張表，其中填滿了塊，。在正常操作過程中，刪除了一些行。現有就有了許多浪費的空間：
ORACLE 不會釋放空間以供其他物件使用，有一條簡單的理由：由於空間是為新插入的行保留的，並且要適應現有行的增長。被佔用的最高空間稱為最高使用標記 (HWM)
(三)HWM的資訊儲存在段頭當中.
HWM本身的資訊是儲存在段頭.在段空間是手工管理方式時,ORACLE是透過FREELIST(一個單向連結串列)來管理段內的空間分配.在段空間是自動管理方式時(ASSM),ORACLE是透過BITMAP來管理段內的空間分配.
(四)ORACLE的全表掃描是讀取高水位標記(HWM)以下的所有塊.
所以問題就產生了(一直不解為何ORACLE會採用這種不合理的方式).當使用者發出一個全表掃描時，ORACLE 始終必須從段一直掃描到 HWM，即使它什麼也沒有發現。該任務延長了全表掃描的時間。
(五)當用直接路徑插入行時 — 例如，透過直接載入插入（用 APPEND 提示插入）或透過 SQL*LOADER 直接路徑 — 資料塊直接置於 HWM 之上。它下面的空間就浪費掉了。
來分析這兩個問題,後者只是帶來空間的浪費,但前者不僅是空間的浪費,而且會帶來嚴重的效能問題.來看看下面的例子:
(A)先來搭建測試的環境(172.17.8.202,Linux,ORACLE 版本：1002000100),第一步先建立一個段空間為手工管理的表空間:
CREATE TABLESPACE "SEC"
LOGGING
DATAFILE '/u01/app/oracle/oradata/TSH1/SEC.DBF' 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 SEC;
插入記錄
DECLARE
I NUMBER(10);
BEGIN
FOR I IN 1..10000000 LOOP
INSERT INTO TEST_TAB VALUES(I,'TESTSTRING');
END LOOP;
COMMIT;
END ;
/
使用了1.3G的表空間，好大！
(C)來查詢一下,看在插入一千萬條記錄後所訪問的塊數和查詢所用時間:
conn /as sysdba
SET TIMING ON
SET AUTOTRACE TRACEONLY
SELECT COUNT(*) FROM sec.TEST_TAB;
SQL> SELECT COUNT(*) FROM sec.TEST_TAB;
Elapsed: 00:01:36.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2778628632
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34513 (2)| 00:06:55 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST_TAB | 10M| 34513 (2)| 00:06:55 |
-----------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
312025 consistent gets
154253 physical reads
11233336 redo size
411 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
這句SQL總供耗時是:1分36秒.訪問方式是採用全表掃描方式(FTS),邏輯讀了 312025 個BLOCK,物理讀了 154253 個BLOCK.
來分析一下這個表:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'SEC',
TABNAME=> 'TEST_TAB',
PARTNAME=> NULL);END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:01:58.63
SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS from dba_tables where table_name = 'TEST_TAB';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
10004718 156011 0
發現這個表目前使用的BLOCK有: 156011,未使用的BLOCK(EMPTY_BLOCKS)為:0,總行數為(NUM_ROWS):10004718
再查詢一下結果：
SQL> SELECT COUNT(*) FROM sec.TEST_TAB;
Elapsed: 00:00:40.67
Execution Plan
----------------------------------------------------------
Plan hash value: 2778628632
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34500 (2)| 00:06:54 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST_TAB | 10M| 34500 (2)| 00:06:54 |
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
156034 consistent gets
152544 physical reads
0 redo size
411 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
這句SQL總供耗時是:40秒.訪問方式是採用全表掃描方式(FTS),邏輯讀了 156034 個BLOCK,物理讀了 152544 個BLOCK.
(D)接下來把此表的記錄用DELETE方式刪掉,然後再來看看SELECT COUNT(*) FROM TEST_TAB所花的時間:
SQL> DELETE FROM sec.test_tab;
10000000 rows deleted.
Elapsed: 00:10:22.64
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT COUNT(*) FROM sec.TEST_TAB;
Elapsed: 00:01:32.12
Execution Plan
----------------------------------------------------------
Plan hash value: 2778628632
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34500 (2)| 00:06:54 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST_TAB | 10M| 34500 (2)| 00:06:54 |
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
156034 consistent gets
154379 physical reads
0 redo size
410 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
在DELETE表後,此時表中已沒有一條記錄,為什麼SELECT COUNT(*) FROMTEST_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表後段空間實際使用的狀況:
VAR TOTAL_BLOCKS NUMBER
VAR TOTAL_BYTES NUMBER
VAR UNUSED_BLOCKS NUMBER
VAR UNUSED_BYTES NUMBER
VAR LAST_USED_EXTENT_FILE_ID NUMBER
VAR LAST_USED_EXTENT_BLOCK_ID NUMBER
VAR LAST_USED_BLOCK NUMBER
EXEC DBMS_SPACE.UNUSED_SPACE('SEC','TEST_TAB','TABLE',:TOTAL_BLOCKS,:TOTAL_BYTES,:UNUSED_BLOCKS,:UNUSED_BYTES,:LAST_USED_EXTENT_FILE_ID,:LAST_USED_EXTENT_BLOCK_ID,:LAST_USED_BLOCK);
PRINT TOTAL_BLOCKS
PRINT TOTAL_BYTES
PRINT UNUSED_BLOCKS
PRINT UNUSED_BYTES
PRINT LAST_USED_EXTENT_FILE_ID
PRINT LAST_USED_EXTENT_BLOCK_ID
PRINT LAST_USED_BLOCK
輸出結果為:
TOTAL_BLOCKS ------------ 156544 --總共164352塊
TOTAL_BYTES ------------ 1282408448
UNUSED_BLOCKS ------------ 532 --有7168塊沒有用過,也就是在HWM上面的塊數
UNUSED_BYTES ------------ 4358144
LAST_USED_EXTENT_FILE_ID ------------ 6
LAST_USED_EXTENT_BLOCK_ID ------------ 148489 --- BLOCK ID 是針對資料檔案來編號的，表示最後使用的一個EXTENT的第一個BLOCK的編號
LAST_USED_BLOCK ------------ 7660 ---在最後使用的一個EXTENT 中一共用了1024塊
總共用了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 sec.TEST_TAB DEALLOCATE UNUSED; 看看段空間的使用狀況:
TOTAL_BLOCKS ------------ 156016 157184
TOTAL_BYTES ------------ 1278083072 1287651328
UNUSED_BLOCKS ------------ 4 0
UNUSED_BYTES ------------ 32768 0
LAST_USED_EXTENT_FILE_ID ------------ 6 9
LAST_USED_EXTENT_BLOCK_ID ------------ 148489 158856
LAST_USED_BLOCK ------------ 7660 1024
此時再代入上面的公式,算出HWM的位置: 157184-0=157184 HWM所在的BLOCKID是158856+1024-1=159879,跟剛剛的沒有變化,也就是說執行ALTER TABLETEST_TAB DEALLOCATE UNUSED後,段的高水位標記的位置沒有改變,但是看UNUSED BLOCKS變為0了,總的塊數減少到157184,這證明,DEALLOCATE UNUSED為釋放HWM上面的未使用空間,但是並不會釋放HWM下面的自由空間,也不會移動HWM的位置.
第二步:再來看看執行ALTER TABLE sec.TEST_TAB MOVE;後段空間的使用狀況:
TOTAL_BLOCKS ------------ 8 8
TOTAL_BYTES ------------ 65536 65536
UNUSED_BLOCKS ------------ 7 5
UNUSED_BYTES ------------ 57344 40960
LAST_USED_EXTENT_FILE_ID ------------ 6 9
LAST_USED_EXTENT_BLOCK_ID ------------ 9 2632
LAST_USED_BLOCK ------------ 1 3
此時,總共用到的塊數已變為8, 再代入上面的公式,算出HWM的位置: 8-5=3HWM所在的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> SET AUTOTRACE TRACEONLY
SQL>SELECT COUNT(*) FROM sec.TEST_TAB;
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 2778628632
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34500 (2)| 00:06:54 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST_TAB | 10M| 34500 (2)| 00:06:54 |
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
410 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
很快,不到1秒.
最後再來對錶作一次分析, 此時這個表目前使用的BLOCK為: 0,未使用的BLOCK(EMPTY_BLOCKS)為:0,總行數為(NUM_ROWS):0從中也可以發現,分析表和SHOW_SPACE顯示的資料有點不一致.那麼哪個是準的呢?其實這兩個都是準的,只不過計算的方法有點不同.事實上,當你建立了一個物件如表以後,不管你有沒有插入資料,它都會佔用一些塊,ORACLE也會給它分配必要的空間.同樣,用ALTER TABLE MOVE釋放自由空間後,還是保留了一些空間給這個表.最後,再來執行TRUNCATE命令,截斷這個表,看看段空間的使用狀況:
SQL> truncate table sec.test_tab;
TOTAL_BLOCKS ------------ 8 8
TOTAL_BYTES ------------ 65536 65536
UNUSED_BLOCKS ------------ 7 5
UNUSED_BYTES ------------ 57344 40960
LAST_USED_EXTENT_FILE_ID ------------ 6 9
LAST_USED_EXTENT_BLOCK_ID ------------ 9 2632
LAST_USED_BLOCK ------------ 1 3
發現TRUNCATE後和MOVE沒有什麼變化.為了最終驗證一下我上面的觀點,我再DROP一下表,然後新建這個表,看看這時在沒有插入任何資料之前,是否ORACLE確實有給這個物件分配必要的空間:
DROP TABLE sec.TEST_TAB;
CREATE TABLE sec.TEST_TAB(C1 NUMBER(10),C2 CHAR(100)) TABLESPACE SEC;
TOTAL_BLOCKS ------------ 8 8
TOTAL_BYTES ------------ 65536 65536
UNUSED_BLOCKS ------------ 7 5
UNUSED_BYTES ------------ 57344 40960
LAST_USED_EXTENT_FILE_ID ------------ 6 9
LAST_USED_EXTENT_BLOCK_ID ------------ 17 2112
LAST_USED_BLOCK ------------ 1 3
,即使我沒有插入任何一行記錄,ORACLE還是給它分配了8個塊.當然這個跟建表語句的INITIAL 引數及MINEXTENTS引數有關:請看TEST_TAB的儲存引數:
STORAGE
(
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 REUSESTORAGE,僅將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
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在插入資料時,當現有空間不足而進行空間的擴充套件時會向上移,但刪除資料時不會往下移.
這就好比是水庫的水位,當漲水時,水位往上移,當水退出後,最高水位的痕跡還是清淅可見.
考慮讓看一個段，如一張表，其中填滿了塊，。在正常操作過程中，刪除了一些行。現有就有了許多浪費的空間：
ORACLE 不會釋放空間以供其他物件使用，有一條簡單的理由：由於空間是為新插入的行保留的，並且要適應現有行的增長。被佔用的最高空間稱為最高使用標記 (HWM)
(三)HWM的資訊儲存在段頭當中.
HWM本身的資訊是儲存在段頭.在段空間是手工管理方式時,ORACLE是透過FREELIST(一個單向連結串列)來管理段內的空間分配.在段空間是自動管理方式時(ASSM),ORACLE是透過BITMAP來管理段內的空間分配.
(四)ORACLE的全表掃描是讀取高水位標記(HWM)以下的所有塊.
所以問題就產生了(一直不解為何ORACLE會採用這種不合理的方式).當使用者發出一個全表掃描時，ORACLE 始終必須從段一直掃描到 HWM，即使它什麼也沒有發現。該任務延長了全表掃描的時間。
(五)當用直接路徑插入行時 — 例如，透過直接載入插入（用 APPEND 提示插入）或透過 SQL*LOADER 直接路徑 — 資料塊直接置於 HWM 之上。它下面的空間就浪費掉了。
來分析這兩個問題,後者只是帶來空間的浪費,但前者不僅是空間的浪費,而且會帶來嚴重的效能問題.來看看下面的例子:
(A)先來搭建測試的環境(172.17.8.202,Linux,ORACLE 版本：1002000100),第一步先建立一個段空間為手工管理的表空間:
CREATE TABLESPACE "SEC"
LOGGING
DATAFILE '/u01/app/oracle/oradata/TSH1/SEC.DBF' 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 SEC;
插入記錄
DECLARE
I NUMBER(10);
BEGIN
FOR I IN 1..10000000 LOOP
INSERT INTO TEST_TAB VALUES(I,'TESTSTRING');
END LOOP;
COMMIT;
END ;
/
使用了1.3G的表空間，好大！
(C)來查詢一下,看在插入一千萬條記錄後所訪問的塊數和查詢所用時間:
conn /as sysdba
SET TIMING ON
SET AUTOTRACE TRACEONLY
SELECT COUNT(*) FROM sec.TEST_TAB;
SQL> SELECT COUNT(*) FROM sec.TEST_TAB;
Elapsed: 00:01:36.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2778628632
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34513 (2)| 00:06:55 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST_TAB | 10M| 34513 (2)| 00:06:55 |
-----------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
312025 consistent gets
154253 physical reads
11233336 redo size
411 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
這句SQL總供耗時是:1分36秒.訪問方式是採用全表掃描方式(FTS),邏輯讀了 312025 個BLOCK,物理讀了 154253 個BLOCK.
來分析一下這個表:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'SEC',
TABNAME=> 'TEST_TAB',
PARTNAME=> NULL);END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:01:58.63
SQL> select NUM_ROWS,BLOCKS,EMPTY_BLOCKS from dba_tables where table_name = 'TEST_TAB';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
10004718 156011 0
發現這個表目前使用的BLOCK有: 156011,未使用的BLOCK(EMPTY_BLOCKS)為:0,總行數為(NUM_ROWS):10004718
再查詢一下結果：
SQL> SELECT COUNT(*) FROM sec.TEST_TAB;
Elapsed: 00:00:40.67
Execution Plan
----------------------------------------------------------
Plan hash value: 2778628632
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34500 (2)| 00:06:54 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST_TAB | 10M| 34500 (2)| 00:06:54 |
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
156034 consistent gets
152544 physical reads
0 redo size
411 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
這句SQL總供耗時是:40秒.訪問方式是採用全表掃描方式(FTS),邏輯讀了 156034 個BLOCK,物理讀了 152544 個BLOCK.
(D)接下來把此表的記錄用DELETE方式刪掉,然後再來看看SELECT COUNT(*) FROM TEST_TAB所花的時間:
SQL> DELETE FROM sec.test_tab;
10000000 rows deleted.
Elapsed: 00:10:22.64
SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT COUNT(*) FROM sec.TEST_TAB;
Elapsed: 00:01:32.12
Execution Plan
----------------------------------------------------------
Plan hash value: 2778628632
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34500 (2)| 00:06:54 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST_TAB | 10M| 34500 (2)| 00:06:54 |
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
156034 consistent gets
154379 physical reads
0 redo size
410 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
在DELETE表後,此時表中已沒有一條記錄,為什麼SELECT COUNT(*) FROMTEST_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表後段空間實際使用的狀況:
VAR TOTAL_BLOCKS NUMBER
VAR TOTAL_BYTES NUMBER
VAR UNUSED_BLOCKS NUMBER
VAR UNUSED_BYTES NUMBER
VAR LAST_USED_EXTENT_FILE_ID NUMBER
VAR LAST_USED_EXTENT_BLOCK_ID NUMBER
VAR LAST_USED_BLOCK NUMBER
EXEC DBMS_SPACE.UNUSED_SPACE('SEC','TEST_TAB','TABLE',:TOTAL_BLOCKS,:TOTAL_BYTES,:UNUSED_BLOCKS,:UNUSED_BYTES,:LAST_USED_EXTENT_FILE_ID,:LAST_USED_EXTENT_BLOCK_ID,:LAST_USED_BLOCK);
PRINT TOTAL_BLOCKS
PRINT TOTAL_BYTES
PRINT UNUSED_BLOCKS
PRINT UNUSED_BYTES
PRINT LAST_USED_EXTENT_FILE_ID
PRINT LAST_USED_EXTENT_BLOCK_ID
PRINT LAST_USED_BLOCK
輸出結果為:
TOTAL_BLOCKS ------------ 156544 --總共164352塊
TOTAL_BYTES ------------ 1282408448
UNUSED_BLOCKS ------------ 532 --有7168塊沒有用過,也就是在HWM上面的塊數
UNUSED_BYTES ------------ 4358144
LAST_USED_EXTENT_FILE_ID ------------ 6
LAST_USED_EXTENT_BLOCK_ID ------------ 148489 --- BLOCK ID 是針對資料檔案來編號的，表示最後使用的一個EXTENT的第一個BLOCK的編號
LAST_USED_BLOCK ------------ 7660 ---在最後使用的一個EXTENT 中一共用了1024塊
總共用了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 sec.TEST_TAB DEALLOCATE UNUSED; 看看段空間的使用狀況:
TOTAL_BLOCKS ------------ 156016 157184
TOTAL_BYTES ------------ 1278083072 1287651328
UNUSED_BLOCKS ------------ 4 0
UNUSED_BYTES ------------ 32768 0
LAST_USED_EXTENT_FILE_ID ------------ 6 9
LAST_USED_EXTENT_BLOCK_ID ------------ 148489 158856
LAST_USED_BLOCK ------------ 7660 1024
此時再代入上面的公式,算出HWM的位置: 157184-0=157184 HWM所在的BLOCKID是158856+1024-1=159879,跟剛剛的沒有變化,也就是說執行ALTER TABLETEST_TAB DEALLOCATE UNUSED後,段的高水位標記的位置沒有改變,但是看UNUSED BLOCKS變為0了,總的塊數減少到157184,這證明,DEALLOCATE UNUSED為釋放HWM上面的未使用空間,但是並不會釋放HWM下面的自由空間,也不會移動HWM的位置.
第二步:再來看看執行ALTER TABLE sec.TEST_TAB MOVE;後段空間的使用狀況:
TOTAL_BLOCKS ------------ 8 8
TOTAL_BYTES ------------ 65536 65536
UNUSED_BLOCKS ------------ 7 5
UNUSED_BYTES ------------ 57344 40960
LAST_USED_EXTENT_FILE_ID ------------ 6 9
LAST_USED_EXTENT_BLOCK_ID ------------ 9 2632
LAST_USED_BLOCK ------------ 1 3
此時,總共用到的塊數已變為8, 再代入上面的公式,算出HWM的位置: 8-5=3HWM所在的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> SET AUTOTRACE TRACEONLY
SQL>SELECT COUNT(*) FROM sec.TEST_TAB;
Elapsed: 00:00:00.06
Execution Plan
----------------------------------------------------------
Plan hash value: 2778628632
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34500 (2)| 00:06:54 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST_TAB | 10M| 34500 (2)| 00:06:54 |
-----------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
410 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
很快,不到1秒.
最後再來對錶作一次分析, 此時這個表目前使用的BLOCK為: 0,未使用的BLOCK(EMPTY_BLOCKS)為:0,總行數為(NUM_ROWS):0從中也可以發現,分析表和SHOW_SPACE顯示的資料有點不一致.那麼哪個是準的呢?其實這兩個都是準的,只不過計算的方法有點不同.事實上,當你建立了一個物件如表以後,不管你有沒有插入資料,它都會佔用一些塊,ORACLE也會給它分配必要的空間.同樣,用ALTER TABLE MOVE釋放自由空間後,還是保留了一些空間給這個表.最後,再來執行TRUNCATE命令,截斷這個表,看看段空間的使用狀況:
SQL> truncate table sec.test_tab;
TOTAL_BLOCKS ------------ 8 8
TOTAL_BYTES ------------ 65536 65536
UNUSED_BLOCKS ------------ 7 5
UNUSED_BYTES ------------ 57344 40960
LAST_USED_EXTENT_FILE_ID ------------ 6 9
LAST_USED_EXTENT_BLOCK_ID ------------ 9 2632
LAST_USED_BLOCK ------------ 1 3
發現TRUNCATE後和MOVE沒有什麼變化.為了最終驗證一下我上面的觀點,我再DROP一下表,然後新建這個表,看看這時在沒有插入任何資料之前,是否ORACLE確實有給這個物件分配必要的空間:
DROP TABLE sec.TEST_TAB;
CREATE TABLE sec.TEST_TAB(C1 NUMBER(10),C2 CHAR(100)) TABLESPACE SEC;
TOTAL_BLOCKS ------------ 8 8
TOTAL_BYTES ------------ 65536 65536
UNUSED_BLOCKS ------------ 7 5
UNUSED_BYTES ------------ 57344 40960
LAST_USED_EXTENT_FILE_ID ------------ 6 9
LAST_USED_EXTENT_BLOCK_ID ------------ 17 2112
LAST_USED_BLOCK ------------ 1 3
,即使我沒有插入任何一行記錄,ORACLE還是給它分配了8個塊.當然這個跟建表語句的INITIAL 引數及MINEXTENTS引數有關:請看TEST_TAB的儲存引數:
STORAGE
(
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 REUSESTORAGE,僅將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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-557311/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- delete與高水位線HWM回收delete
- Oracle 高水位(HWM)Oracle
- oracle的高水位線HWMOracle
- oracle的高水位線(HWM)Oracle
- ORACLE 高水位線（HWM）Oracle
- Oracle 高水位(HWM)標記Oracle
- 深入瞭解oracle的高水位(HWM)Oracle
- Oracle表段中的高水位線HWMOracle
- 關於高水位的知識
- Oracle高水位線(HWM)及效能優化Oracle優化
- Oracle案例10——HWM（高水位線）效能優化Oracle優化
- 關於高水位線和deletedelete
- oracle11g表的高水位線hwm與dbms_space系列一Oracle
- Oracle段高水位(HWM, high water mark)問題Oracle
- 各個Oracle 版本下如何調整高水位(HWM)Oracle
- 關於檔案格式Fuzzing測試與漏洞挖掘的學習
- Oracle表段中高水位線HWMOracle
- oracle的學習方法——關於測試的兩點體會Oracle
- Oracle SCN相關問題學習與測試Oracle
- 資料塊的插入與高水位
- Oracle Job學習與測試Oracle
- Oracle privilege學習與測試Oracle
- Oracle Roles學習與測試Oracle
- Oracle Audit 學習與測試Oracle
- Oracle約束的學習與測試Oracle
- 關於 VC 編譯的猜想與試驗 (轉)編譯
- 關於軟體驗證中的單元測試
- 關於介面測試——自動化框架的設計與實現框架
- oracle 高水位線及如何有效的降低高水位線Oracle
- 關於INNODB SYSTEM RECORD infimum和supremum的學習和實驗研究REM
- 關於orm的個人測試——SqlSugar與FreeSqlORMSqlSugar
- 關於發版測試的認知與案例
- RMAN Catalog 學習與測試
- 關於mongodb的學習與探索二MongoDB
- 並行執行的學習與測試並行
- 對Oracle高水位線的研究實踐Oracle
- Oracle高水位Oracle
- 關於IOS測試iOS