1、Oracle數(shù)據(jù)庫的優(yōu)化摘要本文提出了一種優(yōu)化rale數(shù)據(jù)庫的方法。rale中SQL語句的執(zhí)行過程可分為解析Parse、執(zhí)行Exeute和提取結(jié)果Feth三步，此方法就是通過對SQL語句在rale數(shù)據(jù)庫中優(yōu)化執(zhí)行的三個過程來進步rale數(shù)據(jù)庫的性能。關鍵詞數(shù)據(jù)庫掃描多表聯(lián)結(jié)子查詢1如何優(yōu)化Parse11SQL語句的Parse處理步驟：1計算語句值2共享池中有無與此語句值一樣的語句？3共享池中有與此語句字符完全匹配的語句？4準備要運行的SQL語句5為新語句在共享池中創(chuàng)立空間6將語句存放在共享池中7修改共享池圖，標明語句的值和在共享池中的位置8執(zhí)行準備好的SQL語句最理想的是，語句只執(zhí)行1、2、3

2、和8步來進展處理。不經(jīng)過2、3步來測試被傳給rale的語句要使用18步進展處理。只經(jīng)過1、2、3、8的SQL語句要比經(jīng)過18步的語句更為有效。12在共享池中重用SQL語句當SQL語句被傳遞給rale處理時，其秘訣是重復使用已經(jīng)在共享池中的語句，而不是讓rale在承受語句時去準備新的語句。前面說明，假如rale承受了一個與共享池中的語句相一致的語句，就重用共享池中的語句。rale提供在數(shù)據(jù)庫中存儲代碼的才能。當應用系統(tǒng)開場運行時，從數(shù)據(jù)庫中讀取代碼可用PL/SQL語句編制并像其它語句那樣傳遞到共享池中去處理。從數(shù)據(jù)庫中取出的代碼是編譯過的并駐留在共享池中?？梢岳脭?shù)據(jù)庫中存儲的程序代碼設計應用系

3、統(tǒng)，檢查所有的事務處理以及主要的通用的過程，研究現(xiàn)有的應用系統(tǒng)并把主要的處理程序轉(zhuǎn)換為數(shù)據(jù)庫中存儲的程序代碼。在rale中存儲代碼可以通過過程、程序包、函數(shù)、觸發(fā)器等來實現(xiàn)。2如何優(yōu)化Exeute和Feth21防止無方案的全表掃描全表掃描連續(xù)從表讀取所有數(shù)據(jù)，而不管數(shù)據(jù)是否與查詢有關。防止不必要的全表掃描有兩個充足理由：1全表掃描沒有選擇性2過全表掃描讀取的數(shù)據(jù)很快從SGA的緩沖區(qū)移走假如正在掃描的表不是“高速存儲的表在基于規(guī)那么優(yōu)化的情況下，假如以下任何條件在SGA語句出現(xiàn)，就要對一個表進展全表掃描。1該表無索引2對返回的行無任何限定條件如無here語句3對數(shù)據(jù)表與任何索引主列相對應的行無限

4、定條件。例如，在ityStateZip列上創(chuàng)立了三列復合索引，那么僅對State列有限定條件的查詢不能使用這個索引，因為State不是索引的主列。4對索引主列的行有限定條件，但條件或者是NULL或者是不相等。例如，ity列上存在索引，在所有以下情況下都不會使用索引。hereityisnullhereityisntnullhereity!=lianing5對索引主列的行有限定條件，但條件在表達式里使用。例如，假如在ity列上索引，那么限定條件hereity=lianing可以使用索引。然而，假如限定條件是hereUPPER(ity)=lianing那么不會使用ity列上的索引，因為ity列在UP

5、PER函數(shù)里。假如將ity列與文本字符串聯(lián)結(jié)在一起，也不會使用索引。例如，假如限定條件是hereity|xlikelianing%那么不會使用ity列上的索引。6對索引主列的行有限定條件，但條件使用Like操作以及值以開場或者值是一個賦值變量。例如，在所有以下情況下都不會使用索引：hereitylike%anin%hereitylike:ity_Bind_Variable假如表孝索引列無選擇性，基于開銷的優(yōu)化器可能決定使用全表掃描。22只使用選擇性索引索引的選擇性是指索引列里不同值的數(shù)目與表中記錄數(shù)的比。假如表有1000個記錄，表索引列有950個不同值，那么這個索引的選擇性就是950/1000

6、或者0.95。最好的可能性選擇是1.0。根據(jù)非空值列的唯一索引，通常其選擇性為1.0。假如使用基于開銷的最優(yōu)化，優(yōu)化器不應該使用選擇性不好的索引。索引的選擇性是指索引列里不同值的數(shù)目與表中記錄數(shù)的比。假如表有1000個記錄，表索引列有950個不同值，那么這個索引的選擇性就是950/1000或者0.95。最好的可能性選擇是1.0。根據(jù)非空值列的唯一索引，通常其選擇性為1.0。索引的選擇性是指索引列里不同值的數(shù)目與表中記錄數(shù)的比。假如表有1000個記錄，表索引列有950個不同值，那么這個索引的選擇性就是950/1000或者0.95。最好的可能性選擇是1.0。根據(jù)非空值列的唯一索引，通常其選擇性為1

7、.0。23管理多表聯(lián)結(jié)rale提供了3個聯(lián)結(jié)操作：NESTEDLPS、HASHJIN和ERGEJIN。ERGEJIN是一組操作，在所有行被處理完之前，它不返任何記錄給下一操作。NESTEDLPS和HASH是行操作，因此會很快將第一批記錄返回給下一個操作。在每個聯(lián)結(jié)選項里，必須執(zhí)行一些步驟以獲取最好的聯(lián)結(jié)性能。假如沒有適當?shù)貎?yōu)化聯(lián)結(jié)操作，那么聯(lián)結(jié)所需的時間也許隨著表的增長而呈指數(shù)級地增長。24管理包含視圖的SQL語句假如查詢包含視圖，優(yōu)化器有兩種執(zhí)行查詢的方法：首先解決視圖然后執(zhí)行查詢，或者把視圖文本集成到查詢里去。假如首先執(zhí)行視圖，那么首先完成全部的結(jié)果集，然后用其余的查詢條件做過濾器。首先解

8、決視圖會導致查詢性能下降的問題，這取決于所涉及表的相對大校假如視圖被集成到查詢里，那么查詢的條件也可以應用于視圖里，并且可以使用一個小一些的結(jié)果集。然而在一些情況下，也答應以通過視圖別離組操作進步查詢性能。假如一個視圖包含集合的操作如Grupby、SU、UNT或者DISTINT，那么視圖不能被集成到查詢里去。不使用組或者沒有集合操作的視圖的SQL語法可以被集成到大的查詢里去。25優(yōu)化子查詢當使用自查詢時，也許會碰到幾個獨特的問題。涉及子查詢的查詢潛在問題如下：也許在執(zhí)行完查詢的剩余局部前執(zhí)行子查詢與執(zhí)行分組功能的視圖相似。子查詢也許要求特定的提示，但這些提示不直接與調(diào)用該子查詢的查詢有關可以作

9、為單個查詢執(zhí)行的子查詢也許被代替寫成幾個不同的子查詢。也許在使用ntin子句或者ntexists子句時，不能在最有效的方式下進展子查詢的存在查詢。1當執(zhí)行子查詢時假如一個查詢包含子查詢，那么優(yōu)化器有兩種完成查詢的方法：首先完成子查詢，然后完成查詢“視圖的方法，或者將子查詢集成到查詢里去“聯(lián)結(jié)的方法。假如首先解決子查詢，那么整個子查詢的結(jié)果集將首先被計算，并且用查詢條件的剩余局部做過濾器。假如沒有使用子查詢?nèi)ミM展存在檢查，那么“聯(lián)結(jié)方法將通常要比“視圖方法完成得好。假如一個子查詢包括集合操作，如grupby，SU或者DISTINT，那么不能集成子查詢到查詢的其余局部里去。非集成的子查詢限制了提供

10、應優(yōu)化器的選項。2如何組合子查詢一個查詢可以包含多個子查詢，使用的子查詢越多，集成或者重寫它們到大的聯(lián)結(jié)里就越困難。既然有多個子查詢使集成困難，就應該盡可能地組合多個子查詢。3怎樣進展存在檢查有時子查詢不返回行記錄，但可以進展數(shù)據(jù)正確性檢查。在相關表里的記錄或者存在或者不存在的邏輯檢查，稱為存在檢查?？梢允褂胑xists和ntexists子句進步存在檢查的性能。26管理對非常宏大的表的訪問隨著表增長到比SGA的數(shù)據(jù)塊高速緩沖區(qū)存儲器的空間顯著大時，需要從另一個角度優(yōu)化對這個表的查詢。1問題當表和它的索引小的時候，在SGA里可以有高度的數(shù)據(jù)共享。多用戶讀表或索引范圍掃描可以反復使用同一個塊。隨著

11、表的增長，表的索引也在增長。隨著表和它的索引增長到比SGA里提供的空間大時，范圍掃描需要的下一行將在SGA里找到的可能性變小，數(shù)據(jù)庫的命中率將減校最后，每一個邏輯讀將要求一個單獨的物理讀。對使用非常大的表的優(yōu)化方法著眼于特別的索引技術和有關索引的選擇。2管理數(shù)據(jù)接近在訪問非常大的表期間，假如傾向于繼續(xù)使用索引，那么應該關注數(shù)據(jù)接近，即邏輯相關記錄的物理關系。為了使數(shù)據(jù)最大限度地接近，應該連續(xù)往表里插入記錄。記錄按通常在表的范圍掃描里使用的列排序。3防止沒有幫助的索引掃描假如要對大表使用索引掃描，那么不能假定索引掃描將比全表掃描執(zhí)行得更好。不緊跟表訪問的索引唯一掃描或范圍掃描執(zhí)行得比擬好，但緊跟

12、通過RID的表訪問的索引范圍掃描也許執(zhí)行得差。隨著表增長到比數(shù)據(jù)塊高速緩沖存儲器大得多，最終，索引掃描和全表掃描間的平衡點打破。4創(chuàng)立充分索引的表假如表中的數(shù)據(jù)相當穩(wěn)定，充分索引一個表是很有用的。創(chuàng)立一個復合索引，它包括所有在查詢期間通常選擇的列。在查詢期間，查詢要求的所有數(shù)據(jù)可以通過索引訪問提供，不需要任何表訪問。5并行選項可以把一個數(shù)據(jù)庫任務，比方Selet語句，分為多個單元的工作，由多個rale進程同時執(zhí)行。這種可以允許數(shù)據(jù)庫的單個查詢活動由多個協(xié)調(diào)的進程透明地進展處理的才能，稱為并行查詢選項PQ。并行選項調(diào)用多個進程來利用空閑的系統(tǒng)資源，以減少完成任務所需要的時間。并行選項并不減少處理

13、過程所要求的資源數(shù)量，而是把處理的任務分散給多個PU。為了從并行選項中得到最大的好處，應該使PU和磁盤上的I/不要滿負荷使用。因為并行的目的是使更多的PU和磁盤同時參與處理數(shù)據(jù)庫的命令，一個缺乏PU和I/資源的效勞程序是不能從并行選項中得到好處的。27使用UNINALL而不是UNIN最常用的集操作是UNIN操作，UNIN操作使多個記錄集聯(lián)結(jié)成為單個集。UNIN操作的數(shù)學定義是返回記錄的單個集并且沒有重復的行，所以在合并結(jié)果集里，rale只返回不同的記錄。當UNIN操作用作SQL語句的一局部時，唯一性要求強迫rale移走重復的記錄。rale的移走重復記錄的功能是SRTUNIQUE操作，它與使用D

14、ISTINT子句時執(zhí)行的操作類似。UNINALL操作允許重復。UNINALL不要求SRTUNIQUE操作，從而節(jié)省了開銷。UNINALL是一個行操作，所以當其變?yōu)橛行Ь头祷亟o用戶。而UNIN包括SRTUNIQUE集操作，在全部記錄的集的排序完畢前，不返回任何記錄給用戶。當UNINALL操作產(chǎn)生宏大的結(jié)果集時，不需要任何排序便返回記錄給應用的事實意味著第一行檢索的響應時間更快，并且在許多情況下，可以不用臨時段完成操作。在有些情況下，UNINALL和UNIN不返回同樣的結(jié)果。假如在應用環(huán)境中，結(jié)果集并不包含任何重復的記錄，那么可以把UNIN轉(zhuǎn)換成UNINALL。28防止在SQL里使用PL/SQL功

15、能調(diào)用對于增加PL/SQL的使用，許多用戶試圖利PL/SQL功能的優(yōu)勢產(chǎn)生可重復使用的代碼。其中一個強迫重復使用PL/SQL功能的方法是在SQL語句里使用。例如，可以創(chuàng)立一個將國際貨幣轉(zhuǎn)換為US$的函數(shù)。這個函數(shù)稱為US$。例如如下：selettransatin_type,US$(aunt,urreny)frinternatinal_transatinhereUS$(aunt,urreny)1000;執(zhí)行前面的SQL語句沒有所期望的那樣好。在測試時，它的性能大約比下面得出一樣結(jié)果的SQL語句慢大約幾十倍。selettransatin_type,aunt*exhange_rateUS$frexh

16、ange_rateer,internatinal_transatinithereer.urreny=it.urrenyandaunt*exhange_rate1000;響應時間不同的原因是混合PL/SQL和SQL時，rale使用的機制不同。在SQL查詢里嵌入PL/SQL功能時，在執(zhí)行時，rale將調(diào)用分成兩局部：用帶有賦值變量的SQL語句代替功能調(diào)用以及對每一個函數(shù)調(diào)用的無名PL/SQL塊。selettransatin_type,:a1frinternatinal_transatinhere:a11000和BEGIN:a1:=US$(:aunt,:urreny);END對在internatinal_transatin表里的每一行，將執(zhí)行在前面例如里顯示的無名塊兩次。無名塊調(diào)用導致查詢響應時間的劇增。應該防止在SQL語句里使用PL/SQ