-- 作者：wangxinxin
-- 發布時間：2010-12-11 11:13:01
-- Oracle Tuning的一些總結
關于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>
|