Oracle高水位線(HWM)及性能優(yōu)化[轉(zhuǎn)]
說到HWM,我們首先要簡要的談?wù)凮RACLE的邏輯存儲管理.我們知道,ORACLE在邏輯存儲上分4個粒度:表空間,段,區(qū)和塊.
(1)塊:是粒度最小的存儲單位,現(xiàn)在標(biāo)準(zhǔn)的塊大小是8K,ORACLE每一次I/O操作也是按塊來操作的,也就是說當(dāng)ORACLE從數(shù)據(jù)文件讀數(shù)據(jù)時,是讀取多少個塊,而不是多少行.
(2)區(qū):由一系列相鄰的塊而組成,這也是ORACLE空間分配的基本單位,舉個例子來說,當(dāng)我們創(chuàng)建一個表PM_USER時,首先ORACLE會分配一區(qū)的空間給這個表,隨著不斷的INSERT數(shù)據(jù)到PM_USER,原來的這個區(qū)容不下插入的數(shù)據(jù)時,ORACLE是以區(qū)為單位進(jìn)行擴展的,也就是說再分配多少個區(qū)給PM_USER,而不是多少個塊.
(3)段:是由一系列的區(qū)所組成,一般來說,當(dāng)創(chuàng)建一個對象時(表,索引),就會分配一個段給這個對象.所以從某種意義上來說,段就是某種特定的數(shù)據(jù).如CREATE TABLE PM_USER,這個段就是數(shù)據(jù)段,而CREATE INDEX ON PM_USER(NAME),ORACLE同樣會分配一個段給這個索引,但這是一個索引段了.查詢段的信息可以通過數(shù)據(jù)字典: SELECT * FROM USER_SEGMENTS來獲得,
(4)表空間:包含段,區(qū)及塊.表空間的數(shù)據(jù)物理上儲存在其所在的數(shù)據(jù)文件中.一個數(shù)據(jù)庫至少要有一個表空間.
OK,我們現(xiàn)在回到HWM上來,那么,什么是高水位標(biāo)記呢?這就跟ORACLE的段空間管理相關(guān)了.
(一)ORACLE用HWM來界定一個段中使用的塊和未使用的塊.
舉個例子來說,當(dāng)我們創(chuàng)建一個表:PT_SCHE_DETAIL時,ORACLE就會為這個對象分配一個段.在這個段中,即使我們未插入任何記錄,也至少有一個區(qū)被分配,第一個區(qū)的第一個塊就稱為段頭(SEGMENT HEADE),段頭中就儲存了一些信息,基中HWM的信息就存儲在此.此時,因為第一個區(qū)的第一塊用于存儲段頭的一些信息,雖然沒有存儲任何實際的記錄,但也算是被使用,此時HWM是位于第2個塊.當(dāng)我們不斷插入數(shù)據(jù)到PM_USER后,第1個塊已經(jīng)放不下后面新插入的數(shù)據(jù),此時,ORACLE將高水位之上的塊用于存儲新增數(shù)據(jù),同時,HWM本身也向上移.也就是說,當(dāng)我們不斷插入數(shù)據(jù)時,HWM會往不斷上移,這樣,在HWM之下的,就表示使用過的塊,HWM之上的就表示已分配但從未使用過的塊.
(二)HWM在插入數(shù)據(jù)時,當(dāng)現(xiàn)有空間不足而進(jìn)行空間的擴展時會向上移,但刪除數(shù)據(jù)時不會往下移.
這就好比是水庫的水位,當(dāng)漲水時,水位往上移,當(dāng)水退出后,最高水位的痕跡還是清淅可見.
考慮讓我們看一個段，如一張表，其中填滿了塊，如圖 1 所示。在正常操作過程中，刪除了一些行，如圖 2 所示。現(xiàn)有就有了許多浪費的空間：(I) 在表的上一個末端和現(xiàn)有的塊之間，以及 (II) 在塊內(nèi)部，其中還有一些沒有刪除的行。
圖1：分配給該表的塊。用灰色正方形表示行
ORACLE 不會釋放空間以供其他對象使用，有一條簡單的理由：由于空間是為新插入的行保留的，并且要適應(yīng)現(xiàn)有行的增長。被占用的最高空間稱為最高使用標(biāo)記 (HWM)，如圖 2 所示。
圖2：行后面的塊已經(jīng)刪除了；HWM 仍保持不變
(三)HWM的信息存儲在段頭當(dāng)中.
HWM本身的信息是儲存在段頭.在段空間是手工管理方式時,ORACLE是通過FREELIST(一個單向鏈表)來管理段內(nèi)的空間分配.在段空間是自動管理方式時(ASSM),ORACLE是通過BITMAP來管理段內(nèi)的空間分配.
(四)ORACLE的全表掃描是讀取高水位標(biāo)記(HWM)以下的所有塊.
所以問題就產(chǎn)生了.當(dāng)用戶發(fā)出一個全表掃描時，ORACLE 始終必須從段一直掃描到 HWM，即使它什么也沒有發(fā)現(xiàn)。該任務(wù)延長了全表掃描的時間。
(五)當(dāng)用直接路徑插入行時 — 例如，通過直接加載插入（用 APPEND 提示插入）或通過 SQL*LOADER 直接路徑 — 數(shù)據(jù)塊直接置于 HWM 之上。它下面的空間就浪費掉了。
我們來分析這兩個問題,后者只是帶來空間的浪費,但前者不僅是空間的浪費,而且會帶來嚴(yán)重的性能問題.我們來看看下面的例子:
(A)我們先來搭建測試的環(huán)境,第一步先創(chuàng)建一個段空間為手工管理的表空間:
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)創(chuàng)建一個表,注意,此表的第二個字段我故意設(shè)成是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)我們來查詢一下,看在插入一千萬條記錄后所訪問的塊數(shù)和查詢所用時間:
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
我們來看上面的執(zhí)行計劃,這句SQL總供耗時是:1分3秒.訪問方式是采用全表掃描方式(FTS),邏輯讀了156310個BLOCK,物理讀了154239個BLOCK.
我們來分析一下這個表:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'TEST',
TABNAME=> 'TEST_TAB',
PARTNAME=> NULL);END;
/
發(fā)現(xiàn)這個表目前使用的BLOCK有: 156532,未使用的BLOCK(EMPTY_BLOCKS)為:0,總行數(shù)為(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,總行數(shù)為(NUM_ROWS)已變成:0
為什么表目前使的BLOCK數(shù)還是156532呢?
問題的根源就在于ORACLE的HWM.也就是說,在新增記錄時,HWM會慢慢往上移,但是在刪除記錄后,HWM卻不會往下移,也就是說,DELETE一千萬條記錄后,此表的HWM根本沒移動,還在原來的那個位置,所以,HWM以下的塊數(shù)同樣也是一樣的.ORACLE的全表掃描是讀取ORACLE高水位標(biāo)記下的所有BLOCK,也就是說,不管HWM下的BLOCK現(xiàn)在實際有沒有存放數(shù)據(jù),ORACLE都會一一讀取,這樣,大家可想而知,在我們DELETE表后,ORACLE讀了大量的空塊,耗去了大量的時間.
我們再來看DELETE表后段空間實際使用的狀況:
SQL> EXEC SHOW_SPACE('TEST_TAB','TEST');
TOTAL BLOCKS............................164352 --總共164352塊
TOTAL BYTES.............................1346371584
UNUSED BLOCKS...........................7168 --有7168塊沒有用過,也就是在HWM上面的塊數(shù)
UNUSED BYTES............................58720256
LAST USED EXT FILEID....................9
LAST USED EXT BLOCKID...................158856-- BLOCK ID 是針對數(shù)據(jù)文件來編號的，表示最后使用的一個EXTENT的第一個BLOCK的編號
LAST USED BLOCK.........................1024 --在最后使用的一個EXTENT 中一共用了1024塊
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED
總共用了164352塊，除了一個SEGMENT HEADER,實際總共用了164351個塊，有7168塊從來沒有使用過。LAST USED BLOCK表示在最后一個使用的EXTENT 中使用的BLOCK, 結(jié)合 LAST USED EXT BLOCK ID可以計算 HWM 位置 :
LAST USED EXT BLOCK ID + LAST USED BLOCK -1 = HWM 所在的數(shù)據(jù)文件的BLOCK編號
代入得出: 158856+1024-1=159879,這個就是HWM所有的BLOCK編號
HWM所在的塊:TOTAL BLOCKS- UNUSED BLOCKS=164352-7168=157184,也就是說,HWM在第157184個塊,其BLOCKID是159879
(E)結(jié)下來,我們再做幾個試驗:
第一步:執(zhí)行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,跟剛剛的沒有變化,也就是說執(zhí)行ALTER TABLE TEST_TAB DEALLOCATE UNUSED后,段的高水位標(biāo)記的位置沒有改變,但是大家看看UNUSED BLOCKS變?yōu)?了,總的塊數(shù)減少到157184,這證明,DEALLOCATE UNUSED為釋放HWM上面的未使用空間,但是并不會釋放HWM下面的自由空間,也不會移動HWM的位置.
第二步:我們再來看看執(zhí)行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
此時,總共用到的塊數(shù)已變?yōu)?, 我們再代入上面的公式,算出HWM的位置: 8-5=3 HWM所在的BLOCK ID是2632+3-1=2634,
OK,我們發(fā)現(xiàn),此時HWM的位置已經(jīng)發(fā)生變化,現(xiàn)在HWM的位置是在第3個BLOCK,其BLOCK ID是2634,所有數(shù)據(jù)文件的ID是9(這個沒有發(fā)生變化,數(shù)據(jù)文件還是原來的那個數(shù)據(jù)文件,只是釋放了原來的自由空間),最后使用的塊數(shù)也變?yōu)?,也就是說已經(jīng)使用了3塊,HWM就是在最后一個使用的塊上,即第3個塊上.大家可能會覺得奇怪,為什么釋放空間后,未使用的塊還有5個啊?也就是說HWM之上還是有5個已分配但從未使用的塊.答案就跟HWM移動的規(guī)律有關(guān).當(dāng)我們在插入數(shù)據(jù)時,ORACLE首先在HWM之下的塊當(dāng)中定位自由空間(通過自由列表FREELIST),如果FREELIST當(dāng)中沒有自由塊了,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,總行數(shù)為(NUM_ROWS):0
從中我們也可以發(fā)現(xiàn),分析表和SHOW_SPACE顯示的數(shù)據(jù)有點不一致.那么哪個是準(zhǔn)的呢?其實這兩個都是準(zhǔn)的,只不過計算的方法有點不同.事實上,當(dāng)你創(chuàng)建了一個對象如表以后,不管你有沒有插入數(shù)據(jù),它都會占用一些塊,ORACLE也會給它分配必要的空間.同樣,用ALTER TABLE MOVE釋放自由空間后,還是保留了一些空間給這個表.
最后,我們再來執(zhí)行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>
我們發(fā)現(xiàn)TRUNCATE后和MOVE沒有什么變化.
為了,最終驗證一下我上面的觀點,我再DROP一下表,然后新建這個表,看看這時在沒有插入任何數(shù)據(jù)之前,是否ORACLE確實有給這個對象分配必要的空間:
DROP TABLE TEST_TAB;
CREATE TABLE TEST_TAB(C1 NUMBER(10),C2 CHAR(100)) TABLESPACE RAINNY;
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...................2112
LAST USED BLOCK.........................3
大家看,即使我沒有插入任何一行記錄,ORACLE還是給它分配了8個塊.當(dāng)然這個跟建表語句的INITIAL 參數(shù)及MINEXTENTS參數(shù)有關(guān):請看TEST_TAB的存儲參數(shù):
S TORAGE
(
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
);
也就是說,在這個對象創(chuàng)建以后,ORACLE至少給它分配一個區(qū),初始大小是64K,一個標(biāo)準(zhǔn)塊的大小是8K,剛好是8個BLOCK.
總結(jié):
在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 之上的空間完全釋放(交還給操作系統(tǒng)),并且重置HWM。
(4)如果僅是要移動HWM,而不想讓表長時間鎖住,可以用TRUNCATE TABLE REUSE STORAGE,僅將HWM重置。
(5)ALTER TABLE MOVE會將HWM移動,但在MOVE時需要雙倍的表空間,而且如果表上有索引的話,需要重構(gòu)索引
(6)DELETE表不會重置HWM,也不會釋放自由的空間(也就是說DELETE空出來的空間只能給對象本身將來的INSERT/UPDATE使用,不能給其它的對象使用)
在ORACLE 10G:
可以使用ALTER TABLE TEST_TAB SHRINK SPACE命令來聯(lián)機移動HWM,
如果要同時壓縮表的索引,可以發(fā)布:ALTER TABLE TEST_TAB SHRINK SPACE CASCADE
注意:在使用此命令時需要先使行可遷移row movement(具體見例子)。
與使用ALTER TABLE MOVE 不同的是執(zhí)行此命令后并不需要重構(gòu)索引。
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個數(shù)據(jù)塊，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');
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
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這個塊上面!