Oracle記憶體參數調優技術詳解

前言

近來公司技術,研發都在問我關於記憶體參數如何設定可以最佳化oracle的效能,所以抽時間整理了這篇文檔,以做參考.

目的

希望通過整理此文檔,使大家對oracle記憶體結構有一個全面的瞭解,並在實際的工作中靈活應用,使oracle的記憶體效能達到最優配置，提升應用程式反應速度，並進行合理的記憶體使用量.

內容

執行個體結構

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

oracle執行個體啟動的過程，其實就是oracle記憶體參數設定的值載入到記憶體中，並啟動相應的後台進程進行相關的服務過程。

進程結構

oracle進程=伺服器處理序+使用者進程

幾個重要的後台進程：

DBWR：資料寫入進程.

LGWR:日誌寫入進程.

ARCH:歸檔進程.

CKPT:檢查點進程(日誌切換；上一個檢查點之後，又超過了指定的時間；預定義的日誌塊寫入磁碟；常式關閉，DBA強制產生,資料表空間offline)

LCKn（0-9）：封鎖進程.

Dnnn:調度進程.

記憶體結構(我們重點講解的)

記憶體結構=SGA（系統全域區）+PGA（程式全域區）

SGA：是用於儲存資料庫資訊的記憶體區，該資訊為資料庫進程所共用。它包含Oracle 伺服器的資料和控制資訊,它是在Oracle伺服器所駐留的電腦的實際記憶體中得以分配，如果實際記憶體不夠再往虛擬記憶體中寫

 

我們重點就是設定SGA，理論上SGA可占OS系統實體記憶體的1/2——1/3

原則：SGA+PGA+OS使用記憶體<總物理RAM

SGA=((db_block_buffers*blocksize)+(shared_pool_size+large_pool_size+java_pool_size+log_buffers)+1MB

1、SGA系統全域區.(包括以下五個區)

A、資料緩衝區:（db_block_buffers）儲存由磁碟資料檔案讀入的資料。

大小: db_block_buffers*db_block_size

         Oracle9i設定資料緩衝區為：Db_cache_size

         原則：SGA中主要設定對象，一般為可用記憶體40%。

B、共用池:（shared_pool_size）:資料字典，sql緩衝，pl/sql文法分析.加大可提速度。

原則：SGA中主要設定對象，一般為可用記憶體10%

C、日誌緩衝區:（log_buffer）儲存資料庫的修改資訊.

         原則：128K ---- 1M 之間，不應該太大

D 、JAVA池（Java_pool_size）主要用於JAVA語言的開發.

         原則：若不使用java，原則上不能小於20M，給30M通常就夠了
E、 大池（Large_pool_size）  如果不設定MTS，主要用於Database Backup復原管理員RMAN。

         原則：若不使用MTS,5---- 10M 之間，不應該太大

SGA=. db_block_buffers*db_block_size+ shared_pool_size+ log_buffer+Java_pool+size+large_pool_size

       原則： 達到可用記憶體的55-58%就可以了.

2、PGA程式全域區

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

A、Sort_area_size 用於排序所佔記憶體

B、Hash_area_size 用於散列聯結，位元影像索引

這兩個參數在非MTS下都是屬於PGA ，不屬於SGA,是為每個session單獨分配的，在我們的伺服器上除了OS + SGA,一定要考慮這兩部分
原則：OS 使用記憶體+SGA+並發執行進程數*(sort_area_size+hash_ara_size+2M) < 0.7*總記憶體

執行個體配置

一：實體記憶體多大

二：作業系統估計需要使用多少記憶體

三：資料庫是使用檔案系統還是裸裝置

四：有多少並發串連

五：應用是OLTP 類型還是OLAP 類型

基本掌握的原則是， db_block_buffer 通常可以儘可能的大，shared_pool_size 要適度，log_buffer 通常大到幾百K到1M就差不多了

     A、如果512M RAM 單個CPU   db_block_size 是8192 bytes
 SGA=0.55*512M=280M左右

建議 shared_pool_size = 50M, db_block_buffer* db_block_size = 200M
具體: shared_pool_size =52428800 #50M

db_block_buffer=25600      #200M

log_buffer = 131072         # 128k (128K*CPU個數)

             large_pool_size=7864320      #7.5M

             java_pool_size = 20971520    # 20 M

             sort_area_size = 524288      # 512k (65k--2M)

             sort_area_retained_size = 524288   # MTS 時 sort_area_retained_size = sort_area_size

B、如果1G RAM     單個CPU   db_block_size 是8192 bytes
SGA=0.55*1024M=563M左右
建議 shared_pool_size = 100M , db_block_buffer* db_block_size = 400M
具體: shared_pool_size=104857600    #100M

          db_block_buffer=51200         #400M

 log_buffer = 131072         # 128k (128K*CPU個數)

               large_pool_size=15728640       #15M

          java_pool_size = 20971520    # 20 M

sort_area_size = 524288      # 512k (65k--2M)

              sort_area_retained_size = 524288   # MTS 時 sort_area_retained_size = sort_area_size

 

C、如果2G     單個CPU   db_block_size 是8192 bytes

SGA=0.55*2048M=1126.4M左右
建議 shared_pool_size = 200M , db_block_buffer *db_block_size = 800M

       具體: shared_pool_size=209715200    #200M

          db_block_buffer=103192         #800M

 log_buffer = 131072            # 128k (128K*CPU個數)

               large_pool_size= 31457280      #30M

          java_pool_size = 20971520      # 20 M

sort_area_size = 524288         # 512k (65k--2M)

              sort_area_retained_size = 524288   # MTS 時 sort_area_retained_size = sort_area_size

 

 

假定64 bit ORACLE

記憶體4G

shared_pool_size = 200M , data buffer = 2.5G

 

記憶體8G

shared_pool_size = 300M , data buffer = 5G

 

記憶體 12G

shared_pool_size = 300M-----800M , data buffer = 8G

參數更改方式

oracle8i：

主要都是通過修改oracle啟動參數檔案進行相關的配置

參數檔案位置：

d:\oracle\admin\DB_Name\pfile\init.ora

按以上修改以上參數值即可。

Oracle9i:

兩種方式：第一種是修改oracle啟動參數檔案後，通過此參數檔案再建立伺服器參數檔案

          第二種是直接運行oracle修改命令進行修改。

SQL>alter system set db_cache_size=200M scope=spfile;

SQL>alter system set shared_pool_size=50M scope=spfile;