資料庫物理設計經驗談(二)

qinwen740發表於2010-04-30
資料庫
六．設定和管理表空間
資料庫檔案之間的I/O競爭是資料庫之大忌, 所以對資料庫規劃之前要先對資料檔案的I/O進行初步的評估, 通常情況下, 應用的產品資料庫表所在的表空間會很活躍, 索引表空間和資料字典之類的表空間也很活躍的, 對於事物比較頻繁的應用中, 重做表空間也很活躍的, 所以對不同型別的資料庫其資料檔案的I/O競爭也會略有不同的, 但是基本上還是遵從以下的原則比較好: 應用的表和索引通常應該被分配或分割槽到多個表空間中, 以降低單個資料檔案的I/O, 最好把每一種功能相同的區域物件建立單獨的表空間; 沒有理由把除資料字典表和系統回退段外的其他東西放到系統表空間中, 要把能移出系統表空間的物件都移出; 索引段不應該和相關表放在同一表空間中, 因為他們在資料管理和查詢時會產生很多的併發I/O; 臨時表空間是用以儲存大量的排序, 所以其它的應用物件是不能放在臨時表空間。
以上講的是資料庫檔案分佈的原理, 原理歸原理, 事實應用中, 我們還是以經驗來設定分佈比較好些, 當然在沒有經驗之前還是參考原理以致於不會走彎路。
資料庫和表空間可以是一對多的關係，表空間和資料檔案也可以是一對多的關係，資料檔案和資料物件也可以是一對多的關係。當建立一個資料物件（如表或索引） 時，可以通過預設值或特殊命令將其賦予一個表空間，這樣就會在該表空間中建立一個段（Segment）來儲存與該物件有關的資料。一個段由一些稱作區間 （Extent，一組連續的Oracle塊）的區段組成，一但現有的區段不能儲存資料時，這個段就要獲得另一個區間來支援將資料插入到物件中。因此這個段 所使用的空間由它的引數決定的，這些引數可以在建立時指定，也可以在以後更改。如在Create table，Create index，Create cluster，Create rollback segment命令中沒有指定儲存引數，則資料庫會預設它儲存所在的表空間的引數，這些引數有initial，next，pctincrease， maxextents，minextents等。在建立後不能修改initial和minextents值，每個表空間的儲存引數預設值可以在 Dba_tablespaces檢視中查詢出來。
磁碟I/O是系統效能的瓶頸，解決好磁碟I/O，可明顯提高效能。通過查詢V$filestat可以知道每個物理檔案的使用頻率（phyrds表示每個資料檔案讀的次數，phywrts表示每個資料檔案寫的次數）
　　SELECT name,phyrds,phywrts FROM V$datafile df,V$filestat fs
　　WHERE df.file# =fs.file#;
對於使用頻率較高的物理檔案,可以採用以下策略: 將I/O儘可能平均分配在儘可能多的磁碟上；為表和索引建立不同的表空間；將資料檔案與重做日誌檔案分離在不同的磁碟上；減少不經Oracle server的磁碟I/O。
如果大家沒有經驗的情況下不小心把資料檔案規劃的不恰當, 以致於產生了大量的I/O竟爭現象, 那麼就要根據上面的原則重新調整資料檔案的分佈, 以平衡資料檔案之間的I/O竟爭, 具體如何移動資料檔案, 各種資料庫的方法不盡相同, 但是基本的原理還是相同的,下面是針對Oracle8i如何移動資料檔案的兩種方法的舉例(9i略有不同)：
第一種方法﹕(Alter database)
關閉資料庫-移動資料庫檔案-裝載並改名-啟動

1> svrmgrl
2> connect internal
3> shutdown
4> exit
5> mv /u/product/oradata/foxmold/user01.dbf /db3/oradata
6> svrmgrl
7> connect internal
8> startup mount foxmold
9> alter database rename file
‘/u/product/oradata/foxmold/user01.dbf’ to
‘/db3/oradata/user01.dbf’
10> alter database open
第二種方法﹕(Alter tablespace)
關閉資料庫-移動資料庫檔案-裝載並改名-啟動
1> svrmgrl
2> connect internal
3> shutdown
4> exit
5> mv /u/product/oradata/foxmold/user01.dbf /db3/oradata
6> svrmgrl
7> connect internal
10> alter database rename file
‘/u/product/oradata/foxmold/user01.dbf’ to
‘/db3/oradata/user01.dbf’
8> alter database open
上面的foxmold代表當前的database name。

七．設定和管理回滾段
回滾段一般可以處理任意大小的事物，所以也就需要大小不同的回滾段。回滾段的大小是通過建立回滾段時指定儲存子句來設定，但一般會遵從下面原則：
OLTP事物特徵有許多併發的事物，每個可能只修改少量的資料，可以建立10KB到20KB大小的回滾段，每個有2到4的範圍；對於時間很長的查詢為了維 護讀一致性有大量的回滾資訊，所以需要較大的回滾段，建立回滾段的大小最好為最大表的10%左右（大多數查詢隻影響到表約10%的資料量）。設定回滾段的 大小可以通過Create rollback segment和Alter rollback segment語句來實現。一般情況下，initial=next ，設定Optimal引數來節約空間的使用 ，不要設定maxextents為unlimited，回滾段應建立在一個特定的回滾段表空間內 。
回滾段的目標容量可以用儲存引數Optimal來定義，它指定回滾段要縮小到的尺寸。如果發現回滾段由於Optimal的緣故持續地收縮，那麼很可能是回滾段設定不恰當，這種可以通過動態性檢視V$rollstat來確定是否有問題，如：
SELECT substr(name,1,40) name,extents,resize,aveactive,aveshrink,
extends,shrink FROM V$rollname rn,V$rollstat rs WHERE rn.usn=rs.usn;
結果如下：
name extents resize aveactive aveshrint extents shrinks
----- ------- ------ ------- -------- -------- --------
system 4 202876 0 0 0 0
csirsl 2 202876 55192 0 0 0
如果平均大小接近於Optimal，則Optimal正確，如果extents和shrinks高，則必須增加Optimal值。在設計Optimal值時如果有長時間查詢和執行的事物，則應該把Optimal設定大些。
使用回滾段可以改善系統效能，減少競爭，回滾段的多少應該由資料庫中的併發事物決定，太多的事物使用一個回滾段時會發生競爭，檢視動態效能表V$WAITSTAT可以檢視回滾段上是否有競爭：
SELECT class,count FROM V$waitstat WHERE class in(‘undo header’,’undo block’,’system undo header’,’system undo block’);
結果如下：
CLASS COUNT
------------------------------- ---------------
system undo header 0
system undo block 0
undo header 0
undo block 0
然後將這些值和資料請求的總數相比較。資料請求的總數等於V$sysstat中的db buffer gets和consistent gets之和，查詢語句為：
SELECT sum(value) ‘Data Requests’ FROM V$systat where name in(‘db block gets’,’consistent gets’);
結果如下：
Data Requests
---------------------------
5105
如果任何一個class/sum(value)>10%,那麼考慮增加回退段。回退段的數量一般按如下規律設定：
　　使用者數　　　　　　　　　 回退段個數
n<16　　　　　 　　　　　　4
1632<=n　　　　　　　　　　　n/4 但不超過50
　　和回滾段有關的引數還有: transactions指出併發事物的數量;transcative_per_rollback_segment指出每個回滾段的事物數量。
使用set transaction語句將事物分配到適當大小的回滾段命令如下：
set transcation use rollback segment RBS13；
把當前的事物分配到回滾段RBS13。

八．設定和管理聯機重做日誌
重做日誌的大小也能影響效能，因為資料庫的寫入和歸檔取決於重做日誌的大小，通常情況下，更大的重做日誌檔案可以提供好一些的效能，小的能增加檢查點的活動和降低頻率。
不可能為一個重做日誌檔案提供特定大小的建議，重做日誌檔案在幾百兆位元組到幾GB位元組都被認為是合理的，根據系統產生的聯機重做數量決定日誌檔案的大小，一般情況下應保持在約20分鐘交換日誌檔案一次。
如果發生重做日誌緩衝區競爭，對資料庫的效能影響也將是很大的。為了減少重做日誌緩衝區競爭，我們可以通過查詢V$sysstat表判定redo log 檔案緩衝區是否足夠。
　　SELECT name,value FROM V$sysstat WHERE log space request‘;
　　此處value的值應接近於0，否則，應增大初始化引數檔案的Log_buffers的值
重做日誌檔案被建立後大小不能被改變，但是可以增加新的、更大的檔案，並且原來的檔案能被刪除。具體的實現方法如下：
1．假設現有三個日誌組，每個組內有一個成員，每個成員的大小為1MB，現在想把此三個日誌組的成員大小都改為20MB。
2．建立新的日誌群組
alter database add logfile group4(‘d:\oradb\redo04.log’) size 2048k;
alter database add logfile group5(‘d:\oradb\redo05.log’) size 2048k;
3. 切換當前日誌到新的日誌組
alter system switch logfile；
alter system switch logfile；
4．刪除舊的日誌
alter database drop logfile group 1；
alter database drop logfile group 2；
alter database drop logfile group 3；
5．到作業系統下刪除原舊的日誌檔案組1，2，3中的檔案。
6．重建日誌組1，2，3
alter database add logfile group 1 ('d:\oradb\redo01_1.log') size 20M;
alter database add logfile group 2 ('d:\oradb\redo02_1.log') size 20M;
alter database add logfile group 3 ('d:\oradb\redo03_1.log') size 20M;
7．切換日誌組
alter system switch logfile；
alter system switch logfile；
alter system switch logfile；
8．刪除中間過渡的日誌組4，5
alter database drop logfile group 4；
alter database drop logfile group 5；
9．到作業系統下刪除過渡日誌檔案組4，5中的檔案。
10．備份當前最新的控制檔案
SQL>connect internal
SQL>alter database backup controlfile to trace resetlogs;
聯機重做日誌檔案也是可以移動的，具體的方法是：首先關閉資料庫，移動聯機重做日誌檔案，然後安裝資料庫，使用alter database命令通知資料庫聯機重做日誌檔案的新位置。然後就可以用新位置上的日誌檔案開啟例項。

九．設定和管理歸檔重做日誌
當Oracle以archivelog模式執行時,資料庫在每個聯機重做日誌檔案寫滿後,對它進行拷貝,通常是寫入磁碟,也可以寫入別的裝置,但這需要人 為的干預的。arch後臺執行歸檔功能，如果有大量頻繁的事物的時候，會產生重做日誌檔案磁碟方面的競爭，避免這種競爭的方式是將聯機重做日誌檔案分佈到 多個磁碟上。為了提高歸檔的效能，可以建立具有多個成員的聯機重做日誌檔案組，但是必須考慮到每個裝置的I/O。
歸檔重做日誌檔案不應與system、rbs、data、temp、indexes表空間等儲存在同一個裝置中，更不能與任何的聯機重做日誌檔案儲存在同一個裝置中，以免發生磁碟的競爭。
歸檔重做日誌檔案備份之後是可以刪除或移走的,否則會佔據比較大的空間影響硬碟使用和降低系統的效能。

十．設定和管理控制檔案
控制檔案的位置在例項初始化引數檔案中指定的，若要移動控制檔案，必須先關閉資料庫例項，移動控制檔案，編輯初始化引數檔案，然後重新啟動該例項。
下面就以Oracle8i為例說明如何移動控制檔案:
OS為Linux,Database為Oracle8i。
1. 查詢當前資料庫的控制檔案的位置
SELECT * FROM v$controlfile;
2. 把控制檔案/u/oradata/foxmold/control01.ctl
移到/db4/oradata/目錄下。
3. svrmgrl
4. connect internal
5. shutdown
6. exit
7. cp /u/oradata/foxmold/control01.ctl /db4/oradata/control01.ctl
8. chmod 660 /db4/oradata/control01.ctl
9. initsid.ora control_files=…
10. startup mount foxmold
上面的foxmold代表當前的database name。

十一．總結
以上是針對Oracle資料庫來對資料庫物理設計作以簡單陳述, 針對各種不同的資料庫可能會略有不同, 但是整體思想還是一致的。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15688952/viewspace-661721/，如需轉載，請註明出處，否則將追究法律責任。

相關文章