1、Oracle 高性能SQL優(yōu)化,航天信息股份有限公司,什么是Oracle性能管理,Oracle性能管理是一種利用已證實過的方法，反復(fù)試驗、判斷和實現(xiàn)優(yōu)化方案的循序漸進(jìn)的過程。 SQL語句優(yōu)化是性能管理的重要組成部分。,概述,Oracle性能管理的類型（1）,主動的性能管理？ 以一種高性能的體系結(jié)構(gòu)思想指導(dǎo)設(shè)計和開發(fā)完善的系統(tǒng)。 它也指定期監(jiān)控系統(tǒng)的性能，注意各種苗頭，在它們成為實際問題前加以解決。,概述,Oracle性能管理的類型（2）,被動的性能管理？ 涉及性能評估、故障排除、優(yōu)化以及在現(xiàn)有硬件和軟件體系結(jié)構(gòu)內(nèi)對環(huán)境進(jìn)行的優(yōu)化。它在出現(xiàn)問題時進(jìn)行處理。 相對于所獲得的性能收益，其代價一般較高

2、。,概述,什么是優(yōu)化,優(yōu)化是有目的地更改系統(tǒng)的一個或多個組件，使其滿足一個或多個目標(biāo)的過程。 對Oracle來說，優(yōu)化是進(jìn)行有目的的調(diào)整組件以改善性能，即增加吞吐量，減少響應(yīng)時間。,概述,SQL優(yōu)化的目標(biāo),去掉不必要的大型表的全表掃描 緩存小型表的全表掃描 檢驗優(yōu)化索引的使用 檢驗優(yōu)化的連接技術(shù) 檢查子查詢,概述,不同優(yōu)化階段性能收益,設(shè)計階段,開發(fā)階段,測試階段,運行維護階段,生命周期,效果,概述,SQL調(diào)整的障礙,對特定的SQL生成器的調(diào)整 調(diào)整不可再用的SQL語句 來自管理方面的抵制 來自SQL程序員的抵制,概述,SQL語句處理過程,SQL執(zhí)行,SQL語句的解析過程,調(diào)入庫緩存 (內(nèi)存)

3、 檢驗安全性 檢驗語法 重新格式化查詢 (查詢重寫),SQL執(zhí)行,減少SQL解析的技術(shù),將所有的SQL語句放置在存儲過程中 在SQL語句中避免使用直接量,SQL執(zhí)行,生成執(zhí)行計劃,對Oracle來說查詢速度和查詢效率是兩個完全不同的概念。Oracle有兩個優(yōu)化目標(biāo)，這兩個目標(biāo)分別反映了為了滿足SQL的要求而使用的各自不同的通用手段。 最大速度 以最短的時間返回結(jié)果集 (Oracle的first_rows優(yōu)化器模式) 最小資源占用 使用少量的機器和磁盤資源 (all_rows優(yōu)化器模式),SQL執(zhí)行,SQL語句性能評估的方法,SQL語句的執(zhí)行計劃 執(zhí)行SQL語句所需要的時間,SQL執(zhí)行,執(zhí)行SQ

4、L語句所需要的時間,SQL執(zhí)行,查看SQL語句的執(zhí)行計劃,SQL執(zhí)行,SQL語句的執(zhí)行計劃,表訪問方式 全表掃描 散列獲取 ROWID訪問,索引訪問方式 索引范圍掃描 單個索引掃描 降序索引范圍掃描 And-equal過濾器,連接操作方式 嵌套循環(huán)連接 散列連接 排序合并連接 Star連接 Connect by連接,SQL執(zhí)行,SQL語句的優(yōu)化器,優(yōu)化器種類: 基于規(guī)則的優(yōu)化器-RBO 基于成本的優(yōu)化器-CBO 優(yōu)化器模式: Rule Choose First_rows All_rows,SQL優(yōu)化器,基于規(guī)則的優(yōu)化器-RBO,RBO不使用任何表或索引的統(tǒng)計數(shù)據(jù),而是用試探法來確定到達(dá)數(shù)據(jù)的最

5、佳訪問路徑。RBO根據(jù)迭代過程來生成執(zhí)行計劃，并且檢查from子句中的每個表，以及在該查詢中每個表與其他表的所有連接方式。每一個可行的路徑根據(jù)它們所要消耗的成本進(jìn)行排序，并選擇成本最低的路徑。,SQL優(yōu)化器,RBO選擇低成本的依據(jù),01）ROWID讀取 02）使用簇連接的單獨記錄 03）使用散列簇主鍵的單獨記錄 04）使用主鍵的單獨記錄 05）簇連接 06）散列簇主鍵 07）索引簇主鍵 08）復(fù)合主鍵 09）單字段索引 10）索引字段的結(jié)合范圍查找 11）排序合并連接 12）索引字段上的MAX或MIN 13）索引字段上的ORDER BY 14）全表掃描,SQL優(yōu)化器,基于規(guī)則優(yōu)化器的特性,總是

6、使用索引。 總是從驅(qū)動表開始。 只有在不可避免的情況下，才使用全表掃描。 任何索引都可以有時會選擇一個并非最理想的索引來對查詢服務(wù)。,SQL優(yōu)化器,基于規(guī)則的SQL語句優(yōu)化,更改基于規(guī)則的驅(qū)動表。 如果基于規(guī)則的優(yōu)化器沒有使用正確的索引，可通過索引提示強制使用我們想要使用的索引，或者在索引中混入數(shù)據(jù)類型的方式禁用那些不想的查詢中使用的索引。,SQL優(yōu)化器,基于成本的優(yōu)化器-CBO,Oracle基于成本的優(yōu)化器的創(chuàng)建目的是為基于規(guī)則的優(yōu)化提供更加復(fù)雜的替代方式。 CBO 優(yōu)化器需要了解表和索引統(tǒng)計數(shù)據(jù)的細(xì)節(jié), SQL優(yōu)化根據(jù)成本進(jìn)行排序,并選擇成本最低的路徑。,SQL優(yōu)化器,表和索引的統(tǒng)計數(shù)據(jù),

7、表數(shù)據(jù) 記錄的數(shù)目 物理數(shù)據(jù)塊的數(shù)目 索引數(shù)據(jù) 索引中惟一值的數(shù)目 索引中值的分布 索引的可選擇性 索引簇因素,SQL優(yōu)化器,全表掃描 索引訪問 快速完全索引掃描,SQL訪問數(shù)據(jù)塊的方法,優(yōu)化SQL表訪問,基于規(guī)則的優(yōu)化器如果探測到 可用的索引，總是選擇使用索引，否則使用全表掃描。 基于成本的優(yōu)化器擁有有關(guān)表中數(shù)據(jù)的信息，當(dāng)估計到全表掃描的成本低于索引訪問的成本時，即使存在索引，它也選擇全表掃描。 ALL_ROWS優(yōu)化器模式更傾向于全表掃描，更適用于批量處理的查詢。 FIRST_ROWS優(yōu)化器模式確保以最快的速度返回記錄。如果目標(biāo)表中不存在可用索引，那么它將執(zhí)行全表掃描。,優(yōu)化器如何選擇全表掃

8、描,優(yōu)化SQL表訪問,使用NULL條件的查詢 對沒有索引字段的查詢 帶有l(wèi)ike條件且掩碼開始端使用%字符 帶有Not Equals條件的查詢 內(nèi)置函數(shù)使索引無效 使用ALL_ROWS提示 使用并行提示,可能導(dǎo)致全表掃描的語法,優(yōu)化SQL表訪問,更改表訪問方式,將子查詢替換為標(biāo)準(zhǔn)的連接 重新書寫SQL語句以更改表訪問方法 添加或刪除索引 使用提示強制改變表訪問方法 在話會級更改optimizer_goal參數(shù) 在實例級optimizer_mode,優(yōu)化SQL表訪問,SQL優(yōu)化過程概述,定位 ：定位不友好的、影響大的SQL語句 解釋：用 PL/SQL或Explain Plan獲得執(zhí)行計劃 調(diào)整：

9、使用索引、提示和查詢重寫等手段對SQL語句進(jìn)行優(yōu)化。關(guān)注點是：執(zhí)行計劃和運行時間。,過程概述,調(diào)整SQL語句,提示: 添加SQL提示會修改執(zhí)行計劃 索引: 添加B樹索引去掉全表掃描 重新書寫: 改變執(zhí)行計劃，尤其是在使用RBO時更改FROM子句中表的順序時更明顯。 位圖索引: 查詢中where子句中提及的所有低基數(shù)字段添加位圖索引。 PL/SQL：SQL被替換為對PL/SQL軟件包的調(diào)用，軟件包中含執(zhí)行查詢所需的存儲過程。,過程概述,評估全表掃描的合法性,對于原始排序的表讀取少于記錄數(shù)40%的查詢應(yīng)使用索引范圍掃描。反之，使用全表掃描。 對于未排序的表讀取少于記錄數(shù)7%的查詢應(yīng)使用索引范圍掃描

10、。反之，使用全表掃描。,全表掃描和并行查詢,對小表的全表掃描,select DISTINCT A.FUNC_DM,A.FUNC_MC,A.URL,A.FUNC_DM_F,A.DESC,A.YJCZBZ from HTJS.HT_GNRKB A, HTJS.HT_ROLE_MENU B where A.SWJG_JC = 5 and B.ROLE_ID = 報稅業(yè)務(wù)操作員 and not A.FUNC_DM = bs and substr(A.FUNC_DM, 0, 2) = bs and (B.YXBZ = Y and substr(A.FUNC_DM, 0, length(B.FUNC_DM

11、) = B.FUNC_DM) and A.FUNC_DM not in (select A.FUNC_DM from HTJS.HT_GNRKB A, HTJS.HT_ROLE_MENU B where B.ROLE_ID = 報稅業(yè)務(wù)操作員 and B.YXBZ = N and substr(A.FUNC_DM, 0, length(B.FUNC_DM) = B.FUNC_DM) ;,全表掃描和并行查詢,該語句的執(zhí)行計劃,全表掃描和并行查詢,避免全表掃描的方法,添加B樹索引 添加位圖索引 添加基于函數(shù)的索引 強制CBO使用帶有index提示的索引,全表掃描和并行查詢,Oracle并行查詢,持

12、久并行機制不推薦 alter table table_name parallel degree N ; 單個查詢的并行機制 select /*+ FULL(emp) PARALLEL(emp,5) */ ename from emp;,全表掃描和并行查詢,設(shè)置最優(yōu)的并行度,服務(wù)器上CPU的數(shù)量 存儲表所在磁盤數(shù) 如果根據(jù)分區(qū)實施并行化，則其中一個因素是基于分區(qū)劃分的將被分區(qū)訪問的分區(qū)的數(shù)目。 如果并行DML操作使用全局索引維護，所有全局索引中事務(wù)處理空閑表的最小值需要更新。 最優(yōu)的并行度可以安全地設(shè)置為N-1，而N代表SMP或MPP簇中處理器的數(shù)量,全表掃描和并行查詢,并行查詢和表連接,帶有并

13、行查詢的嵌套循環(huán)連接 排序合并連接和并行查詢 并行化散列連接,全表掃描和并行查詢,帶有并行查詢的嵌套循環(huán)連接,在嵌套循環(huán)連接中，通常使用索引對表進(jìn)行連接。然而，可以創(chuàng)建一個執(zhí)行計劃，其中該執(zhí)行計劃調(diào)用嵌套循環(huán)連接，一般只對連接中的一個驅(qū)動表執(zhí)行全表掃描。在大多數(shù)情況下，索引訪問更快!,全表掃描和并行查詢,并行查詢和表連接,排序合并連接和并行查詢,合并連接總是對表執(zhí)行全表掃描，所以排序合并連接可以提供最理想的并行查詢。排序合并連接最適合產(chǎn)生非常大結(jié)果集的查詢，可能適合于沒有where子句的大表連接，或沒有可用索引對表進(jìn)行連接的查詢。,全表掃描和并行查詢,并行查詢和表連接,并行化散列連接,Orac

14、le將驅(qū)動表讀入hash_area_size中的一個RAM隊列中，生成內(nèi)存數(shù)組，并使用專用的散列方法將內(nèi)存數(shù)組與更大的表連接起來。 對于等值連接操作，散列連接可能勝過嵌套循環(huán)連接，特別是驅(qū)動表小到可以完全裝入hash_area_size的情況下。如果驅(qū)動表太大，散列連接會把臨時段寫入TEMP表空間中，導(dǎo)致查詢速度降低。,全表掃描和并行查詢,并行查詢和表連接,Oracle的排序,Oracle的排序通常發(fā)生在如下情況： SQL語句中包含order by子句 SQL中包含group by子句 SQL中包含select distinct子句 創(chuàng)建索引時 SQL中包含union或minus SQL優(yōu)化器

15、調(diào)用排序合并連接,優(yōu)化SQL語句排序,添加索引避免排序,避免全表掃描和不必要排序的最好方法是添加索引。使用first_rows模式時，Oracle通常使用索引代替排序操作，從而避免order by子句。,優(yōu)化SQL語句排序,不必要的排序,缺失索引 查詢需要字段索引 排序合并連接 就會執(zhí)行排序以連接關(guān)鍵字，通常嵌套循環(huán)連接好 使用distinct 索引中使用distinct子句通常會調(diào)用索引以去掉重復(fù)記錄,優(yōu)化SQL語句排序,SQL查詢中使用提示的重要原則(1),仔細(xì)檢查提示語法 最好使用完整注釋語法，如使用/*+ hint*/ 如果在查詢中為表指定了別名，那么不能使用表名稱 select /*

16、+ index(e,dept_idx)*/ ename,deptno from emp e; 不要在提示中使用模式名稱，否則提示被忽略。 select /*+ index(scott.emp,dept_idx) */ ename from emp;,使用提示進(jìn)行優(yōu)化,SQL查詢中使用提示的重要原則(2),檢驗提示 如果提示指定不可用的訪問路徑，提示將被忽略。如索引范圍掃描上指定一個parallel提示，則提示被忽略。提示和查詢之間的矛盾并不是顯而易見的，下面的emp表的ename不存在索引時，則 Select /*+ first_rows */ ename,deptno from emp or

17、der by ename;,使用提示進(jìn)行優(yōu)化,使提示無效條件,Cluster與非簇表一同使用 Hash與非簇表一同使用 Hash_aj不存在子查詢 Index指定索引不存在 Index_combine不存在位圖索引 Merge_aj不存在子查詢 Parallel調(diào)用的不是TABLE ACCESS FULL計劃 Push_subq不存在子查詢 Star事實表中存在不恰當(dāng)索引 Use_concat在where子句中不存在多個or條件 Use_nl表中不存在索引,使用提示進(jìn)行優(yōu)化,優(yōu)化器提示(1),first_rows提示 是一種基于成本的優(yōu)化方法，目的是提供最快的反應(yīng)時間，要保證查詢中所涉及的表及

18、索引擁有統(tǒng)計數(shù)據(jù)。 一般來說，optimizer_mode=choose模式的SQL都應(yīng)該使用first_rows和rule提示進(jìn)行測試，觀察運行性能是否有所提高。,使用提示進(jìn)行優(yōu)化,優(yōu)化器提示(2),all_rows提示 是一種基于成本的優(yōu)化方法，目的是提供最佳吞量和最小的資源消耗。傾向于使用全表掃描，而且不適用于OLTP數(shù)據(jù)庫。在基于規(guī)則的數(shù)據(jù)庫中添加提示時，要保證查詢中所涉及的表及索引擁有統(tǒng)計資料。,使用提示進(jìn)行優(yōu)化,優(yōu)化器提示(3),rule提示 在懷凝CBO生成了非優(yōu)化的執(zhí)行計劃時，通常在調(diào)整SQL語句時先嘗試使用rule提示。,使用提示進(jìn)行優(yōu)化,表連接提示(1),use_nl提示

19、強制對目標(biāo)表執(zhí)行嵌套循環(huán)連接。與其他連接不同，只需要驅(qū)動表的名稱。嵌套循環(huán)連接是最古老的連接方式，它幾乎總是與基于規(guī)則的優(yōu)化方式共同使用。 select /*+ use_nl(e) */ e.ename, hiredate, m from emp e,bonus b where e.ename=b.ename;,使用提示進(jìn)行優(yōu)化,表連接提示(2),use_hash提示 散列連接是Oracle用以從驅(qū)動表向RAM區(qū)中裝載記錄的方法。當(dāng)這兩個表都非常大的情況下，散列連接通常與并行查詢結(jié)合使用！ select /*+ use_hash(e,b) parallel(e,4) */ e.ename,hi

20、redate,m. from emp e,bonus b where =b.ename ; 散列連接通?？煊谇短籽h(huán)連接，特別是在驅(qū)動表已經(jīng)在查詢的where子句中過濾，只乘下少量記錄的情況。Use_hash提示非常挑剔，必須滿足很多條件，如統(tǒng)計數(shù)據(jù)。,使用提示進(jìn)行優(yōu)化,表連接提示(3),use_merge提示 強制執(zhí)行一個排序合并操作，排序合并操作通常與并行查詢結(jié)合使用，因為排序合并連接對表執(zhí)行全表掃描。 排序合并連接最適用于生成大型結(jié)果集的查詢，如日常報表和表細(xì)節(jié)總結(jié)查詢，或用來處理不使用連接主鍵索引的表。 select /*+ use_merge(a,b) parallel(

21、e,4) */ e.ename, hiredate, m from emp e,bonus b where e.ename=b.ename ;,使用提示進(jìn)行優(yōu)化,表連接提示(4),star提示 star提示強制使用星型查詢計劃。前提是查詢中存在至少三個表，而且在事實表中存在恰當(dāng)?shù)乃饕１葌鹘y(tǒng)的連接方式快得多，傳統(tǒng)的連接方式先將最小的引用表與事實表連接，再將其化的引用表與中間生成表連接。,使用提示進(jìn)行優(yōu)化,表連接提示(4)-續(xù),星型連接工作過程： Oracle先服務(wù)于小維表查詢，并將結(jié)果連接到笛卡爾生產(chǎn)表中，其中這個表存儲Oracle內(nèi)存中。這個虛擬表包含來自所有相關(guān)維表的所有記錄。這個虛擬表的

22、主鍵是來自維表所有主鍵的組合。如果這個主鍵與事實表中組合索引相匹配，這個查詢將很快處理。一旦確定了引用表的數(shù)目，則Oracle將基于事實表執(zhí)行中間表的嵌套循環(huán)連接。,使用提示進(jìn)行優(yōu)化,表連接提示(4)-續(xù),使用star提示必須滿足下列條件: 必須存在連接在一起的至少三個表，其中有一個大的事實表和幾個小維表。 在事實表的字段上必須存在一個索引，這些字段將是每個表的連接主鍵。從Oracle8i開始，需要使用位圖索引，而不是連接在一起的索引。 必須檢驗執(zhí)行計劃，以確保在執(zhí)行連接時使用嵌套循環(huán)連接操作。,使用提示進(jìn)行優(yōu)化,表反連接提示（1）,merge_aj提示 在使用全表訪問比索引訪問更好的情況下，

23、可以在NOT IN子查詢中使用merge_aj提示以便執(zhí)行反連接。 SelectDNAME FromDEPT Where DEPTNO not in (select /*+ merge_aj */DEPTNO fromEMP where JOB=SALESMAN)；,使用提示進(jìn)行優(yōu)化,表反連接提示（1）-續(xù),使用NOTIN的條件: 如果在子查詢的字段中確實不存在空值，而又在子查詢中使用了提示，外層查詢塊生成大量記錄的情況下，才考慮使用NOT IN子查詢。 根據(jù)連接的類型，可以考慮使用hash_aj或merge_aj提示執(zhí)行反連接。而只有在NOT IN子句中要求的字段有非空限制的時候，反連接ha

24、sh_aj或merge_aj才起作用。,使用提示進(jìn)行優(yōu)化,表反連接提示（2）,hash_aj提示 hash_aj提示放置于NOT IN子查詢中用來在希望執(zhí)行散列連接時，執(zhí)行散列反連接。,使用提示進(jìn)行優(yōu)化,Index提示(1),使用Index提示的原則 如果表名或索引名拼寫錯誤，查詢將不使用提示。 Select /*+ index(erp,dept_idx) */ from emp; 表名在提示中必須指定 Select /*+ index(dept_idx) */ from emp; 如果在查詢中使用表別名，那么提示必須使用表別名。 Select /*+ index(emp,dept_idx)

25、*/ from emp e; 索引名稱是可選的，如果沒有指定，優(yōu)化器將指定表中“最佳的”索引，但不推薦！ Select /*+ index(e) */ from emp e;,使用提示進(jìn)行優(yōu)化,指定索引提高性能的例子,select DISTINCT FP_DMfrom htjs.FP_SFDwhere FP_DM like %3300% and SWJG_DM = 133010300; -IDX_FP_SFD_FPDM_SFLB_FPQSHM; -INDEX_FP_SFD(SWJG_DM,SFLB,SFRQ);,使用提示進(jìn)行優(yōu)化,指定索引前的執(zhí)行計劃,使用提示進(jìn)行優(yōu)化,指定索引提高性能的例子,

26、select /*+ index(fp_sfd,index_fp_sfd) */ DISTINCT FP_DM from htjs.FP_SFD where FP_DM like %3300% and SWJG_DM = 133010300 ;,使用提示進(jìn)行優(yōu)化,指定索引后該例的執(zhí)行計劃,使用提示進(jìn)行優(yōu)化,Index提示(2),and_equal提示 如果表擁有非惟一字段索引，而且你期望使用多個索引服務(wù)于該查詢，那么可以使用and_equal提示。and_equal提示將合并這些索引，并使這些單獨的索引操作時就像單個連鎖索引一樣。 and_equal提示需要指定目標(biāo)表名和至少兩個索引名，但索引

27、名不能超過五個。,使用提示進(jìn)行優(yōu)化,Index提示(2)-續(xù),Select /*+ and_equal(emp,job_idx,mgr_idx) */ ENAME, JOB, MGR From EMP Where Job=SALESMAN And Mgr=7628 ;,使用提示進(jìn)行優(yōu)化,Index提示(3),index_desc提示 index_desc提示要求在范圍掃描中使用降序索引。在使用MAX內(nèi)置函數(shù)計算字段的最大值時，index_desc提示將提供更好的性能。 Select /*+ index_desc(emp,sal_idx) */ ENAME, Max(SALARY) From E

28、MP Group by ENAME ;,使用提示進(jìn)行優(yōu)化,Index提示(4),index_combine提示 index_combine提示強制使用位圖索引作為表的訪問路徑。index_combine提示要求以優(yōu)化器對兩個位圖索引都執(zhí)行ROWID交集操作。實際操作中最好指定表名和兩個索引名。 Select /*+ rule(emp) index_combine(emp,dept_bit,job_bit) */ ENAME,JOB,DEPTNO, MGR From EMP Where JOB=SALESMAN And DEPTNO=30 ; -這種位圖合并方法將在很大程度上降低大表的執(zhí)行時間,

29、使用提示進(jìn)行優(yōu)化,Index提示(5),index_ffs提示 索引快速完全掃描可以在不訪問任何記錄的情況下完成查詢。優(yōu)化器使用多塊讀取掃描索引中的所有數(shù)據(jù)塊。 可以同時使用index_ffs提示和parallel提示，使索引快速完全掃描的速度更快。,使用提示進(jìn)行優(yōu)化,Index提示(5)續(xù),index_ffs提示 create index Dept_job_idx on Emp(job); select ename,job,deptno,mgr from emp where job=SALESMAN; select /*+ index_ffs(emp,dept_job_idx) */ ena

30、me,job,deptno,mgr from emp where job=SALESMAN;,使用提示進(jìn)行優(yōu)化,Index提示(6),use_concat提示 use_concat提示要求為查詢中所有OR條件使用union all執(zhí)行計劃，并將這個查詢重新書寫為多個查詢。 當(dāng)一個查詢對位圖索引字段使用多個OR條件時，Oracle將自動使用位圖索引。 如果有SQL查詢的where子句中存在大量的OR條件，那么通常需要使用use_concat提示。,使用提示進(jìn)行優(yōu)化,Index提示(6)-續(xù),use_concat提示 select ENAME from EMP where deptno=10 or

31、 sal5000 or job=CLERK;,供選擇的解決辦法: 1)有的索引字段基數(shù)較低，可以在deptno、sal、job上創(chuàng)建三個位圖索引，并生成一個位圖合并執(zhí)行計劃。 2）調(diào)用use_concat提示，將查詢分解為三個獨立的B樹索引掃描。,使用提示進(jìn)行優(yōu)化,Parallel提示,對于全表掃描來說，parallel提示要求表查詢以并行模式執(zhí)行，同時，并行查詢從動進(jìn)程的數(shù)量由degree參數(shù)決定。 select /*+ FULL(emp) PARALLEL(emp,) */ ename, sal from emp ; 需要同時使用full提示和parallel提示以確保調(diào)用全表掃描。如果優(yōu)

32、化器沒有使用全表掃描，則parallel提示被忽略。,使用提示進(jìn)行優(yōu)化,表訪問提示(1),full提示 使用full提示最常見的情況是，已經(jīng)確定索引范圍掃描要讀取大量表中數(shù)據(jù)塊，而并行全表掃描將提高查詢的速度。,使用提示進(jìn)行優(yōu)化,表訪問提示(2),簇表提示 簇通常用于兩個表之間存在著一對多的關(guān)系，而大多數(shù)查詢都是從主表向成員表訪問的情況。將主表和成員表的記錄存儲在相同的數(shù)據(jù)塊，從而一個單獨的數(shù)據(jù)塊輸入輸出可以讀取來自兩個表的記錄。,使用提示進(jìn)行優(yōu)化,表訪問提示(2)-續(xù),hash提示 select /*+ hash */ e.ename, d.deptno from emp e, dept d

33、 where e.deptno=d.deptno and deptno=20 ;,cluster提示 select /*+ cluster */ e.ename, d.deptno from emp e, dept d where e.deptno=d.deptno and deptno=20 ;,使用提示進(jìn)行優(yōu)化,ordered 提示,在基于成本的優(yōu)化器中，ordered提示要求按from子句中指定的順序進(jìn)行連接，而指定from子句中的第一個表作為驅(qū)動表。通常與其他提示結(jié)合使用，以確保多個表按照正確的順序連接。從而免避過多的評估所花費的時間。 例如： select /* ordered us

34、e_hash(emp,dept) use_nl(sal,bonus */ emp.ename, dept.deptno from emp, dept, salgrade, bonus where ,使用提示進(jìn)行優(yōu)化,子查詢中的提示,SQL語句中的每一個子查詢都可以使用提示來改進(jìn)執(zhí)行計劃。位于外部查詢的指定表的提示不會干涉子查詢，子查詢指定表的提示也不會干涉外部查詢。 selectA.EMPNO from DEPT B,EMP A,SALGRADE C where exists ( select /*+ INDEX(A) */ A.EMPNO from DEPT B,EMP A,SALGRADE

35、 C where A.DEPTNO=B.DEPTNO ) and A.DEPTNO=B.DEPTNO And A.JOBclerk ;,使用提示進(jìn)行優(yōu)化,調(diào)用基于規(guī)則的優(yōu)化器,設(shè)置init.ora參數(shù)optimizer_mode=rule 會話層使用alter session set optimizer_mode=rule 為基于成本的SQL添加rule提示：/*+ rule*/,基于規(guī)則優(yōu)化器調(diào)整,使用choose作為默認(rèn)優(yōu)化模式時存在的問題,使用choose作為默認(rèn)優(yōu)化模式時,根據(jù)CBO統(tǒng)計資料的存在與否來評估是使用RBO還是CBO。 使用choose模式的真正問題是，只要任何表或索引包含

36、了統(tǒng)計資料，choose模式就假定你期望使用CBO。,基于規(guī)則優(yōu)化器調(diào)整,默認(rèn)優(yōu)化器模式,選擇具有最優(yōu)總體性的默認(rèn)優(yōu)化器模式的目標(biāo)是：最小化人工SQL調(diào)整的數(shù)量。 決定是否使用基于規(guī)則的優(yōu)化模式作為默認(rèn)值的過程是：首先使用rule模式運行一天生產(chǎn)數(shù)據(jù)庫，接著使用first_rows模式再運行一天，然后根據(jù)最終用戶的性能反饋和STATSPACK的性能測量，最后決定基于規(guī)則的優(yōu)化是否可以作為合適的默認(rèn)優(yōu)化器模式。,基于規(guī)則優(yōu)化器調(diào)整,基于規(guī)則的優(yōu)化器,驅(qū)動表位置 在RBO中，驅(qū)動表是from子句中的最后一個表。 select /*+ rule */ Emp.ename, Emp.deptno, B

37、m from Emp, Bonus where Emp.ename=bonus.ename；,驅(qū)動表和表基數(shù) 驅(qū)動表應(yīng)當(dāng)是返回最小數(shù)目記錄的表，而不一定是擁有最少記錄數(shù)的表。 select customer_name from customer,order where customer.cust_nbr= order.cust_nbr and order_status=backordered;,基于規(guī)則優(yōu)化器調(diào)整,有關(guān)調(diào)整基于規(guī)則查詢的提示,在RBO中進(jìn)行優(yōu)化是通過重新書寫查詢更改執(zhí)行計劃來實現(xiàn)的。主要建議是： 1）重新排序表名 2）重新排序布爾謂詞 更改where子句中語句的順序。Oracl

38、e將從SQL語句的底層按照與由AND分開的布爾表達(dá)式相反的順序?qū)QL進(jìn)行解析。 3）添加基于成本的提示 4）仔細(xì)評估連接方法 如果使用RBO，必須處理嵌套循環(huán)連接。盡管在RBO中可以使用排序合并連接，但應(yīng)當(dāng)將所有執(zhí)行排序合并連接的RBO查詢，替換為使用Oracle并行查詢的基于成本的等價訪問。,基于規(guī)則優(yōu)化器調(diào)整,基于規(guī)則的優(yōu)化器無效的情況,在基于規(guī)則的優(yōu)化模式中，所有的索引都有同等的級別！ 在有些情況下,基于規(guī)則的優(yōu)化器無法選擇優(yōu)化的執(zhí)行計劃。這通常是因為RBO無法使用高級的索引結(jié)構(gòu)和并行查詢。即使在新功能的幫助下，RBO有時因為優(yōu)化器沒有意識到表和索引中惟一值的數(shù)量，可能選擇了非優(yōu)化的索

39、引為查詢服務(wù)。 基于規(guī)則的優(yōu)化器不識別位圖索引和基于函數(shù)的索引，將會導(dǎo)致全表掃描。,基于規(guī)則優(yōu)化器調(diào)整,統(tǒng)計數(shù)據(jù)和基于成本的優(yōu)化,使用基于成本優(yōu)化的一個前提條件是，存在表和索引的統(tǒng)計數(shù)據(jù)。 對于表來說，統(tǒng)計數(shù)據(jù)包括記錄數(shù)和表中記錄平均長度等信息。 索引的統(tǒng)計數(shù)據(jù)包括：有關(guān)索引中惟一值的數(shù)量、索引中元素的數(shù)量，以及在表空間中物理存儲等方面的信息。,基于成本優(yōu)化器調(diào)整,基于成本優(yōu)化和SQL 優(yōu)化,Oracle8i基于成本的優(yōu)化器已經(jīng)可以與基于規(guī)則優(yōu)化相兼容，那么許多SQL調(diào)整專業(yè)人員就可以使用基于成本的默認(rèn)優(yōu)化器模式，再對個體查詢進(jìn)行調(diào)整，從而達(dá)到最快的執(zhí)行計劃。 大多數(shù)SQL調(diào)整專業(yè)人員在遇到可

40、疑的SQL語句時，做的第一件事就是添加rule提示，并察看生成的執(zhí)行計劃是否更加有效。 在讀取任何記錄之前，基于成本的優(yōu)化器必須創(chuàng)建一個執(zhí)行計劃，這個執(zhí)行計劃告訴Oracle訪問目標(biāo)表和索引的順序，CBO首先對不同的數(shù)據(jù)訪問路徑的成本進(jìn)行評估，然后選擇成本最低的路徑。,基于成本優(yōu)化器調(diào)整,基于成本的表連接,通用原則： 嵌套循環(huán)連接和散列連接應(yīng)當(dāng)始終使用所有表上的索引，除了使用排序合并連接以外，在服務(wù)于表連接時，一般不要調(diào)用全表掃描。,優(yōu)化步驟： 1）執(zhí)行計劃中查找全表掃描。如果發(fā)現(xiàn)了則嘗試做的第一件事是，使用index提示或rule提示，從而強制對所有的表使用索引。 2）嘗試計算使用不同連接技

41、術(shù)的查詢的執(zhí)行時間。,基于成本優(yōu)化器調(diào)整,基于成本的表連接與Oracle版本,在Oracle8i之前版本的基于成本的優(yōu)化器中存在的一個缺陷是，如果在查詢中使用了多于4個表的連接，那么基于成本的優(yōu)化器幾乎總是為其中的一個表調(diào)用全表掃描。 在Oracle8i及以后版本中，仍然可能有這樣的非優(yōu)化的查詢計劃。,基于成本優(yōu)化器調(diào)整,帶有子查詢的SQL語句的優(yōu)化（1）,首先應(yīng)該檢查where子句，并確定該子查詢是非關(guān)聯(lián)子查詢還是關(guān)聯(lián)子查詢。 標(biāo)準(zhǔn)連接通常比子查詢快，因此，要仔細(xì)評估所有個體SQL查詢，以決定是否子查詢可以更改為標(biāo)準(zhǔn)連接。,基于成本優(yōu)化器調(diào)整,帶有子查詢的SQL語句的優(yōu)化（2）,Select

42、 * from student where student_id in (select student_id from registration where grade=A ) ;,select student.* from student, registration where student.student_id= registration.student_id and registration.grade=A ;,基于成本優(yōu)化器調(diào)整,優(yōu)化復(fù)雜的布爾查詢（1）,布爾謂詞中的復(fù)合OR條件 1：OR中包含相同字段的數(shù)值 2：OR條件引用不同的字段,基于成本優(yōu)化器調(diào)整,優(yōu)化復(fù)雜的布爾查詢（2）,

43、對于所有字段都相同的OR類型，基于成本的執(zhí)行計劃將取決于是否存在索引。 如果一個查詢的位圖索引字段存在多個OR條件，那么Oracle將自動使用位圖索引。,Select /*+ first_rows */ ENAME From EMP Where Deptno in (10,20,30,40) And ( job=CLEAK or job=SALESMAN or job=SECRETARY ) ；,基于成本優(yōu)化器調(diào)整,布爾謂詞中的復(fù)合AND條件(1),Select/*+ first_rows */ ENAME， JOB， DEPTNO From EMP Where Deptno = 10 and

44、 Sal 5000 and Job=CLERK;,三個字段都有B樹索引，CBO選擇具有選擇最具有選擇性的索引。 如果以上三個字段上建立三個位圖索引，則調(diào)用了BITMAPCONVERSION執(zhí)行計劃的變更。,基于成本優(yōu)化器調(diào)整,布爾謂詞中的復(fù)合AND條件(2),select/*+ index_combine(emp,emp_deptno_bit,emp_JOB_bit,emp_sal_bit) */ ENAME， JOB， DEPTNO from EMP where Deptno = 10 and Sal 5000 and Job=CLERK;,有位圖索引時使用index_combine 提示將

45、對表強制使用位圖訪問路徑。如果語句中沒有這個提示，優(yōu)化器將選擇表訪問成本最低的位圖索引的布爾組合。使用這個提示指導(dǎo)優(yōu)化器執(zhí)行兩個位圖的ROWID 交集操作。在實際操作中，最好指定表名稱和提示中的兩個索引名稱。,基于成本優(yōu)化器調(diào)整,基于規(guī)則的優(yōu)化器和基于成本的優(yōu)化器,RBO非常穩(wěn)定，而且結(jié)果可以預(yù)測；而CBO更加智能，但是通常不可預(yù)測。 隨著CBO一個新版本的產(chǎn)生，使用RBO的數(shù)據(jù)庫的數(shù)量就會降低。 如果使用基于成本的優(yōu)化器作為默認(rèn)值，可通過使用rule提示來覆蓋所指定的查詢。 如果使用基于規(guī)則的優(yōu)化器作為默認(rèn)值，可通過添加基于成本提示并分析所選定的表索引來調(diào)整SQL查詢。 在RBO中，from

46、子句中表的順序決定表的連接順序。From子句中最后一個表是驅(qū)動表 RBO中最常見的缺陷是，它無法了解表中每個索引的可選擇性。 where子句中布爾謂詞的順序也會影響RBO的行為。,基于規(guī)則優(yōu)化器調(diào)整,什么是索引,索引是建立在表的一列或多個列上的輔助對象，它有利于快速訪問表的數(shù)據(jù)。 索引由于其內(nèi)在的結(jié)構(gòu)，它具有某些內(nèi)在的開銷，這些開銷依賴于為了檢索由索引中ROWID指定的行所訪問的表中的塊數(shù)，并且這個開銷可能會超過進(jìn)行全表掃描的成本。,用索引優(yōu)化SQL,何時使用索引,建立索引的目的之一是減少I/O操作，如果當(dāng)一個查詢使用索引時相對于全表掃描執(zhí)行了更多的I/O操作，則使用索引的意義會明顯降低。 主

47、鍵：即主鍵約束，Oracle會自動建立惟一性索引，因此不要再為主鍵中的列建立索引了。,用索引優(yōu)化SQL,具有吸引力的索引,非惟一索引 惟一索引 位圖索引 基于函數(shù)的索引 反向鍵索引 降序索引 索引編排表,局部有前綴分區(qū)索引 局部無前綴分區(qū)索引 全局有前綴分區(qū)索引 散列分區(qū)索引 組合分區(qū)索引,用索引優(yōu)化SQL,怎樣建立最佳索引,建立索引不是一個很容易完成的任務(wù)，因為它完全依賴于應(yīng)用程序的數(shù)據(jù)查詢模式。 如果了解應(yīng)用程序，則問題不復(fù)雜；如果了解數(shù)據(jù)訪問的最常用的方法，則能夠建立最佳索引是肯定的；總之，必須確定最常使用列的列表，并且作出關(guān)于要建立的索引數(shù)量、需要的列組合以及將建立的索引的類型的決定。

48、,用索引優(yōu)化SQL,建立最佳索引需要回答的問題,特定表中的數(shù)據(jù)訪問的最常用的列組合是什么？ 打算在其上建立索引的一組給定列，其選擇性是什么？ 如果一些列始終有值并且相對惟一，則它們應(yīng)該是索引的前導(dǎo)列，為建立索引，按照可能具有惟一值的概率，對這些列進(jìn)行子程序排列。 在where子句中引用的所有列都需要進(jìn)行索引嗎？ 如果列具有很低的數(shù)據(jù)基數(shù)，并且或者可具有空值的話，則不需要。要有意識地從索引中去掉這樣的列。 索引所基于的表用于事務(wù)處理還是主要用于查詢？ 如果它是事務(wù)處理的表，則需要確定由于給出附加的索引對事務(wù)處理的潛在的負(fù)面影響。在較好的查詢性能和事務(wù)處理時的負(fù)面影響之間怎樣權(quán)衡？如果它是主要用于

49、查詢的表，則最好建立索引。 是建立單列索引與組合索引？ 如果了解應(yīng)用程序中經(jīng)常使用的列，則建立一個組合索引比分別建立單個索引更好。,用索引優(yōu)化SQL,索引不當(dāng)?shù)睦?DELETE FROM HTJS.RZ_WLRZ_FPDKL_MXWHERE NSRSBH = 440301192193934 AND (FPDM = 3300043140 AND FPHM = 02436911 OR FPMW =4647450*5-70051/188-77171*/4832+39889760-74+5884); - RZ_WLRZ_FPDKL_MX主鍵索引為FPID、NSRSBH,用索引優(yōu)化SQL,該例的執(zhí)行計

50、劃,用索引優(yōu)化SQL,索引不當(dāng)?shù)睦?SELECT * FROM FX_QY_NSRXX WHERE NSRSBH = :b1 AND FX_ZT = 1 ; -PK_FX_QY_NSRXX(SWJG_DM,NSRSBH); -IDX_FX_QY_NSRXX_SWJG_DM_CSSJ;,用索引優(yōu)化SQL,該例的執(zhí)行計劃,用索引優(yōu)化SQL,通過添加索引來消除排序,消除排序的最好的方法之一就是添加索引。當(dāng)運行在first_rows模式時，Oracle總是會用排序操作場所中的索引來分析order by子句。 有時，Oracle會用全索引掃描來代替磁盤排序操作。Oracle優(yōu)化器會考慮查詢返回的記錄數(shù)

51、目，而且會在排序成本與通過索引檢索記錄的成本之間做出權(quán)衡。在大多數(shù)情況下，Oracle只會在有大量的記錄將被檢索出來時考慮全索引掃描，而且要滿足這個查詢還必須進(jìn)行一次磁盤排序。,用索引優(yōu)化SQL,推薦使用索引的兩個原因,提高從數(shù)據(jù)表中檢索少量的數(shù)據(jù)集的速度。 預(yù)先排序結(jié)果集，這樣SQL的order by等子句就不會引發(fā)內(nèi)部排序了。,用索引優(yōu)化SQL,使索引無效的情況,在where子句中執(zhí)行一個函數(shù)會使索引失效。 使用不相等運算符也會使索引失效 like參數(shù)使用了通配符%指定了高層項 在使用RBO時，位圖索引和基于函數(shù)的索引都會被忽略。,用索引優(yōu)化SQL,特殊類型的B樹索引,位圖索引 過去認(rèn)為位

52、圖索引僅僅適用于非常少量的惟一數(shù)據(jù)值的數(shù)據(jù)列（少于50個數(shù)據(jù)值），現(xiàn)在位圖索引功能已增強，最近的研究表明這一數(shù)據(jù)值已擴大到1000了。 基于函數(shù)的索引BIF 反向（主）鍵索引 如果主鍵的值是按照上升的順序生成的，那么所有的插入操作都需要改變B樹中高一級的節(jié)點的數(shù)據(jù)塊。 通常反主鍵索引可以用于合成主鍵索引中。,用索引優(yōu)化SQL,帶有IN條件查詢的用法,SQL優(yōu)化器常常會執(zhí)行一個全表掃描，但可能會有許多不同的執(zhí)行計劃。 select ename from emp where job IN (manager,presedent) ;,select /*+ first_rows */ Ename fr

53、om Emp where Job=manager union all select Ename from Emp where job=presedent;,用索引優(yōu)化SQL,表連接類型（1）,等連接 兩個表中的一對記錄通過一個公共字段的匹配連接。 外部連接 是一個保存不完整記錄的連接，兩個表之間不存在匹配條件。 自連接 表和自身連接的特殊情況。 反連接 NOT IN或NOT EXISTS子句的子查詢中會用到。 半連接 返回滿足EXISTS子句的查詢記錄，即使條件右邊有多條記錄滿足子查詢中條件，該連接也不會復(fù)制謂詞左邊的記錄。,優(yōu)化表連接,表連接類型（2）,等連接 在任何一個引用了兩個或兩個以上

54、表的SQL語句中，在where子句中使用等式為表指定連接條件。 Oracle為等連接提供了三種連接方式，嵌套循環(huán)連接方式、散列連接方式和排序合并連接方式。,優(yōu)化表連接,表連接類型（3）,外部連接 查詢結(jié)果中將包含表中不匹配的字段。外部連接是通過在where子句的等式謂詞中放置一個加號（+）來實現(xiàn)的。 Select Emp.ename, Bm. From Emp, Bonus Where Emp.ename=bonus.ename(+) ；,優(yōu)化表連接,表連接類型（4）,反連接 子查詢中找到的任何記錄都不包含在結(jié)果集中的子查詢。 select /* first_rows */ ename, de

55、ptno from emp where ename not in (select ename from bonus ); where emp.ename=bonus.ename(+) and m is null代替,優(yōu)化表連接,表連接類型（4）-續(xù),反連接提示 表反連接提示 表反連接提示merge_aj和hash_aj有助于提高反連接的效果。在使用時通常要求NOT IN子句中的字段有非空限制！可以在很大程序上提高NOT IN查詢的性能。 合并反連接 是在NOT IN子查詢執(zhí)行的，以完成一個反連接，其中這個反連接傾向于使用全表掃描，而不是索引訪問。 注：盡量不要使用NOT IN子句（它調(diào)用子查詢

56、），并且盡量使用NOT EXISTS,優(yōu)化表連接,表連接類型（5）,半連接 Select /* first_rows */ dname From dept Where exists (select * from emp where dept.deptno=emp.deptno and m5000 )；,半連接的替代形式： Select distinct /*+ first_rows */ Dname From Dept, Emp Where Dept.deptno=emp.deptno And Em5000 ；,優(yōu)化表連接,表連接方法(1),嵌套循環(huán)連接 散列連接 排序合并連接 星型連接,優(yōu)化

57、表連接,表連接方法(2),嵌套循環(huán)連接 嵌套循環(huán)連接是最古老最基本的表連接方式，通常通過比較外部表的每一個主鍵和內(nèi)部表的每一個主鍵進(jìn)行操作。一般只有被連接表中至少有一個表支持索引的情況下，才考慮使用這種連接方式。 在使用CBO時，嵌套循環(huán)連接可以利用表大小的差異，通常選擇較小的表作為驅(qū)動表。如果在RBO時，from子句中最后一個表作為驅(qū)動表，以確保最小的表作為嵌套循環(huán)查詢的驅(qū)動表。,select /*+ rule*/ ename, dname from emp, dept where emp.deptno= dept.deptno and emp.deptno=10;,優(yōu)化表連接,表連接方法(

58、3),散列連接 通過將驅(qū)動表加載進(jìn)RAM，并使用散列技術(shù)以連接到第二個表，提高了等連接的速度. 如果表的大小不同，并且小表的大小接近hash_area_size中的可用內(nèi)存時，推薦使用散列連接。 use_hash提示將對指定表執(zhí)行散列連接。,select /*+ ordered use_hash(e,b) parallel(e,4) parallel(b,4) */ e.ename, hiredate, m. From bonus b, emp e where e.ename=b.ename ;,優(yōu)化表連接,表連接方法(4),排序合并連接 最簡單最古老的表連接方式之一。排序合并連接不使用索引，

59、對兩個表的訪問都是通過全表掃描實現(xiàn)的。在全表掃描進(jìn)行后，來自每個掃描的結(jié)果集進(jìn)行獨立排序，然后，經(jīng)過排序的結(jié)果集合并為查詢結(jié)果集。,select /*+ first_rows*/ ename, dname From DEPT, EMP where emp.deptno=dept.deptno and emp.deptno=10;,優(yōu)化表連接,表連接方法(4)-續(xù),在下列情況下才會調(diào)用排序合并連接： 1)在連接表字段中不存在可用索引 2)查詢將返回兩個表中大部分的數(shù)據(jù)塊 3)CBO認(rèn)為全表掃描比索引掃描執(zhí)行得快 use_merge提示會強制調(diào)用排序合并操作，排序合并操作通常與并行查詢結(jié)合使用。 如果兩個表的deptno字段添加索引后，且兩個表都中包含少量的記錄，CBO會探測到較低的基數(shù)，并且調(diào)用排序合并連接。,select /*+ use_merge(e,b) parallel(e,2） parallel(b,2) */ E.ENAME, B.COMM from EMP e, BONUS b where e.ENAME=b