Oracle數(shù)據(jù)庫(kù)的優(yōu)化

Oracle數(shù)據(jù)庫(kù)的優(yōu)化

摘要本文提出了一種優(yōu)化Oracle數(shù)據(jù)庫(kù)的方法。Oracle中SQL語句的執(zhí)行過程可分為解析（Parse）、執(zhí)行（Execute）和提取結(jié)果（Fetch）三步，此方法就是通過對(duì)SQL語句在Oracle數(shù)據(jù)庫(kù)中優(yōu)化執(zhí)行的三個(gè)過程來提高Oracle數(shù)據(jù)庫(kù)的性能。關(guān)鍵詞數(shù)據(jù)庫(kù)掃描多表聯(lián)結(jié)子查詢1如何優(yōu)化Parse1．

1SQL語句的Parse處理步驟：1）

計(jì)算語句值2）

共享池中有無與此語句值相同的語句？3）

共享池中有與此語句字符完全匹配的語句？4）

準(zhǔn)備要運(yùn)行的SQL語句5）

為新語句在共享池中創(chuàng)建空間6）

將語句存放在共享池中7）

修改共享池圖，標(biāo)明語句的值和在共享池中的位置8）

執(zhí)行準(zhǔn)備好的SQL語句最理想的是，語句只執(zhí)行1、2、3和8步來進(jìn)行處理。不經(jīng)過2、3步來測(cè)試被傳給Oracle的語句要使用1～8步進(jìn)行處理。只經(jīng)過1、2、3、8的SQL語句要比經(jīng)過1～8步的語句更為有效。1．2在共享池中重用SQL語句

當(dāng)SQL語句被傳遞給Oracle處理時(shí)，其秘訣是重復(fù)使用已經(jīng)在共享池中的語句，而不是讓Oracle在接受語句時(shí)去準(zhǔn)備新的語句。前面表明，如果Oracle接受了一個(gè)與共享池中的語句相一致的語句，就重用共享池中的語句。Oracle提供在數(shù)據(jù)庫(kù)中存儲(chǔ)代碼的能力。當(dāng)應(yīng)用系統(tǒng)開始運(yùn)行時(shí)，從數(shù)據(jù)庫(kù)中讀取代碼（可用PL/SQL語句編制）并像其它語句那樣傳遞到共享池中去處理。從數(shù)據(jù)庫(kù)中取出的代碼是編譯過的并駐留在共享池中?？梢岳脭?shù)據(jù)庫(kù)中存儲(chǔ)的程序代碼設(shè)計(jì)應(yīng)用系統(tǒng)，檢查所有的事務(wù)處理以及主要的通用的過程，研究現(xiàn)有的應(yīng)用系統(tǒng)并把主要的處理程序轉(zhuǎn)換為數(shù)據(jù)庫(kù)中存儲(chǔ)的程序代碼。在Oracle中存儲(chǔ)代碼可以通過過程、程序包、函數(shù)、觸發(fā)器等來實(shí)現(xiàn)。2如何優(yōu)化Execute和Fetch2．1避免無計(jì)劃的全表掃描全表掃描連續(xù)從表讀取所有數(shù)據(jù)，而不管數(shù)據(jù)是否與查詢有關(guān)。避免不必要的全表掃描有兩個(gè)充足理由：1）全表掃描沒有選擇性2）

過全表掃描讀取的數(shù)據(jù)很快從SGA的緩沖區(qū)移走（如果正在掃描的表不是“高速存儲(chǔ)”的表）在基于規(guī)則優(yōu)化的情況下，如果下列任何條件在SGA語句出現(xiàn)，就要對(duì)一個(gè)表進(jìn)行全表掃描。1）

該表無索引2）

對(duì)返回的行無任何限定條件（如無Where語句）3）

對(duì)數(shù)據(jù)表與任何索引主列相對(duì)應(yīng)的行無限定條件。例如，在City－State－Zip列上創(chuàng)建了三列復(fù)合索引，那么僅對(duì)State列有限定條件的查詢不能使用這個(gè)索引，因?yàn)镾tate不是索引的主列。4）

對(duì)索引主列的行有限定條件，但條件或者是NULL或者是不相等。例如，City列上存在索引，在所有下列情況下都不會(huì)使用索引。WherecityisnullWherecityisnotnullWherecity!=’liaoning’5）

對(duì)索引主列的行有限定條件，但條件在表達(dá)式里使用。例如，如果在City列上索引，那么限定條件WhereCity=’liaoning’可以使用索引。然而，如果限定條件是

WhereUPPER(City)=’liaoning’那么不會(huì)使用City列上的索引，因?yàn)镃ity列在UPPER函數(shù)里。如果將City列與文本字符串聯(lián)結(jié)在一起，也不會(huì)使用索引。例如，如果限定條件是

WhereCity||’x’like‘liaoning%’那么不會(huì)使用City列上的索引。6）

對(duì)索引主列的行有限定條件，但條件使用Like操作以及值以‘％’開始或者值是一個(gè)賦值變量。例如，在所有下列情況下都不會(huì)使用索引：WhereCitylike‘%aonin%’

WhereCitylike:City_Bind_Variable

如果表小、索引列無選擇性，基于開銷的優(yōu)化器可能決定使用全表掃描。2．2只使用選擇性索引索引的選擇性是指索引列里不同值的數(shù)目與表中記錄數(shù)的比。如果表有1000個(gè)記錄，表索引列有950個(gè)不同值，那么這個(gè)索引的選擇性就是950/1000或者0.95。最好的可能性選擇是1.0。依據(jù)非空值列的唯一索引，通常其選擇性為1.0。如果使用基于開銷的最優(yōu)化，優(yōu)化器不應(yīng)該使用選擇性不好的索引。索引的選擇性是指索引列里不同值的數(shù)目與表中記錄數(shù)的比。如果表有1000個(gè)記錄，表索引列有950個(gè)不同值，那么這個(gè)索引的選擇性就是950/1000或者0.95。最好的可能性選擇是1.0。依據(jù)非空值列的唯一索引，通常其選擇性為1.0。索引的選擇性是指索引列里不同值的數(shù)目與表中記錄數(shù)的比。如果表有1000個(gè)記錄，表索引列有950個(gè)不同值，那么這個(gè)索引的選擇性就是950/1000或者0.95。最好的可能性選擇是1.0。依據(jù)非空值列的唯一索引，通常其選擇性為1.0。2．3管理多表聯(lián)結(jié)Oracle提供了3個(gè)聯(lián)結(jié)操作：NESTEDLOOPS、HASHJOIN和MERGEJOIN。MERGEJOIN是一組操作，在所有行被處理完之前，它不返任何記錄給下一操作。NESTEDLOOPS和HASH是行操作，因此會(huì)很快將第一批記錄返回給下一個(gè)操作。在每個(gè)聯(lián)結(jié)選項(xiàng)里，必須執(zhí)行一些步驟以獲取最好的聯(lián)結(jié)性能。如果沒有適當(dāng)?shù)貎?yōu)化聯(lián)結(jié)操作，那么聯(lián)結(jié)所需的時(shí)間也許隨著表的增長(zhǎng)而呈指數(shù)級(jí)地增長(zhǎng)。2．4管理包含視圖的SQL語句如果查詢包含視圖，優(yōu)化器有兩種執(zhí)行查詢的方法：首先解決視圖然后執(zhí)行查詢，或者把視圖文本集成到查詢里去。如果首先執(zhí)行視圖，那么首先完成全部的結(jié)果集，然后用其余的查詢條件做過濾器。首先解決視圖會(huì)導(dǎo)致查詢性能下降的問題，這取決于所涉及表的相對(duì)大小。如果視圖被集成到查詢里，那么查詢的條件也可以應(yīng)用于視圖里，并且可以使用一個(gè)小一些的結(jié)果集。然而在一些情況下，也許可以通過視圖分離組操作提高查詢性能。如果一個(gè)視圖包含集合的操作（如Groupby、SUM、COUNT或者DISTINCT），那么視圖不能被集成到查詢里去。不使用組或者沒有集合操作的視圖的SQL語法可以被集成到大的查詢里去。2．5優(yōu)化子查詢當(dāng)使用自查詢時(shí)，也許會(huì)碰到幾個(gè)獨(dú)特的問題。涉及子查詢的查詢潛在問題如下：

也許在執(zhí)行完查詢的剩余部分前執(zhí)行子查詢（與執(zhí)行分組功能的視圖相似）。

子查詢也許要求特定的提示，但這些提示不直接與調(diào)用該子查詢的查詢有關(guān)

可以作為單個(gè)查詢執(zhí)行的子查詢也許被代替寫成幾個(gè)不同的子查詢。

也許在使用notin子句或者notexists子句時(shí)，不能在最有效的方式下進(jìn)行子查詢的存在查詢。1）當(dāng)執(zhí)行子查詢時(shí)

如果一個(gè)查詢包含子查詢，那么優(yōu)化器有兩種完成查詢的方法：首先完成子查詢，然后完成查詢（“視圖的方法”），或者將子查詢集成到查詢里去（“聯(lián)結(jié)”的方法）。如果首先解決子查詢，那么整個(gè)子查詢的結(jié)果集將首先被計(jì)算，并且用查詢條件的剩余部分做過濾器。如果沒有使用子查詢?nèi)ミM(jìn)行存在檢查，那么“聯(lián)結(jié)”方法將通常要比“視圖”方法完成得好。

如果一個(gè)子查詢包括集合操作，如groupby，SUM或者DISTINCT，那么不能集成子查詢到查詢的其余部分里去。非集成的子查詢限制了提供給優(yōu)化器的選項(xiàng)。2）如何組合子查詢一個(gè)查詢可以包含多個(gè)子查詢，使用的子查詢?cè)蕉?，集成或者重寫它們到大的?lián)結(jié)里就越困難。既然有多個(gè)子查詢使集成困難，就應(yīng)該盡可能地組合多個(gè)子查詢。3）怎樣進(jìn)行存在檢查有時(shí)子查詢不返回行（記錄），但可以進(jìn)行數(shù)據(jù)正確性檢查。在相關(guān)表里的記錄或者存在或者不存在的邏輯檢查，稱為存在檢查?？梢允褂胑xists和notexists子句提高存在檢查的性能。2．6管理對(duì)非常巨大的表的訪問隨著表增長(zhǎng)到比SGA的數(shù)據(jù)塊高速緩沖區(qū)存儲(chǔ)器的空間顯著大時(shí)，需要從另一個(gè)角度優(yōu)化對(duì)這個(gè)表的查詢。1）問題當(dāng)表和它的索引小的時(shí)候，在SGA里可以有高度的數(shù)據(jù)共享。多用戶讀表或索引范圍掃描可以反復(fù)使用同一個(gè)塊。隨著表的增長(zhǎng)，表的索引也在增長(zhǎng)。隨著表和它的索引增長(zhǎng)到比SGA里提供的空間大時(shí)，范圍掃描需要的下一行將在SGA里找到的可能性變小，數(shù)據(jù)庫(kù)的命中率將減小。最后，每一個(gè)邏輯讀將要求一個(gè)單獨(dú)的物理讀。對(duì)使用非常大的表的優(yōu)化方法著眼于特別的索引技術(shù)和有關(guān)索引的選擇。2）管理數(shù)據(jù)接近在訪問非常大的表期間，如果傾向于繼續(xù)使用索引，那么應(yīng)該關(guān)注數(shù)據(jù)接近，即邏輯相關(guān)記錄的物理關(guān)系。為了使數(shù)據(jù)最大限度地接近，應(yīng)該連續(xù)往表里插入記錄。記錄按通常在表的范圍掃描里使用的列排序。3）避免沒有幫助的索引掃描如果要對(duì)大表使用索引掃描，那么不能假定索引掃描將比全表掃描執(zhí)行得更好。不緊跟表訪問的索引唯一掃描或范圍掃描執(zhí)行得比較好，但緊跟通過RowID的表訪問的索引范圍掃描也許執(zhí)行得差。隨著表增長(zhǎng)到比數(shù)據(jù)塊高速緩沖存儲(chǔ)器大得多，最終，索引掃描和全表掃描間的平衡點(diǎn)打破。4）創(chuàng)建充分索引的表如果表中的數(shù)據(jù)相當(dāng)穩(wěn)定，充分索引一個(gè)表是很有用的。創(chuàng)建一個(gè)復(fù)合索引，它包括所有在查詢期間通常選擇的列。在查詢期間，查詢要求的所有數(shù)據(jù)可以通過索引訪問提供，不需要任何表訪問。5）并行選項(xiàng)可以把一個(gè)數(shù)據(jù)庫(kù)任務(wù)，比如Select語句，分為多個(gè)單元的工作，由多個(gè)Oracle進(jìn)程同時(shí)執(zhí)行。這種能夠允許數(shù)據(jù)庫(kù)的單個(gè)查詢活動(dòng)由多個(gè)協(xié)調(diào)的進(jìn)程透明地進(jìn)行處理的能力，稱為并行查詢選項(xiàng)（PQO）。并行選項(xiàng)調(diào)用多個(gè)進(jìn)程來利用空閑的系統(tǒng)資源，以減少完成任務(wù)所需要的時(shí)間。并行選項(xiàng)并不減少處理過程所要求的資源數(shù)量，而是把處理的任務(wù)分散給多個(gè)CPU。為了從并行選項(xiàng)中得到最大的好處，應(yīng)該使CPU和磁盤上的I/O不要滿負(fù)荷使用。因?yàn)椴⑿械哪康氖鞘垢嗟腃PU和磁盤同時(shí)參與處理數(shù)據(jù)庫(kù)的命令，一個(gè)缺乏CPU和I/O資源的服務(wù)程序是不能從并行選項(xiàng)中得到好處的。2．7使用UNIONALL而不是UNION最常用的集操作是UNION操作，UNION操作使多個(gè)記錄集聯(lián)結(jié)成為單個(gè)集。UNION操作的數(shù)學(xué)定義是返回記錄的單個(gè)集并且沒有重復(fù)的行，所以在合并結(jié)果集里，Oracle只返回不同的記錄。當(dāng)UNION操作用作SQL語句的一部分時(shí)，唯一性要求強(qiáng)迫Oracle移走重復(fù)的記錄。Oracle的移走重復(fù)記錄的功能是SORTUNIQUE操作，它與使用DISTINCT子句時(shí)執(zhí)行的操作類似。UNIONALL操作允許重復(fù)。UNIONALL不要求SORTUNIQUE操作，從而節(jié)省了開銷。UNIONALL是一個(gè)行操作，所以當(dāng)其變?yōu)橛行Ь头祷亟o用戶。而UNION包括SORTUNIQUE集操作，在全部記錄的集的排序結(jié)束前，不返回任何記錄給用戶。當(dāng)UNIONALL操作產(chǎn)生巨大的結(jié)果集時(shí)，不需要任何排序便返回記錄給應(yīng)用的事實(shí)意味著第一行檢索的響應(yīng)時(shí)間更快，并且在許多情況下，可以不用臨時(shí)段完成操作。在有些情況下，UNIONALL和UNION不返回同樣的結(jié)果。如果在應(yīng)用環(huán)境中，結(jié)果集并不包含任何重復(fù)的記錄，則可以把UNION轉(zhuǎn)換成UNIONALL。2．8避免在SQL里使用PL/SQL功能調(diào)用對(duì)于增加PL/SQL的使用，許多用戶試圖利PL/SQL功能的優(yōu)勢(shì)產(chǎn)生可重復(fù)使用的代碼。其中一個(gè)強(qiáng)迫重復(fù)使用PL/SQL功能的方法是在SQL語句里使用。例如，可以創(chuàng)建一個(gè)將國(guó)際貨幣轉(zhuǎn)換為US$的函數(shù)。這個(gè)函數(shù)稱為US$。示例如下：select

transaction_type，US$(amount，currency)from

international_transactionwhere

US$(amount，currency)>1000;執(zhí)行前面的SQL語句沒有所期望的那樣好。在測(cè)試時(shí)，它的性能大約比下面得出相同結(jié)果的SQL語句慢大約幾十倍。select

transaction_type，amount*exchange_rateUS$from

exchange_rateer，international_transactionitwhere

er.currency=it.currencyand

amount*exchange_rate>1000;響應(yīng)時(shí)間不同的原因是混合PL/SQL和SQL時(shí)，Oracle使用的機(jī)制不同。在SQL查詢里嵌入PL/SQL功能時(shí)，在執(zhí)行時(shí)，Oracle將調(diào)