Oracle常用調(diào)優(yōu)手段有哪些

Oracle常用調(diào)優(yōu)手段有哪些，相信很多沒(méi)有經(jīng)驗(yàn)的人對(duì)此束手無(wú)策，為此本文總結(jié)了問(wèn)題出現(xiàn)的原因和解決方法，通過(guò)這篇文章希望你能解決這個(gè)問(wèn)題。

Oracle數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的調(diào)優(yōu)主要包括十個(gè)方面：
(1)、優(yōu)化數(shù)據(jù)庫(kù)內(nèi)存；
(2)、在Oracle共享池中固定應(yīng)用程序代碼；
(3)、優(yōu)化數(shù)據(jù)存儲(chǔ)；
(4)、優(yōu)化數(shù)據(jù)排序的技術(shù)；
(5)、優(yōu)化SQL語(yǔ)句；
(6)、優(yōu)化回退段；
(7)、優(yōu)化索引；
(8)、優(yōu)化磁盤I/O；
(9)、定期生成數(shù)據(jù)庫(kù)對(duì)象的狀態(tài)統(tǒng)計(jì)信息；
(10)、優(yōu)化操作系統(tǒng)環(huán)境。
其實(shí)質(zhì)就是降低CPU負(fù)載、改善I/O性能。

1、化磁盤I/O
數(shù)據(jù)庫(kù)的作用就是實(shí)現(xiàn)對(duì)數(shù)據(jù)的管理和查詢，所以必然存在對(duì)數(shù)據(jù)的大量讀寫(xiě)操作，其I/O問(wèn)題也往往是導(dǎo)致Oracle數(shù)據(jù)庫(kù)性能問(wèn)題的重要原因。
1.1、Oracle中I/O的產(chǎn)生
1.2、優(yōu)化OS存儲(chǔ)
一、在UNIT環(huán)境下，采用裸設(shè)備（Raw Device）作為Oracle數(shù)據(jù)文件的存儲(chǔ)設(shè)備比采文件系統(tǒng)（File System）存儲(chǔ)Oracle數(shù)據(jù)文件具有較高的讀寫(xiě)效率。
二、采用異步IO（Asynchronous IO）方式。在異步IO模式下，進(jìn)程發(fā)出IO請(qǐng)求后無(wú)需等待IO完成，可以去處理其它事情；IO請(qǐng)求被放入一個(gè)隊(duì)列中，一旦IO完成，系統(tǒng)會(huì)發(fā)出信號(hào)通知進(jìn)程。異步IO可以使需要大量寫(xiě)的Oracle進(jìn)程（如DBWn進(jìn)程）將IO請(qǐng)求隊(duì)列化，以充分利用硬件的IO帶寬，從而使它們能最大程度實(shí)現(xiàn)并行處理。確認(rèn)操作系統(tǒng)已經(jīng)設(shè)置支持AIO后，還需要設(shè)置Oracle初始化參數(shù)“DISK_ASYNCH_IO”為“true”以支持異步IO。
三、磁盤負(fù)載均衡及條帶化（Striping）。條帶化技術(shù)就是將數(shù)據(jù)分成很多小部分并把他們分別存儲(chǔ)到不同磁盤上的不同區(qū)域中去。這就能使多個(gè)進(jìn)程同時(shí)訪問(wèn)數(shù)據(jù)的多個(gè)不同部分而不會(huì)造成磁盤沖突。很多操作系統(tǒng)、磁盤設(shè)備供應(yīng)商、各種第三方軟件都能做到條帶化。通過(guò)條帶化，DBA可以很輕松的做到IO負(fù)載均衡而無(wú)需去手工配置。
1.3、優(yōu)化IO配置
一、利用LVM（Logical Volume Manager邏輯卷管理器）軟件合理的配置條帶的寬度和深度。
二、采用分離文件策略，避免磁盤熱點(diǎn)。盡管我們?cè)谟布筒僮飨到y(tǒng)層面通過(guò)磁盤條帶化實(shí)現(xiàn)了磁盤負(fù)載均衡，但我們依然不能避免某些數(shù)據(jù)文件成為“熱點(diǎn)文件”。
分離表、索引和臨時(shí)表空間的存儲(chǔ)，即為應(yīng)用系統(tǒng)建立單獨(dú)的數(shù)據(jù)表空間、索引表空間、臨時(shí)表空間。
分離重做日志(Redo Log)文件。如果Redo Log文件的IO吞吐率高，則應(yīng)將Redo Log存儲(chǔ)在單獨(dú)磁盤上，在有充裕磁盤的的情況下，應(yīng)將Redo Log文件條帶化分布到多個(gè)磁盤上去；
分離歸檔日志文件(Archive Log)。當(dāng)ORACLE數(shù)據(jù)庫(kù)運(yùn)行在歸檔模式下時(shí)，歸檔進(jìn)程(ARCn )必然會(huì)產(chǎn)生大量的磁盤讀寫(xiě)。所以應(yīng)將歸檔日志文件條帶化分布到多個(gè)磁盤上單獨(dú)存儲(chǔ)。
1.4、優(yōu)化ORACLE I/O相關(guān)的參數(shù)設(shè)置
db_file_multiblock_read_count：用于控制一個(gè)完全連續(xù)掃描中一次 I/O 操作所讀取的數(shù)據(jù)塊的最大值。默認(rèn)值8
db_writer_processes：數(shù)據(jù)庫(kù)“寫(xiě)進(jìn)程”的初始數(shù)量。
disk_asynch_io：用于控制數(shù)據(jù)文件、 控制文件和日志文件的 I/O 是否異步 。只有在平臺(tái)支持磁盤異步 I/O的情況下才能更改該參數(shù)。
log_archive_max_processes：指定歸檔模式下ARCH 進(jìn)程的數(shù)量。

2.優(yōu)化數(shù)據(jù)存儲(chǔ)

2.1、表空間優(yōu)化
SYSTEM表空間用于存放ORACLE系統(tǒng)的有關(guān)信息，一般的用戶建立的對(duì)象（object）不應(yīng)放在SYSTEM表空間中，另外還需要注意增加SYSTEM表空間合適的大小，保證有100M左右的空閑空間。
對(duì)于ORACLE的數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)，都應(yīng)該為應(yīng)用系統(tǒng)建立獨(dú)立的ORACLE用戶(scheme)、數(shù)據(jù)表空間、索引表空間、臨時(shí)表空間。應(yīng)將表空間及數(shù)據(jù)文件一次性設(shè)置成合適的大小，避免數(shù)據(jù)文件自動(dòng)增長(zhǎng)，造成數(shù)據(jù)段塊/段的不連續(xù)，影響系統(tǒng)性能。
定期檢查數(shù)據(jù)庫(kù)表空間的使用情況，防止表空間碎塊過(guò)多，影響系統(tǒng)性能。通過(guò)查詢視圖dba_extents可獲取表空間詳細(xì)的使用情況。
整理表空間碎片，回收數(shù)據(jù)表段的可用空間。
合并表空間碎片
alter tablespace tablespacename coalesce;
回收數(shù)據(jù)表段的空閑空間
alter table tablename deallocate unused;

2.2、合理配置回滾段大小
在Oracle 9i以后，系統(tǒng)采用了透明的本地化的管理方式。默認(rèn)情況下，回滾段表空間是比較小的，往往不能滿足實(shí)際應(yīng)用中處理大型的事務(wù)的需要，就需要為專門的大型應(yīng)用或事務(wù)建立大的回滾段。

2.3、臨時(shí)表空間設(shè)計(jì)規(guī)劃
臨時(shí)表空間主要用于查詢操作中的distinct 、union 、order by以及create index操作及存儲(chǔ)臨時(shí)表數(shù)據(jù)等。Oracle缺省表空間為Temp，其大小為1MB，對(duì)于一個(gè)真正的中、大型應(yīng)用系統(tǒng)是遠(yuǎn)遠(yuǎn)不夠的，因此需做如下工作：
增加Temp表空間到合適的大小，一般為300M-500M左右。
創(chuàng)建用戶時(shí)應(yīng)為其選擇專用的臨時(shí)表空間。
應(yīng)該為大的應(yīng)用系統(tǒng)建立專門的大的臨時(shí)表空間，用于進(jìn)行系統(tǒng)的月報(bào)、季報(bào)、年報(bào)統(tǒng)計(jì)存儲(chǔ)臨時(shí)表數(shù)據(jù)等。

2.4、將數(shù)據(jù)文件和日志文件存儲(chǔ)于不同磁盤上
數(shù)據(jù)文件的寫(xiě)入是通過(guò)DBWR后臺(tái)進(jìn)程實(shí)現(xiàn)，日志文件的寫(xiě)入是通過(guò)LGWR后臺(tái)進(jìn)程實(shí)現(xiàn)，由于日志文件是連續(xù)的寫(xiě)入，因此無(wú)并發(fā)處理現(xiàn)象。而數(shù)據(jù)文件的寫(xiě)入相對(duì)是隨機(jī)的，為避免在同一時(shí)間內(nèi)DBWR和LGWR的沖突，應(yīng)將日志文件和數(shù)據(jù)文件放在不同的硬盤上。
另外，在ARCHIVELOG模式時(shí)，也可能產(chǎn)生日志文件寫(xiě)磁盤與日志歸檔間的沖突，這種沖突的避免只能通過(guò)將日志文件分配到多個(gè)磁盤才能解決。

3、優(yōu)化操作系統(tǒng)環(huán)境

3.1、配置操作系統(tǒng)合適的信號(hào)量
Oracle在某些Unix操作系統(tǒng)環(huán)境下運(yùn)行需要合適的操作系統(tǒng)信號(hào)量。應(yīng)該根據(jù)Oracle版本發(fā)行的要求進(jìn)行設(shè)置，譬如在SOLARIS環(huán)境下，需要以root 登錄并根據(jù)Oracle安裝手冊(cè)的參數(shù)要求修改/etc目錄的system文件。

3.2、配置合適大小的交換區(qū)
在UNIX操作系統(tǒng)環(huán)境下，交換區(qū)是Oracle的一項(xiàng)基本的要求?？梢愿鶕?jù)Oracle的發(fā)行要求來(lái)確定。建議交換區(qū)的大小是該服務(wù)器內(nèi)存的2倍以上。

3.3、配置操作系統(tǒng)啟用異步I/O
目前大多數(shù)操作系統(tǒng)都支持異步I/O，但如果讓Oracle運(yùn)行在異步I/O模式下，就必須做相應(yīng)的配置。譬如在HP－UNIX下，需要把“/dev /async”授權(quán)給操作系統(tǒng)的oracle用戶,并且要修改Oracle參數(shù)disk_asynch_io＝true。

3.4、將Oracle的SGA鎖定在物理內(nèi)存中。
幾乎所有的操作系統(tǒng)都支持虛擬內(nèi)存，所以即使我們使用的內(nèi)存小于物理內(nèi)存，也不能避免操作系統(tǒng)將SGA交換到虛擬內(nèi)存（SWAP），通過(guò)相應(yīng)配置將Oracle SGA鎖定在物理內(nèi)存避免被交換到虛擬內(nèi)存中，可以減少頁(yè)面的換入和換出，從而提高性能。
HP-UNIX下的配置方法：
#/etc/setprivgrp dba MLOCK
調(diào)整ORACLE參數(shù)：lock_sga＝TRUE
重啟數(shù)據(jù)庫(kù)
AIX 5L（AIX 4.3.3）下的配置方法：
$/usr/sbin/vmo -r -o v_pinshm=1（或vmtune -s 1）
調(diào)整ORACLE參數(shù)：lock_sga＝TRUE
重啟數(shù)據(jù)庫(kù)

3.5、控制內(nèi)存交換操作(Paging)
大量的內(nèi)存交換操作會(huì)極大地影響系統(tǒng)的性能，尤其是在當(dāng)數(shù)據(jù)庫(kù)文件創(chuàng)建在文件系統(tǒng)上時(shí)影響更大。在這種情況下經(jīng)常訪問(wèn)的數(shù)據(jù)，即在SGA中存在也同樣在文件的緩存中存在。這種相同的數(shù)據(jù)在內(nèi)存中緩存兩次的情況，會(huì)降低內(nèi)存的使用效率，從而使內(nèi)存頻繁進(jìn)行交換操作，造成系統(tǒng)的I/O瓶頸，降低整個(gè)系統(tǒng)的性能。 通過(guò)將ORACLE數(shù)據(jù)文件建在裸設(shè)備上和調(diào)整系統(tǒng)文件緩存，可以有效降低內(nèi)存交換操作。
在AIX上，可以通過(guò)vmtune命令中的MINPERM（缺省值20）和MAXPERM（缺省值80）參數(shù)來(lái)調(diào)整系統(tǒng)文件緩存，用以控制內(nèi)存交換操作。一般將MINPERM和MAXPERM分別設(shè)為5%和20%甚至更小，可使內(nèi)存更多地被用于Oracle的SGA而不是系統(tǒng)的文件緩存。
＃vmtune -p 5 -P 20
在HP-UINX 10.X以前的版本中，分配過(guò)大的文件系統(tǒng)緩存，也會(huì)導(dǎo)致Oracle的SGA被交換到虛擬內(nèi)存中去。在10.X以后的版本中文件系統(tǒng)緩存動(dòng)態(tài)分配。不合理的設(shè)置dbc_min_pct和dbc_max_pct也會(huì)導(dǎo)致無(wú)法解釋的偶然或間歇性停頓出現(xiàn)。

4.優(yōu)化數(shù)據(jù)庫(kù)內(nèi)存

4.1、Oracle內(nèi)存結(jié)構(gòu)

4.2、優(yōu)化SGA(System Global Area )
對(duì)于Oracle內(nèi)存的調(diào)整設(shè)置，要根據(jù)實(shí)際情況酌情考慮，基本的原則是：
數(shù)據(jù)緩沖區(qū)(data buffer)：用于存放從數(shù)據(jù)文件讀入的數(shù)據(jù)塊，可以盡可能的大；
共享池(shared_pool_size)：用于保存數(shù)據(jù)字典及當(dāng)前執(zhí)行的SQL語(yǔ)句和存儲(chǔ)過(guò)程，要適度；
日志緩沖區(qū)(log_buffer)：用于緩存用戶更新的數(shù)據(jù)，不需太大。
shared_pool_size：要大小要適度，通常設(shè)為500M左右，不應(yīng)超過(guò)700M。
log_buffer ：通常設(shè)為512K到1M。
large_pool_size ：如果不設(shè)置MTS（Multi-Threaded Server），該部分內(nèi)存只會(huì)在 RMAN（恢復(fù)管理） 、OPQ（并行查詢） 中使用到，通常設(shè)置為是16M-64M 。
java_pool_size : 如果在數(shù)據(jù)庫(kù)里不使用java，通常設(shè)置為16M。
data buffer ：在做了上面的設(shè)置后，凡可以提供給Oracle的內(nèi)存，都應(yīng)該給data buffer = (db_block_size * db_block_buffers) 。
SGA=data buffer+shared_pool_size+log_buffer+java_pool_size+large_pool_size

4.3、優(yōu)化PGA (Program Global Area )
sort_area_size：默認(rèn)64K,通常設(shè)置為128K到512K
hash_area_size:不做配置，是根據(jù)2*sort_area_size計(jì)算得到
這兩個(gè)參數(shù)在非MTS下都是屬于PGA(Program Global Area)，不屬于SGA。它是為每個(gè)session單獨(dú)分配的，所以服務(wù)器上內(nèi)存開(kāi)銷情況通常要求：OS內(nèi)存+SGA+session* (sort_area_size+hash_area_size+2M)<總物理RAM。

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