Oracle 記憶體參數調優設定

標籤：tabs   其他應用   abs   sse   包含   增加   資料   進程結構   瞭解   

 

　　Oracle 資料庫系統中起到調節作用的參數叫初始化參數，資料庫管理員根據實際情況需要適當調整這些 初始化參數以最佳化Oracle系統。

 

　　1 主要系統參數調優介紹

　　

 

　　2 系統記憶體參數的分配

 

　　2.1 Oracle 執行個體= 記憶體結構 + 進程結構

　　記憶體結構 = SGA + PGA

　　SGA(系統全域區)： 使用者儲存資料庫資訊的記憶體區，該地區為資料庫進程所共用。它包含伺服器的資料和控制資訊，主要包含高速資料緩衝區、共用池、重做日誌緩衝區、Java池，大型池等記憶體結構。

　　SGA的設定，理論上SGA的大小應該占OS的記憶體的 1/3-1/2左右。

　　SGA + PGA + OS使用的記憶體 < 總的實體記憶體

 

　　查看當前系統SGA的資訊

select name,bytes/1024/1024 as "Size(M)"
from v$sgainfo;

　　根據查詢資訊顯示當前還有148M可用的SGA記憶體，系統當前的記憶體配置並不是最優的，我們在實際使用過程中根據情況可以重新分配記憶體。

 

　　2.2 SGA的設定原則

　　SGA = (db_block_buffers * db_block_size) + (shared_pool_size + large_pool_size + java_pool_size + log_buffers)

 

　　(1)緩衝區快取(database_buffer_cache)：主要儲存由磁碟資料檔案寫入的資料

　　大小：db_block_buffers * db_block_size

分配原則：緩衝區快取的調整，使用者進程所存取的所有資料都是經過緩衝區快取來存取，所以該部分的命中率，對效能至關重要。緩衝區快取的使用方式記錄在動態效能表v$sysstat中，可通過查詢該表來瞭解其活動情況，以決定如何調整。

　　select name,value from v$sysstat where name in (‘dbblock gets‘,‘consistent gets‘,‘physical reads‘); 
     dbblock gets和consistent gets的值是請求資料緩衝區中讀的總次數。physical reads的值是請求資料時引起從盤中讀檔案的次數。從緩衝區快取中讀的可能性的高低稱為緩衝區的命中率，計算公式：

　　Hit Ratio=1-(physical reds/(dbblock gets+consistent gets)) ，

select 1-(phys.value/(cur.value + con.value)) "HIT RATIO"
from 　　 v$sysstat cur, v$sysstat con, v$sysstat phys
where　　cur.name = ‘db block gets‘ 
　　　　   and con.name = ‘consistent gets‘ 
　　　　　and phys.name = ‘physical reads‘;

如果Hit Ratio<60%～70%，則應增大db_block_buffers的參數值。db_block_buffers可以調整分配給緩衝區快取的記憶體量，即db_block_buffers可設定分配緩衝區快取的資料區塊的個數。緩衝區快取的總位元組數=db_block_buffers的值*db_block_size的值。db_block_size 的值表示資料區塊大小的位元組數，可查詢 v$parameter 表：

select name,value
from v$parameter
where name=‘db_block_size‘;

在修改了上述資料庫的初始化參數以後，必須先關閉資料庫，在重新啟動資料庫後才能使新的設定起作用。

 

　　(2)共用池(shared_pool_size)

分配原則：在shared pool中，主要包含兩個cache，即Library Cache和Data Dictionary Cache，但是不能對它們進行單獨調整，只能通過調整shared_pool_size來進行。

Library Cache用於存放共用SQL語句和PL/SQL語句，採用LRU（Least Recently Used）演算法進行管理，Oracle可以用已經cache在其中的SQL語句，而不需要re-parsing，我們可以通過下面的SQL語句來查詢Library Cache的命中率（Hit Ratio）：

select GETHITRATIO
from v$librarycache
where namespace ＝‘SQL AREA‘;

　　如果得到的結果小於90%，那麼說明命中率不高，需要增大Library Cache了。

 

Data Dictionary Cache的調整，資料字典緩衝區包含了有關資料庫的結構、使用者、實體資訊。資料字典的命中率，對系統效能影響極大。資料字典緩衝區的使用方式記錄在動態效能表v$librarycache中，可通過查詢該表來瞭解其活動情況，以決定如何調整。
select sum(gets),sum(getmisses)

from v$rowcache; 

gets列是對相應項請求次數的統計，getmisses 列是引起緩衝區出錯的資料的請求次數。對於頻繁訪問的資料字典緩衝區，sum(getmisses)/sum(gets)<10%。若大於此百分數，則應考慮增加資料字典緩衝區的容量，即需調整初始化參數shared_pool_size來重新調整分配給共用池的記憶體量。

　　

　　(3) 大池(large_pool_size ): 主要用於Database Backup管理器RAM

　　原則： 根據實際情況

 

　　(4) Java池(java_pool_size): 主要用於Java語言開發

　　原則： 根據實際情況

 

　　(5) 日誌緩衝區(log_buffers): 儲存資料修改資訊

　　原則：根據實際情況

 

　　2.3 PGA 程式全域區

　　PGA包含單個伺服器處理序或者單個後台進程的資料和控制資訊， 與幾個進程共用的SGA正好相反，是一個只被一個進程使用的地區，PGA在建立進程時分配終止進程時回收。

　　(1) sort_area_size 使用者排序所佔的記憶體

　　(2) hash_area_size 使用者散列聯結， 位元影像索引

 

　　這兩個參數在非MTS模式下都是屬於PGA，是為每個session單獨分配的，在我們的伺服器上除了OS + SGA一定要考慮這兩部分大小的設定

　　OS + SGA + 並發執行的進程數*(sort_area_size + hash_area_size) < 0.7 * OS的總實體記憶體

 

　　3 執行個體配置

　　(1) 實體記憶體大小

　　(2) 作業系統及其他應用程式需要的記憶體

　　(3) 資料庫系統使用的是檔案裝置還是裸裝置

　　(4) 有多少並發串連

　　(5) 應用是OLTP還是OLAP類型

 

　　基本分配原則，db_block_buffers儘可能的大， shared_pool_size適度， log_buffer通常幾百KB到1M就可以了。　

　　1G記憶體，1個CPU， db_block_size 為8192B

　　SGA = 1024 * 0.5 =512   -- 最大一般為OS 記憶體的一半左右，不超過60%

　　(1) databse buffer cache：512 * 40% = 205M     db_block_buffer應設為：52352(409*1024*1024/8192)  -- 一般為sga_max_size的40%

　　(2) shared_pool_size: 563* 40% = 205M 　-- 一般為sga_max_size的40%

　　(3) log_buffer: 128K (128K*CPU個數)

　　(4) java_pool_size: 4M

　　(5) large_pool_size: 4M

　　(6) sort_area_size: 根據實際情況 65K - 2M

　　(7) sort_area_retained_size: 根據實際情況

Oracle 記憶體參數調優設定