Sybase數據庫的碎片整理

　　作者: 佘濤
　　
　　對於像Sybase這樣的大型DBMS系統而言作為OLTP(聯機事務處理)應用的基石它需要能每天小時每年天不間斷運行由於其應用程序每天對數據庫進行大量的插入更新刪除等操作在數據庫的物理存儲介質上產生了大量存儲碎片從而影響了存儲的效率以及數據庫應用運行的速度是否可以像Windows操作系統的碎片整理程序一樣整理這些碎片從而優化數據庫存儲提高數據庫的運行速度呢？答案是肯定的本文將介紹Sybase數據庫的碎片類型以及碎片整理方法
　　碎片類型
　　由於Sybase是通過OAM頁分配單元和擴展頁來管理數據的所以對OLTP應用的Database Server會十分頻繁地進行數據刪除插入和更新等操作時間一長就會出現以下幾種情況
　　 頁碎片
　　即本來可以存放在一個頁上的數據卻分散地存儲在多個頁上如果這些頁存儲在不同的擴展單元上Database Server就要訪問多個擴展單元因此降低了系統性能
　　 擴展單元碎片
　　在堆表中當刪除數據鏈中間的記錄行時會出現空頁隨著空頁的累積擴展單元的利用率也會下降從而出現擴展單元碎片帶cluster index的table也有可能出現擴展單元碎片
　　當有擴展單元碎片存在會出現以下問題
　　● 對表進行處理時常常出現死鎖
　　● 利用較大的I/O操作或增加I/O緩沖區的大小也無法改變較慢的I/O速度
　　● 行操作的爭用
　　 擴展單元遍歷
　　帶有cluster index的table會由於插入記錄而導致頁分裂但當刪除記錄後頁會獲得釋放從而形成跨幾個擴展單元和分配單元的數據而要訪問該數據就必須遍歷幾個擴展單元和分配單元這將導致訪問/查詢記錄的時間大大延長開始時數據庫的性能雖然較高但使用一段時間後性能就會下降等問題
　　實際上數據在存儲空間上排列得越緊密有序Database Server訪問的速度就越快消除碎片有助於提高系統的性能和更有效地利用數據存儲空間
　　碎片優化方法
　　處理碎片有多種方法如重新定義table的填充因子根據table的定義刪除並重新創建索引重建表等
　　本文給出的方法是通過BCP實用程序將用戶數據庫的數據以文本形式導出然後將用戶數據庫徹底清空截斷再將文本數據導入到數據庫從而達到消除碎片的目的具有通用性
　　下面以Sun Solaris 操作系統下的Sybase Adaptive Server Enterprise 為例說明整理數據庫數據的具體方法
　　 備份數據庫
　　為防止在數據庫碎片整理過程中出現不可預見的問題有必要先備份數據庫
　　 創建bcp out腳本並導出數據
　　● 創建包含下列SQL語句的文件
　　cre_bcp_outsql
　　select bcp + name + out / + name + _outtxt Udboname Pdbopwd Ssys_name c
　　from sysobjects where type = U
　　order by name
　　go
　　● isql Udboname Pdbopwd Ssystemname < cre_bcp_out. sql > b_out
　　● 編輯輸出文件去掉文件第一行和最後兩行無關的字符vi b_out
　　● 執行腳本將數據庫的數據導出到文本文件sh b_out
　　 創建truncate table腳本並截斷數據庫
　　● 創建包含下列SQL語句的文件
　　cre_trunc_outsql
　　select truncate table + name from sysobjects where type = U
　　order by name
　　go
　　● isql Udboname Pdbopwd Ssystemname < cre_ trunc_out. sql > trunc_out sql
　　● 編輯輸出文件去掉文件第一行和最後兩行無關的字符並在最後一行加入 go構成完整的SQL語句vi trunc_out
　　● 執行以下語句清空數據庫的數據
　　isql Udboname Pdbopwd < trunc_out. sql
　　4. 創建bcp in腳本並導入數據
　　● 創建包含下列SQL語句的文件：
　　cre_bcp_in. sql
　　select “bcp” + name + “in ./” + name + “_out.txt -Udboname -Pdbopwd -Ssys_name -c”from sysobjects where type = ‘U’
　　order by name
　　go
　　● isql -Udboname -Pdbopwd -Ssystemname < cre_ bcp_in. sql > b_in
　　● 編輯輸出文件，去掉文件第一行和最後兩行無關的字符：vi b_in
　　● 從文本中導入數據：sh b_in
　　5. 更新數據庫狀態
　　Sybase不自動維護索引的統計信息，當用truncate table截斷數據庫時，索引並沒有改變，所以必須用update statistics來確保索引的統計信息對應當前表數據的統計。Tw.wiNgwit.CoM
　　● 創建包含下列SQL語句的文件：
　　cre_upd_st. sql
　　select “update statistics” + name from sysobjects where type = “U” order by name
　　go
　　● isql -Udboname -Pdbopasswd -Ssystemname < cre_upd_st. sql > upd_st. sql
　　● 編輯輸出文件，去掉文件第一行和最後兩行無關的字符，在最後一行加入 go構成完整的SQL語句：
　　vi upd_st. sql
　　● 更新數據庫狀態：
　　isql -Udboname -Pdbopasswd -Ssystemname < upd_st. sql
　　至此，基本上完成了數據庫用戶表的碎片整理工作。
　　小 結
　　在整理過程中，有以下兩點需要注意:
　　1. Tempdb的大小
　　當Sybase執行bcp in腳本時，會占用導入數據2倍的tempdb空間，因此在執行前要仔細估計最大的table的大小，保證有足夠的tempdb空間。當空間不夠時，要考慮用分割table或刪除陳舊數據的方法縮小table的大小，或者考慮增加tempdb的大小。
　　2. 數據庫配置選項的設置
　　當數據庫執行bcp in腳本時會產生大量的log，為保證bcp in進程不致因為log溢出而中斷，應該設置database的選項“truncate log on chkpt”為“true”。
　　雖然Sybase數據庫是自優化的，但只要數據庫是動態的，數據庫碎片現象就會存在。在OLTP應用的場合，隨著數據的不斷增大，系統變得越來越緩慢，並且經常出現死鎖時，應該檢查數據庫的碎片，並且采用以上方法進行優化。
　　實際上，應該定期做數據庫的碎片整理，保證數據庫的物理存儲經常處於最優狀態，相對於增加硬件而言，這是一種更好的保持數據庫性能的低成本的途徑。
　　
　　
　　

From:http://tw.wingwit.com/Article/program/Oracle/201311/17019.html