Oracle常用調優手段

1、化磁碟I/O
資料庫的作用就是實現對資料的管理和查詢，所以必然存在對資料的大量讀寫操作，其I/O問題也往往是導致Oracle資料庫效能問題的重要原因。
1.1、Oracle中I/O的產生
1.2、最佳化OS儲存
一、在UNIT環境下，採用裸裝置（Raw Device）作為Oracle資料檔案的儲存裝置比採檔案系統（File System）儲存Oracle資料檔案具有較高的讀寫效率。
二、採用非同步IO（Asynchronous IO）方式。在非同步IO模式下，程式發出IO請求後無需等待IO完成，可以去處理其它事情；IO請求被放入一個佇列中，一旦IO完成，系統會發出訊號通知程式。非同步IO可以使需要大量寫的Oracle程式（如DBWn程式）將IO請求佇列化，以充分利用硬體的IO頻寬，從而使它們能最大程度實現並行處理。確認作業系統已經設定支援AIO後，還需要設定Oracle初始化引數“DISK_ASYNCH_IO”為“true”以支援非同步IO。
三、磁碟負載均衡及條帶化（Striping）。條帶化技術就是將資料分成很多小部分並把他們分別儲存到不同磁碟上的不同區域中去。這就能使多個程式同時訪問資料的多個不同部分而不會造成磁碟衝突。很多作業系統、磁碟裝置供應商、各種第三方軟體都能做到條帶化。透過條帶化，DBA可以很輕鬆的做到IO負載均衡而無需去手工配置。
1.3、最佳化IO配置
一、利用LVM（Logical Volume Manager邏輯卷管理器）軟體合理的配置條帶的寬度和深度。
二、採用分離檔案策略，避免磁碟熱點。儘管我們在硬體和作業系統層面透過磁碟條帶化實現了磁碟負載均衡，但我們依然不能避免某些資料檔案成為“熱點檔案”。
分離表、索引和臨時表空間的儲存，即為應用系統建立單獨的資料表空間、索引表空間、臨時表空間。
分離重做日誌(Redo Log)檔案。如果Redo Log檔案的IO吞吐率高，則應將Redo Log儲存在單獨磁碟上，在有充裕磁碟的的情況下，應將Redo Log檔案條帶化分佈到多個磁碟上去；
分離歸檔日誌檔案(Archive Log)。當ORACLE資料庫執行在歸檔模式下時，歸檔程式(ARCn )必然會產生大量的磁碟讀寫。所以應將歸檔日誌檔案條帶化分佈到多個磁碟上單獨儲存。
1.4、最佳化ORACLE I/O相關的引數設定
db_file_multiblock_read_count：用於控制一個完全連續掃描中一次 I/O 操作所讀取的資料塊的最大值。預設值8
db_writer_processes：資料庫“寫程式”的初始數量。
disk_asynch_io：用於控制資料檔案、 控制檔案和日誌檔案的 I/O 是否非同步 。只有在平臺支援磁碟非同步 I/O的情況下才能更改該引數。
log_archive_max_processes：指定歸檔模式下ARCH 程式的數量。

2.最佳化資料儲存

2.1、表空間最佳化
SYSTEM表空間用於存放ORACLE系統的有關資訊，一般的使用者建立的物件（object）不應放在SYSTEM表空間中，另外還需要注意增加SYSTEM表空間合適的大小，保證有100M左右的空閒空間。
對於ORACLE的資料庫應用系統，都應該為應用系統建立獨立的ORACLE使用者(scheme)、資料表空間、索引表空間、臨時表空間。應將表空間及資料檔案一次性設定成合適的大小，避免資料檔案自動增長，造成資料段塊/段的不連續，影響系統效能。
定期檢查資料庫表空間的使用情況，防止表空間碎塊過多，影響系統效能。透過查詢檢視dba_extents可獲取表空間詳細的使用情況。
整理表空間碎片，回收資料表段的可用空間。
合併表空間碎片
alter tablespace tablespacename coalesce;
回收資料表段的空閒空間
alter table tablename deallocate unused;

2.2、合理配置回滾段大小
在Oracle 9i以後，系統採用了透明的本地化的管理方式。預設情況下，回滾段表空間是比較小的，往往不能滿足實際應用中處理大型的事務的需要，就需要為專門的大型應用或事務建立大的回滾段。

2.3、臨時表空間設計規劃
臨時表空間主要用於查詢操作中的distinct 、union 、order by以及create index操作及儲存臨時表資料等。Oracle預設表空間為Temp，其大小為1MB，對於一個真正的中、大型應用系統是遠遠不夠的，因此需做如下工作：
增加Temp表空間到合適的大小，一般為300M-500M左右。
建立使用者時應為其選擇專用的臨時表空間。
應該為大的應用系統建立專門的大的臨時表空間，用於進行系統的月報、季報、年報統計儲存臨時表資料等。

2.4、將資料檔案和日誌檔案儲存於不同磁碟上
資料檔案的寫入是透過DBWR後臺程式實現，日誌檔案的寫入是透過LGWR後臺程式實現，由於日誌檔案是連續的寫入，因此無併發處理現象。而資料檔案的寫入相對是隨機的，為避免在同一時間內DBWR和LGWR的衝突，應將日誌檔案和資料檔案放在不同的硬碟上。
另外，在ARCHIVELOG模式時，也可能產生日誌檔案寫磁碟與日誌歸檔間的衝突，這種衝突的避免只能透過將日誌檔案分配到多個磁碟才能解決。

3、最佳化作業系統環境

3.1、配置作業系統合適的訊號量
Oracle在某些Unix作業系統環境下執行需要合適的作業系統訊號量。應該根據Oracle版本發行的要求進行設定，譬如在SOLARIS環境下，需要以root 登入並根據Oracle安裝手冊的引數要求修改/etc目錄的system檔案。

3.2、配置合適大小的交換區
在UNIX作業系統環境下，交換區是Oracle的一項基本的要求。可以根據Oracle的發行要求來確定。建議交換區的大小是該伺服器記憶體的2倍以上。

3.3、配置作業系統啟用非同步I/O
目前大多數作業系統都支援非同步I/O，但如果讓Oracle執行在非同步I/O模式下，就必須做相應的配置。譬如在HP－UNIX下，需要把“/dev /async”授權給作業系統的oracle使用者,並且要修改Oracle引數disk_asynch_io＝true。

3.4、將Oracle的SGA鎖定在實體記憶體中。
幾乎所有的作業系統都支援虛擬記憶體，所以即使我們使用的記憶體小於實體記憶體，也不能避免作業系統將SGA交換到虛擬記憶體（SWAP），透過相應配置將Oracle SGA鎖定在實體記憶體避免被交換到虛擬記憶體中，可以減少頁面的換入和換出，從而提高效能。
HP-UNIX下的配置方法：
#/etc/setprivgrp dba MLOCK
調整ORACLE引數：lock_sga＝TRUE
重啟資料庫
AIX 5L（AIX 4.3.3）下的配置方法：
$/usr/sbin/vmo -r -o v_pinshm=1（或vmtune -s 1）
調整ORACLE引數：lock_sga＝TRUE
重啟資料庫

3.5、控制記憶體交換操作(Paging)
大量的記憶體交換操作會極大地影響系統的效能，尤其是在當資料庫檔案建立在檔案系統上時影響更大。在這種情況下經常訪問的資料，即在SGA中存在也同樣在檔案的快取中存在。這種相同的資料在記憶體中快取兩次的情況，會降低記憶體的使用效率，從而使記憶體頻繁進行交換操作，造成系統的I/O瓶頸，降低整個系統的效能。 透過將ORACLE資料檔案建在裸裝置上和調整系統檔案快取，可以有效降低記憶體交換操作。
在AIX上，可以透過vmtune命令中的MINPERM（預設值20）和MAXPERM（預設值80）引數來調整系統檔案快取，用以控制記憶體交換操作。一般將MINPERM和MAXPERM分別設為5%和20%甚至更小，可使記憶體更多地被用於Oracle的SGA而不是系統的檔案快取。
＃vmtune -p 5 -P 20
在HP-UINX 10.X以前的版本中，分配過大的檔案系統快取，也會導致Oracle的SGA被交換到虛擬記憶體中去。在10.X以後的版本中檔案系統快取動態分配。不合理的設定dbc_min_pct和dbc_max_pct也會導致無法解釋的偶然或間歇性停頓出現。

4.最佳化資料庫記憶體

4.1、Oracle記憶體結構

4.2、最佳化SGA(System Global Area )
對於Oracle記憶體的調整設定，要根據實際情況酌情考慮，基本的原則是：
資料緩衝區(data buffer)：用於存放從資料檔案讀入的資料塊，可以儘可能的大；
共享池(shared_pool_size)：用於儲存資料字典及當前執行的SQL語句和儲存過程，要適度；
日誌緩衝區(log_buffer)：用於快取使用者更新的資料，不需太大。
shared_pool_size：要大小要適度，通常設為500M左右，不應超過700M。
log_buffer ：通常設為512K到1M。
large_pool_size ：如果不設定MTS（Multi-Threaded Server），該部分記憶體只會在 RMAN（恢復管理） 、OPQ（並行查詢） 中使用到，通常設定為是16M-64M 。
java_pool_size : 如果在資料庫裡不使用java，通常設定為16M。
data buffer ：在做了上面的設定後，凡可以提供給Oracle的記憶體，都應該給data buffer = (db_block_size * db_block_buffers) 。
SGA=data buffer+shared_pool_size+log_buffer+java_pool_size+large_pool_size

4.3、最佳化PGA (Program Global Area )
sort_area_size：預設64K,通常設定為128K到512K
hash_area_size:不做配置，是根據2*sort_area_size計算得到
這兩個引數在非MTS下都是屬於PGA(Program Global Area)，不屬於SGA。它是為每個session單獨分配的，所以伺服器上記憶體開銷情況通常要求：OS記憶體+SGA+session* (sort_area_size+hash_area_size+2M)<總物理RAM。

--感謝廣州老聶分享