? 最近單位要求寫技術(shù)總結(jié)，我根據(jù)《ORACLE9i_優(yōu)化設(shè)計(jì)與系統(tǒng)調(diào)整.》以及網(wǎng)上相關(guān)文章，主要結(jié)合自己的實(shí)際工作經(jīng)歷，寫了一篇《oracle 9i 應(yīng)用系統(tǒng)優(yōu)化》,本人水平有限，如果有誤的話請(qǐng)多多指正，避免誤導(dǎo)他人，同時(shí)也希望大家共享相關(guān)的經(jīng)驗(yàn)，讓我也學(xué)習(xí)學(xué)習(xí)。
寫這篇文章還是花了不少時(shí)間，如果轉(zhuǎn)載的話請(qǐng)注明出處。
Oracle9i 應(yīng)用系統(tǒng)優(yōu)化
1、優(yōu)化前提
應(yīng)用系統(tǒng)方案制定準(zhǔn)確，對(duì)應(yīng)用系統(tǒng)運(yùn)行環(huán)境分析合理、正確，在數(shù)據(jù)庫服務(wù)器性能、存儲(chǔ)空間、網(wǎng)絡(luò)帶寬等方面的配置能夠達(dá)到系統(tǒng)運(yùn)行要求。
2、優(yōu)化目標(biāo)
l?????? 響應(yīng)時(shí)間與吞吐量平衡
l?????? 臨界資源
2.1 響應(yīng)時(shí)間與吞吐量平衡
根據(jù)應(yīng)用類型的不同，性能優(yōu)化的目標(biāo)不同：
在線事務(wù)處理系統(tǒng)OLTP）把吞吐量定義為性能指標(biāo)；
決策支持系統(tǒng)（DSS）把響應(yīng)時(shí)間定義為性能指標(biāo)。
響應(yīng)時(shí)間
響應(yīng)時(shí)間=服務(wù)時(shí)間+等待時(shí)間
系統(tǒng)吞吐量
系統(tǒng)吞吐量指在給定的時(shí)間內(nèi)所完成的工作量。有以下兩種技術(shù)：
l???????? 以相同的資源來完成更多的工作（減少服務(wù)時(shí)間）；
l???????? 通過減少整個(gè)響應(yīng)時(shí)間來更快完成工作。
等待時(shí)間
當(dāng)競(jìng)爭(zhēng)增強(qiáng)的時(shí)候，某個(gè)任務(wù)的服務(wù)時(shí)間也許保持不變，但它的等待時(shí)間將增長。
??? 我們開發(fā)的系統(tǒng)一般為OLTP和DSS的復(fù)合系統(tǒng)，側(cè)重于OLTP,在硬件允許的情況下最好能夠?qū)⑦\(yùn)行數(shù)據(jù)庫、分析數(shù)據(jù)庫分離。
諸如 CPU、內(nèi)存、I/O容量、網(wǎng)絡(luò)帶寬等資源，都是減少時(shí)間的關(guān)鍵因素。性能好壞取決于以下因素：
l???????? 可用資源的數(shù)量
l???????? 需要該資源的客戶方的數(shù)目
l???????? 客戶方等待資源所消耗的時(shí)間
l???????? 客戶保持資源的時(shí)間長短
隨著請(qǐng)求單元的增加，服務(wù)時(shí)間也增加。為了處理這種情形，用戶可以選擇：
l???????? 通過限制請(qǐng)求的速率，從而維護(hù)可接受響應(yīng)時(shí)間
l???????? 還可通過增加資源數(shù)目，如CPU和硬盤（增加資源的前提是應(yīng)用系統(tǒng)設(shè)計(jì)良好，并且已經(jīng)做了充分的優(yōu)化）
3、優(yōu)化階段
從實(shí)際做的項(xiàng)目過程來看，除了系統(tǒng)安裝優(yōu)化外，系統(tǒng)優(yōu)化往往都是在系統(tǒng)實(shí)施、運(yùn)行時(shí)才考慮，其實(shí)到這階段做系統(tǒng)優(yōu)化的局限性比較大，因?yàn)橄到y(tǒng)架構(gòu)設(shè)計(jì)都成型、固化，大幅度調(diào)整設(shè)計(jì)的代價(jià)非常昂貴，一般只能在局部領(lǐng)域做優(yōu)化，只能通過重新分配內(nèi)存或優(yōu)化I/O來或多或少地提高性能，實(shí)際上優(yōu)化應(yīng)該貫穿系統(tǒng)設(shè)計(jì)、開發(fā)、安裝、測(cè)試、運(yùn)行整個(gè)過程。
3.1 設(shè)計(jì)階段
為了達(dá)到最佳的效果，優(yōu)化工作應(yīng)當(dāng)從設(shè)計(jì)階段進(jìn)行，而不是在系統(tǒng)實(shí)施后進(jìn)行。
在數(shù)據(jù)庫設(shè)計(jì)階段，個(gè)人認(rèn)為需要注意如下幾個(gè)方面：
l???????? 業(yè)務(wù)對(duì)象不能建立在系統(tǒng)表空間；
l???????? 索引表空間和業(yè)務(wù)表空間分開；
l???????? 將LOB類型的字段與其它的類型分開；
l???????? 根據(jù)應(yīng)用系統(tǒng)功能確定是否要采用冗余字段；
l???????? 正確的主鍵字段的選擇，建議采用數(shù)字，不推薦使用復(fù)合主鍵；
3.2 開發(fā)、測(cè)試階段
在開發(fā)實(shí)現(xiàn)階段，個(gè)人認(rèn)為需要注意如下幾個(gè)方面：
l???????? 執(zhí)行sql使用變量綁定的方式，盡可能的保留在共享內(nèi)存中，提高sql命中率；
l???????? 多表關(guān)聯(lián)查詢時(shí)采用有效的連接順序；
l???????? 盡可能的降低客戶端和服務(wù)器的網(wǎng)絡(luò)數(shù)據(jù)交互，某個(gè)業(yè)務(wù)功能點(diǎn)需要頻繁和數(shù)據(jù)庫交互的，建議采用存儲(chǔ)過程、臨時(shí)表實(shí)現(xiàn)；
l???????? 根據(jù)查詢條件建立必要的索引，查詢條件中使用oracle函數(shù)建立相對(duì)應(yīng)的函數(shù)索引,數(shù)據(jù)值范圍較小的采用位圖索引
l???????? 多張表關(guān)聯(lián)查詢時(shí)，有時(shí)可采用先查詢符合條件對(duì)應(yīng)的表中關(guān)鍵字，然后通過關(guān)鍵字再查詢對(duì)應(yīng)表中相關(guān)信息；
l???????? 頻繁訪問，較少更新的數(shù)據(jù)量較小的表信息可采用緩存的方式；
l???????? 在實(shí)現(xiàn)批量更新、插入時(shí)，要采用jdbc批量執(zhí)行方法，并且調(diào)整對(duì)應(yīng)的fetchsize參數(shù)。
在測(cè)試階段，應(yīng)該模擬實(shí)際運(yùn)行環(huán)境，測(cè)試出相關(guān)性能較差的功能點(diǎn)。
因?yàn)樵谠O(shè)計(jì)、開發(fā)階段往往因?yàn)椴l(fā)用戶少、數(shù)據(jù)量小，很多性能問題顯現(xiàn)不出來，如果軟件測(cè)試充分，很多性能問題都可以顯現(xiàn)出來，現(xiàn)在有很多優(yōu)秀的軟件測(cè)試工具，如LoadRunner、Robert在做壓力測(cè)試方面都比較方便、優(yōu)秀。
盡量將系統(tǒng)因程序設(shè)計(jì)、編碼不當(dāng)導(dǎo)致的性能問題暴露在測(cè)試階段。
3.3 安裝階段
???? 一般在安裝生產(chǎn)數(shù)據(jù)庫時(shí)，我們根據(jù)系統(tǒng)最早的規(guī)劃，集合軟、硬件環(huán)境，需要調(diào)整操作系統(tǒng)以及數(shù)據(jù)庫參數(shù)，
3.3.1操作系統(tǒng)交換區(qū)
交換區(qū)是Oracle的一項(xiàng)基本的要求。可以根據(jù)Oracle的發(fā)行要求來確定。一般交換區(qū)大小的要求是該服務(wù)器內(nèi)存的2倍至4倍之間，建議是內(nèi)存的4倍
3.3.2操作系統(tǒng)內(nèi)核參數(shù)
??????
shmmax | 共享內(nèi)存段，建議設(shè)大點(diǎn)， 達(dá)到最大SGA |
shmmin | 最小的共享內(nèi)存段. |
shmmni? | 共享內(nèi)存標(biāo)志符的數(shù)量. |
shmseg? | 一個(gè)進(jìn)程可分配的最大內(nèi)存段數(shù). |
shmall? | 最大可允許的內(nèi)存數(shù)，比SGA還要大. |
semmns | 信號(hào)量，跟ORACLE的PROCESS數(shù)有關(guān). |
semmsl ? | 一個(gè)信號(hào)量中最大的信號(hào)量數(shù). |
3.3.3 oracle 文件設(shè)置
當(dāng)服務(wù)器平臺(tái)已完成操作系統(tǒng)的安裝后，就應(yīng)該開始認(rèn)真的考慮下面的問題：
l???????? 是否采用裸設(shè)備
?
實(shí)際應(yīng)用的生產(chǎn)系統(tǒng)基本都是采用裸設(shè)備，使用裸設(shè)備對(duì)于讀寫頻繁的數(shù)據(jù)庫應(yīng)用來說，可以極大地提高數(shù)據(jù)庫系統(tǒng)的性能。
?
l???????? 安裝點(diǎn)的考慮
?
Oracle的安裝點(diǎn)就是指數(shù)據(jù)文件、日志文件和控制文件的安置路徑，為了使系統(tǒng)在以后運(yùn)行性能達(dá)到優(yōu)化，建議將數(shù)據(jù)文件、日志文件和控制文件的安置路徑與數(shù)據(jù)庫系統(tǒng)存放在不同的路徑上。最好將數(shù)據(jù)文件、日志文件和控制文件分別存放在不同的路徑。
?
l???????? SYSTEM表空間對(duì)應(yīng)數(shù)據(jù)文件
?
在自定義安裝會(huì)話中，建議你根據(jù)需要設(shè)置system表空間所對(duì)應(yīng)的數(shù)據(jù)文件的大小。一般要設(shè)置比默認(rèn)值的2倍。該數(shù)據(jù)文件的大小最好是在300MB至500MB間。因?yàn)閿?shù)據(jù)文件太小不利于系統(tǒng)的運(yùn)行。
?
l???????? 臨時(shí)表空間對(duì)應(yīng)的數(shù)據(jù)文件
?
臨時(shí)表空間對(duì)應(yīng)的數(shù)據(jù)文件可以根據(jù)將來系統(tǒng)存放的應(yīng)用的處理情況來定。比如系統(tǒng)將來可能要經(jīng)常進(jìn)程排序處理，則需要設(shè)置較大的臨時(shí)表空間，也可能需要再建立新的臨時(shí)表空間。這里建議臨時(shí)表空間的數(shù)據(jù)文件在100MB至300MB左右。
?
l???????? 回滾段表空間對(duì)應(yīng)的數(shù)據(jù)文件
?
9i回滾表空間都是系統(tǒng)管理，初始值也是根據(jù)系統(tǒng)事務(wù)量預(yù)估計(jì)的值，實(shí)際到運(yùn)行階段如果系統(tǒng)常出現(xiàn)ora-01555錯(cuò)誤的時(shí)候，可能就需要增加回滾表空間的大小。
?
l???????? 日志文件的大小
?
日志文件的大小對(duì)于Oracle系統(tǒng)的運(yùn)行也是相當(dāng)重要。默認(rèn)值是太小。實(shí)際根據(jù)事務(wù)繁忙預(yù)估計(jì)日志大小，沒有固定的具體值范圍，建議重做日志切換時(shí)間不能過短也不能過長，一般在20－40分鐘左右。該參數(shù)可以在系統(tǒng)運(yùn)行期間根據(jù)數(shù)據(jù)庫系統(tǒng)日志切換時(shí)間重新調(diào)整，控制文件的大小。
?
l???????? 數(shù)據(jù)庫塊的大小
?
如果你的應(yīng)用系統(tǒng)是OLTP的話，可以采用較小的數(shù)據(jù)庫塊。如果是DSS類型的應(yīng)用系統(tǒng)，則可以設(shè)置較大的數(shù)據(jù)庫塊，目前Oracle產(chǎn)品所允許的數(shù)據(jù)庫塊可以是2KB至64KB之間。無論你選擇較大的塊或較小的塊，它的值都必須是2的整數(shù)倍，比如2048,4096,8192等。但需要注意的是，如果操作系統(tǒng)為64位，則可選擇較大的塊。
?
?
l???????? 字符集的選擇
?
字符集是Oracle系統(tǒng)專門支持的一項(xiàng)技術(shù)。詳細(xì)請(qǐng)參考另外的章節(jié)。一般不要與另外的已經(jīng)存放的Oracle系統(tǒng)的字符集產(chǎn)生沖突即可。但如果你的環(huán)境是一個(gè)新的平臺(tái)，不需要與其它平臺(tái)進(jìn)行數(shù)據(jù)交換的話，建議選擇默認(rèn)的字符集。這樣可以利于將來的修改。
?
3.3.4數(shù)據(jù)庫啟動(dòng)參數(shù)
sga_max_size | 例程存活期間所占用的系統(tǒng)全局區(qū)的最大大小，一般為物理內(nèi)存的1/2-1/3 |
shared_pool_size | 指定共享池的大小，共享池包含：共享游標(biāo)、存儲(chǔ)的過程、控制結(jié)構(gòu)和并行執(zhí)行消息緩沖區(qū)等對(duì)象，較大的值用于改善多用戶系統(tǒng)的性能，該參數(shù)調(diào)整不能過大，會(huì)增加管理負(fù)擔(dān)和latch 的開銷，一般是在200M-500M左右 |
db_cache_size | 該參數(shù)指定數(shù)據(jù)緩沖區(qū)的大小，原則上時(shí)越大越好，
取代了8i中的db_block_size * db_block_buffers |
log_buffer | 重做日志緩沖區(qū)大小，該參數(shù)設(shè)置大沒有意義，
Oracle推薦log_buffer最大為cpu_count乘以128KB或512KB中最大值 |
processes | 系統(tǒng)用戶進(jìn)程的最大數(shù)量，該參數(shù)設(shè)置為系統(tǒng)最繁忙時(shí)估計(jì)并發(fā)用戶數(shù) |
large_pool_size | 如果不設(shè)置MTS，通常在 RMAN 、OPQ 會(huì)使用到，但是在10M --50M應(yīng)該差不多了。可以考慮為 session * (sort_area_size + 2M)。 |
Java_pool_size | 它用于存放java代碼，若不使用java，建議設(shè)置為30M |
pga_aggregate_target | 程序全局區(qū)大小,
1.對(duì)于OLTP系統(tǒng)PGA_AGGREGATE_TARGET? = （<Total Physical Memory > * 80%） * 20%
2.對(duì)于DSS系統(tǒng)PGA_AGGREGATE_TARGET? = （<Total Physical Memory > * 80%） * 50% |
timed_statistics | 建議將timed_statistics 設(shè)置為true,否則無法查看到準(zhǔn)確的統(tǒng)計(jì)信息（9i版本后的設(shè)置為true對(duì)系統(tǒng)性能影響較小，千分之一） |
?
上述參數(shù)基本是初始估計(jì)值，在運(yùn)行階段可能會(huì)根據(jù)實(shí)際運(yùn)行情況再調(diào)整。
?
3.4 運(yùn)行階段
這也是實(shí)際優(yōu)化工作最多的階段，個(gè)人認(rèn)為運(yùn)行階段優(yōu)化的真正工作是解決因?yàn)閷?shí)際運(yùn)行數(shù)據(jù)庫參數(shù)設(shè)置不當(dāng)、表、索引統(tǒng)計(jì)信息不準(zhǔn)確，執(zhí)行路徑不當(dāng)?shù)葘?dǎo)致的性能問題。
????? 優(yōu)化工作應(yīng)該作為日常工作的一部分，而不是等到用戶反映系統(tǒng)慢，系統(tǒng)宕機(jī)時(shí)才去優(yōu)化，那時(shí)已經(jīng)是亡羊補(bǔ)牢，為時(shí)有點(diǎn)晚，從實(shí)際項(xiàng)目來看，往往都是應(yīng)用程序編寫的sql、表、索引統(tǒng)計(jì)信息不準(zhǔn)確，執(zhí)行路徑不當(dāng)而導(dǎo)致的性能問題，個(gè)人認(rèn)為一般的sql調(diào)優(yōu)還是有章可循的,基本三步： 查找、分析、優(yōu)化。
3.4.1查找
3.4.1.1 非實(shí)時(shí)查找
??? 查找工具常用的就是statspack,該工具的安裝、使用比較簡(jiǎn)便。
????腳本路徑${oracle_home}/rdbms/admin目錄下，常用腳本如下：
spdrop.sql?? | ?刪除腳本，丟棄統(tǒng)計(jì)分析的相關(guān)包、視圖、表、同義詞等對(duì)象(首次創(chuàng)建無須執(zhí)行) |
spcreate.sql | ?創(chuàng)建腳本，生成統(tǒng)計(jì)分析的相關(guān)包、視圖、表、同義詞等對(duì)象（首次執(zhí)行前建議創(chuàng)建一個(gè)統(tǒng)計(jì)用的表空間） |
spreport.sql | 生成報(bào)告記錄sql ,生成的報(bào)告文件在系統(tǒng)當(dāng)前路徑下，
文件名默認(rèn)為：sp_開始快照號(hào)_結(jié)束快照號(hào).lst? |
sprepsql.sql? | 分析相關(guān)快照中的sql執(zhí)行計(jì)劃。 |
sppurge.sql? | 刪除在兩個(gè)快照號(hào)之間包括本身的所有統(tǒng)計(jì)分析數(shù)據(jù)。 |
sptrunc.sql? | 截取statspack統(tǒng)計(jì)分析的相關(guān)數(shù)據(jù)? 在統(tǒng)計(jì)分析的對(duì)應(yīng)用戶perfstat下執(zhí)行 |
?
執(zhí)行時(shí)間：
統(tǒng)計(jì)時(shí)生成兩次快照，一般在30-40分鐘左右
????執(zhí)行方法：
?????????用sys登陸sqlplus后間隔對(duì)應(yīng)時(shí)間執(zhí)行兩次 exec statspack.snap;
統(tǒng)計(jì)結(jié)果視圖：
stats$snapshot | 快照相關(guān)信息； select snap_id,snap_time from stats$snapshot; |
stats$sqltext | 快照統(tǒng)計(jì)sql信息,查詢統(tǒng)計(jì)sql(statspack報(bào)告中sql過長會(huì)被截掉）select sql_text? from stats$sqltext where hash_value=查詢值 and last_snap_id=begin_snap_id order by piece; |
?
3.4.1.2 實(shí)時(shí)查找
?如果需要實(shí)時(shí)的查找性能隱患的相關(guān)sql,通過v$session_wait,v$session,v$sqltext_with_newlines三張動(dòng)態(tài)視圖就可以基本查找到相關(guān)的sql，腳本如下：
select?sql_text?,sw.event
from?v$sqltext_with_newlines?st,v$session?se,v$session_wait?sw
where?st.address=se.sql_address?and?st.hash_value=se.sql_hash_value
and?se.sid?=sw.sid?and??
(sw.event?=?'buffer?busy?waits'?or
??sw.event?=?'enqueue'?or
??sw.event?=?'free?buffer?waits'?or
??sw.event?=?'global?cache?freelist?wait'?or
??sw.event?=?'latch?free'?or
??sw.event?=?'log?buffer?space'?or
??sw.event?=?'parallel?query?qref?latch'?or
??sw.event?=?'pipe?put'?or
??sw.event?=?'write?complete?waits'?or
??sw.event?like?'library?cache%'?or
??sw.event?like?'log?file?switch%'?
)??
order?by?st.hash_value,st.piece;?
3.4.2分析
分析報(bào)告?zhèn)€人一般主要關(guān)注top 5 event以及相關(guān)的讀邏輯塊、物理塊、執(zhí)行次數(shù)較多的sql,實(shí)際上更多的側(cè)重在sql分析上
一般常見的top 5 事件如下：
db file sequential read | 等待事件，一般問題出現(xiàn)在讀索引上，建議將業(yè)務(wù)表空間和索引表空間分開存儲(chǔ)在不同的物理卷下，以提高磁盤的I/O性能。 |
db file scattered read | 建議程序中盡量避免使用全表掃描的語句，或者可以增大db_file_multiblock_read_count的值，提高全表掃描一次讀取數(shù)據(jù)塊的速度，減少磁盤I/O。 |
db file parallel write | 說明DBWR進(jìn)程正等待把緩沖區(qū)的內(nèi)容并行寫入數(shù)據(jù)文件中去,等待將一直持續(xù)到所有的I/O全部完成。建議增大初始化參數(shù)中的db_writer_processes的值 |
log file sync | 說明任何時(shí)候一個(gè)事物提交時(shí)，它將通知LGWR將LOG_BUFFER寫入日志文件，如果此部分占用時(shí)間較長，應(yīng)減少COMMIT的次數(shù)，建議將重做日志放到較快的磁盤上進(jìn)行存儲(chǔ)。 |
log file parallel write | 等待事件，和上面一樣建議將重做日志放到較快的磁盤上進(jìn)行存儲(chǔ)。 |
提取出sql以后就可以進(jìn)行分析，主要采用分析執(zhí)行計(jì)劃的方式。個(gè)人一般喜歡如下的方式進(jìn)行分析：
l???????? 生成計(jì)劃表（初次）
以sys用戶執(zhí)行腳本${oracle_home}/rdbms/admin/utlxplan.sql，
l???????? 創(chuàng)建公用同義詞，方便在每個(gè)用戶下生成執(zhí)行計(jì)劃（初次）
Create public synonym plan_table for plan_table;
Grant all on plan_table to public;
l???????? 每次分析時(shí)設(shè)置sqlplus環(huán)境變量
Set timing on
Set autotrace traceonly
l???????? 查看相關(guān)sql執(zhí)行計(jì)劃
其他客戶端軟件pl/sql developer,toad 分析執(zhí)行計(jì)劃都比較方便。
l???????? 執(zhí)行計(jì)劃路徑解釋
常見路徑解釋：
Full Table Scans | 全表掃描、無可用索引 |
Index Unique Scans | 索引唯一掃描 |
IndexRange Scans | 索引范圍掃描 |
IndexRange Scans Descending | 索引降序范圍掃描 |
Index Skip Scans | 索引跳躍掃描 |
Full Scans | 全索引掃描 |
Fast Full Index Scans | 快速全索引掃描 |
Index Joins | 索引連接 |
Bitmap Joins | 位圖連接 |
?
常見連接解釋：
Nested Loops | 會(huì)循環(huán)外表（驅(qū)動(dòng)表），逐個(gè)比對(duì)和內(nèi)表的連接是否符合條件。
基于Cost的Oracle優(yōu)化器(CBO)會(huì)自動(dòng)選擇較小的表做外表。 | 優(yōu)點(diǎn)：在驅(qū)動(dòng)表比較小，內(nèi)表比較大，而且內(nèi)外表的連接列有索引的時(shí)候比較好，嵌套循環(huán)連接比其他連接方法有優(yōu)勢(shì),它可以快速地從結(jié)果集中提取第一批記錄,而不用等待整個(gè)結(jié)果集完全確定下來。
缺點(diǎn)：如果內(nèi)部行源表(讀取的第二張表（內(nèi)表）已連接的列上不包含索引,或者索引不是高度可選時(shí), 嵌套循環(huán)連接效率是很低的。 |
SORT- merge JOIN | 將兩表的連接列各自排序然后合并，只能用于連接列相等的情況，適合兩表大小相若的情況 | 優(yōu)點(diǎn)：在缺乏數(shù)據(jù)的選擇性或者可用的索引時(shí),或者兩個(gè)源表都過于龐大(超過記錄數(shù)的5%)時(shí),排序合并連接將比嵌套循環(huán)連更加高效。
局限性：排列合并連接只能用于等價(jià)連接排列合并連接需要臨時(shí)的內(nèi)存塊,以用于排序( 如果SORT_AREA_SIZE設(shè)置得太小的話)。這將導(dǎo)致在臨時(shí)表空間占用更多的內(nèi)存和磁盤I/O |
HASH JOIN | 在其中一表的連接列上作散列，因此只有另外一個(gè)表做排序合并，
只有基于代價(jià)的優(yōu)化器才可以使用哈希連接。 | 當(dāng)缺少有用的索引時(shí)，哈希連接比嵌套循環(huán)連接更加有效。哈希連接可能比排序合并連接更快，，哈希連接使用內(nèi)存資源，并且當(dāng)用于排序內(nèi)存不足時(shí)，會(huì)增加臨時(shí)表空間的I/O（這將使這種連接方法速度變得極慢 |
3.4.3優(yōu)化
???? Oracle運(yùn)行階段優(yōu)化的更多是對(duì)sql的優(yōu)化，個(gè)人理解工作主要是：
?????? 分析性能較差sql;
調(diào)整性能較差的sql的實(shí)現(xiàn)方式，協(xié)助程序員更改相關(guān)程序;
對(duì)相關(guān)的查詢條件建立合理的索引;
根據(jù)需要合理的更新表、索引的程序信息;
3.4.3.1 oracle 優(yōu)化器
l???????? 優(yōu)化器優(yōu)化方式
Oracle的優(yōu)化器共有兩種的優(yōu)化方式,即基于規(guī)則的優(yōu)化方式(Rule-Based Optimization,簡(jiǎn)稱為RBO)和基于代價(jià)的優(yōu)化方式(Cost-Based Optimization,簡(jiǎn)稱為CBO)。
A、RBO方式：優(yōu)化器在分析SQL語句時(shí),所遵循的是Oracle內(nèi)部預(yù)定的一些規(guī)則。比如我們常見的,當(dāng)一個(gè)where子句中的一列有索引時(shí)去走索引。
B、CBO方式：依詞義可知,它是看語句的代價(jià)(Cost)了,這里的代價(jià)主要指Cpu和內(nèi)存。優(yōu)化器在判斷是否用這種方式時(shí),主要參照的是表及索引的統(tǒng)計(jì)信息。統(tǒng)計(jì)信息給出表的大小、有少行、每行的長度等信息。這些統(tǒng)計(jì)信息起初在庫內(nèi)是沒有的,是你在做analyze后才出現(xiàn)的,很多的時(shí)侯過期統(tǒng)計(jì)信息會(huì)令優(yōu)化器做出一個(gè)錯(cuò)誤的執(zhí)行計(jì)劃,因些我們應(yīng)及時(shí)更新這些信息。在Oracle8及以后的版本,Oracle推薦用CBO的方式。
l???????? 優(yōu)化器的優(yōu)化模式(Optermizer Mode)
Rule:走基于規(guī)則的方式。
?
Choose:默認(rèn)的情況下Oracle用的是這種方式，不建議修改該參數(shù)。指的是當(dāng)一個(gè)表或索引有統(tǒng)計(jì)信息,則走CBO的方式,如果表或索引沒統(tǒng)計(jì)信息,表又不是特別的小,而且相應(yīng)的列有索引時(shí),那么就走索引,走RBO的方式。
?
First Rows:它與Choose方式是類似的,所不同的是當(dāng)一個(gè)表有統(tǒng)計(jì)信息時(shí),它將是以最快的方式返回查詢的最先的幾行,從總體上減少了響應(yīng)時(shí)間。
?
All Rows:也就是我們所說的Cost的方式,當(dāng)一個(gè)表有統(tǒng)計(jì)信息時(shí),它將以最快的方式返回表的所有的行,從總體上提高查詢的吞吐量。沒有統(tǒng)計(jì)信息則走基于規(guī)則的方式。
3.4.3.2 常見優(yōu)化問題
l???????? ????明明有索引，表的數(shù)據(jù)量也非常大，執(zhí)行路徑不走索引
??????? 對(duì)應(yīng)表、索引的統(tǒng)計(jì)信息有誤，可以通過dba_tables,dba_indexes視圖中的num_rows查看對(duì)應(yīng)表、索引的統(tǒng)計(jì)信息，如果有誤，重新統(tǒng)計(jì)。
??????? Analyze table table_name compute statistics
??????? For table? /*統(tǒng)計(jì)表*/
??????? For all indexed columns? /*統(tǒng)計(jì)有索引的表列*/
l???????? ????統(tǒng)計(jì)后性能反而變差
??????? 雖然oracle推薦采用CBO方式，但有時(shí)對(duì)應(yīng)的執(zhí)行路徑并不是最佳，所
??????? 以我們?cè)诮y(tǒng)計(jì)信息時(shí)只有針對(duì)性的統(tǒng)計(jì)相關(guān)表、索引信息。
??????? 一般有兩種處理方法
??????? a. 刪除對(duì)應(yīng)的統(tǒng)計(jì)信息
??????? Analyze table table_name delete statistics
?????? ?Analyze index index_name delete statistics
?
??????? 對(duì)應(yīng)的系統(tǒng)包dbms_stats也可實(shí)現(xiàn)生成、刪除表、索引的統(tǒng)計(jì)信息
??????? b. 使用hints 明確指定對(duì)應(yīng)的執(zhí)行路徑
3.4.3.3 hints
常用的幾種hints如下：
Hint Syntax? | Description? |
優(yōu)化方式 | |
/*+ ALL_ROWS */ ? | 表明對(duì)語句塊選擇基于開銷的優(yōu)化方法,并獲得最佳吞吐量,使資源消耗最小化 |
/*+ CHOOSE */ ? | 表明如果數(shù)據(jù)字典中有訪問表的統(tǒng)計(jì)信息,將基于開銷的優(yōu)化方法,并獲得最佳的吞吐量;
表明如果數(shù)據(jù)字典中沒有訪問表的統(tǒng)計(jì)信息,將基于規(guī)則開銷的優(yōu)化方法; |
/*+ FIRST_ROWS */ ? | 表明對(duì)語句塊選擇基于開銷的優(yōu)化方法,并獲得最佳響應(yīng)時(shí)間,使資源消耗最小化，在做分頁查詢時(shí)有時(shí)可以改善性能 |
/*+ RULE */ ? | 表明對(duì)語句塊選擇基于規(guī)則的優(yōu)化方法 |
處理方法 | |
/*+ AND_EQUAL(table index) */ ? | 提示明確進(jìn)行執(zhí)行規(guī)劃的選擇,將幾個(gè)單列索引的掃描合起來.
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306' |
/*+ CLUSTER(table) */ ? | Explicitly chooses a cluster scan to access the specified table. ? |
/*+ FULL(table) */ ? | 表明對(duì)表選擇全局掃描的方法
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT' |
/*+ INDEX(table index) */ ? | 表明對(duì)表選擇索引的掃描方法.
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M'; |
/*+ INDEX_ASC(table index) */ ? | 表明對(duì)表選擇索引升序的掃描方法 |
/*+ INDEX_COMBINE(table index) */ ? | 為指定表選擇位圖訪問路經(jīng),如果INDEX_COMBINE中沒有提供作為參數(shù)的索引,將選擇出位圖索引的布爾組合方式.
例如:
??? SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE ? |
/*+ INDEX_DESC(table index) */ ? | 表明對(duì)表選擇索引降序的掃描方法. |
/*+ INDEX_FFS(table index) */ ? | 對(duì)指定的表執(zhí)行快速全索引掃描,而不是全表掃描的辦法. |
/*+ NO_INDEX(table index) */ ? | 表明優(yōu)化器不采用掃描相對(duì)應(yīng)的索引 |
/*+ ROWID(table) */ ? | 明確表明對(duì)指定表根據(jù)ROWID進(jìn)行訪問.
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA' AND EMP_NO='SCOTT'; |
/*+ USE_CONCAT */ ? | 對(duì)查詢中的WHERE后面的OR條件進(jìn)行轉(zhuǎn)換為UNION ALL的組合查詢. |
連接操作 | |
/*+ USE_HASH(table) */ ? | 將指定的表與其他行源通過哈希連接方式連接起來.
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO; |
/*+ USE_MERGE(table) */ ? | 將指定的表與其他行源通過合并排序連接方式連接起來.
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO; |
/*+ USE_NL(table) */ ? | 將指定表與嵌套的連接的行源進(jìn)行連接,并把指定表作為內(nèi)部表.
? |
4、參考資料
l???????? 《ORACLE9i_優(yōu)化設(shè)計(jì)與系統(tǒng)調(diào)整》
l???????? http://oracle.chinaitlab.com/induction/398193.html