效能優化 - Oracle Tuning 總結 1

關於Oracle的效能調整，一般包括兩個方面，一是指Oracle資料庫本身的調整，比如SGA、PGA的優化設定，二是連線Oracle的應用程式以及SQL語句的優化。做好

這兩個方面的優化，就可以使一套完整的Oracle應用系統處於良好的執行狀態。
          本文主要是把一些Oracle Tuning的文章作了一個簡單的總結，力求以實際可操作為目的，配合講解部分理論知識，使大部分具有一般Oracle知識的使

用者能夠對Oracle Tuning有所瞭解，並且能夠根據實際情況對某些引數進行調整。關於更加詳細的知識，請參見本文結束部分所提及的推薦書籍，同時由於該話

題內容太多且複雜，本文必定有失之偏頗甚至錯誤的地方，請不吝賜教，並共同進步。

1.    SGA的設定
   在Oracle Tuning中，對SGA的設定是關鍵。SGA，是指Shared Global Area , 或者是 System Global Area , 稱為共享全域性區或者系統全域性區，結構如下圖所示。

    對於SGA區域內的記憶體來說，是共享的、全域性的，在UNIX 上，必須為oracle 設定共享記憶體段（可以是一個或者多個），因為oracle 在UNIX上是多程式；而

在WINDOWS上oracle是單程式（多個執行緒），所以不用設定共享記憶體段。

1.1    SGA的各個組成部分
下面用 sqlplus 查詢舉例看一下 SGA 各個組成部分的情況：
SQL> select * from v$sga;
NAME                        VALUE
--------------------                ----------
Fixed Size                     104936
Variable Size                823164928
Database Buffers            1073741824
Redo Buffers                   172032

或者
SQL> show sga
Total System Global Area     1897183720 bytes
Fixed Size                     104936 bytes
Variable Size                823164928 bytes
Database Buffers            1073741824 bytes
Redo Buffers                   172032 bytes

Fixed Size
     oracle 的不同平臺和不同版本下可能不一樣，但對於確定環境是一個固定的值，裡面儲存了SGA 各部分元件的資訊，可以看作引導建立SGA的區域。

Variable Size
      包含了shared_pool_size、java_pool_size、large_pool_size 等記憶體設定

Database Buffers
      指資料緩衝區，在8i 中包含db_block_buffer*db_block_size、buffer_pool_keep、buffer_pool_recycle 三部分記憶體。在9i 中包含db_cache_size、

db_keep_cache_size、db_recycle_cache_size、db_nk_cache_size。

Redo Buffers
         指日誌緩衝區，log_buffer。在這裡要額外說明一點的是，對於v$parameter、v$sgastat、v$sga查詢值可能不一樣。v$parameter 裡面的值，是指用

戶在初始化引數檔案裡面設定的值，v$sgastat是oracle 實際分配的日誌緩衝區大小（因為緩衝區的分配值實際上是離散的，也不是以block 為最小單位進行分

配的），v$sga 裡面查詢的值，是在oracle 分配了日誌緩衝區後，為了保護日誌緩衝區，設定了一些保護頁，通常我們會發現保護頁大小是8k(不同環境可能不

一樣)。參考如下內容
SQL> select substr(name,1,10) name,substr(value,1,10) value
         2 from v$parameter where name = 'log_buffer';
NAME                   VALUE
--------------------    --------------------
log_buffer                163840

SQL> select * from v$sgastat where pool is null;

POOL          NAME                         BYTES
----------- --------------------------                  ----------
              fixed_sga                        104936
              db_block_buffers              1073741824
              log_buffer                       163840

SQL> select * from v$sga;

NAME                        VALUE
--------------------                ----------
Fixed Size                     104936
Variable Size                 823164928
Database Buffers             1073741824
Redo Buffers                   172032

172032 – 163840 = 8192

（以上試驗資料是在 HP B.11.11 + Oracle 8.1.7.4 環境下得到的）

1.2    SGA的大小設定
         在對SGA的結構進行簡單分析以後，下面是關於如何根據系統的情況正確設定SGA大小的問題。
SGA是一塊記憶體區域，佔用的是系統實體記憶體，因此對於一個Oracle應用系統來說，SGA決不是越大越好，這就需要尋找一個系統優化的平衡點。

1.2.1    設定引數前的準備
在設定SGA的記憶體引數之前，我們首先要問自己幾個問題
一：實體記憶體多大
二：作業系統估計需要使用多少記憶體
三：資料庫是使用檔案系統還是裸裝置
四：有多少併發連線
五：應用是OLTP 型別還是OLAP 型別

根據這幾個問題的答案，我們可以粗略地為系統估計一下記憶體設定。那我們現在來逐個問題地討論，首先實體記憶體多大是最容易回答的一個問題，然後作業系統

估計使用多少記憶體呢？從經驗上看，不會太多，通常應該在200M 以內（不包含大量程式PCB）。
接下來我們要探討一個重要的問題，那就是關於檔案系統和裸裝置的問題，這往往容易被我們所忽略。作業系統對於檔案系統，使用了大量的buffer 來快取操作

系統塊。這樣當資料庫獲取資料塊的時候，雖然SGA 中沒有命中，但卻實際上可能是從作業系統的檔案快取中獲取的。而假如資料庫和作業系統支援非同步IO，則

實際上當資料庫寫程式DBWR寫磁碟時，作業系統在檔案快取中標記該塊為延遲寫，等到真正地寫入磁碟之後，作業系統才通知DBWR寫磁碟完成。對於這部分檔案

快取，所需要的記憶體可能比較大，作為保守的估計，我們應該考慮在 0.2——0.3 倍記憶體大小。但是如果我們使用的是裸裝置，則不考慮這部分快取的問題。這

樣的情況下SGA就有調大的機會。
關於資料庫有多少併發連線，這實際上關係到PGA 的大小（MTS 下還有large_pool_size）。事實上這個問題應該說還跟OLTP 型別或者OLAP 型別相關。對於OLTP

型別oracle 傾向於可使用MTS,對於OLAP 型別使用獨立模式，同時OLAP 還可能涉及到大量的排序操作的查詢，這些都影響到我們記憶體的使用。那麼所有的問題綜

合起來，實際上主要反映在UGA的大小上。UGA主要包含以下部分記憶體設定
SQL> show parameters area_size

NAME                                   TYPE      VALUE
------------------------------------                 -------       --------
bitmap_merge_area_size                     integer      1048576
create_bitmap_area_size                     integer      8388608
hash_area_size                             integer       131072
sort_area_size                              integer       65536
SQL>

在這部分記憶體中我們最關注的通常是sort_area_size，這是當查詢需要排序的時候，資料庫會話將使用這部分記憶體進行排序，當記憶體大小不足的時候，使用臨時

表空間進行磁碟排序。由於磁碟排序效率和記憶體排序效率相差好幾個數量級，所以這個引數的設定很重要。
當出現大量排序時的磁碟I/O操作時，可以考慮增加sort_area_size的值。sort_area_size是Oracle用於一次排序所需的最大記憶體數，在排序結束但是結果列返回之前，Oracle會釋放sort_area_size大小的記憶體，但是會保留sort_area_retained_size大小的記憶體，知道最後一行結果列返回以後，才釋放所有的記憶體。
會導致排序的語句有 SELECT DISTINCT , MINUS , INTERSECT , UNION 和 min()、max()、count() 操作；而不會導致排序的語句有 UPDATE , 帶BETWEEN子句的SELECT 等等。
這四個引數都是針對會話進行設定的，是單個會話使用的記憶體的大小，而不是整個資料庫使用的。偶爾會看見有人誤解了這個引數以為是整個資料庫使用的大小，這是極其嚴重的錯誤。假如設定了MTS，則UGA被分配在large_pool_size，也就是說放在了共享記憶體裡面，不同程式（執行緒）之間可以共享這部分記憶體。在這個基礎上，我們假設資料庫存在併發執行server process 為100 個，根據上面我們4 個引數在oracle8.1.7 下的預設值，我們來計算獨立模式下PGA 的大致大小。

由於會話並不會經常使用create_bitmap_area_size 、bitmap_merge_area_size，所以我們通常不對四個引數求和。在考慮到除這四個引數外會話所儲存的變數、堆疊等資訊，我們估計為2M，則200 個程式最大可能使用200M 的PGA。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-616752/，如需轉載，請註明出處，否則將追究法律責任。