Oracle中 HWM與資料庫效能的探討
本文討論的是oracle中關於table的HWM的內容，主要包括這樣幾個內容：
1. 什麼是HWM？
2. HWM是如何移動的。
3. HWM對於效能的影響
4. 何時應該降低以及如何降低HWM
5. 其他一些影響HWM的操作
除了特殊註明，本章內容所有的實驗都基於：win2000,oracle9201,bolcksize 8K的實驗環境。
一、什麼是HWM
HWM（high water mark）,高水標記,這個概念在segment的儲存內容中是比較重要的.簡單來說,HWM就是一個segment中已使用和未使用的block的分界線.
在oracle的concept中對於HWM的說明是這樣的：在一個segment中，HWM是使用和未使用空間的分界線。當請求新的空閒塊，並且現有空閒列表中的塊不能滿足要求時，HWM指向的塊將被標記為已使用，然後HWM將移動指向下一個未使用過的塊。
我們知道,在oracle中,儲存資料的最小單元是block,對於一個segment(table或index),都是由很多的block組成的,這些block的狀態分為已使用和未使用兩種,一般來說，在HWM之下的block都是儲存過資料的. 如圖:
從上面的圖，我們就能很清楚的看到，一個segment中的block的分佈情況。在HWM左邊的block是已使用的，或者說是可以用來儲存資料的。而HWM右邊的block是不能用來儲存資料的。當HWM左邊的block空間都使用完之後，還有新的資料需要儲存，怎樣處理呢？這時oracle會向右移動HWM，即把右邊的沒有使用的block移到HWM的左邊，這時HWM左邊的block就增加了，那麼就有新的block空間可供使用了。
Oracle從9i開始，推出了新的一種segment的空間管理方式，即ASSM（auto segment space management）。這種segment在空間管理上和以前的FLM（freelist management）是不一樣的。這裡我們簡單地介紹一下。
在FLM模式下，對於一個segment的HWM下的所有block空間的使用，是通過freelist來管理的，freelist位於segment的第一個extent中。一個block何時應該位於freelist之上，取決於PCTUSED和PCTFREE這樣兩個引數。基於freelist管理模式和位於segment header的情況，如果對一個segment進行高併發的頻繁的DML操作，不可避免的出現header爭用的情況，雖然我們可以採用增加freelists或freelist group的方式來緩解這種狀況。
那麼從oracle92開始，推出了ASSM這樣一種全新的segmeng空間管理的方式（又稱為Bitmap Managed Segments）, freelist被點陣圖所取代，使用點陣圖來管理block的空間使用狀況，並且這些點陣圖塊分散在segment中。ASSM管理的segment會略掉任何為PCTUSED、NEXT和FREELISTS所指定的值。
使用ASSM也有一定的侷限性：
ASSM只能位於Local Manage的tablespace之上；
不能夠使用ASSM建立臨時的tablespace；
LOB物件不能在一個指定進行自動段空間管理的tablespace中建立。
以上我們簡單地介紹了ASSM和FLM的概念和區別，接下來，我們來看看這兩種segmeng空間管理模式在HWM的處理上有什麼不同。
二、初始建立的table中HWM的不同情況
FLM管理的table：我們先建立名為HWM的tablespace，指定非自動段空間管理，extent大小為40K。並在上面建立table TEST_HWM，PCTFREE 40 PCTUSED 20。
|
SQL> connect dlinger/dlinger@oracle9i_dl 連線到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production
SQL> CREATE TABLESPACE HWM 2 DATAFILE 'D:\ORACLE\ORADATA\ORACLE9I\HWM.dbf' 3 SIZE 50M uniform. size 40K;
表空間已建立。
SQL> select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT, 2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT 3 from dba_tablespaces where TABLESPACE_NAME = 'HWM';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT --------------- ---------- ----------------- --------------- ------------------------ HWM 8192 LOCAL UNIFORM MANUAL
SQL> alter user dlinger default tablespace hwm;
使用者已更改。
SQL> CREATE TABLE TEST_HWM (ID CHAR(2000) , NAME CHAR(2000) ) 2 STORAGE ( MINEXTENTS 2) PCTFREE 40 PCTUSED 20;
表已建立。
SQL>select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS from dba_extents 2 where segment_name='TEST_HWM' ;
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 11 11 9 5 1 11 11 14 5
SQL> alter system dump datafile 11 block 9;
系統已更改。 |
Table TEST_HWM位於datafile 11 ，segment header為block9，我們dump出block9來看看：
|
*** 2004-06-09 20:31:26.000 *** SESSION ID:(9.5) 2004-06-09 20:31:26.000 Start dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9 buffer tsn: 14 rdba: 0x02c00009 (11/9) scn: 0x0000.013e974e seq: 0x01 flg: 0x00 tail: 0x974e1001 frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 9 last map 0x00000000 #maps: 0 offset: 4128 Highwater:: 0x02c0000a ext#: 0 blk#: 0 ext size: 4 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Unlocked Map Header:: next 0x00000000 #extents: 2 obj#: 32377 flag: 0x40000000 Extent Map ----------------------------------------------------------------- 0x02c0000a length: 4 0x02c0000e length: 5
nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 0 SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000 End dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9
|
從dump的trace檔案，我們可以獲得這樣的資訊：
我們可以看到Highwater:: 0x02c0000a，在FLM的segment下，初始建立的table，HWM是從第一個extent的第二個block開始的。為segment header 保留一個塊。我們從這裡也可以看出來：
Extent Map
-----------------------------------------------------------------
0x02c0000a length: 4
說明第一個extent可用的block為4。
我們這裡看到的結果是在預設freelist 為1的條件下得到的。在FLM下，如果對segment設定了freelist groups N，則HWM指向第 N＋2個block，當N+2 > initextent的block數時，會返回ORA-03237的錯誤資訊，這裡tablespace HWM的extent為40K，block_size 為8K：
|
SQL> CREATE TABLE TEST_HWM2 (ID CHAR(2000) , NAME CHAR(2000) ) 2 STORAGE ( MINEXTENTS 2 freelist groups 4) PCTFREE 40 PCTUSED 20; CREATE TABLE TEST_HWM2 (ID NUMBER(10) , NAME CHAR(2000) ) * ERROR 位於第 1 行: ORA-03237: 在表空間 (HWM) 無法分配指定大小的初始區
|
在ASSM下，情況是怎樣的呢？
我們建立名為ASSM的tablespace，指定自動段空間管理，extent大小為40K。並在上面建立table TEST_HWM1，注意，這裡我們只指定了PCTFREE 40，因為PCTUSED在ASSM下的segment中是無效的。
|
SQL> CREATE TABLESPACE ASSM 2 DATAFILE 'D:\ORACLE\ORADATA\ORACLE9I\ASSM.dbf' 3 SIZE 50M uniform. size 40K segment space management auto;
表空間已建立。
SQL> select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT, 2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT 3 from dba_tablespaces where TABLESPACE_NAME = 'ASSM';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT --------------- ---------- ----------------- --------------- ------------------------ ASSM 8192 LOCAL UNIFORM AUTO
SQL> CREATE TABLE TEST_HWM1 (ID CHAR(2000), NAME CHAR(2000) ) 2 Tablespace ASSM 3 STORAGE ( MINEXTENTS 2) PCTFREE 40;
表已建立。
SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents 3 where segment_name='TEST_HWM1' ;
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 12 12 9 5 1 12 12 14 5
SQL> alter system dump datafile 12 block min 9 block max 11;
系統已更改。
|
在FMT下，segment的第一個block是儲存segment header的，在本例中，ASSM下，oracle使用segment的至少前3個block來儲存segment header。這裡，我們dump 9－11的block資訊。我們來看一下dump的結果和FMT下有什麼不同：
|
Start dump data blocks tsn: 15 file#: 12 minblk 9 maxblk 11 buffer tsn: 15 rdba: 0x03000009 (12/9) scn: 0x0000.01ca6d7f seq: 0x02 flg: 0x00 tail: 0x6d7f2002 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 2 parent dba: 0x0300000a poffset: 0 unformatted: 7 total: 10 first useful block: 3 owning instance : 1 instance ownership changed at Last successful Search Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295 First free datablock : 3 Bitmap block lock opcode 3 Locker xid: : 0x0004.008.0000713c Highwater:: 0x0300000c ext#: 0 blk#: 3 ext size: 5 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x03000009 Length: 5 Offset: 0 0x0300000e Length: 5 Offset: 5
0:Metadata 1:Metadata 2:Metadata 3:unformatted 4:unformatted 5:unformatted 6:unformatted 7:unformatted 8:unformatted 9:unformatted -------------------------------------------------------- buffer tsn: 15 rdba: 0x0300000a (12/10) scn: 0x0000.01ca6d7e seq: 0x02 flg: 0x00 tail: 0x6d7e2102 frmt: 0x02 chkval: 0x0000 type: 0x21=SECOND LEVEL BITMAP BLOCK Dump of Second Level Bitmap Block number: 1 nfree: 1 ffree: 0 pdba: 0x0300000b opcode:0 xid: L1 Ranges : -------------------------------------------------------- 0x03000009 Free: 5 Inst: 1
-------------------------------------------------------- buffer tsn: 15 rdba: 0x0300000b (12/11) scn: 0x0000.01ca6d80 seq: 0x01 flg: 0x00 tail: 0x6d802301 frmt: 0x02 chkval: 0x0000 type: 0x23=PAGETABLE SEGMENT HEADER Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 10 last map 0x00000000 #maps: 0 offset: 2716 Highwater:: 0x0300000c ext#: 0 blk#: 3 ext size: 5 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Unlocked -------------------------------------------------------- Low HighWater Mark : Highwater:: 0x0300000c ext#: 0 blk#: 3 ext size: 5 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 Level 1 BMB for High HWM block: 0x03000009 Level 1 BMB for Low HWM block: 0x03000009 -------------------------------------------------------- Segment Type: 1 nl2: 1 blksz: 8192 fbsz: 0 L2 Array start offset: 0x00001434 First Level 3 BMB: 0x00000000 L2 Hint for inserts: 0x0300000a Last Level 1 BMB: 0x03000009 Last Level II BMB: 0x0300000a Last Level III BMB: 0x00000000 Map Header:: next 0x00000000 #extents: 2 obj#: 32499 flag: 0x20000000 Extent Map ----------------------------------------------------------------- 0x03000009 length: 5 0x0300000e length: 5
Auxillary Map -------------------------------------------------------- Extent 0 : L1 dba: 0x03000009 Data dba: 0x0300000c Extent 1 : L1 dba: 0x03000009 Data dba: 0x0300000e --------------------------------------------------------
Second Level Bitmap block DBAs -------------------------------------------------------- DBA 1: 0x0300000a
|
這裡可以看到Highwater:: 0x0300000c ，HWM指向的第一個extent的第四個block，也就是說，segment head保留了3個block。
為什麼前面我們說oracle在ASSM的segment中至少用前3個block來儲存segment header的資訊呢？我們可以建立一個extent為256K 的tablespace來，然後在上面建立table，來看看結果：
|
SQL> create tablespace assm 2 datafile '/data1/oracle/oradata/assm01.dbf' 3 size 10M 4 extent management local uniform. size 256K 5 segment space management auto 6 /
Tablespace created.
SQL> CREATE TABLE TEST_HWM1 (ID CHAR(2000), NAME CHAR(2000) ) 2 Tablespace ASSM 3 STORAGE ( MINEXTENTS 2) PCTFREE 40 4 /
Table created.
SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents 3 where segment_name='TEST_HWM1' 4 /
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 7 7 9 32 1 7 7 41 32
SQL> alter system dump datafile 7 block min 9 block max 11;
System altered.
|
我們看其中一部分的trace檔案的內容：
|
Start dump data blocks tsn: 16 file#: 7 minblk 9 maxblk 11 buffer tsn: 16 rdba: 0x01c00009 (7/9) scn: 0x0000.01444ea9 seq: 0x02 flg: 0x00 tail: 0x4ea92002 frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK Dump of First Level Bitmap Block -------------------------------- nbits : 4 nranges: 1 parent dba: 0x01c0000b poffset: 0 unformatted: 12 total: 16 first useful block: 4 owning instance : 1 instance ownership changed at Last successful Search Freeness Status: nf1 0 nf2 0 nf3 0 nf4 0
Extent Map Block Offset: 4294967295 First free datablock : 4 Bitmap block lock opcode 0 Locker xid: : 0x0000.000.00000000 Highwater:: 0x01c0000d ext#: 0 blk#: 4 ext size: 32 #blocks in seg. hdr's freelists: 0 #blocks below: 0 mapblk 0x00000000 offset: 0 HWM Flag: HWM Set -------------------------------------------------------- DBA Ranges : -------------------------------------------------------- 0x01c00009 Length: 16 Offset: 0
0:Metadata 1:Metadata 2:Metadata 3:Metadata 4:unformatted 5:unformatted 6:unformatted 7:unformatted 8:unformatted 9:unformatted 10:unformatted 11:unformatted 12:unformatted 13:unformatted 14:unformatted 15:unformatted -------------------------------------------------------- |
我們發現，這裡使用了前4個block來儲存segment header的內容。
三、 insert資料時HWM的移動
LMT下:
|
SQL> insert into test_hwm values('1','dlinger');
已建立 1 行。
SQL> alter system dump datafile 11 block 9;
系統已更改。
SQL> insert into test_hwm values('2','dlinger');
已建立 1 行。
SQL> alter system dump datafile 11 block 9;
系統已更改。
SQL> insert into test_hwm values('3','dlinger');
已建立 1 行。
SQL> alter system dump datafile 11 block 9;
系統已更改。
SQL> insert into test_hwm values('4','dlinger');
已建立 1 行。
SQL> alter system dump datafile 11 block 9;
系統已更改。
SQL> insert into test_hwm values('5','dlinger'); 已建立 1 行。 SQL> alter system dump datafile 11 block 9;
系統已更改。
|
檢視_bump_highwater_mark_count引數:
|
select x.ksppinm name, y.ksppstvl value, from sys.x$ksppi x, sys.x$ksppcv y
NAME VALUE ----------------------------- -------- _bump_highwater_mark_count 0
|
看看dump的結果:
|
*** 2004-06-14 10:46:56.000 Start dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9 buffer tsn: 14 rdba: 0x02c00009 (11/9) scn: 0x0000.015032ef seq: 0x01 flg: 0x00 tail: 0x32ef1001 frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 9 last map 0x00000000 #maps: 0 offset: 4128 Highwater:: 0x02c0000b ext#: 0 blk#: 1 ext size: 4 #blocks in seg. hdr's freelists: 1 #blocks below: 1 mapblk 0x00000000 offset: 0 Unlocked Map Header:: next 0x00000000 #extents: 2 obj#: 32387 flag: 0x40000000 Extent Map ----------------------------------------------------------------- 0x02c0000a length: 4 0x02c0000e length: 5
nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 1 SEG LST:: flg: USED lhd: 0x02c0000a ltl: 0x02c0000a End dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9 *** 2004-06-14 10:47:25.000 Start dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9 buffer tsn: 14 rdba: 0x02c00009 (11/9) scn: 0x0000.01503349 seq: 0x02 flg: 0x00 tail: 0x33491002 frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 9 last map 0x00000000 #maps: 0 offset: 4128 Highwater:: 0x02c0000c ext#: 0 blk#: 2 ext size: 4 #blocks in seg. hdr's freelists: 1 #blocks below: 2 mapblk 0x00000000 offset: 0 Unlocked Map Header:: next 0x00000000 #extents: 2 obj#: 32387 flag: 0x40000000 Extent Map ----------------------------------------------------------------- 0x02c0000a length: 4 0x02c0000e length: 5
nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 2 SEG LST:: flg: USED lhd: 0x02c0000b ltl: 0x02c0000b End dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9 *** 2004-06-14 10:47:50.000 Start dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9 buffer tsn: 14 rdba: 0x02c00009 (11/9) scn: 0x0000.01503350 seq: 0x02 flg: 0x00 tail: 0x33501002 frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 9 last map 0x00000000 #maps: 0 offset: 4128 Highwater:: 0x02c0000d ext#: 0 blk#: 3 ext size: 4 #blocks in seg. hdr's freelists: 1 #blocks below: 3 mapblk 0x00000000 offset: 0 Unlocked Map Header:: next 0x00000000 #extents: 2 obj#: 32387 flag: 0x40000000 Extent Map ----------------------------------------------------------------- 0x02c0000a length: 4 0x02c0000e length: 5
nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 3 SEG LST:: flg: USED lhd: 0x02c0000c ltl: 0x02c0000c End dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9 *** 2004-06-14 10:48:04.000 Start dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9 buffer tsn: 14 rdba: 0x02c00009 (11/9) scn: 0x0000.015033a4 seq: 0x02 flg: 0x00 tail: 0x33a41002 frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 9 last map 0x00000000 #maps: 0 offset: 4128 Highwater:: 0x02c0000e ext#: 0 blk#: 4 ext size: 4 #blocks in seg. hdr's freelists: 1 #blocks below: 4 mapblk 0x00000000 offset: 0 Unlocked Map Header:: next 0x00000000 #extents: 2 obj#: 32387 flag: 0x40000000 Extent Map ----------------------------------------------------------------- 0x02c0000a length: 4 0x02c0000e length: 5
nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 4 SEG LST:: flg: USED lhd: 0x02c0000d ltl: 0x02c0000d End dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9 *** 2004-06-14 10:50:20.000 Start dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9 buffer tsn: 14 rdba: 0x02c00009 (11/9) scn: 0x0000.0150350e seq: 0x03 flg: 0x00 tail: 0x350e1003 frmt: 0x02 chkval: 0x0000 type: 0x10=DATA SEGMENT HEADER - UNLIMITED Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 2 #blocks: 9 last map 0x00000000 #maps: 0 offset: 4128 Highwater:: 0x02c00013 ext#: 1 blk#: 5 ext size: 5 #blocks in seg. hdr's freelists: 5 #blocks below: 9 mapblk 0x00000000 offset: 1 Unlocked Map Header:: next 0x00000000 #extents: 2 obj#: 32387 flag: 0x40000000 Extent Map ----------------------------------------------------------------- 0x02c0000a length: 4 0x02c0000e length: 5
nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 5 SEG LST:: flg: USED lhd: 0x02c0000e ltl: 0x02c00012 End dump data blocks tsn: 14 file#: 11 minblk 9 maxblk 9
|
分析一下這個結果:
Highwater:0x02c0000b à Highwater: 0x02c0000c à Highwater: 0x02c0000d
à Highwater: 0x02c0000e à Highwater: 0x02c00013
當我們沒有設定_bump_highwater_mark_count時,在前五個資料塊，HWM是以1為步長移動的；在五塊以後，HWM是以5為步長移動的。
對於ASSM來說,情況又是不一樣的。
對於 extents <= 16 blocks的情況，HWM 移動遵循:
第一次移動----à extent blocks - metadata
第二次移動----à extent blocks
對於 extents > 16 blocks的情況，HWM移動遵循:
每次移動32個blocks，但是HWM包含未格式化的block，每次格式化16個block或者16 -metadata blocks。
我們在這裡只是提出這樣的問題讓大家注意，不對ASSM的問題進行專門的討論。
四、HWM對效能的影響
我們對一個table進行DML操作，主要是insert，update，delete這三種。當一個table進行了多次的insert資料時，前面我們已經討論了，table的HWM會不停地提升。現在我們來這樣一種情況：如果在這期間我們對這個table進行了大量的delete操作，這是table的HWM會不會隨著資料量的減少而下降呢？我們將通過一個實現來說明這個問題：
這裡我們要先引入一個procedure(轉自tom的《oracle高階專家程式設計》)：
|
create or replace procedure show_space ( p_segname in varchar2, p_owner in varchar2 default user, p_type in varchar2 default 'TABLE', p_partition in varchar2 default NULL ) as l_total_blocks number; l_total_bytes number; l_unused_blocks number; l_unused_bytes number; l_LastUsedExtFileId number; l_LastUsedExtBlockId number; l_last_used_block number; procedure p( p_label in varchar2, p_num in number ) is begin dbms_output.put_line( rpad(p_label,40,'.') || p_num ); end; begin
dbms_space.unused_space ( segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, partition_name => p_partition, total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, last_used_extent_file_id => l_LastUsedExtFileId, last_used_extent_block_id => l_LastUsedExtBlockId, last_used_block => l_last_used_block );
p( 'Total Blocks', l_total_blocks ); p( 'Total Bytes', l_total_bytes ); p( 'Unused Blocks', l_unused_blocks ); p( 'Unused Bytes', l_unused_bytes ); p( 'Last Used Ext FileId', l_LastUsedExtFileId ); p( 'Last Used Ext BlockId', l_LastUsedExtBlockId ); p( 'Last Used Block', l_last_used_block ); end; /
|
通過這個procedure顯示的結果，我們可以得到一個segment的HWM的位置。在sqlplus中，我們要看到這個procedure顯示的結果，需要設定: set serveroutput on
這裡，HWM = total_blocks - Unused Blocks +1
我們來看這樣一個實驗：
使用系統檢視all_objects來建立測試table MY_OBJECTS，然後insert 31007行資料：
|
SQL> create table MY_OBJECTS as 2 select * from all_objects;
Table created SQL> select count(*) from MY_OBJECTS;
COUNT(*) ---------- 31007
SQL> exec show_space(p_segname=>'MY_OBJECTS',p_owner =>'DLINGER',p_type => 'TABLE'); Total Blocks............................425 Total Bytes.............................3481600 Unused Blocks...........................3 Unused Bytes............................24576 Last Used Ext FileId....................11 Last Used Ext BlockId...................439 Last Used Block.........................2
|
這時，我們使用show_space來計算table MY_OBJECTS的HWM，這裡
HWM=425 - 3 + 1 = 423 ;
我們現在對table MY_OBJECTS 進行delete操作，刪除前15000行資料：
|
SQL> delete from MY_OBJECTS where rownum <15000;
已刪除14999行。
SQL> exec show_space(p_segname => 'MY_OBJECTS',p_owner => 'DLINGER',p_type => 'TABLE'); Total Blocks............................425 Total Bytes.............................3481600 Unused Blocks...........................3 Unused Bytes............................24576 Last Used Ext FileId....................11 Last Used Ext BlockId...................439 Last Used Block.........................2
PL/SQL 過程已成功完成。 |
現在我們再來觀察HWM的結果，可以看到：這裡HWM=425 - 3 + 1 = 423 。
HWM的位置並沒有發生變化。這說明對table MY_OBJECTS 刪除了14999行資料後，並不會改變HWM的位置。
那麼，HWM過高會對資料庫的效能有什麼樣的影響呢？
這裡我們以全表掃描為例，來討論HWM過高的不良影響。
同樣，我們也通過一個實驗來看full table scan在delete前後訪問的block數量的情況：
|
SQL> set autotrace traceonly SQL> select count(*) from MY_OBJECTS;
COUNT(*) ---------- 31007
Statistics ---------------------------------------------------------- 。。。 422 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 。。。
|
這裡，我們通過oracle的autotrace來觀察sql的執行情況。
看看這個sql訪問的block：422 physical reads
我們通過Statistics的內容，可以看到，在table MY_OBJECTS有31007行資料的情況下，對table MY_OBJECTS 進行一次full table scan，oracle需要訪問了422個block。
這裡，我們發現full table scan時訪問的block數和HWM之下的block數量是一致的。
如果我們刪除table MY_OBJECTS 的一部分資料後，那我們對table MY_OBJECTS進行一次full table scan需要訪問的block會不會隨著資料行數的減少而降低呢？
我們delete 14999行資料，這是隻剩16008行資料了：
|
SQL> delete from MY_OBJECTS where rownum<15000;
14999 rows deleted
SQL> commit;
Commit complete
在這裡，我們把oracle先shutdown，然後在startup，以便清空cache中的資料。
SQL> set autotrace traceonly
SQL> select count(*) from MY_OBJECTS;
COUNT(*) ---------- 16008
Statistics ---------------------------------------------------------- 。。。 422 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 。。。
|
通過上面的Statistics的內容，可以看到，table full scan仍然訪問了422個block。
當我沒有delete前14999行資料時，全表掃描需要訪問31007行資料；而當delete了14999行資料之後，全表掃描實際需要訪問的資料行減少了，但是oracle訪問的block數量並沒有減少。這說明進行table full scan時，實際上是對HWM下所有的block進行訪問。我們知道，訪問的block數量越多，代表需要消耗的資源越多。那麼，當一個table在進行了大量的delete操作後，或者說，當一個table在HWM之下的block上的資料不飽和時，我們應該考慮採用一些方法來降低該表的HWM，以減小table full scan時需要訪問的block數量。
五、何時該降低HWM
Table包含兩種空閒的block：
在HWM之上的空閒block。我們執行analyze table後，這些空閒的blocks會在user_tables的 EMPTY_BLOCKS中被統計。這些空閒的blocks實際上是從來沒有儲存過資料的，我們可以用以下命令來釋放這些空間：
Alter table table_name deallocate unused;
在HWM之下的空閒block。當資料插入到一個block後，那麼HWM就移動到這個block之上了。然後後續的操作又將這個block中的資料刪除了，那麼，這個block實際上是空閒的。但是這些blocks位於HWM之下，所以是不會出現在EMPTY_BLOCKS中的。那麼，這樣的block過多，是會影響效能的，就像前面我們討論過table full scan 中看到的那樣。
我們同樣用系統檢視all_objects來建立測試table MY_OBJECTS,然後隨意delete其中的一部分資料，然後我們在對table MY_OBJECTS進行分析，來觀察現在這個table的HWM之下的資料分佈狀況。
對於LMT下FLM：
我們可以用這個方法來一個table在HWM有多少blocks是不包含資料的。：
|
SQL> analyze table MY_OBJECTS compute statistics;
Table analyzed SQL> select (1- a.num/ b.num_total)*100 as percent from 2 (select count(distinct substr(rowid,1,15)) num from MY_OBJECTS)a , 3 (select BLOCKS - EMPTY_BLOCKS num_total from user_tables where table_name= 'MY_OBJECTS') b;
PERCENT ---------- 24.8606346 |
從上面的結果，我們可以看到，table MY_OBJECTS中HWM下有24.86%的blocks是不包含資料的。當這個值比較高的時時候，我們可以考慮用一些方法來釋放HWM下的空閒blocks了。注意，這裡一定要先對table進行分析。
我們還可以考察這樣一個指標：
|
SQL>select NUM_ROWS*AVG_ROW_LEN/ ((BLOCKS-EMPTY_BLOCKS)*((100-PCT_FREE)/100)*8192)*100 percnt 2 from dba_tables where table_name = 'MY_OBJECTS';
PERCNT ---------- |
72.1461836
這裡，我們可以看到table MY_OBJECTS的平均blocks的資料充滿度為72%。注意，這裡我的環境下oracle的block_size為8k,那麼在不同的block_size下，我們應該修改上面的sql中的8192的數值。這裡計算時已經除去的PCTFREE的部分，MY_OBJECTS的PCTFREE為10，那麼block的平均資料充滿度實際上是72%×90%= 64.8%。
如果table經常進行全表掃描，或範圍掃描，那麼當這個值比較低的時候，也應該考慮來合併HWM下的blocks，將空閒的block釋放。
對於ASSM:
對於ASSM的segment來說，考察HWM下的blocks的空間使用狀況相對要簡單一些。在這裡，我們可以使用這樣一個procedure來得到table的blocks使用情況：
|
create or replace procedure show_space_assm( p_segname in varchar2, p_owner in varchar2 default user, p_type in varchar2 default 'TABLE' ) as l_fs1_bytes number; l_fs2_bytes number; l_fs3_bytes number; l_fs4_bytes number; l_fs1_blocks number; l_fs2_blocks number; l_fs3_blocks number; l_fs4_blocks number; l_full_bytes number; l_full_blocks number; l_unformatted_bytes number; l_unformatted_blocks number; procedure p( p_label in varchar2, p_num in number ) is begin dbms_output.put_line( rpad(p_label,40,'.') ||p_num ); end; begin dbms_space.space_usage( segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, fs1_bytes => l_fs1_bytes, fs1_blocks => l_fs1_blocks, fs2_bytes => l_fs2_bytes, fs2_blocks => l_fs2_blocks, fs3_bytes => l_fs3_bytes, fs3_blocks => l_fs3_blocks, fs4_bytes => l_fs4_bytes, fs4_blocks => l_fs4_blocks, full_bytes => l_full_bytes, full_blocks => l_full_blocks, unformatted_blocks => l_unformatted_blocks, unformatted_bytes => l_unformatted_bytes); p('free space 0-25% Blocks:',l_fs1_blocks); p('free space 25-50% Blocks:',l_fs2_blocks); p('free space 50-75% Blocks:',l_fs3_blocks); p('free space 75-100% Blocks:',l_fs4_blocks); p('Full Blocks:',l_full_blocks); p('Unformatted blocks:',l_unformatted_blocks); end; / |
我們知道，在ASSM下，block的空間使用分為free space： 0-25%，25-50%，50-75%，70-100%，full 這樣5中情況，show_space_assm會對需要統計的table彙總這5中型別的block的數量。
我們來看table HWM1的空間使用情況：
|
SQL> exec show_space_assm('HWM1','DLINGER'); free space 0-25% Blocks:.................0 free space 25-50% Blocks:...............1 free space 50-75% Blocks:...............0 free space 75-100% Blocks:..............8 Full Blocks:.....................................417 Unformatted blocks:.........................0 |
這個結果顯示，table HWM1，full的block有417個，free space 為75-100% Block有8個，free space 25-50% Block有1個。當table HWM下的blocks的狀態大多為free space
較高的值時，我們考慮來合併HWM下的blocks，將空閒的block釋放，降低table的HWM。
六、如何降低HWM
在oracle8i以前的版本，如果我們需要降低segment 的HWM，可以採用兩種方法：EXP/IMP 和CTAS，對這兩種方法大家都很熟悉，我們在這裡就不做討論了。
(1) Move
從8i開始，oracle開始提供Move的命令。我們通常使用這個命令，將一個table segment從一個tablespace移動到另一個tablespace。
Move實際上是在block之間物理的copy資料，那麼，我們可以通過這種方式來降低table的HWM。我們先通過一個實驗來看看move是如何移動資料的。建立table TEST_HWM，insert一些資料：
|
SQL> create table TEST_HWM (id int ,name char(2000)) tablespace hwm; Table created
|
我們往table TEST_HWM 中insert如下資料：
insert into TEST_HWM values (1,'aa');
insert into TEST_HWM values (2,'bb');
insert into TEST_HWM values (2,'cc');
insert into TEST_HWM values (3,'dd');
insert into TEST_HWM values (4,'ds');
insert into TEST_HWM values (5,'dss');
insert into TEST_HWM values (6,'dss');
insert into TEST_HWM values (7,'ess');
insert into TEST_HWM values (8,'es');
insert into TEST_HWM values (9,'es');
insert into TEST_HWM values (10,'es');
我們來看看這個table的rowid和block的ID和資訊：
|
SQL> select rowid , id,name from TEST_HWM;
ROWID ID NAME ------------------------- ----------- ------ AAAH7JAALAAAAAUAAA 1 aa AAAH7JAALAAAAAUAAB 2 bb AAAH7JAALAAAAAUAAC 2 cc AAAH7JAALAAAAAVAAA 3 dd AAAH7JAALAAAAAVAAB 4 ds AAAH7JAALAAAAAVAAC 5 dss AAAH7JAALAAAAAWAAA 6 dss AAAH7JAALAAAAAWAAB 7 ess AAAH7JAALAAAAAWAAC 8 es AAAH7JAALAAAAAXAAA 9 es AAAH7JAALAAAAAXAAB 10 es SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents where segment_name='TEST_HWM' ;
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 11 11 19 5 |
這裡，簡單地介紹一下rowid的相關知識：
ROWID 在磁碟上需要10 個字節的儲存空間並使用18 個字符來顯示它包含下列元件：
資料物件編號：每個資料物件如表或索引在建立時分配，並且此編號在資料庫中是唯一的；
相關檔案編號：此編號對於一個表空間中的每個檔案是唯一的；
塊編號：表示包含此行的塊在檔案中的位置；
行編號：標識塊頭中行目錄位置的位置；
在內部資料物件編號需要32 位，相關檔案編號需要10 位，塊編號需要22，位行編號需要16 位，加起來總共是80 位或10 個字節，ROWID 使用以64 為基數的編碼方案來顯示該方案將六個位置用於資料物件，編號三個位置用於相關檔案編號六個位置用於塊編號三個位置用於行編號以64 為基數的編碼方案使用字符A-Z a-z 0-9 + 和/共64 個字符，如下例所示：
AAAH7J AAL AAAAAU AAA
在本例中
AAAH7J 是資料物件編號
AAL 是相關檔案編號
AAAAAU 是塊編號
AAA 是行編號
那麼，我們根據資料的rowid，可以看出這11行資料分佈在AAAAAU，AAAAAV，AAAAAW，AAAAAX這四個block中。
然後我們從table TEST_HWM中delete一些資料：
delete from TEST_HWM where id = 2;
delete from TEST_HWM where id = 4;
delete from TEST_HWM where id = 3;
delete from TEST_HWM where id = 7;
delete from TEST_HWM where id = 8;
delete from TEST_HWM where id = 9;
我們在來看看這個table的rowid和block的ID和資訊：
|
SQL> select rowid , id,name from TEST_HWM;
ROWID ID NAME ------------------ ---------- --------- -------------- AAAH7JAALAAAAAUAAA 1 aa AAAH7JAALAAAAAVAAC 5 dss AAAH7JAALAAAAAWAAA 6 dss AAAH7JAALAAAAAXAAB 10 es SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents where segment_name='TEST_HWM' ;
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 11 11 19 5 |
在這裡，我們可以看到，資料的rowid沒有發生改變，我們根據資料的rowid，可以看出這4行資料依然分佈在AAAAAU，AAAAAV，AAAAAW，AAAAAX這四個block中。
接下來我們對table TEST_HWM進行move的操作，然後再來觀察rowid，blockid的資訊：
|
SQL> alter table TEST_HWM move; Table altered
SQL> select rowid,id,name from HWM;
ROWID ID NAME ------------------ ---------- -------- --------------- AAAH7NAALAAAANrAAA 1 aa AAAH7NAALAAAANrAAB 5 dss AAAH7NAALAAAANrAAC 6 dss AAAH7NAALAAAANsAAA 10 es
SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents where segment_name=' TEST_HWM ' ;
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 11 11 874 5 |
我們可以看到，對table TEST_HWM進行move後，該table所在blockid發生了改變，那麼資料的rowid自然也發生了改變。從上面的結果，我們可以看到，現在table TEST_HWM 的資料分佈在AAAANr，AAAANs兩個block中了。但是這四行資料的rowid的順序來看，這四行資料在table中的儲存順序並沒有發生改變。move是在block之間對於資料的物理copy。
我們再來看看move操作對於table的HWM的位置有什麼變化，我們同樣使用系統檢視all_objects來建立測試table my_objects，然後delete前9999行資料：
|
SQL> create table my_objects tablespace HWM 2 as select * from all_objects; SQL> delete from my_objects where rownum<10000; 9999 rows deleted SQL> select count(*) from my_objects; COUNT(*) ---------- 21015 SQL> exec show_space(p_segname => 'MY_OBJECTS',p_owner => 'DLINGER',p_type => 'TABLE'); Total Blocks............................425 Total Bytes.............................3481600 Unused Blocks...........................3 Unused Bytes............................24576 Last Used Ext FileId....................11 Last Used Ext BlockId...................1294 Last Used Block.........................2 |
這裡HWM=425 - 3 + 1 = 423
然後對table MY_OBJECTS進行move操作：
|
SQL> alter table MY_OBJECTS move; 表已更改。 SQL> exec show_space(p_segname => 'MY_OBJECTS',p_owner => 'DLINGER',p_type => 'TABLE'); Total Blocks............................290 Total Bytes.............................2375680 Unused Blocks...........................1 Unused Bytes............................8192 Last Used Ext FileId....................11 Last Used Ext BlockId...................1584 Last Used Block.........................4 |
我們可以看到，table MY_OBJECTS的HWM從423移動到290，table的HWM降低了！
我們還可以使用別的方法來降低table的HWM，比如CTAS，insert into 等，那麼move操作對redo logo的寫和其他的方式比較起來是相對較少的，我們在這裡就不列出把具體的實驗結果了，大家有興趣的可以自己動手來證實一下。
上面我們討論了move的執行機制和如何使用move降低table的HWM，這裡，我們補充說明move的另外一些用法，以及使用move時的一些要注意的問題。
Move的一些用法
以下是alter table 中move子句的完整語法，我們介紹其中的幾點：
MOVE [ONLINE]
[segment_attributes_clause] [data_segment_compression] [index_org_table_clause] [ { LOB_storage_clause | varray_col_properties } [ { LOB_storage_clause | varray_col_properties } ]... ] [parallel_clause]
a. 我們可以使用move將一個table從當前的tablespace上移動到另一個tablespace上，如：
alter table t move tablespace tablespace_name;
b. 我們還可以用move來改變table已有的block的儲存引數,如：
alter table t move storage (initial 30k next 50k);
另外，move操作也可以用來解決table中的行遷移的問題。
使用move的一些注意事項
a. table上的index需要rebuild：
在前面我們討論過，move操作後，資料的rowid發生了改變，我們知道，index是通過rowid來fetch資料行的，所以，table上的index是必須要rebuild的。
|
SQL> create index i_my_objects on my_objects (object_id); Index created
SQL> alter table my_objects move; Table altered
SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS';
INDEX_NAME STATUS ------------------------------ -------- I_MY_OBJECTS UNUSABLE |
從這裡可以看到，當table MY_OBJECTS進行move操作後，該table 上的inedx的狀態為UNUSABLE，這時，我們可以使用alter index I_MY_OBJECTS rebuild online的命令，對index I_MY_OBJECTS進行線上rebuild。
b. move時對table的鎖定
當我們對table MY_OBJECTS進行move操作時，查詢v$locked_objects檢視可以發現，table MY_OBJECTS上加了exclusive lock：
|
SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects;
OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE ---------- ---------- ------------------ ----------- 32471 9 DLINGER 6 SQL> select object_id from user_objects where object_name = 'MY_OBJECTS';
OBJECT_ID ---------- 32471 |
這就意味著，table在進行move操作時，我們只能對它進行select的操作。反過來說，當我們的一個session對table進行DML操作且沒有commit時，在另一個session中是不能對這個table進行move操作的，否則oracle會返回這樣的錯誤資訊：ORA-00054: 資源正忙，要求指定 NOWAIT。
c. 關於move時空間使用的問題：
當我們使用alter table move來降低table的HWM時，有一點是需要注意的，這時，當前的tablespace中需要有1倍於table的空閒空間以供使用：
|
SQL> CREATE TABLESPACE TEST1 2 DATAFILE 'D:\ORACLE\ORADATA\ORACLE9I\TEST1.dbf' SIZE 5M 3 UNIFORM. SIZE 128K ;
表已建立。
--------------- 3.125
SQL> alter table MY_OBJECTS move;
* ERROR 位於第 1 行: ORA-01652: 無法通過16（在表空間TEST1中）擴充套件 temp 段
2 DATAFILE 'D:\ORACLE\ORADATA\ORACLE9I\TEST1.DBF' RESIZE 7M;
SQL> alter table MY_OBJECTS move; 表已更改。
|
(2) DBMS_REDEFINITION
這個包是從oracle 9i開始引入的，用來作table的聯機重組和重定義。我們可以通過這種方法線上地重組table，來移動table中的資料，降低HWM，修改table的儲存引數，分割槽等等。
這個操作要求table上有一個主鍵，並要求預先建立一個帶有要求修改的儲存引數的table，以便儲存重新組織後的資料。儲存重新組織的資料的tble叫臨時表，它只在重新組織期間被使用，在操作完成後可以被刪除。
使用DBMS_REDEFINITION Package需要如下許可權：
Create any table；
alter any table；
drop any table；
lock any table；
select any table；
在DBMS_REDEFINITION上執行操作
使用DBMS_REDEFINITION重組table一般是這樣幾個步驟：
a. 使用DBMS_REDEFINITION.CAN_REDEF_TABLE()驗證所選擇的table能夠被重建；
b. 建立空的臨時表，確保這個臨時表定義了主鍵；
c. 使用DBMS_REDEFINITION.START_REDEF_TABLE()進行table的重組；
d. 在臨時表上建立觸發器，索引和約束，一般來說，這些物件於源有表中的是一致的，但是名稱必須不同。同時要確保所建立的所有外來鍵約束不可用。在重組結束時，所有這些物件將替換定義在源表上的物件。
e. 使用DBMS_REDEFINITION.FINISH_REDEF_TABLE()完成重組的過程。在這期間，源表將會lock較短的時間。
f．刪除臨時表。
在這裡，我們只是簡單第介紹如何使用DBMS_REDEFINITION對table進行線上重組和重定義，關於這個package具體的使用方法和使用上的限制，可以查閱oracle的官方文件：
http://tahiti.oracle.com/
(3). Shrink
從10g開始，oracle開始提供Shrink的命令，假如我們的表空間中支援自動段空間管理 (ASSM),就可以使用這個特性縮小段，即降低HWM。這裡需要強調一點，10g的這個新特性，僅對ASSM表空間有效，否則會報 ORA-10635: Invalid segment or tablespace type。
在第4部分，我們已經討論過，如何考察在ASSM下table是否需要回收浪費的空間，這裡，我們來討論如和對一個ASSM的segment回收浪費的空間。
同樣，我們用系統檢視all_objects來在tablespace ASSM上建立測試表my_objects，這一小節的內容，實驗環境為oracle10.1.0.2：
|
SQL> select * from v$version;
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod PL/SQL Release 10.1.0.2.0 - Production CORE 10.1.0.2.0 Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production NLSRTL Version 10.1.0.2.0 - Production
SQL> select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT, 2 ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT 3 from dba_tablespaces where TABLESPACE_NAME = 'ASSM';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MANAGEMENT ALLOCATION_TYPE SEGMENT_SPACE_MANAGEMENT ---------------- ---------- ----------------- --------------- ------------------------ ASSM 8192 LOCAL UNIFORM AUTO
SQL> create table my_objects tablespace assm 2 as select * from all_objects; Table created |
然後我們隨機地從table MY_OBJECTS中刪除一部分資料：
|
SQL> select count(*) from my_objects; COUNT(*) ---------- 47828 SQL> delete from my_objects where object_name like '%C%'; 16950 rows deleted
SQL> delete from my_objects where object_name like '%U%'; 4503 rows deleted
SQL> delete from my_objects where object_name like '%A%'; 6739 rows deleted |
現在我們使用show_space和show_space_assm來看看my_objects的資料儲存狀況：
|
SQL> exec show_space('MY_OBJECTS','DLINGER'); Total Blocks............................680 Total Bytes.............................5570560 Unused Blocks...........................1 Unused Bytes............................8192 Last Used Ext FileId....................6 Last Used Ext BlockId...................793 Last Used Block.........................4
PL/SQL 過程已成功完成。
SQL> exec show_space_assm('MY_OBJECTS','DLINGER'); free space 0-25% Blocks:................0 free space 25-50% Blocks:...............205 free space 50-75% Blocks:...............180 free space 75-100% Blocks:..............229 Full Blocks:............................45 Unformatted blocks:.....................0
PL/SQL 過程已成功完成。 |
這裡，table my_objects的HWM下有679個block，其中，free space為25-50%的block有205個，free space為50-75%的block有180個，free space為75-100%的block有229個，full space的block只有45個，這種情況下，我們需要對這個table的現有資料行進行重組。
要使用assm上的shink，首先我們需要使該表支援行移動，可以用這樣的命令來完成：
alter table my_objects enable row movement;
現在，就可以來降低my_objects的HWM，回收空間了，使用命令：
alter table bookings shrink space;
我們具體的看一下實驗的結果：
|
SQL> alter table my_objects enable row movement; 表已更改。
SQL> alter table my_objects shrink space; 表已更改。
SQL> exec show_space('MY_OBJECTS','DLINGER'); Total Blocks............................265 Total Bytes.............................2170880 Unused Blocks...........................2 Unused Bytes............................16384 Last Used Ext FileId....................6 Last Used Ext BlockId...................308 Last Used Block.........................3
PL/SQL 過程已成功完成。
SQL> exec show_space_assm('MY_OBJECTS','DLINGER'); free space 0-25% Blocks:................0 free space 25-50% Blocks:...............1 free space 50-75% Blocks:...............0 free space 75-100% Blocks:..............0 Full Blocks:............................249 Unformatted blocks:.....................0
PL/SQL 過程已成功完成。 |
在執行玩shrink命令後，我們可以看到，table my_objects的HWM現在降到了264的位置，而且HWM下的block的空間使用狀況，full space的block有249個，free space 為25-50% Block只有1個。
我們接下來討論一下shrink的實現機制，我們同樣使用討論move機制的那個實驗來觀察。
|
SQL> create table TEST_HWM (id int ,name char(2000)) tablespace ASSM;
Table created |
往table test_hwm中插入如下的資料：
insert into TEST_HWM values (1,'aa');
insert into TEST_HWM values (2,'bb');
insert into TEST_HWM values (2,'cc');
insert into TEST_HWM values (3,'dd');
insert into TEST_HWM values (4,'ds');
insert into TEST_HWM values (5,'dss');
insert into TEST_HWM values (6,'dss');
insert into TEST_HWM values (7,'ess');
insert into TEST_HWM values (8,'es');
insert into TEST_HWM values (9,'es');
insert into TEST_HWM values (10,'es');
我們來看看這個table的rowid和block的ID和資訊：
|
SQL> select rowid , id,name from TEST_HWM;
ROWID ID NAME ------------------ ---------- ----- --------- AAANhqAAGAAAAFHAAA 1 aa AAANhqAAGAAAAFHAAB 2 bb AAANhqAAGAAAAFHAAC 2 cc AAANhqAAGAAAAFIAAA 3 dd AAANhqAAGAAAAFIAAB 4 ds AAANhqAAGAAAAFIAAC 5 dss AAANhqAAGAAAAFJAAA 6 dss AAANhqAAGAAAAFJAAB 7 ess AAANhqAAGAAAAFJAAC 8 es AAANhqAAGAAAAFKAAA 9 es AAANhqAAGAAAAFKAAB 10 es
11 rows selected
SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents where segment_name='TEST_HWM' ;
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 6 6 324 5 1 6 6 329 5 |
然後從table test_hwm中刪除一些資料：
delete from TEST_HWM where id = 2;
delete from TEST_HWM where id = 4;
delete from TEST_HWM where id = 3;
delete from TEST_HWM where id = 7;
delete from TEST_HWM where id = 8;
觀察table test_hwm的rowid和blockid的資訊：
|
SQL> select rowid , id,name from TEST_HWM;
ROWID ID NAME ------------------ ---------- ----- -------- AAANhqAAGAAAAFHAAA 1 aa AAANhqAAGAAAAFIAAC 5 dss AAANhqAAGAAAAFJAAA 6 dss AAANhqAAGAAAAFKAAA 9 es AAANhqAAGAAAAFKAAB 10 es
SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents where segment_name='TEST_HWM' ;
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 6 6 324 5 1 6 6 329 5 |
從以上的資訊，我們可以看到，在table test_hwm中，剩下的資料是分佈在AAAAFH，AAAAFI，AAAAFJ，AAAAFK這樣四個連續的block中。
|
SQL> exec show_space_assm('TEST_HWM','DLINGER'); free space 0-25% Blocks:................0 free space 25-50% Blocks:...............1 free space 50-75% Blocks:...............3 free space 75-100% Blocks:..............3 Full Blocks:............................0 Unformatted blocks:.....................0 |
通過show_space_assm我們可以看到目前這四個block的空間使用狀況，AAAAFH，AAAAFI，AAAAFJ上各有一行資料，我們猜測free space為50-75%的3個block是這三個block，那麼free space為25-50%的1個block就是AAAAFK了，剩下free space為 75-100% 的3個block，是HWM下已格式化的尚未使用的block。（關於assm下hwm的移動我們前面已經詳細地討論過了，在extent不大於於16個block時，是以一個extent為單位來移動的）
然後，我們對table my_objects執行shtink的操作：
|
SQL> alter table test_hwm enable row movement;
Table altered
SQL> alter table test_hwm shrink space;
Table altered
SQL> select rowid ,id,name from TEST_HWM;
ROWID ID NAME ------------------ ---------- ------ ----------- AAANhqAAGAAAAFHAAA 1 aa AAANhqAAGAAAAFHAAB 10 es AAANhqAAGAAAAFHAAD 9 es AAANhqAAGAAAAFIAAC 5 dss AAANhqAAGAAAAFJAAA 6 dss
SQL> select EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS 2 from dba_extents where segment_name='TEST_HWM' ;
EXTENT_ID FILE_ID RELATIVE_FNO BLOCK_ID BLOCKS ---------- ---------- ------------ ---------- ---------- 0 6 6 324 5 1 6 6 329 5 |
當執行了shrink操作後，有意思的現象出現了。我們來看看oracle是如何移動行資料的，這裡的情況和move已經不太一樣了。我們知道，在move操作的時候，所有行的rowid都發生了變化，table所位於的block的區域也發生了變化，但是所有行物理儲存的順序都沒有發生變化，所以我們得到的結論是，oracle以block為單位，進行了block間的資料copy。那麼shrink後，我們發現，部分行資料的rowid發生了變化，同時，部分行資料的物理儲存的順序也發生了變化，而table所位於的block的區域卻沒有變化，這就說明，shrink只移動了table其中一部分的行資料，來完成釋放空間，而且，這個過程是在table當前所使用的block中完成的。
那麼oracle具體移動行資料的過程是怎樣的呢？我們根據這樣的實驗結果，可以來猜測一下：
Oracle是以行為單位來移動資料的。Oracle從當前table儲存的最後一行資料開始移動，從當前table最先使用的block開始搜尋空間，所以，shrink之前，rownum＝10的那行資料（10,es），被移動到block AAAAFH上，寫到（1,aa）這行資料的後面，所以（10,es）的rownum和rowid同時發生改變。然後是（9,es）這行資料，重複上述過程。這是oracle從後向前移動行資料的大致遵循的規則，那麼具體移動行資料的的演算法是比較複雜的，包括向ASSM的table中insert資料使用block的順序的演算法也是比較複雜的，大家有興趣的可以自己來研究，在這裡我們不多做討論。
我們還可以在shrink table的同時shrink這個table上的index：
alter table my_objects shrink space cascade;
同樣地，這個操作只有當table上的index也是ASSM時，才能使用。
Shrink的幾點問題：
a. shrink後index是否需要rebuild：
因為shrink的操作也會改變行資料的rowid，那麼，如果table上有index時，shrink table後index會不會變為UNUSABLE呢？我們來看這樣的實驗，同樣構建my_objects的測試表：
create table my_objects tablespace ASSM as select * from all_objects where rownum<20000;
create index i_my_objects on my_objects (object_id);
delete from my_objects where object_name like '%C%';
delete from my_objects where object_name like '%U%';
現在我們來shrink table my_objects：
|
SQL> alter table my_objects enable row movement;
Table altered
SQL> alter table my_objects shrink space;
Table altered SQL> select index_name,status from user_indexes where index_name='I_MY_OBJECTS';
INDEX_NAME STATUS ------------------------------ -------- I_MY_OBJECTS VALID |
我們發現，table my_objects上的index的狀態為VALID，估計shrink在移動行資料時，也一起維護了index上相應行的資料rowid的資訊。我們認為，這是對於move操作後需要rebuild index的改進。但是如果一個table上的index數量較多，我們知道，維護index的成本是比較高的，shrink過程中用來維護index的成本也會比較高。
b. shrink時對table的lock
在對table進行shrink時，會對table進行怎樣的鎖定呢？當我們對table MY_OBJECTS進行shrink操作時，查詢v$locked_objects檢視可以發現，table MY_OBJECTS上加了row-X (SX) 的lock：
|
SQL>select OBJECT_ID, SESSION_ID,ORACLE_USERNAME,LOCKED_MODE from v$locked_objects;
OBJECT_ID SESSION_ID ORACLE_USERNAME LOCKED_MODE ---------- ---------- ------------------ ----------- 55422 153 DLINGER 3 SQL> select object_id from user_objects where object_name = 'MY_OBJECTS';
OBJECT_ID ---------- 55422 |
那麼，當table在進行shrink時，我們對table是可以進行DML操作的。
c. shrink對空間的要求
我們在前面討論了shrink的資料的移動機制，既然oracle是從後向前移動行資料，那麼，shrink的操作就不會像move一樣，shrink不需要使用額外的空閒空間。
小結：我們在這一部分介紹了三種降低table HWM的方法，那麼實際的環境中，我們選擇move還是shrink，可以針對這幾項的特性，考慮你的系統的情況，做出選擇。
七、其餘幾種會移動HWM的操作
還有幾種操作是會改變HWM的：insert append，truncate。
還有一些方法也可以用來降低HWM，比如：exp/imp等，我們在這裡不做討論。
(1).insert append
當我們使用insert /*+ append */ into向一個table中插入資料時，oracle不會在HWM以下尋找空間，而是直接移動HWM，從EMPTY_BLOCKS中獲得要使用的block空間，來滿足這一操作的blocks的需要。
我們來看一個實驗：
|
SQL> create table hwm as select * from all_objects;
Table created
SQL> select count(*) from hwm;
COUNT(*) ---------- 31009 SQL> delete from hwm;
31009 rows deleted SQL> exec show_space(p_segname => 'HWM',p_owner => 'DLINGER',p_type => 'TABLE'); Total Blocks............................425 Total Bytes.............................3481600 Unused Blocks...........................3 Unused Bytes............................24576 Last Used Ext FileId....................11 Last Used Ext BlockId...................439 Last Used Block.........................2 |
我們往表hwm中先插入31009條資料，然後在delete掉所有的資料。前面我們討論過，delete操作不會降低HWM，所以這時的HWM = 425 - 3 + 1 = 423。
下面，我們來比較一下insert和insert append的不同結果：
我們先使用insert into向表HWM中插入1000行資料，結果HWM沒有移動。
|
SQL> insert into hwm select * from all_objects where rownum<1000;
999 rows inserted
SQL> commit;
Commit complete SQL> exec show_space(p_segname => 'HWM',p_owner => 'DLINGER',p_type => 'TABLE'); Total Blocks............................425 Total Bytes.............................3481600 Unused Blocks...........................3 Unused Bytes............................24576 Last Used Ext FileId....................11 Last Used Ext BlockId...................439 Last Used Block.........................2 |
然後我們delete掉所有的資料，再用insert append來作同樣的操作。可以看到，使用append提示後，結果就不一樣了。
|
SQL> delete from hwm;
999 rows deleted
SQL> commit;
Commit complete
SQL> insert /*+ append */ into hwm select * from all_objects where rownum<1000;
999 rows inserted
SQL> commit;
Commit complete SQL> exec show_space(p_segname => 'HWM',p_owner => 'DLINGER',p_type => 'TABLE'); Total Blocks............................440 Total Bytes.............................3604480 Unused Blocks...........................3 Unused Bytes............................24576 Last Used Ext FileId....................11 Last Used Ext BlockId...................459 Last Used Block.........................2 |
我們發現，往hwm中插入同樣的999行資料，使用insert append，HWM = 440 - 3 + 1 = 438 ，HWM從423移動到了438！
我們再來比較以下insert和insert append的效能，對HWM插入同樣的10000條資料。
構建表T：
|
SQL> create table t as select * from all_objects;
Table created SQL> insert /*+ append */ into t select * from t; 31010 rows inserted SQL> commit; Commit complete SQL> insert /*+ append */ into t select * from t; 62020 rows inserted SQL> commit; Commit complete SQL> select count(*) from t;
COUNT(*) ---------- 124040 |
對HWM插入資料：
|
SQL> set timing on SQL> insert into hwm select * from t;
124040 rows inserted
已用時間: 00: 00: 02.93 SQL> commit;
Commit complete
已用時間: 00: 00: 00.20 SQL> insert /*+ append */ into hwm select * from t;
124040行rows inserted
已用時間: 00: 00: 01.02 SQL> commit;
Commit complete
已用時間: 00: 00: 00.30 |
當使用insert來插入124040行資料時，使用了2.93sec；而使用insert append插入124040行資料時，只使用了1.02sec。
在這裡，提一下使用append的一個需要注意的問題：
當我們使用insert append時，oracle會生成表級的獨佔鎖：
|
SQL> select * from v$mystat where rownum <2;
SID STATISTIC# VALUE ---------- ---------- ---------- 13 0 1
SQL> insert /*+ append */ into hwm select * from all_objects where rownum<1000; 999 rows inserted ――我們在這裡不作commit
――在另一個session中執行： QL> select * from v$mystat where rownum <2;
SID STATISTIC# VALUE ---------- ---------- ---------- 10 0 1
SQL> insert into hwm select * from all_objects where rownum<10; ――這個session出現等待 |
現在我們觀察v$lock：
|
SQL> select SID,TYPE,ID1,ID2,LMODE,REQUEST,BLOCK from v$lock;
SID TYPE ID1 ID2 LMODE REQUEST BLOCK ---------- ---- ---------- ---------- ---------- ---------- ---------- ...... 10 TM 32398 0 0 3 0 13 TX 65579 22477 6 0 0 13 TM 32398 0 6 0 1 --13阻塞了一個process SQL> select object_name from user_objects where object_id = '32398';
OBJECT_NAME -------------------------------------------------------------------------------- HWM |
Session 13在HWM上加上了exclusive的TM鎖，這時session 13 blocking了session 10。
這裡我們是在LMT下的segment中做的測試。在ASSM中append鎖表的情況同樣存在（直到oracle10g的ASSM中依然如此）。
(2).Truncate
我們討論truncate table，一般是和delete from table做比較。
前面，我們已經討論過delete不會降低HWM的問題，這裡我們再來看一下truncate的情況：
|
SQL> exec show_space(p_segname => 'HWM',p_owner => 'DLINGER',p_type => 'TABLE'); Total Blocks............................3380 Total Bytes.............................27688960 Unused Blocks...........................18 Unused Bytes............................147456 Last Used Ext FileId....................11 Last Used Ext BlockId...................5069 Last Used Block.........................2
PL/SQL 過程已成功完成。 --這裡HWM = 3380 - 18 + 1= 3363 SQL> truncate table HWM;
表已截掉。
SQL> exec show_space(p_segname => 'HWM',p_owner => 'DLINGER',p_type => 'TABLE'); Total Blocks............................5 Total Bytes.............................40960 Unused Blocks...........................4 Unused Bytes............................32768 Last Used Ext FileId....................11 Last Used Ext BlockId...................19 Last Used Block.........................1
PL/SQL 過程已成功完成。 --執行truncate後HWM = 5 - 4 + 1 = 2 |
我們發現，truncate table之後，HWM又回到了1中我們看到的segment初始化狀態下HWM的位置。
Note: 本文出去:
http://www.eygle.com/archives/2011/11/oracle_hwm_tuning.html
僅供學習參考用!!!
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26464953/viewspace-713937/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- zt_eygle_Oracle中 HWM與資料庫效能的探討Oracle資料庫
- Oracle Freelist和HWM原理探討及相關效能優化Oracle優化
- Oracle Freelist和HWM原理探討及相關效能優化(轉)Oracle優化
- Oracle Freelist和HWM原理探討及相關效能最佳化Oracle
- Oracle Freelist和HWM原理探討及相關效能最佳化(1)Oracle
- Oracle Freelist和HWM原理探討及相關效能最佳化(2)Oracle
- Oracle Freelist和HWM原理探討及相關效能最佳化(3)Oracle
- Oracle 資料庫分散式技術的探討Oracle資料庫分散式
- Oracle資料庫升級或資料遷移的方法探討Oracle資料庫
- ORACLE資料庫體系框架介紹及SQL語句效能探討(轉)Oracle資料庫框架SQL
- 資料庫恢復原理探討資料庫
- Oracle資料庫實施產品相容性與最佳實踐探討Oracle資料庫
- NoSQL資料庫探討 -- 非關係型資料庫SQL資料庫
- 資料庫設計中的反規範技術探討(轉)資料庫
- Oracle資料庫伺服器IO高的分析方案和案例探討Oracle資料庫伺服器
- 時序資料庫破局開放探討資料庫
- 用裸裝置與Oracle資料庫的效能Oracle資料庫
- Oracle資料庫效能Oracle資料庫
- MySQL資料庫資料一致性比對的方案的探討MySql資料庫
- Oracle Freelist和HWM的效能優化Oracle優化
- Oracle Stream 深入探討Oracle
- 基於物件導向（OO）的資料庫設計模式探討物件資料庫設計模式
- NoSQL資料庫探討之一 － 為什麼要用非關聯式資料庫？SQL資料庫
- oracle資料庫的效能調整Oracle資料庫
- 重新開貼！探討web應用中ResultSet返回的資料表示Web
- 轉：Oracle Freelist和HWM的效能優化Oracle優化
- 探討Web開發中的Session儲存與管理WebSession
- Oracle 技術探討3Oracle
- Oracle 技術探討2Oracle
- Oracle 技術探討1Oracle
- 資料倉儲資料質量的問題探討(轉)
- 【轉】關於oracle中Move機制的一點探討Oracle
- Oracle資料庫效能優化Oracle資料庫優化
- oracle資料庫的效能調整(轉)Oracle資料庫
- 找到Oracle資料庫中效能最差的查詢語句BSOracle資料庫
- 深入探討Java中的異常與錯誤處理Java
- 共同探討資料庫技術與行業數字化轉型過程的融合創新資料庫行業
- Oracle高水位線(HWM)及效能優化Oracle優化