Oracle_sql优化基础——优化器总结
優(yōu)化器的基礎(chǔ)：
1、Oracle里的優(yōu)化器：
? ?優(yōu)化器是Oracle數(shù)據(jù)庫中內(nèi)置的一個核心子系統(tǒng)，優(yōu)化器的目的就是按照一定的判斷原則來得到它認為目標(biāo)sql在當(dāng)前情形下最高效的執(zhí)行路徑，也就是說是為了得到目標(biāo)sql的執(zhí)行計劃。
? ?
Oracle數(shù)據(jù)庫的優(yōu)化器分為：RBO和CBO兩種類型：
? ?RBO：基于規(guī)則的優(yōu)化器（在得到sql執(zhí)行計劃時，RBO所用的判斷原則為一組內(nèi)置的規(guī)則）
? ?CBO：基于成本的優(yōu)化器（在得到sql執(zhí)行計劃時，CBO所用的判斷原則為成本，它會從目標(biāo)sql諸多可能的執(zhí)行路線中選擇成本值最小的一條來為其執(zhí)行計劃） ?
注意：
①：從Oracle10G開始，RBO已不再被Oracle支持，但是RBO的相關(guān)實現(xiàn)代碼并么有從Oracle代碼中移除，這也就是說在Oracle11GR2中依然可以通過修改優(yōu)化器模式或使用rule hint來繼續(xù)使用RBO；
②：RBO的缺點很多，其中Oracle很多很好的特性、功能均不能再RBO下使用，因為他們均不被RBO支持。
? ?有如下情形的即使修改了優(yōu)化器模式或使用了RULE hint Oracle依然不會使用RBO（而是會強制使用CBO）
? ?①：目標(biāo)sql中涉及的對象有IOT（index organized table）
? ?②：目標(biāo)sql中涉及的對象有分區(qū)表
? ?③：使用了并行查詢或者并行DML
? ?④：使用了星型連接
? ?⑤：使用了哈希鏈接
? ?⑥：使用了索引快速全掃描
? ?⑦：使用了函數(shù)索引
? ?
?SQL> set autotrace traceonly ? ?----開啟sql跟蹤，查看執(zhí)行計劃 ?
? ?
1.1、基于規(guī)則的優(yōu)化器-RBO?
? ??
在當(dāng)前會話中將優(yōu)化器模式修改為RULE，表示當(dāng)前的session中啟用了RBO
SQL> alter session set optimizer_mode='RULE';
注意：在使用RBO的情況下可以通過等價改寫目標(biāo)sql（加0或者空字符串的方式）來調(diào)整該sql的執(zhí)行計劃
如：select * from emp_temp where mgr>100 and deptno+0>100; ? （deptno類型為number）
說明：
?①：當(dāng)目標(biāo)sql有兩條或者兩條以上的執(zhí)行計劃的等級值相同時，我們確實可以通過調(diào)整相關(guān)對象在數(shù)據(jù)字典緩存中的緩存順序來影響RBO對于其執(zhí)行計劃的選擇；
?②：如果RBO僅憑目標(biāo)sql各條執(zhí)行路徑等級值的大小就可以選擇出執(zhí)行計劃，那么無論怎么調(diào)整相關(guān)對象在該sql的sql文本中的位置，對于該sql最終的執(zhí)行計劃都不會有任何影響；
1.2、基于成本的優(yōu)化器-CBO（從Oracle10G開始解析目標(biāo)sql時默認使用CBO）
? ?基于成本的優(yōu)化器是指Oracle根據(jù)相關(guān)對象的統(tǒng)計信息計算出來的一個值，它實際上就是目標(biāo)sql對應(yīng)執(zhí)行步驟的I/O CPU 和網(wǎng)絡(luò)資源的消耗量的一個估算值；
? ?
2、CBO的一些基本概念：
? ?①：cardinality（集的優(yōu)勢）：是CBO特有的概念，它是指指定集合所包含的記錄數(shù)；它實際上表示對目標(biāo)sql的某個具體執(zhí)行步驟的執(zhí)行結(jié)果所包含記錄數(shù)的估算。
? ?②：可選擇率：也是CBO特有的概念，它是指施加指定謂詞條件后返回結(jié)果集的記錄數(shù)占未施加任何謂詞條件的原始結(jié)果集的記錄數(shù)的比率。可選擇率的取值范圍是0~1，它的值越小，就表名可選擇性越好，毫無疑問，可選擇率為1時的可選擇性是最差的；
? ?③：可傳遞性：而是CBO特有的概念，其含義是指CBO可能會對原目標(biāo)SQL做簡單的等價改寫，即在原目標(biāo)SQL中加上根據(jù)sql現(xiàn)有的謂詞條件推算出來的新謂詞條件，這么做的目的是提供更多的執(zhí)行路徑給CBO做選擇，進而增加得到更高效執(zhí)行計劃的可能性；
?在Oracle里，可傳遞性又分為如下三種情形：
? ?①：簡單謂詞傳遞
? ? ? 比如原目標(biāo)sql的謂詞條件是“t1.c1=t2.c1 and t1.c1=10”,則CBO可能會在這個謂詞條件中額外地加上“t2.c1=10”，即被修改成
? ? ? t1.c1=t2.c1 and t1.c1=10 and t2.c1=10?
? ?②：連接謂詞傳遞
? ? ? 比如原來目標(biāo)sql中的謂詞條件是“t1.c1=t2.c1 and t2.c1=t3.c1”,則CBO可能會在這個謂詞條件中額外地加上“t1.c1=t3.c1”,即被
? ? ? 修改成“t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1=t3.c1”
? ?③：外連接謂詞傳遞
? ? ? 比如原目標(biāo)sql中的謂詞條件是“t1.c1=t2.c1(+) and t1.c1=10”,則CBO可能會在這個謂詞條件中額外加上“t2.c1(+)=10”,即被修改成
? ? ? “t1.c1(+) and t1.c1=10 and t2.c1(+)=10”
3、CBO的局限性：
①：CBO會默認目標(biāo)sql語句where條件中出現(xiàn)的各個列之間是獨立的，沒有關(guān)聯(lián)關(guān)系 ??
②：CBO會假設(shè)所有的目標(biāo)sql都是單獨執(zhí)行的，并且互不干擾
③：CBO對直方圖統(tǒng)計信息有諸多限制
④：CBO在解析多表關(guān)聯(lián)的目標(biāo)sql時，可能會漏選正確的執(zhí)行計劃
#######################################################################
4、優(yōu)化器的模式
? ?在Oracle數(shù)據(jù)庫中，優(yōu)化器的模式是由參數(shù)optimizer_mode的值來決定的，optimizer_mode的值可能是rule ?choose ?first_rows_n(n=1,10,100,1000) first_rows 或all_rows；
?
?①：rule ?
? ? ?rule表示Oracle將使用RBO來解析目標(biāo)sql，此時目標(biāo)sql中所涉及的各個對象的統(tǒng)計信息對于RBO來說將沒有任何作用。
?
?②：choose
? ? ?choose是Oracle9i的optimizer_mode的默認值，它表示Oracle在解析目標(biāo)sql時到底是使用RBO還是使用CBO取決于該sql中所涉及的表對象是否有統(tǒng)計信息。
? ? ?（只要改sql中所涉及的表對象中有一個有統(tǒng)計信息，那么Oracle在解析該sql時就會使用CBO，如果該sql中所涉及的表對象均沒有統(tǒng)計信息，那么Oracle就不會使用RBO）
?
?③：first_rows_n(n=1,10,100,1000) ?
? ? ?optimizer_mode可以是上面其中的任意一個值，其含義是指當(dāng)optimizer_mode的值為first_rows_n(n=1,10,100,1000)時，Oracle會使用CBO來解析目標(biāo)sql，且此時CBO在計算該sql的各條執(zhí)行路徑的成本值時的側(cè)重點在于以最快的響應(yīng)速度返回頭n (n=1,10,100,1000)條記錄。 ??
?
?④：first_rows
? ? ?first_rows是一個在Oracle9i已經(jīng)過時的參數(shù)，它表示Oracle在解析目標(biāo)sql時會聯(lián)合使用CBO和RBO。這里的聯(lián)合是大多數(shù)情況下，first_rows還是會使用CBO來解析目標(biāo)sql；
?
?⑤：all_rows
? ? ?all_rows是Oracle10G以后后續(xù)版本數(shù)據(jù)庫版本中optimizer_mode的默認值，它表示Oracle會使用CBO來解析目標(biāo)sql，且此時CBO在計算該sql的各條 執(zhí)行路徑的成本值時的側(cè)重點在于最佳的吞吐量（即最小的系統(tǒng)I/O 和 CPU資源的消耗量）；
《結(jié)果集》
結(jié)果集是指包含指定執(zhí)行結(jié)果的集合，對于優(yōu)化器而言（無論是RBO還是CBO）結(jié)果集和目標(biāo)sql執(zhí)行計劃的執(zhí)行步驟相對應(yīng)，一個執(zhí)行步驟所產(chǎn)生的執(zhí)行結(jié)果就是該執(zhí)行步驟所對應(yīng)的輸出結(jié)果集；
轉(zhuǎn)載于:https://blog.51cto.com/fengfeng688/1952652
總結(jié)
以上是生活随笔為你收集整理的Oracle_sql优化基础——优化器总结的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Sundown EK：漏洞利用工具中的抄
- 下一篇： 《深入理解C++11:C++ 11新特性