Oracle資料庫優化實務經驗談─運作速度優化與記憶體配置
本文將分別從：記憶體管理、Row chaining與Row migration、實體檔案規劃、SQL優化等四個角度，探討如何解決連線數量增加後，面臨Oracle資料庫系統效率與管理問題。

話說某橫跨兩岸三地集團，過去10年業績成長顯著，大陸工廠人員也由1000來人成長至10000人，7年前導入以Oracle為資料庫的ERP，經由MIS的修修補補，功能面也已發展成熟。

不過由於公司持續的擴廠，使用ERP人員不斷增加，最近大陸ERP的使用反而發生許多不穩定狀況，包括：
●整體運作速度變慢。
●ERP明明沒問題，但就是會報錯，特別是人多的時候。
●MIS說ERP主機的記憶體夠用，不應該發生這樣的問題。
●相關外掛系統很容易就被Oracle自動斷線或是連不上。

扣除兩岸專線頻寬不足、或是ERP有Bug因素後，還需再考量的就是資料庫問題。但對大部份熟悉SQL Server「下一步」的MIS而言，Oracle可就沒那麼容易進行優化！

Oracle database與SQL Server至少有幾個不同處：
●一個SQL Server instance，可以安裝多個資料庫(除非你願意在一個作業系統中，每一個資料庫就安裝一套SQL Server，並開立不同的Instance)；Oracle database server則不同，一個資料庫就是一個Instance。
●同一個Instance的SQL Server所有資料庫，共用該Instance所有記憶體；Oracle database則是每一個資料庫有各自的記憶體。
●SQL Server有集中的自我管理模式；Oracle卻得由DBA自行優化。
●SQL Server沒有太多參數可以讓DBA進行優化；Oracle則有成堆的參數讓你進行資料庫優化與控制。

以下筆者將就相關參數，以及Oracle提供的統計功能，分別從：記憶體管理、Row chaining與Row migration、實體檔案規劃、SQL優化等四個角度，探討如何解決人員增加之後，所面臨的Oracle效率與管理問題(注意：Oracle 11g中有很多工具可以協助DBA優化，但對大多數5、6年前已導入ERP的公司，資料庫可能還停留在Oracle 9i或更早之前的版本，所以筆者盡可能使用SQL解說，不刻意引用Oracle 11g的Oracle Enterprise Manager資料與畫面來分析)。


了解你的作業系統以及Oracle database版本資訊
不同的Oracle database版本所提供的優化機制也各不相同，例如Oracle 9i提供各自SGA、PGA優化參數，但到了Oracle 11g，則提供了更進階的整體記憶體優化參數。

不同作業系統以及版本也一樣會影響優化方式，例如Oracle 9i在Linux的使用上，如果為32位元版本，則SGA有1.7G使用的限制，但如果為64位元版本，則無此限制。(圖1、圖2、圖3)


▲圖1 查看Oracle database版本。


▲圖2 查看Oracle database安裝平台資訊。


▲圖3 查看Linux版本，以及為32或64位元版本。


平時有多少程式會連接到Oracle database
談這個問題之前，且讓我們先了解Oracle database的記憶體組成：System Global Area(以下簡稱SGA)與Program Global Area(以下簡稱PGA)。(圖4)


▲圖4 Oracle Instance記憶體組成。

SGA中最重要的3個組成為：
●Database buffer cache：儲存從實體檔案中讀出來的資料。
●Redo log buffer：儲存用戶改變資料的信息，當用戶進行Roll back 時即是靠Redo log buffer的資料。
●Shared pool：包含Library cache(儲存執行SQL過程所需的收集、分解、解析SQL所有資訊)、Dictionary cache(儲存有關於Table、View等結構相關資訊)、Result cache(儲存執行結果)。

這些是給Oracle Instance共用的記憶體，「所有」用戶在進行資料庫存取時都會「共用」SGA。
至於PGA則如其名，是給每一個Session所對應的Background process或Server Process使用，每一個Session都對應一塊「私有記憶體」，彼此互不共用，當Session結束時這PGA也會被回收。

PGA是用來執行Process SQL statement，並包含用戶登入以及用戶的相關資訊，主要包含以下2部份：
●Session memory：儲存有關於Session variable，例如用戶登入資訊。
●Private SQL Area：包含變數資料、查詢狀態、查詢執行結果存放區(Query execution work area，例如SQL中的Order by、Group by運行時的資料暫存區)。

從以上的理論說明中可以明確感受到：SGA太小，則與資料讀取有關的部份速度會變慢；而PGA太小，則SQL運算、特別是與Order by、Group by有關的運算會變慢。那麼這與有多少程式會連接到Oracle有何關係？

程式建立Session、Session建立一個或多個Process(或一個Process對應多個Session)、每一個Process會耗用一定的PGA，但在Oracle設計中PGA的總記憶體是被參數所控制的，所以Session越多、Process越多，每一個Process所能分到的PGA就會越少，SQL的運算就會變慢！Oracle針對Process數量可以進行參數設定，而經由此參數，又可以限定所能連接的Session數，一般算法為：sessions=1.1*processes + 5。

Process與Session這兩者互為一體兩面。Process設定太大，那麼就要給予更多的PGA，否則程式的SQL運算會變慢；設定太小。如果某天你的用戶連線到Oracle Instance拒絕連線，或許就是因為Process設定不夠大，導致可以連線的Session不夠多。

有關於目前Oracle instance中有多少Session，可以用以下的SQL抓取：
select count(*) from v$session

有關於目前Oracle instance中有多少Process，可以用以下的SQL抓取：
Select count(*) from v$process


該如何配置記憶體？該配多少記憶體？
Oracle配置記憶體的方法依不同版本而有不同作法：例如Oracle 11g你可能只要設定總記憶體參數memory_target，即可由Oracle Instance自行進行記憶體配置；但在Oracle 9i則是SGA與PGA的記憶體需各自設定；或是不管在哪一個版本，你都可以進行全手工設定！

當主機有足夠記憶體時，記憶體配置不會是大問題(只要把所有與記憶體有關的參數都放大即可)，但若主機的總記憶體不多，或是如前述因版本問題，碰到SGA記憶體不得超過1.7G限制、session總數破2000時，記憶體的管理可能就是大問題。

筆者建議採用SGA與PGA分開優化的半自動記憶體管理策略較有彈性，在此情況下不使用memory_target參數，而改用以下參數設定：

sga_max_size
設定分配給SGA的總記憶體上限，當sga_max_size的記憶體量大於分配給SGA相關參數記憶體總合時，多出來的部份，會分配給share_pool_size：
●buffer_cacahe_size：理論上越大越好，日後讀取相同資料時，可以由記憶體中讀取，不需讀取實體檔案。
●shared_pool_size：理論上越大越好，日後有相同的SQL需要執行時就不需要重新解析。
●log_buffers：對數據庫的任何修改都按順序被記錄在該緩沖，然後由LGWR 進程將它寫入Disk，但正常情況下LGWR的寫入條件中包含有一條「大於1M重做日誌緩沖區未被寫入Disk」，因此我們可以說大於1M的log buffer值意義並不大。

pga_aggregate_target
設定PGA可用總記憶體數量目標(也就是說：實際Oracle instance使用時有可能超出一點點，但不可以超過太多)，當pga_agreegate_target夠大時，每一個用戶所分配的private sql area就會比較大，如有Order by 或Group by時計算自然較快。

例如pga_aggregate_target設為300MB，5個用戶連接時每個用戶可能分發10MB的PGA內存，共分發50MB的PGA內存；300個用戶連接時每個用戶可能分發1.3MB的PGA內存，共分發390MB(當用戶連接多時, Oracle會降低每個用戶的PGA內存使用量)。

那麼SGA相關參數該如何設定？首先我們得先了解目前的SGA設定狀況，可以由圖5中的指令得知現行SGA的分配狀況。


▲圖5 SGA現行配置狀況。

至於多少的記憶體才算是足夠？對任何DBA而言都沒有標準答案，但Oracle針對這個問題使用了如表1的命中率的觀念來說明。




另外，如果你是使用如Oracle 11g等較高版本，並有安裝Oracle Enterprise Manager，則你可以引用圖6、圖7的資料來進行SGA、PGA記憶體的規劃(當記憶體增加，並無法降低資料讀取次數、或提高執行速度時，代表該點即為記憶體設定的最佳值)。


▲圖6 由Oracle依內部統計資料建議的SGA最佳值。


▲圖7 由Oracle依內部統計資料建議的PGA最佳值。


關於Row chaining、Row migration對資料讀寫速度的影響
在現實的運作環境中，資料並非如字典一般規矩的排列在實體資料庫中！至少有以下因素會導致資料的存放異常：
●Row chaining：當Insert一筆較長的資料，但該Data block又無法容納該筆資料時，則該筆資料會存放在一個或多個Data block。日後要讀取該資料時，則需讀取多個Data block造成速度變慢。
●Row migration：當Data block將用完，如果對已有的資料Update為較長的資料，則Oracle會將原有的資料移到一個新的Data block，而只在原有的資料空間存放一個指針(但不存放資料)指到新的存放位置。日後要讀取該資料時，則需讀取多個Data block(讀到原有的Data block，再經由指針讀到真正資料存放處)造成速度變慢。

Row chaining與Row migration會讓Insert、Update的動作變慢(需跨多個Data block)；也會讓Select的過程讀取更多實體資料，但在實務上又無法避免，一般而言，你可用以下的方法來改善：
●加大Data block。例如Data block為4K，但Row的平均長度為6K時，加大Data block可以有效的改善Row chaining的問題。需注意的是：已建立的Tablespace， Data block的大小是不能再更改的，如果要進行這樣的處理，你所能做的是：設定參數檔db_nk_cache_size參數以滿足讀取較大空間的需求；建立新的Tablespace，並在建立過程宣告較大的Data block；將這類的Table移動、指定存放到該Tablespace。
●加大PCTFREE參數的大小。將Table 的PCTFREE放大，多保留一些空間供日後Update使用，此方法可以降低Row migration的情況。
●針對Row chaining或Migration嚴重的Table定期進行Export、Import。

有關於Table row chaining或Row migration的情況，可以利用以下的SQL抓取資料分析(注意：對Oracle Instance而言，Row migration被視為Row chaining的一種特例，並沒有獨立分類)：





如何進行實體檔案規劃
典型的Oracle實體檔案組成包括如下：

●Datafile：負責儲存來自User所輸入的資料、相關索引、以及其他DDL、DML資料，每一個資料庫都會有一個或多個Datafile。當User有存取資料需求時會經由Oracle instance先進行需求分析，再由Oracle instance內的Process，進行Tablespace所對應的Datafile資料的存取。

●Redo log file：一份交易的記錄資訊，每當Oracle instance對資料庫進行資料的新增、刪除、修改時，就會將該交易記錄寫入Redo log file。若系統發生異常狀況，系統即可以Redo log file中的資訊，針對Oracle失效當時的交易進行Forward commit或Roll back commit的Recovery處理。

●Archived file：已歸檔、已Commit交易過程記錄檔案(即已滿的Redo log file)，當有Recovery需求時，Oracle可以參考Archived file的交易過程記錄資訊，將Oracle資料庫Recovery到特定時期。

●Control file：儲存的是有關Oracle的相關核心資訊，如有那些Datafile、Control file、名稱為何、存放於何處。Oracle於啟動過程會讀取本檔的資料。

●InitORA_DB.ora / SPFILE：所存放的資訊包括建立資料庫過程需參考的資訊，以及後續資料庫的運作資訊。

●Alter file / Trace file：存放預警、追蹤的資訊。每一個Server與Background process，都可以將相關的警示與錯誤信息寫入Trace file或Log file。

這些實體檔案的存放位置，對於資料庫的運作速度也會有影響。一般而言建議將Datafile分別建立在不同的硬碟上，例如存放Index的Tablespace所對應的Dataflie存放在A硬碟，存放資料的Tablespace所對應的Datafile存放在B硬碟，這樣在多人連線、多用戶存取時由於各硬碟可以同時運作存取資料，因此會有更好的效率。

另外，Datafile、Redo log file、Archived file最好不要放在同一個硬碟，以避免同一時間、同一硬碟要同時讀寫不同資料。同時，Redo log file及Archived file都是做為資料庫復原的重要依據，如果將這3種資料實體檔案存放在同一個硬碟，當硬碟損毀將會造成資料無法復原。


從如何寫好SQL開始
從Oracle Instance的角度來看，「好」SQL代表能善用DBA已建立的Index、盡可能不要有Table scan動作、能有少一點的實體檔案讀取；另一方面，資深的程式開發人員以及DBA通常也都會遵守以下規則：
●Primary key建立Index。
●Where引用到的欄位建立Index。
●Join過程中的引用的條件建立Index。
●必要時以Cluster建立Table。
●使用多一點的Where條件，以讓SQL少抓一些資料。
●Select的過程，指名欄位名稱，少用「Select *」之類的寫法。

問題來了：你能用數據證明，你所寫的SQL是以所預期的方式在資料庫內部運行？DBA所建立的Index有發揮作用？

不管Oracle或SQL Server都有類似Query optimizer，從資料庫本身角度(而不是「人」的角度)，進行SQL的拆解(為SQL plan)，並找到其認為最節省成本的執行方式。

在Oracle中你可以用以下方式查看SQL plan：
●建立存放SQL plan的Table。
●將SQL Plan存放至該Table。
●讀取SQL Plan的資料並顯示。

圖8即為筆者解析某SQL的SQL Plan，發現該SQL會引用DBA所建立的Index來進行資料查詢，符合當初建立Index的目的。


▲圖8 由Oracle內部所解析的SQL Plan。


結語
Oracle Database運作速度優化與記憶體配置，並無絕對的標準！除了靠DBA平日對Oracle Instance運作速度觀察、用戶反饋之外，Oracle仍提供一些數字指標(如Cache命中率)與建議方案供DBA做為優化依據。

另外，再配合實體檔案位置規劃、Row chaining問題處理、SQL分析與優化，就可以大幅降低Oracle Instance所造成的運作速度低下與異常情況。更多優化有關的細節，可來信討論：Bountyhunter.z@hotmail.com。

【原文刊載於RUN!PC雜誌：2010年7月號】