淺析ORACLE數據庫中SQL語句優化的影響和建議

摘 要 本文從SQL語句優化角度出發，診斷ORACLE數據庫中SQL語句優化的影響因素，并在最后提出優化建議。

關鍵詞 數據庫 SQL語句優化 影響因素

中圖分類號：TP392 文獻標識碼：A

數據庫系統本身是一個較為復雜的系統，影響其性能的因素也是多方面的。在不斷探索中，SQL語句的執行效率逐漸得到重視，實驗證明，SQL語句的執行效率在提升系統性能中占有重要地位。在Oracle數據庫中，如何優化SQL語句很值得我們深入探討。

1 影響SQL語句優化的因素

優化器可以完成部分工作，當人為改變優化器的執行計劃時同樣要遵循這部分基礎。總的來說，Oracle優化器制定執行計劃的選擇基礎也是影響執行計劃的主要因素，這些因素都可能在特定情況下改變執行計劃的內容，而這些因素都顯式或隱式的由SQL語句的書寫方式和執行情況影響，主要有以下幾點：

1.1 表訪問方式

表的訪問方式主要有三種，全表掃描、Rowid訪問、索引掃描。這里，通過Rowid訪問表的速度是最快的，因為它不用順序的讀取每一塊數據塊去查找符合條件的記錄，而是直接根據該記錄在數據磁盤中的位置獲取記錄信息；其次是索引訪問，它能獲取到該行記錄的Rowid值再查找進行判斷；最后是全表掃描，它只能通過讀取全部數據塊進行查找，極大增加了系統由于邏輯讀取進行的消耗。在實際應用中Rowid的值需要手動獲取，因此并不可能在每個查詢條件中都根據Rowid進行獲取，所以使用最多的是索引掃描對表進行訪問。

1.2 表連接順序

當有兩個或兩個以上的表進行連接時，Oracle選擇一種合適的順序進行連接。在Oracle數據庫中，無論進行連接的表一共有多少個，每次進行連接的表都是兩個，連接成一張表之后再與下一張表進行連接，直至完成所有表的連接，所以每次連接的效率都只與當前連接的兩個表有關。連接順序之所以會影響執行效率是因為連接的兩個表有驅動表和連接表之分。驅動表是主表，是作為連接操作的外層循環的表。連接表是與驅動表進行連接的表，在連接中處于內存循環的位置。CBO對驅動表的每一行都對連接表進行一次全掃描，所以Oracle優化器分別計算每個驅動表和連接表的記錄條數，并選擇最小時間復雜度的順序在執行計劃中被執行。

1.3 表連接方式

表的連接方式主要有三種：嵌套連接、合并連接和哈希連接。嵌套連接是使用驅動表的每一行與連接表的所有滿足條件的行進行連接，這種連接方式使用于任何連接條件，當連接條件中有高選擇性索引或限制性搜索時效率比較高。合并連接是分別取出兩個連接表中滿足查詢條件的所有記錄再對其排序然后進行笛卡爾連接，這種連接方式主要用于不等價連接，且在沒有索引時效率較嵌套連接高。哈希連接是通過將驅動表中的連接列建立一個哈希函數，并對于連接表中滿足條件的每一行計算哈希值進行匹配，這種連接方式只用于等價連接，且沒有索引時比嵌套循環和合并連接效率高，尤其是當數據量較大時。三種連接方式均有各自匹配的情況，但一般情況來說哈希連接效率是最高的，其次是嵌套連接，而合并連接因為要對兩個表都進行排序操作會增加由于排序引起的資源消耗。優化器根據返回的每個表的結果集的大小計算最合適的連接方式并在執行計劃中被安排執行。

1.4 查詢條件的篩選率

Oracle根據系統統計信息計算出SQL語句中每個蹄選條件所能返回的記錄條數并計算所占的比率，然后選擇每次選擇蹄選率最大的語句最先執行。與此同時將返回的結果集作為數據源傳遞給下一個查詢條件，下一個查詢條件對此數據源執行新的蹄選操作，直至所有的查詢條件執行完畢返回最終的記錄集。因此具有較高篩選率的查詢條件在執行計劃中總是被最先安排執行。

1.5 查詢條件的數據類型

優化器根據查詢條件中傳入的值隱式轉換成表中字段所能匹配的類型。當傳入的值和該字段在數據庫中創建時的類型不同時，Oracle嘗試進行將其進行類型轉換，如果轉換不成功則報錯。所以Oracle可以識別隱式和顯式的類型轉換，但每次的轉換都會耗費一定的CPU和內存資源。

1.6 排序操作

排序操作需消耗系統大量內存和CPU，尤其是中間過程的排序，大部分都在內存中直接排序并存儲中間結果，當某個排序過程不是必須的或是可以被其他易操作的執行步驟代替時，Oracle的CBO就會節省系統資源和時間的消耗使用其他方式代替。當Oracle在執行路徑中發現結果集已經按照一定順序排好或者無需排序時就會省略排序過程直接下一步的操作，所以當排序不是必須的選擇或已經進行過排序的結果集時，Oracle優化器在選擇執行計劃時忽略排序的步驟。

2 SQL語句優化的建議

ADDM工具和STA工具配合的使用使特定SQL語句優化方案的使用更為方便，使用以上兩種工具進行優化的主要方法如下：

（1）將系統參數statistics—level設置為typica或all。這個參數的設置決定了收集的統計信息的內容，typical或all級別收集的信息包含在ADDM所需的統計信息內容中。

（2）獲取兩次快照的ID。當數據庫中設置為自動獲取快照時，此步驟可以省略，轉為直接使用數據庫對快照信息的分析結果。

（3）用戶授權。優化建議的獲取需要advisor的權限，需確保當前執行的用戶擁有此權限。

（4）創建優化任務。創建優化任務是使用存儲過程完成的，將傳送過來的SQL語句和用戶名作為參數，調用DBMS-SQLTUNE包的CREATE-TUNING-TASK方法為該任務創建一個任務名，同時使用包屮的EXECUTE-TUNING-TASK方法執行該任務。執行完成后系統根據該SQL語句和統計信息生成優化建議，并把執行結果存放在任務日志中。

（5）根據任務名查看優化建議。DBMS-SQLTUNE包提供了一個方法REPORT_TUNING_TASK （任務名）方法可以查看與任務名匹配的結果，結果中包含三部分。第一部分是此次優化任務的基本信息，如任務名稱、執行時問、范圍和執行的語句等；第二部分是關于此次任務所找到的問題和優化建議；第三部分是原有的執行計劃以及采用優化建議以后的執行計劃的對比。當該任務需要再次被運行時，需要刪除在系統中存儲的該任務名相關的信息，DBMS_SQLTUNE包提供了一個DROP_TUNING-TASK （任務名）的方法除同名的任務，以便于下次執行。

參考文獻

[1] 郭霞.基于Oracle數據庫的SQL語句優化分析[J].電腦知識與技術，2011，7（21）：5063-5065.

[2] 楊宇紅，石紅春.數據庫管理中SQL查詢優化技術的應用[J].信息與電腦，2011，1：108.

[3] 王躍.Oracle數據庫中SQL語句的優化與分析[J].計算機光盤軟件與應用，2011，8：162-163.