|
1 SQL優(yōu)化方面 1.1 盡量減少硬解析 當(dāng)一個用戶與數(shù)據(jù)庫建立連接后，會向數(shù)據(jù)庫發(fā)出操作請求，即向數(shù)據(jù)庫送過去SQL語句。服務(wù)器進(jìn)程會將SQL語句轉(zhuǎn)變?yōu)閍sc碼，并通過一個hash函數(shù)將asc碼生成出一個hash值，服務(wù)器進(jìn)程會到共享池中l(wèi)ibrary cache查詢此hash是否存在，如果存在，服務(wù)器進(jìn)程會從library cache中讀取已經(jīng)解析好的語句來執(zhí)行（軟解析）。如果不存在，則需要做以下步驟：生成執(zhí)行計劃和生成執(zhí)行編碼。解析完成后，Oracle會將SQL語句本身代碼、hash值、編譯代碼、執(zhí)行計劃和所有與該語句相關(guān)的統(tǒng)計數(shù)據(jù)存放到library cache中。（硬解析）。 在優(yōu)化時要注意： 1.SQL書寫要規(guī)范，大小寫不同，空格數(shù)量的不同會生成不同的asc碼在解析時會被認(rèn)為是不同的SQL語句。 2.對大量重復(fù)的SQL使用綁定變量。 1.2 選擇優(yōu)化器 優(yōu)化器是SQL優(yōu)化的關(guān)鍵。SQL在硬解析時會用到優(yōu)化器，優(yōu)化器分為RBO（Rule Based Optimizer）和CBO(Cost Based Optimizer)兩種 。RBO按固定的規(guī)則生成執(zhí)行計劃，CBO更智能 一些會生成消耗cup和O/I最少的執(zhí)行計劃。推薦使用CBO優(yōu)化器。 1.3 SQL語句優(yōu)化 Oracle數(shù)據(jù)庫80%的性能問題是由SQL引起的，盡量減少SQL語句消耗的邏輯讀，物理讀，CUP時間，熱點爭用。 在優(yōu)化時要注意： 1.合理創(chuàng)建索引。 2.避免使用前置通配符如：like ‘%ABC’。 3.用exists,和not exists 代替in 和not in。 4.避免使用*,因為在解析時會查詢數(shù)據(jù)字典將“*”轉(zhuǎn)換成所有列名。 2 內(nèi)存優(yōu)化方面 2.1 合理設(shè)置share pool shared pool 中l(wèi)ibrary cache和data dictionary cache的大小，嚴(yán)重影響著解析過程；library cache越大，存放的執(zhí)行計劃越多，從而減少硬解析。如果library cache過小，SQL語句會經(jīng)常進(jìn)行硬解析，導(dǎo)致SQL語句執(zhí)行時間增加。增大share_pool_size可以保留更多的緩存在內(nèi)存中的sql語句執(zhí)行計劃，也意味著共享SQL的可能性的增大。 Oracle引入了data dictionary cache 組件以用來在內(nèi)存中直接緩存數(shù)據(jù)字典中的信息，避免了系統(tǒng)從磁盤直接讀取信息，提高了系統(tǒng)性能。如果data dictionary cache 組件內(nèi)存很小，或者緩存的信息不是本次所需要的數(shù)據(jù)，就需要從磁盤讀取信息，會直接影響系統(tǒng)性能。shared pool中l(wèi)ibrary cache和data dictionary cache兩個緩沖區(qū)可動態(tài)的擴展或收縮。但是內(nèi)存的擴展與收縮操作，也會產(chǎn)生cpu等開銷。為了避免兩者其一過大，導(dǎo)致另一個緩沖區(qū)過小的狀況，可以設(shè)置緩沖區(qū)的最小值閾值。 2.2 合理設(shè)置DB buffer cache 盡量少讀數(shù)據(jù)文件，提高命中率。在硬解析時，service process會試圖從DB buffer cache中讀取是否存在相關(guān)的緩存數(shù)據(jù)。如果不存在相關(guān)數(shù)據(jù)，則服務(wù)器進(jìn)程會把相應(yīng)的塊從datafiles里讀入到DB buffer cache中，此時如果DB buffer cache空閑空間不足，則會觸發(fā)寫操作—DBWn。就會觸發(fā)DBWr進(jìn)程，將DB buffer cache中臟數(shù)據(jù)寫入數(shù)據(jù)文件。騰出來的空間寫入新數(shù)據(jù)。如果DB buffer cache太小命中率會降低，會增加物理I/O。 2.3 合理設(shè)置 redo log buffer 和redo日志組 當(dāng)redo log files里的一組日志被寫滿時，在開啟歸檔的情況下會觸發(fā)ARCn進(jìn)程對日志進(jìn)行歸檔，并切換到下一組日志進(jìn)行循環(huán)寫。當(dāng)日志再次切換回該組日志但還沒有完成歸檔時，數(shù)據(jù)庫就會等待其完成歸檔再寫入新的日志，這將導(dǎo)致大量的性能下降。所以應(yīng)該適當(dāng)增加日志文件組的個數(shù)和每組日志的大小。但是，當(dāng)日志組個數(shù)很多時并且每組很小時會導(dǎo)致日志組頻繁的進(jìn)行切換，會觸發(fā)CKPT檢查點進(jìn)程，檢查點進(jìn)程會把scn寫進(jìn)數(shù)據(jù)文件頭和控制文件中，當(dāng)需要實例恢復(fù)時，會讀取控制文件中的最后一個檢查點位置作為起點，運用redo進(jìn)行前滾。檢查點進(jìn)程會觸發(fā)DBWn進(jìn)程將DB buffer cache中的臟塊頻繁寫到datafiles中，導(dǎo)取數(shù)據(jù)時先要將datafiles中的數(shù)據(jù)塊讀到DB buffer cache中，增長了SQL語句的執(zhí)行時間。所以，正確的設(shè)置好日志組的個數(shù)以及每組的大小很重要。 2.4 盡量減少排序 如果面臨order by 排序時，將要進(jìn)行PGA sort內(nèi)存排，如果數(shù)據(jù)量很大，PGA不能滿足內(nèi)存排所需空間時，就需要與磁盤上的臨時表空間進(jìn)行存儲分配交換，這個磁盤分配交換將要對臨時文件頻繁進(jìn)行寫和讀，因為對磁盤頻繁操作，導(dǎo)致系統(tǒng)性能大大降低。此時，需要將PGA優(yōu)化，如果不得不使用臨時文件時，就需要創(chuàng)建多個臨時文件，并發(fā)降低讀寫壓力，以達(dá)到優(yōu)化效果。 |
|
|
