Oracle的表空間的儲存管理與最佳化技術

1、 描述資料庫的邏輯儲存結構----表空間（TABLESPACE）

2、 描述字典管理表空間（DMT）的特性以及相應缺點

3、 描述字典管理表空間的最佳化方法

4、 描述本地管理表空間（LMT）的特性以及相應優點

5、 描述9i新的表空間型別以及相應最佳化

6、 描述段自動管理表空間的特點

7、 描述10g新的表空間的特點及相應最佳化

一、表空間的作用與分類

表空間是資料庫中最大的邏輯儲存結構，為資料庫提供使用空間，其對應物理結構是資料檔案，一個表空間可以包含多個資料檔案，但是一個資料檔案只能屬於一個表空間。表空間所包含的資料檔案的大小，也就決定了表空間的大小，所以，表空間也是邏輯結構連線到物理結構的一個紐帶。

既然表空間為資料庫提供使用空間，它就必須有自己的空間管理辦法，在表空間中增加，刪除段的時候，資料庫就必須跟蹤這些段空間的使用。

如下例所示，假定一個新建立的表空間包含了五個表

表一......表二......表三......表四......表五......未用空間

當我們刪除表四的時候，就有如下結果

表一......表二......表三......空閒空間段......表五......未用空間

很明顯，ORACLE需要有一個機制來管理表空間中各資料檔案的這些分配的或未分配的空間，為了跟蹤這些可以使用的空間（包括未分配使用的和可以重複使用的），對於每一個空間，我們必須知道：

1、這個可用空間位於什麼資料檔案

2、這個空間的尺寸是多大

3、如果它在用了，是哪一個段佔用的這個空間

在空間的管理方式上，ORACLE推出了三種主要的空間管理方式，

一種是8i以前的字典管理方式（DMT），把可用空間和未用空間在資料字典中管理，ORACLE透過一個迭歸SQL語句該字典表中請求空間。

另外一種就是8i以後的本地管理模式（LMT），本地管理模式完全放棄以前的管理方法，透過在資料檔案的頭部建立點陣圖區域來管理空間的分配，在一定程度上避免了併發上的衝突；而且本地管理表空間透過儲存上統一的空間管理並取消了為獨立的段的NEXT儲存引數，也解決了表空間一直以來頭疼的碎片問題。

還有一種自動區段空間管理（ASSM）是9iR2推出的一種表空間級別的段空間管理模式，ASSM表空間是透過將SEGMENT SPACE MANAGEMENT AUTO子句新增到本地管理表空間的定義句法裡而實現的。透過使用點陣圖陣列取代傳統單向的連結列表（FREELIST），ASSM表空間會將連結列表的管理自動化，並取消為獨立的段指定PCTUSED、FREELISTS和FREELIST GROUPS儲存引數。

注意：ASSM表空間一定就是LMT表空間

二、字典管理表空間

2.1 字典管理表空間的特性

在8i以前（不包括8i），只存在一種表空間的管理模式，這就是字典管理表空間。

　主要語法：CREATE TABLESPACE 表空間名字

　　　　　　　　　DATAFILE '資料檔案詳細資訊' [SIZE INTETER [K|M]

|[DEFAULT STORAGE]|[PERMANENT|TEMPORARY]]

關鍵字DEFAULT STORAGE指明瞭該表空間的預設儲存格式，包含了INITIAL，NEXT，PCTINCREASE等相關引數的設定，如果建立在該表空間中的物件，不指明儲存引數的話，將採用表空間的預設儲存引數；PERMANENT|TEMPORARY指明瞭該表空間的型別是永久的還是臨時的。

為了確保能儲存以上空間分配的資訊，ORACLE用了兩個資料字典表：UET$（已使用的空間）和FET$（空閒空間）來儲存表空間的空間使用與釋放的資訊，在涉及到空間分配的時候，ORACLE使用一個迭歸SQL語句到該字典表中請求空間。

2.2 字典管理表空間的缺點

字典表空間由於本身的設計上的問題，存在如下缺陷

2.2.1 併發等待

查詢UET$或FET$我們可以看到，每個已使用空間段或空閒空間段（不一定是一個extent，可以是多個extent）都在該表中對應了一行，如：

SQL> select * from UET$;

SEGFILE# SEGBLOCK# EXT# TS# FILE# BLOCK# LENGTH

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

1 127 0 0 1 127 2

1 109 0 0 1 109 4

1 119 0 0 1 119 2

1 42 0 0 1 42 2

1 133 0 0 1 133 10

1 51 0 0 1 51 2

1 69 0 0 1 69 10

......

SQL> select * from FET$;

TS# FILE# BLOCK# LENGTH

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

1 2 1090 64

1 2 2626 128

2 3 2 99

3 4 82 8

3 4 74 8

......

它的工作方式是當建立一個新的段或者段在表空間中請求新的空間時，ORACLE透過一個迭歸SQL語句來完成這個工作，移動或增加相應的行到UET$中，並改變相應FET$；當刪除一個段的時候，ORACLE則移動UET$中相應的行到FET$。這個過程的發生是連續的、序列的，極可能發生等待。當併發性很高的時候，將產生資料字典的爭用。

而且資料字典的表的資訊發生改變，從而同時也使用了在系統表空間裡的回滾段，引起空間爭用，因為每一個字典改變的操作都需要記入系統回滾段，而且當一個段有幾萬或者幾十萬個區間的時候（對應字典表的幾十萬條記錄），不用說管理該字典表的負擔增加，就是對該段的drop操作都會變得異常國難，甚至導致系統回滾段空間不夠而失敗。

2.2.2 空間碎片

當段的空間很不連續或表空間有大量的碎片就會引起資料庫效能上的下降。因為字典管理表空間沒有任何措施可以保證段的所有區間是相鄰儲存的。當要滿足一個空間要求時，資料庫不再合併相鄰的自由範圍（除非別無選擇）， 而是尋找表空間中最大的自由範圍來使用。這樣將逐漸形成越來越多的離散的、分隔的、較小的自由空間，即碎片。隨著時間推移，基於資料庫的應用系統的廣泛使用，產生的碎片會越來越多，將對資料庫有以下兩點主要影響：

·導致系統效能減弱，如上所述，當要滿足一個空間要求時，資料庫將首先查詢當前最大的自由範圍，而"最大"自由範圍逐漸變小，要找到一個足夠大的自由範圍已變得越來越困難，從而導致表空間中的速度障礙，使資料庫的空間分配愈發遠離理想狀態。

·浪費大量的表空間，儘管有一部分自由範圍（如表空間的PCTINCREASE為非0）將會被SMON（系統監控）後臺程式週期性地合併，但始終有一部分自由範圍無法得以自動合併，浪費了大量的表空間。而非0的PCTINCREASE更容易導致更多的碎片。

2.3 字典管理表空間的最佳化

從以上一系列操作中，我們可以看到，UET$記錄了任何使用的段的區間，如果區間數太多，將給表UET$的操作帶來一定壓力，所以在字典管理的表空間中，一般要求區間數少一點比較好，除非有特殊要求，一般建議在20個區間以下。還有一點就是如果發生連續的空間請求，將導致ORACLE在兩個字典表之間的操作等待，對於併發性很高的資料庫來說，這是一個高昂的操作，所以，我們可以採用預分配空間的方式，並不但的監控段的空間使用情況（大小，區間數），這樣就可以在很大程度上解決併發處理帶來的額外的代價。

在另外一個方面，我們可以指定在字典管理表空間中的所有段都具有PCTINCREASE=0的特性，保證每個區間的大小相等，然後可以設定每個區間的大小等於某一個特定數的整數倍，如=n*db_block_size* db_file_multiblock_read_count，這樣可以在很大程度上防止表空間的碎片化。

三、本地管理表空間

3.1本地管理表空間的特性

在Oracle8I的版本中，Oracle推出了一種全新的表空間管理方式：本地化管理的表空間。所謂本地化管理，就是指Oracle不再利用資料字典表來記錄Oracle表空間裡面的區的使用狀況，而是在每個表空間的資料檔案的頭部加入了一個點陣圖區，在其中記錄每個區的使用狀況。每當一個區被使用，或者被釋放以供重新使用時，Oracle都會更新資料檔案頭部的這個記錄，反映這個變化。

本地化管理的表空間的建立過程：

　主要語法：CREATE TABLESPACE 表空間名字

　　　　　　　　　　DATAFILE '資料檔案詳細資訊'

　　　　　　　　　　[EXTENT MANAGEMENT { LOCAL

　　　　　　　　　　{AUTOALLOCATE | UNIFORM. [SIZE INTETER [K|M] ] } } ]

關鍵字EXTENT MANAGEMENT LOCAL 指定這是一個本地化管理的表空間。對於系統表空間，只能在建立資料庫的時候指定EXTENT MANGEMENT LOCAL，因為它是資料庫建立時建立的第一個表空間。

在8i中，字典管理還是預設的管理方式，當選擇了LOCAL關鍵字，即表明這是一個本地管理的表空間。當然還可以繼續選擇更細的管理方式：是自動分配（AUTOALLOCATE） 還是 統一尺寸（UNIFORM）.。若為自動分配，則表明讓Oracle來決定區塊的使用辦法；若選擇了統一尺寸，則還可以詳細指定每個區間（Extent）的大小，若不加指定，則預設每個區使用1M大小。

在表空間的空間管理上，ORACLE將儲存資訊儲存在表空間的頭部的點陣圖中，而不是儲存在資料字典中。透過這樣的方式，在分配或者回收空間的時候，表空間就可以獨立的在資料檔案頭部完成操作而不用與其它物件打交道。

也因為僅僅運算元據檔案頭部幾個塊，不用運算元據字典，所以ORACLE在本地管理的表空間中新增，刪除段的時候，效率要比字典管理的表空間快。特別是在併發性很強的空間請求中。

對於在本地管理的表空間內部建立的物件而言，NEXT擴充套件子句是過時的，因為由本地管理的表空間會自動管理它們。但是，INITIAL引數仍然是需要的，因為Oracle不可能提前知道初始段載入的大小。

在本地管理的表空間中，如果資料庫塊尺寸（db_block_size） 為 16k和16k以下，資料檔案頭是64k 保留空間，若是32k的塊尺寸，則保留128k，以預設的8k的塊大小而言，就是8個塊用在資料檔案頭部用於系統消耗，其中的3~8用於記錄區間的點陣圖資訊。

3.2 管理點陣圖塊的內部結構

3.2.1 統一尺寸的本地管理表空間

如果我們dump統一尺寸方式的本地管理表空間包含的資料檔案的第三個塊，就可以得到類似如下的資訊

Start dump data blocks tsn: 5 file#: 5 minblk 3 maxblk 3

buffer tsn: 5 rdba: 0x01400003 (5/3)

scn: 0x0000.202f7c64 seq: 0x01 flg: 0x00 tail: 0x7c641e01

frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap

File Space Bitmap Block:

BitMap Control:

RelFno: 5, BeginBlock: 9, Flag: 0, First: 19, Free: 63469

FFFF070000000000 0000000000000000 0000000000000000 0000000000000000

......

注意其中的FFFF07轉換為二進位制為1111,1111,1111,1111,0000,0111，注意位元組交換，得到1111,1111,1111,1111,1110,0000

可以看到，該表空間用19個位(bit)來代表共分配的19個區間

3.2.2 自動分配的本地管理表空間

在自動分配的本地管理的表空間中，區間尺寸可能由以下尺寸組成64k, 1m, 8m, 64m 甚至是256m。但是不管多大，都有一個通用尺寸64k，所以64K就是該表空間的一個位標記的大小。如我們同樣的dump檔案頭的第三個塊。

Start dump data blocks tsn: 19 file#: 12 minblk 3 maxblk 3

buffer tsn: 19 rdba: 0x03000003 (12/3)

scn: 0x0000.00f2959b seq: 0x01 flg: 0x00 tail: 0x959b1e01

frmt: 0x02 chkval: 0x0000 type: 0x1e=KTFB Bitmapped File Space Bitmap

File Space Bitmap Block:

BitMap Control:

RelFno: 12, BeginBlock: 9, Flag: 0, First: 800, Free: 62688

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF

FFFFFFFF00000000 0000000000000000 0000000000000000 0000000000000000

0000000000000000 0000000000000000 0000000000000000 0000000000000000

......

可以看到這裡工分配了800個位(bit)，但是自動分配模式下的位大小並不一定等於extent大小，所以不一定是對應800個區間，如它可能對應的是50個1M大小的區間，如50*1024=800*64

3.3 本地管理表空間的優點

Oracle之所以推出了這種新的表空間管理方法，讓我們來看一下這種表空間組織方法的優點：

　　1. 本地化管理的表空間用資料檔案的頭部記錄來管理空閒塊，避免了遞迴的空間管理操作，避免了利用系統回滾段因此帶來的效能與空間問題。

　　2. 本地化管理的表空間避免了在資料字典相應表裡面寫入空閒空間、已使用空間的資訊，從而減少了資料字典表的競爭，提高了空間管理的併發性。

　　3. 區的本地化管理自動跟蹤表空間裡的空閒塊，減少了手工合併自由空間的需要。

　　4. 表空間裡的區的大小可以選擇由Oracle系統來決定，或者由資料庫管理員指定一個統一的大小，避免了字典表空間一直頭疼的碎片問題。　

四、段自動管理表空間

4.1 段自動管理表空間的特性

在920以前，表的剩餘空間的管理與分配都是由連結列表（FREELIST）來完成的，因為連結列表存在序列的問題因此往往容易引起段頭的爭用與空間的浪費，而且還需要DBA 花費大量的精力去管理這些爭用並監控表的空間利用。

自動段空間管理（ASSM），它首次出現在Oracle920裡。有了ASSM，連結列表被點陣圖陣列所取代，它是一個二進位制的陣列，能夠迅速有效地管理儲存擴充套件和剩餘區塊（free block），因此能夠改善分段儲存本質。

讓我們看看點陣圖陣列是如何實現連結列表的功能的。我會從使用區段空間管理自動引數建立表空間開始：

CREATE TABLESPACE demo

DATAFILE '/u01/oracle/demo01.dbf '

SIZE 5M

EXTENT MANAGEMENT LOCAL -- Turn on LMT

SEGMENT SPACE MANAGEMENT AUTO -- Turn on ASSM;

帶有ASSM的本地管理表空間會略掉任何為PCTUSED、NEXT和FREELISTS所指定的值。Oracle9i會使用點陣圖陣列來自動地管理表空間裡物件空間使用。

新的管理機制用點陣圖陣列來跟蹤或管理每個分配到物件的塊，每個塊有多少剩餘空間根據點陣圖的狀態來確定，如>75%,50%-75%,25%-50%和<25%，也就是說點陣圖其實採用了四個狀態位來代替以前的PCTUSED，什麼時候該利用該資料塊則由設定的PCTFREE來確定。

使用ASSM的一個巨大優勢是，點陣圖陣列肯定能夠減輕緩衝區忙等待（buffer busy wait）的負擔，這個問題在Oracle9i以前的版本里曾是一個嚴重的問題，因為在沒有多個點陣圖陣列的時候，每個Oracle段在段的頭部都曾有一個資料塊用於連結列表，用來管理物件所使用的剩餘區塊，併為任何SQL申請的新資料行提供資料塊。當資料緩衝內的資料塊由於被另一個DML事務處理鎖定而無法使用的時候，緩衝區忙等待就會發生。當你需要將多個任務插入到同一個表格裡的時候，這些任務就被強制等待，而同時Oracle會在同時分派剩餘的區塊，一次一個。

有了ASSM之後，Oracle宣稱顯著地提高了DML併發操作的效能，因為點陣圖陣列的不同部分可以被同時使用，這樣就消除了尋找剩餘空間的序列化。根據Oracle的測試結果，使用點陣圖陣列會消除所有分段頭部（對資源）的爭奪，還能獲得超快的併發插入操作。

4.2點陣圖管理段內部結構

如果我們dump ASSM表空間的第10個塊，我們可以發現如下資訊

Start dump data blocks tsn: 6 file#: 7 minblk 10 maxblk 10

buffer tsn: 6 rdba: 0x0680000a (7/10)

scn: 0x0000.00181a39 seq: 0x01 flg: 0x04 tail: 0x1a392101

frmt: 0x02 chkval: 0x2738 type: 0x21=SECOND LEVEL BITMAP BLOCK

Dump of Second Level Bitmap Block

number: 8 nfree: 8 ffree: 0 pdba: 0x0680000b

opcode:0

xid:

L1 Ranges :

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

0x06800009 Free: 5 Inst: 1

0x06800019 Free: 5 Inst: 1

0x06800029 Free: 5 Inst: 1

0x06800039 Free: 5 Inst: 1

0x06800049 Free: 5 Inst: 1

0x06800059 Free: 5 Inst: 1

......

可以看到，塊10是一個二級管理點陣圖塊，負責記錄一級點陣圖塊的地址，其中的9,19,29等就是記錄自由空間的一級點陣圖塊的地址，從這裡可以看到，每個一級點陣圖塊管理16個（十六進位制10個）塊的儲存資訊。如果在併發的處理中，每個一級點陣圖塊可以單獨的管理或者是分配空間，而不是再由以前的一個連結列表塊來進行空間的管理，在實際的情況中，每個一級點陣圖塊也不一定是管理16個塊的空間資訊，也有可能是32個或者更多。

點陣圖資料的級別可以分為三個級別，當存在一個或多個一級點陣圖塊（如塊9，19）的時候，將由二級點陣圖（如塊10）塊來儲存一級點陣圖塊的地址，同理，一個二級點陣圖塊不夠使用而出現多個二級點陣圖塊的時候，將由三級點陣圖塊來儲存二級點陣圖塊的地址（由於三級點陣圖塊的出現需要很多資料塊，所以這裡不討論三級點陣圖塊）。整個點陣圖陣列的結構形成一個樹狀結構，有利於ORACLE跟蹤所有的點陣圖資料塊的位置。

如果我們dump其中的一個一級點陣圖塊，如塊39，對應的是十進位制的57。

Start dump data blocks tsn: 6 file#: 7 minblk 57 maxblk 57

buffer tsn: 6 rdba: 0x06800039 (7/57)

scn: 0x0000.0018b7cb seq: 0x04 flg: 0x04 tail: 0xb7cb2004

frmt: 0x02 chkval: 0x27d2 type: 0x20=FIRST LEVEL BITMAP BLOCK

Dump of First Level Bitmap Block

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

nbits : 4 nranges: 2 parent dba: 0x0680000a poffset: 3

unformatted: 8 total: 16 first useful block: 1

owning instance : 1

instance ownership changed at 08/19/2003 10:41:42

Last successful Search 08/19/2003 10:41:42

Freeness Status: nf1 1 nf2 0 nf3 0 nf4 6

Extent Map Block Offset: 4294967295

First free datablock : 1

Bitmap block lock opcode 0

Locker xid: : 0x0000.000.00000000

Highwater:: 0x06800041 ext#: 6 blk#: 8 ext size: 8

#blocks in seg. hdr's freelists: 0

#blocks below: 50

mapblk 0x00000000 offset: 6

HWM Flag: HWM Set

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

DBA Ranges :

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

0x06800039 Length: 8 Offset: 0

0x06800041 Length: 8 Offset: 8

0:Metadata 1:FULL 2:FULL 3:75-100% free

4:75-100% free 5:75-100% free 6:75-100% free 7:0-25% free

8:unformatted 9:unformatted 10:unformatted 11:unformatted

12:unformatted 13:unformatted 14:unformatted 15:unformatted

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

End dump data blocks tsn: 6 file#: 7 minblk 57 maxblk 57

我們可以看到，在點陣圖塊57管理的16個資料塊中

1個點陣圖+2個FULL+4個75-100% free+1個0-25% free+8個未使用的共16個塊，如果在下次的插入或者更新中，點陣圖塊57將負責這16個資料塊的空間的分配與使用以及相應的狀態記載，可以想象，如果是除了這16個塊之外的塊的空間的管理，將由類似塊57的塊來完成，多個點陣圖塊並行管理將明顯的增加併發的處理能力。

4.3、段自動管理表空間的最佳化

儘管ASSM顯示出了令人激動的特性並能夠簡化Oracle DBA的工作，但是Oracle9iR2的點陣圖分段管理還是有一些侷限性的：

· 一旦DBA被分配之後，它就無法控制表空間內部的獨立表格和索引的儲存行為。

· 大型物件不能夠使用ASSM，而且必須為包含有LOB資料型別的表格建立分離的表空間。

· 你不能夠使用ASSM建立臨時的表空間。這是由排序時臨時分段的短暫特性所決定的。

· 只有本地管理的表空間才能夠使用點陣圖分段管理。

· 使用超高容量的DML（例如INSERT、UPDATE和DELETE等）的時候可能會出現效能上的問題。

正因為ASSM還不是太穩定與完善，所以至少在9iR2的版本上，還不建議生產系統中大規模使用ASSM的表空間。

五、9i對錶空間的管理最佳化

5.1自動undo管理的表空間

在9i以前，回滾段全是手工管理與監控的，DBA需要花費一定的時間去管理與監控回滾段的效能，建立不好或管理不好的回滾段，將引起很大的效能瓶頸。從ORACLE9i，為了更好的管理回滾段，ORACLE，預設採用自動回滾段管理。

自動回滾段管理可以最大限度的避免8i中比較有名的ORA-01555"快照太老"的錯誤，ORACLE9i透過如下四個初試化引數來設定自動回滾段管理：

undo_management string AUTO

undo_retention integer 10800

undo_suppress_errors boolean FALSE

undo_tablespace string UNDOTBS1

undo_management表明了回滾段管理採用自動方式，ORACLE建議採用自動方式，如果不是對資料庫非常瞭解，不要修改該引數。

undo_retention表明了回滾資訊在回滾段中保持的時間，單位是秒，預設3個小時，如果資料庫的事務量特別大，可以適當的減少該引數值，避免回滾表空間的膨脹，但是過小的值也將導致ORA-01555錯誤的重現以及FLASHBACK QUERY功能的侷限。

undo_suppress_errors表明不顯示某些錯誤資訊，如對系統回滾段的操作將不顯示錯誤，雖然這個操作沒有成功。

undo_tablespace表明了使用自動回滾的表空間，DBA需要監控該表空間的大小。

自動回滾段的另外一個好處就是可以利用FLASHBACK QUERY來檢視提交以前的資料或匯出當前時間點以前的資料，防止一定程度上的人為錯誤。

5.2 完全本地的臨時表空間

透過9i預設的本地管理的臨時檔案，總是處於nologging模式，控制檔案也不記錄臨時檔案的位置，由於不記載redo資訊，所以9i的臨時資料檔案不需要進行備份與恢復，如果發生意外，只需要重新建立一個即可。

另外，由於9i預設臨時表空間的出現，減少了9i以前因為預設臨時表空間是系統表空間而導致的表空間碎片問題。

六、10g對錶空間的最佳化

SYSAUX表空間在Oracle Database 10g中被引入，作為SYSTEM表空間的輔助表空間，這是一個管理及規劃上的改進，進一步獨立SYSTEM表空間，保證其儲存及效能。以前版本使用獨立表空間或系統表空間的資料庫元件現在SYSAUX表空間中建立。透過分離這些元件和功能，SYSTEM表空間的負荷得以減輕。反覆建立一些相關物件及元件引起SYSTEM表空間的碎片問題得以避免。而且，由於大量的獨立表空間中的物件都被移往了SYSAUX表空間，使得10g的表空間數目變的很少，對於空間管理，備份與最佳化都是一個不錯的福音。

而且如果SYSAUX表空間的不可用，資料庫核心功能將保持有效，所以使用SYSAUX表空間的特點將會失敗或功能受限，使資料庫變的更穩定可靠。

小結

瞭解字典管理表空間的工作原理，儘量減少空間分配的序列化與表空間的碎片化。

瞭解本地管理表空間的工作原理，儘量使用空間管理的本地化來減少字典管理表空間帶來的問題。

瞭解段自動管理表空間的工作原理，理解連結列表的工作原理，理解ASSM對於大量併發處理的好處以及相關缺點

瞭解9i新型自動重作表空間的好處以及完全本地管理的臨時表空間的優點

瞭解10g新型SYSAUX表空間出現的原因以及相應管理上的最佳化

附錄

表空間的空間監控

表空間的空間使用其實是一個需要特別注意的問題，因為資料檔案不可擴充套件而導致表空間的空間不夠，可能導致無法寫入任何新的資料，而甚至導致資料庫的停止。以下的語句可以監控表空間的空間利用情況，如果使用了9i的完全臨時表空間，則加入後半部分用於檢測臨時表空間。

SELECTD.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
--如果採用了完全本地管理的臨時表空間，就加入如下部分
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

段的空間利用監控

段的空間與區間的利用，在字典管理的表空間有尤為重要，如果一個物件的區間數太多，不但大大加重了字典表的管理負擔與系統回滾段的壓力，也嚴重影響對該段（如表或索引）的效能。該查詢也可以看到現在利用的區間與最大區間數的差異，如果該差值已經很小，就需要注意新的空間的分配，避免因為不能分配新的區間而導致新資料的寫入錯誤。

SELECTS.OWNER,S.SEGMENT_NAME,S.SEGMENT_TYPE,S.PARTITION_NAME,
ROUND(BYTES/(1024*1024),2) "USED_SPACE(M)",
EXTENTS USED_EXTENTS,S.MAX_EXTENTS,S.BLOCKS ALLOCATED_BLOCKS,
S.BLOCKS USED_BOLCKS,S.PCT_INCREASE,S.INITIAL_EXTENT,
S.NEXT_EXTENT/1024"NEXT_EXTENT(K)",S.TABLESPACE_NAME
FROM DBA_SEGMENTS S
WHERE S.OWNER = USER
ORDER BY Used_Extents DESC