Oracle常用調優手段有哪些

Oracle常用調優手段有哪些，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。

Oracle數據庫應用系統的調優主要包括十個方面：
(1)、優化數據庫內存；
(2)、在Oracle共享池中固定應用程序代碼；
(3)、優化數據存儲；
(4)、優化數據排序的技術；
(5)、優化SQL語句；
(6)、優化回退段；
(7)、優化索引；
(8)、優化磁盤I/O；
(9)、定期生成數據庫對象的狀態統計信息；
(10)、優化操作系統環境。
其實質就是降低CPU負載、改善I/O性能。

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。

看完上述內容，你們掌握Oracle常用調優手段有哪些的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！