三個提高Oracle處理大量資料效率的有效途徑__Oracle

Oracle效能話題涉及面非常廣，市場上有很多書籍專門介紹Oracle調優。對效能的追求是無止境的，需要長期不懈的努力；但要避免效能成為問題卻不難，甚至可以說很簡單。本文從簡單實用的角度出發，給出幾個提高Oracle處理大量資料效率的有效途徑。

  一、設定資料庫初始化參數

 

 

資料庫的全部初始化參數可以在OEM中看到。參見下圖：

 

 

 

在建立一資料庫時，如果不配置這些初始化參數，Oracle會給這些參數以預設值。當資料庫規模不大時，採用Oracle的預設值通常不會遇到效能問題。下面介紹對處理大量資料效率有“舉足輕重”影響，並且，預設值會帶來效能問題的幾個參數：

 

1) db_block_size

 

 

該參數設定了Oracle進行一次I/O的基本單位——資料庫塊的大小 (以位元組計)。毫不誇張的說，該參數對於大資料量處理是最重要的一個參數。該參數值設定的越大，對大資料量處理越有利。受作業系統所限，NT4最大隻能設定為8K，Win2k最大隻能設定為16K。Oracle本身允許的最大值是64K。忘了說了，db_block_size應設定為2的冪。

 

對於AnalyticDB，設定為32K是不錯的選擇。對於OLTP系統，筆者沒有多少經驗，網上的說法是設定8K是個比較好的平衡點。Oracle是有許多“神話”的，8K的說法未必符合現在的軟硬體情況，更未必符合我們企業的實際情況。如果有時間有機會，比較一下8K好還是16K好總是不會錯的。

 

db_block_size是最基本的一個參數，也是最容易被忽視的一個參數。該參數只能在建立資料庫時設定，此後不能更改；一旦有所失誤，只能通過重建資料庫的方法補救。因此，您建庫時應當謹慎考慮該參數。

 

2）

db_file_multiblock_read_count

 

Oracle官方的說明：在涉及一個完全連續掃描的一次 I/O 操作過程中讀取的塊的最大數量。對於大的查詢來說，進行全表掃描往往比使用索引效率高很多。全表掃描操作是典型的“完全連續掃描”。如果db_block_size設定為32K，db_file_multiblock_read_count設定為8；則一次I/O操作最多可以連續讀8個資料庫塊，即256K。

 

 

db_file_multiblock_read_count並非越大越好。對於資料分析系統，db_file_multiblock_read_count和db_block_size的乘積為256K足夠了。對於建立在Unix上的OLTP系統，根據網上的說法，二者的乘積為64K是不錯的選擇。

 

根據筆者的經驗，讓資料連續分布在物理磁碟上比考量該參數更加有效。

 

3) sort_area_size

 

 

sort_area_size的重要性可以說是和db_block_size並列的。該參數指出資料庫執行一個查詢時最多可以使用多大記憶體來排序。受系統資源所限，我們無法將該參數設定太大。特別是當我們採用獨立模式建庫時，每個Session都可能會申請一個或多個排序空間。如果我們設定sort_area_size為8M，同時登上來100個使用者並發查詢，則可能會佔去800M記憶體甚至更多。當主存不夠用時，就要用虛擬記憶體了。如果Oracle被迫使用虛擬記憶體，則資料庫的效能將急劇下降。

 

對於該參數的設定，網上有人建議至少應超過用於排序記錄數的平方根。也就是說，對100萬條記錄進行排序，每條記錄佔用1K空間，則sort_area_size至少應設定為1M。對1000萬條記錄進行排序，每條記錄佔用1K空間，sort_area_size設定為4M應該夠用了。

 

根據上述數字，OLTP系統的sort_area_size不妨設定為1M或2M；資料分析系統的sort_area_size不妨設定為4M或8M。

 

db_file_multiblock_read_count和sort_area_size在資料庫建立好以後是可以修改的。修改方法很簡單。搜尋Oracle的安裝目錄，找到PFILE檔案夾（可能會找到多個，其父目錄的名字會給我們提示），裡面有一個init文字檔，照著裡面的內容修改就可以了（找不到相關參數就自己加一個）。修改完畢後重啟資料庫方能生效。Oracle9i以上版本可以做到不用重啟資料庫，本文就不介紹了。

  二、編寫高效的SQL

 

 

 

一般說來，看起來簡單的SQL通常都不會遇到效能問題。SQL的執行效率通常比程式的執行效率要高。因此，盡量用SQL解決問題和盡量用簡單的SQL解決問題應當是我們開發的指導原則。

 

編寫高效的SQL需要一定的基本功。本文不討論SQL的理論基礎。本文僅介紹一個有用的技術：人為幹預SQL的執行計畫。當SQL較複雜時，執行計畫的可能性會非常多。用最短的時間選擇一個最優的執行計畫是Oracle奮鬥的目標。Oracle資料庫有相關的參數來調整挑選執行計畫的演算法，這些參數本文不討論，有興趣的讀者可以自己上網去搜。

 

所謂“人為幹預SQL的執行計畫”實際上是提示Oracle如何去挑選最優的執行計畫。SQL提示的文法很簡單：用“/*+”和“*/”將提示包括起來，中間寫上關鍵字就可以了。SQL:提示的關鍵字有很多，下面介紹幾個典型的關鍵字，更多的用法可以自己上網搜。

 

1）指定全表掃描：

 

SELECT /*+FULL(table_name)*/ field1,field2

 

 

FROM table_name;

 

 

一個大查詢如果用到了一個大表中相當一部分的資料，則採用全表掃描的執行計畫會比採用索引的執行計畫效率高很多。

 

2）資料直接插入到表的最後，可以提高速度：

 

INSERT /*+append*/

 

 

INTO table_name

 

 

select * from table_name1;

 

 

Oracle中很多資料區塊因為曾經做過delete操作而有空閑空間，如果使用append關鍵字，則Oracle不會去尋找這些有空閑空間的資料區塊，從而提高了insert語句的執行速度。需注意append關鍵字只適合於大資料量插入。

  三、分區

 

 

分區技術相對前面介紹的技術而言要複雜一些。分區實際上是據根據某（些）個欄位在物理上將一個大表的資料分開儲存，從而，能提高我們查詢的效率，同時也能加強我們對資料的管理。典型例子的是根據日期欄位分區，從而，當我們查詢某個時期的資料時，只需要掃描某個分區的資料而不需要掃描整表的資料。

 

<