計算機畢業論文:Oracle數據庫的優化

導語：計算機畢業論文:Oracle數據庫的優化一文來源于網友上傳，不代表本站觀點，若需要原創文章可咨詢客服老師，歡迎參考。

摘要本文提出了一種優化oracle數據庫的方法。Oracle中SQL語句的執行過程可分為解析（Parse）、執行（Execute）和提取結果（Fetch）三步，此方法就是通過對SQL語句在Oracle數據庫中優化執行的三個過程來提高Oracle數據庫的性能。

關鍵詞數據庫掃描多表聯結子查詢

1如何優化Parse

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

1）計算語句值

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

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

4）準備要運行的SQL語句

5）為新語句在共享池中創建空間

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

7）修改共享池圖，標明語句的值和在共享池中的位置

8）執行準備好的SQL語句

最理想的是，語句只執行1、2、3和8步來進行處理。不經過2、3步來測試被傳給Oracle的語句要使用1～8步進行處理。只經過1、2、3、8的SQL語句要比經過1～8步的語句更為有效。

1．2在共享池中重用SQL語句

當SQL語句被傳遞給Oracle處理時，其秘訣是重復使用已經在共享池中的語句，而不是讓Oracle在接受語句時去準備新的語句。前面表明，如果Oracle接受了一個與共享池中的語句相一致的語句，就重用共享池中的語句。

Oracle提供在數據庫中存儲代碼的能力。當應用系統開始運行時，從數據庫中讀取代碼（可用PL/SQL語句編制）并像其它語句那樣傳遞到共享池中去處理。從數據庫中取出的代碼是編譯過的并駐留在共享池中。

可以利用數據庫中存儲的程序代碼設計應用系統，檢查所有的事務處理以及主要的通用的過程，研究現有的應用系統并把主要的處理程序轉換為數據庫中存儲的程序代碼。在Oracle中存儲代碼可以通過過程、程序包、函數、觸發器等來實現。

2如何優化Execute和Fetch

2．1避免無計劃的全表掃描

全表掃描連續從表讀取所有數據，而不管數據是否與查詢有關。避免不必要的全表掃描有兩個充足理由：1）全表掃描沒有選擇性

2）過全表掃描讀取的數據很快從SGA的緩沖區移走（如果正在掃描的表不是“高速存儲”的表）

在基于規則優化的情況下，如果下列任何條件在SGA語句出現，就要對一個表進行全表掃描。

1）該表無索引

2）對返回的行無任何限定條件（如無Where語句）

3）對數據表與任何索引主列相對應的行無限定條件。例如，在City－State－Zip列上創建了三列復合索引，那么僅對State列有限定條件的查詢不能使用這個索引，因為State不是索引的主列。

4）對索引主列的行有限定條件，但條件或者是NULL或者是不相等。例如，City列上存在索引，在所有下列情況下都不會使用索引。

Wherecityisnull

Wherecityisnotnull

Wherecity!=’liaoning’

5）對索引主列的行有限定條件，但條件在表達式里使用。例如，如果在City列上索引，那么限定條件

WhereCity=’liaoning’

可以使用索引。然而，如果限定條件是

WhereUPPER(City)=’liaoning’

那么不會使用City列上的索引，因為City列在UPPER函數里。如果將City列與文本字符串聯結在一起，也不會使用索引。例如，如果限定條件是

WhereCity||’x’like‘liaoning%’

那么不會使用City列上的索引。

6）對索引主列的行有限定條件，但條件使用Like操作以及值以‘％’開始或者值是一個賦值變量。例如，在所有下列情況下都不會使用索引：

WhereCitylike‘%aonin%’

WhereCitylike:City_Bind_Variable

如果表小、索引列無選擇性，基于開銷的優化器可能決定使用全表掃描。

2．2只使用選擇性索引

索引的選擇性是指索引列里不同值的數目與表中記錄數的比。如果表有1000個記錄，表索引列有950個不同值，那么這個索引的選擇性就是950/1000或者0.95。最好的可能性選擇是1.0。依據非空值列的唯一索引，通常其選擇性為1.0。

如果使用基于開銷的最優化，優化器不應該使用選擇性不好的索引。

索引的選擇性是指索引列里不同值的數目與表中記錄數的比。如果表有1000個記錄，表索引列有950個不同值，那么這個索引的選擇性就是950/1000或者0.95。最好的可能性選擇是1.0。依據非空值列的唯一索引，通常其選擇性為1.0。

索引的選擇性是指索引列里不同值的數目與表中記錄數的比。如果表有1000個記錄，表索引列有950個不同值，那么這個索引的選擇性就是950/1000或者0.95。最好的可能性選擇是1.0。依據非空值列的唯一索引，通常其選擇性為1.0。

2．3管理多表聯結

Oracle提供了3個聯結操作：NESTEDLOOPS、HASHJOIN和MERGEJOIN。MERGEJOIN是一組操作，在所有行被處理完之前，它不返任何記錄給下一操作。NESTEDLOOPS和HASH是行操作，因此會很快將第一批記錄返回給下一個操作。

在每個聯結選項里，必須執行一些步驟以獲取最好的聯結性能。如果沒有適當地優化聯結操作，那么聯結所需的時間也許隨著表的增長而呈指數級地增長。

2．4管理包含視圖的SQL語句

如果查詢包含視圖，優化器有兩種執行查詢的方法：首先解決視圖然后執行查詢，或者把視圖文本集成到查詢里去。如果首先執行視圖，那么首先完成全部的結果集，然后用其余的查詢條件做過濾器。

首先解決視圖會導致查詢性能下降的問題，這取決于所涉及表的相對大小。如果視圖被集成到查詢里，那么查詢的條件也可以應用于視圖里，并且可以使用一個小一些的結果集。然而在一些情況下，也許可以通過視圖分離組操作提高查詢性能。

如果一個視圖包含集合的操作（如Groupby、SUM、COUNT或者DISTINCT），那么視圖不能被集成到查詢里去。

不使用組或者沒有集合操作的視圖的SQL語法可以被集成到大的查詢里去。

2．5優化子查詢

當使用自查詢時，也許會碰到幾個獨特的問題。涉及子查詢的查詢潛在問題如下：

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

?子查詢也許要求特定的提示，但這些提示不直接與調用該子查詢的查詢有關

?可以作為單個查詢執行的子查詢也許被代替寫成幾個不同的子查詢。

?也許在使用notin子句或者notexists子句時，不能在最有效的方式下進行子查詢的存在查詢。

1）當執行子查詢時

如果一個查詢包含子查詢，那么優化器有兩種完成查詢的方法：首先完成子查詢，然后完成查詢（“視圖的方法”），或者將子查詢集成到查詢里去（“聯結”的方法）。如果首先解決子查詢，那么整個子查詢的結果集將首先被計算，并且用查詢條件的剩余部分做過濾器。如果沒有使用子查詢去進行存在檢查，那么“聯結”方法將通常要比“視圖”方法完成得好。

如果一個子查詢包括集合操作，如groupby，SUM或者DISTINCT，那么不能集成子查詢到查詢的其余部分里去。非集成的子查詢限制了提供給優化器的選項。

2）如何組合子查詢

一個查詢可以包含多個子查詢，使用的子查詢越多，集成或者重寫它們到大的聯結里就越困難。既然有多個子查詢使集成困難，就應該盡可能地組合多個子查詢。

3）怎樣進行存在檢查

有時子查詢不返回行（記錄），但可以進行數據正確性檢查。在相關表里的記錄或者存在或者不存在的邏輯檢查，稱為存在檢查。可以使用exists和notexists子句提高存在檢查的性能。

2．6管理對非常巨大的表的訪問

隨著表增長到比SGA的數據塊高速緩沖區存儲器的空間顯著大時，需要從另一個角度優化對這個表的查詢。

1）問題

當表和它的索引小的時候，在SGA里可以有高度的數據共享。多用戶讀表或索引范圍掃描可以反復使用同一個塊。隨著表的增長，表的索引也在增長。隨著表和它的索引增長到比SGA里提供的空間大時，范圍掃描需要的下一行將在SGA里找到的可能性變小，數據庫的命中率將減小。最后，每一個邏輯讀將要求一個單獨的物理讀。對使用非常大的表的優化方法著眼于特別的索引技術和有關索引的選擇。

2）管理數據接近

在訪問非常大的表期間，如果傾向于繼續使用索引，那么應該關注數據接近，即邏輯相關記錄的物理關系。為了使數據最大限度地接近，應該連續往表里插入記錄。記錄按通常在表的范圍掃描里使用的列排序。

3）避免沒有幫助的索引掃描

如果要對大表使用索引掃描，那么不能假定索引掃描將比全表掃描執行得更好。不緊跟表訪問的索引唯一掃描或范圍掃描執行得比較好，但緊跟通過RowID的表訪問的索引范圍掃描也許執行得差。隨著表增長到比數據塊高速緩沖存儲器大得多，最終，索引掃描和全表掃描間的平衡點打破。

4）創建充分索引的表

如果表中的數據相當穩定，充分索引一個表是很有用的。創建一個復合索引，它包括所有在查詢期間通常選擇的列。在查詢期間，查詢要求的所有數據可以通過索引訪問提供，不需要任何表訪問。

5）并行選項

可以把一個數據庫任務，比如Select語句，分為多個單元的工作，由多個Oracle進程同時執行。這種能夠允許數據庫的單個查詢活動由多個協調的進程透明地進行處理的能力，稱為并行查詢選項（PQO）。

并行選項調用多個進程來利用空閑的系統資源，以減少完成任務所需要的時間。并行選項并不減少處理過程所要求的資源數量，而是把處理的任務分散給多個CPU。為了從并行選項中得到最大的好處，應該使CPU和磁盤上的I/O不要滿負荷使用。因為并行的目的是使更多的CPU和磁盤同時參與處理數據庫的命令，一個缺乏CPU和I/O資源的服務程序是不能從并行選項中得到好處的。

2．7使用UNIONALL而不是UNION

最常用的集操作是UNION操作，UNION操作使多個記錄集聯結成為單個集。UNION操作的數學定義是返回記錄的單個集并且沒有重復的行，所以在合并結果集里，Oracle只返回不同的記錄。

當UNION操作用作SQL語句的一部分時，唯一性要求強迫Oracle移走重復的記錄。Oracle的移走重復記錄的功能是SORTUNIQUE操作，它與使用DISTINCT子句時執行的操作類似。

UNIONALL操作允許重復。UNIONALL不要求SORTUNIQUE操作，從而節省了開銷。UNIONALL是一個行操作，所以當其變為有效就返回給用戶。而UNION包括SORTUNIQUE集操作，在全部記錄的集的排序結束前，不返回任何記錄給用戶。

當UNIONALL操作產生巨大的結果集時，不需要任何排序便返回記錄給應用的事實意味著第一行檢索的響應時間更快，并且在許多情況下，可以不用臨時段完成操作。

在有些情況下，UNIONALL和UNION不返回同樣的結果。如果在應用環境中，結果集并不包含任何重復的記錄，則可以把UNION轉換成UNIONALL。

2．8避免在SQL里使用PL/SQL功能調用

對于增加PL/SQL的使用，許多用戶試圖利PL/SQL功能的優勢產生可重復使用的代碼。其中一個強迫重復使用PL/SQL功能的方法是在SQL語句里使用。例如，可以創建一個將國際貨幣轉換為US$的函數。這個函數稱為US$。示例如下：

selecttransaction_type,US$(amount,currency)

frominternational_transaction

whereUS$(amount,currency)>1000;

執行前面的SQL語句沒有所期望的那樣好。在測試時，它的性能大約比下面得出相同結果的SQL語句慢大約幾十倍。

selecttransaction_type,

amount*exchange_rateUS$

fromexchange_rateer,international_transactionit

whereer.currency=it.currency

andamount*exchange_rate>1000;

響應時間不同的原因是混合PL/SQL和SQL時，Oracle使用的機制不同。在SQL查詢里嵌入PL/SQL功能時，在執行時，Oracle將調用分成兩部分：用帶有賦值變量的SQL語句代替功能調用以及對每一個函數調用的無名PL/SQL塊。

selecttransaction_type,:a1

frominternational_transaction

where:a1>1000

和

BEGIN

:a1:=US$(:amount,:currency);

END

對在international_transaction表里的每一行，將執行在前面示例里顯示的無名塊兩次。無名塊調用導致查詢響應時間的劇增。應該避免在SQL語句里使用PL/SQL功能調用。

參考文獻

[1]甕正科，王新英著.Oracle8.XForWindowsNT實用教程［M］.北京：清華大學出版社，1999.

[2]薩師萱，王珊著.數據庫系統概論［M］.北京：高等教育出版社，2000.2.

[3]EyalAronoff，KevinLoney，NooraliSonawalla著,李逸波、王華駒、馬賽紅、曲寧等譯.Oracle8性能優化和管理手冊［M］.北京：電子工業出版社，2000.1.

[4]PeterKoletzke,Dr.PaulDorsey著,劉曉霞、孫登峰、曲京、何粼等譯.OracleDesigner信息系統開發［M］.

北京：機械工業出版社，1999.8