名家集錦之–oracle 系統參數最佳化

這篇文章摘自玄機逸士為某上市公司ERP系統調優項目總結報告，希望對大家有所協助。

在32位的作業系統上，Oracle系統理論上可以使用的記憶體上限是4G，即2^32位元組，任何多出的部分，Oracle均不會使用；在64位的作業系統上，Oracle系統可以使用的記憶體理論上是2^64位元組，在可以預見的未來的應用，這幾乎和沒有上限限制是一樣的。因此在購買小型機伺服器硬體裝置時（一般都會內建作業系統）要考慮到使用的是多少位的作業系統，否則即使記憶體配得再多，也不會給效能帶來提高。在使用32位的作業系統時，如果該伺服器裝置專門用作資料庫伺服器（即不在其上安裝其它諸如應用伺服器等軟體）時，配置6G的記憶體應該是比較恰當的，除4G被Oracle使用外，2G記憶體用來運行作業系統本身的進程。

 注意1：如果是UNIX或Linux32位作業系統，還要設定作業系統級的參數shmmax。SGA的空間可以大於shmmax，具體情況請參考Oracle安裝手冊以及作業系統手冊;

 注意2: 本小節（下面）中所列的操作可能需要一定的許可權，在實際工作中，如果發現相關許可權的問題，請及時給相關使用者授權或以一個許可權更大的使用者的身份進行操作。

       Oracle總體記憶體結構如所示：

 從中，Oracle的記憶體結構主要有SGA和PGA組成，其中UGA的一部分為SGA所管理，而另一部分則為PGA所管理，CGA則不關UGA被SGA管理還是被PGA所管理，它總是處於UGA之內。

 

或許由於SGA太重要了，以致大家忽略了PGA、UGA和CGA的存在。比如很多人都搞不清楚PGA和UGA兩者之間的區別，實際上兩者之間的區別跟一個進程和一個會話之間的區別是類似的。儘管說進程和會話之間一般都是一對一的關係，但實際上比這個更複雜。一個很明顯的情況是MTS配置，會話往往會比進程多得多。在這種配置下，每一個進程會有一個PGA，每一個會話會有一個UGA。PGA所包含的資訊跟會話是無任何關聯的，而UGA包含的資訊是以特定的會話為基礎的。現在我們對PGA、UGA和CGA做以下介紹：

 

進程全域區(PGA)即可以理解為Process Global Area，也可以理解為Program Global Area。它的記憶體段是在進程私人區(Process Private Memory)而不是在共用區(Shared Memory)。它是個全域區意味著它包含了所有代碼有可能進入的全域變數和資料結構，但是它是不被所有進程共用的。每個Oracle的伺服器處理序都包含有屬於自己的PGA，它只包含了本進程的相關特定資訊。PGA中的結構不需要由latches來保護，因為其它的進程是不能進入到這裡面來訪問的。PGA包含的是有關進程正在使用的作業系統資源資訊以及進程的狀態資訊，而其它的進程所使用的Oracle的共用資源是在SGA中。PGA是私人的而不是共用的，這個機制是有必要的，因為當進程死掉後可以把這些資源清除和釋放掉。PGA包含兩個主要區域：Fixed PGA和Variable PGA或稱為PGA Heap。Fixed PGA的作用跟Fixed SGA是類似的，都包含原子變數(不可分的)，小的資料結構和指向Variable PGA的指標。Variable PGA是一個堆。它的Chunks可以從Fixed Table X$KSMPP查看得到，這個表的結構跟前面有提到的X$KSMSP是相同的。PGA HEAP包含了一些有關Fixed Table的非揮發性記憶體，它跟某些參數的設定有依賴關係。這些參數包含DB_FILES，LOG_FILES，CONTROL_FILES。

 

UGA(User Global Area)包含的是特定會話的資訊，有如下一些:

       － 所開啟遊標的持續和已耗用時間內的地區

       － 包的狀態資訊，特定的變數

       － Java工作階段狀態

       － 可以用的ROLES

       － 被ENABLE的跟蹤事件

       － 起作用的NLS參數設定

       － 開啟的DBLINK

       － 會話的入口控制

 跟PGA一樣，UGA也由兩區組成：Fixed UGA和Variable UGA，也稱為UGA HEAP。Fixed UGA包含了大約70個原子變數，小的資料結構和指向Variable UGA的指標。UGA HEAP中的Chunks可以從它們自己的會話中通過查看錶X$KSMUP獲得相關資訊，這個表的結構跟X$KSMSP是一樣的。UGA HEAP包含了一些有關fixed tables的非揮發性記憶體段，跟一些參數的設定有依賴關係。這些參數有OPEN_CURSORS，OPEN_LINKS和MAX_ENABLE_ROLES。

 

調用全域區(CGA)跟其它的全域區不同，Call Global Area是短暫性存在的。它只有在調用資料期間存在，一般是在對執行個體的最低層級的調用時才需要CGA，如下：

       － 分析一個SQL語句

       － 執行一個SQL語句

       － 取出一個SELECT語句的輸出

一個單獨的CGA在遞迴調用時是需要的。在SQL語句的分析過程中，對資料字典資訊的遞迴調用是需要的，因為要對SQL語句進行文法分析，還有在語句的最佳化期間要計算執行計畫。執行PL/SQL塊時在處理SQL語句的執行時也是需要遞迴調用的，在DML語句的執行時要處理觸發器執行也是需要遞迴調用的。不管UGA是放在PGA中還是在SGA中，CGA都是PGA的一個子堆(Subheap)。這個事實的一個重要推論是在一個調用的期間會話必須是一個進程。對於在一個MTS的Oracle資料庫進程應用開發時關於這一點的理解是很重要的。如果相應的調用較多，就得增加processes的數量以適應調用的增加。沒有CGA中的資料結構，CALLS是沒法工作的。而實際上跟一次CALL相關的資料結構一般都是放在UGA中，如SQL AREA，PL/SQL AREA和SORT AREA它們都必須在UGA中，因為它們要在各CALLS之間要一直存在並且可用。而CGA中所包含的資料結構是要在一次CALL結束後能夠釋放的。例如CGA包含了關於遞迴調用的資訊，直接I/O BUFFER等還有其它的一些臨時性的資料結構。Java Call Memory也是在CGA中。這一段記憶體比Oracle的其它記憶體段管理得更密集。它分成三個Space：Stack Space， New Space， Old Space。在New Space和Old Space中不再被參考使用的Chunks，根據它們在使用期間的長度及SIZE的不同，在調用的執行過程中將被當成不用的Chunks收集起來。New Space Chunks很多次的不用的Chunks的反覆收集過程中沒有被收集的Chunks將會被放到Old Space Chunks中。這是在Oracle記憶體管理中唯一的一個廢物收集(garbage collection)，其它的Oracle記憶體段都是釋放Dead Chunks。

 

正象前面提到的，Oracle資料庫系統和信能相關最主要的就是SGA（System Global Area）了，它由SHARED POOL、DATABASE BUFFER CACHE和Redo LOG BUFFER三部分組成， 其中又以SHARED POOL、DATABASE BUFFER CACHE對於日常碰到的系統效能調整最為重要。現在我們假定Oracle運行在32位的作業系統上，並且系統有足夠多的記憶體可以分配，那麼我們可以修改sga_max_size，使其等於4G的60%（因為PGA也需要使用記憶體，並且它並未包含在SGA中），即：

       sga_max_size=2458M

 

① 調整SHARED POOL。在SHARED POOL中，又包含兩個CACHE，即Library Cache和Data Dictionary Cache。

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

SQL> select GETHITRATIO

from v$librarycache

where NAMESAPCE ＝'SQL AREA';

如果得到的結果小於90%，那麼說明命中率不高，需要增大Library Cache了。如果想要將某個package放於Library Cache中，請使用以下命令：

SQL> execute dbms_shared_pool.keep('package_name');

要將某個package從Library Cache移出，請使用以下命令：

SQL> execute dbms_shared_pool.unkeep('package_name');

 

Data Dictionary Cache主要用來保持字典對象的相關資訊。可以查看視圖v$rowcache，該視圖由以下各列組成：

列名
 說明
 
PARAMETERS
 資料字典項的分類
 
GETS
 對於此類字典項的資訊擷取次數
 
GETMISSES
 對於此類字典項的資訊擷取失敗次數
 

如果GETMISSES/ GETS * 100% > 15%，那麼就要考慮增大Data Dictionary Cache了。

 

很重要地，Library Cache和Data Dictionary Cache不能單獨進行調整，要對它們進行調整，只能通過調整SHARED_POOL_SIZE來進行（Oracle會自動去分配Library Cache和Data Dictionary Cache的大小，如何分配這個過程不被人控制）。比如，如果想增加Library Cache，那麼請增加SHARED_POOL_SIZE。下面是分配SHARED POOL大小的經驗公式：

SHARED_POOL_SIZE = sga_max_size * 40%

       在前面我們已經得出sga_max_size＝2458M了，那麼按照上面這個公式，

       SHARED_POOL_SIZE = 2458 * 40% ＝ 983M

 

② 調整DATABASE BUFFER CACHE。BUFFER CACHE也是SGA的一部分，它主要儲存可以被所有使用者共用的資料區塊。用下面的SQL語句來檢查BUFFER CACHE的命中率：

    SQL> select 1 – (phys.value/(cur.value + con.value)) “CACHE 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’;

如果上面的命令的資料結果小於80%，那麼就需要考慮增大DATABASE BUFFER CACHE了。在Oracle9i以前的版本，需要在init<SID>.ora中增加DB_BLOCK_BUFFERS的數值（因為資料庫系統安裝完成後DB_BLOCK_SIZE就固定了），DATABASE BUFFER CACHE的容量＝DB_BLOCK_BUFFERS * DB_BLOCK_SIZE；在Oracle9i及其以後版本，則需要修改該DB_CACHE_SIZE。

 

       DATABASE BUFFER CACHE的大小計算的經驗公式：

       DATABASE BUFFER CACHE的大小 = sga_max_size * 40%

       在前面我們已經得出sga_max_size＝2458M了，那麼按照上面這個公式，

       DATABASE BUFFER CACHE的大小 = 2458 * 40% ＝ 983M

 

③ 調整PGA大小。調整PGA的大小是通過設定pga_aggregate_target這個參數來實現的。設定此參數時， 要將 SGA 從可用於 Oracle 常式的系統記憶體總量中減去。然後可將剩餘記憶體量分配給 pga_aggregate_target。如果繼續我們前面說到的情況，那麼，

       pga_aggregate_target ＝ 4G － 2458M ＝ 1638M

PGA影響到的最主要是sort_area_size 和 hash_area_size這兩個參數，它們分別根據排序操作的多少/大小，hash_join的多少/大小來決定的。在江鑽現有的系統中，我們發現排序操作和jion的操作都有很多(感覺上join操作比排序操作更多也更複雜)，可以考慮以下的公式：

sort_area_size = pga_aggregate_target * 30%；

hash_area_size ＝ pga_aggregate_target * 40%；

 

④ 其它參數調整。

db_file_multiblock_read_count表明在涉及一個完全連續掃描的一次 I/O 操作過程中讀取的塊的最大數量，預設值是8。增加該值可以提高查詢的速度，但取值到底是多大，要根據作業系統的情況來定，一般而言最好不要超過32（可在實際工作中反覆試試，一遍確定一個最合理的值）。

 

java_pool_size以位元組為單位， 指定 Java 儲存池的大小， 它用於儲存 Java 的方法和類定義在共用記憶體中的標記法， 以及在調用結束時移植到 Java 會話空間的 Java 對象。如果沒有使用Oralce HTTP之類的服務，令java_pool_size = 64M即可

 

large_pool_size指定大型池的分配堆的大小，它可被共用伺服器用作會話記憶體，用作並存執行的訊息緩衝區以及用作 RMAN 備份和恢複的磁碟 I/O 緩衝區。一般可令large_pool_size ＝ pga_aggregate_target * 10%。

 

最後，我們必須要認識到系統參數調優的過程是一個比較複雜的過程，而且根據不同應用目的的系統各種參數的給定有非常大的差別，比如有些系統資料變化不是很頻繁，主要用於查詢，而另外一些系統主要用來插入資料，因此放之四海而皆準的方法或者經驗公式是不存在的。儘管如此，一些經過許多人積累下來的方法或者經驗公式還是提供了一個很好的出發點，在此基礎上，通過反覆實驗，應該可以得到一組相對最佳化的參數取值。

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/pathuang68/archive/2009/04/16/4084139.aspx