淺析ORACLE數(shù)據(jù)庫中SQL語句優(yōu)化的影響和建議

摘 要 本文從SQL語句優(yōu)化角度出發(fā)，診斷ORACLE數(shù)據(jù)庫中SQL語句優(yōu)化的影響因素，并在最后提出優(yōu)化建議。

關(guān)鍵詞 數(shù)據(jù)庫 SQL語句優(yōu)化 影響因素

中圖分類號：TP392 文獻(xiàn)標(biāo)識碼：A

數(shù)據(jù)庫系統(tǒng)本身是一個(gè)較為復(fù)雜的系統(tǒng)，影響其性能的因素也是多方面的。在不斷探索中，SQL語句的執(zhí)行效率逐漸得到重視，實(shí)驗(yàn)證明，SQL語句的執(zhí)行效率在提升系統(tǒng)性能中占有重要地位。在Oracle數(shù)據(jù)庫中，如何優(yōu)化SQL語句很值得我們深入探討。

1 影響SQL語句優(yōu)化的因素

優(yōu)化器可以完成部分工作，當(dāng)人為改變優(yōu)化器的執(zhí)行計(jì)劃時(shí)同樣要遵循這部分基礎(chǔ)。總的來說，Oracle優(yōu)化器制定執(zhí)行計(jì)劃的選擇基礎(chǔ)也是影響執(zhí)行計(jì)劃的主要因素，這些因素都可能在特定情況下改變執(zhí)行計(jì)劃的內(nèi)容，而這些因素都顯式或隱式的由SQL語句的書寫方式和執(zhí)行情況影響，主要有以下幾點(diǎn)：

1.1 表訪問方式

表的訪問方式主要有三種，全表掃描、Rowid訪問、索引掃描。這里，通過Rowid訪問表的速度是最快的，因?yàn)樗挥庙樞虻淖x取每一塊數(shù)據(jù)塊去查找符合條件的記錄，而是直接根據(jù)該記錄在數(shù)據(jù)磁盤中的位置獲取記錄信息；其次是索引訪問，它能獲取到該行記錄的Rowid值再查找進(jìn)行判斷；最后是全表掃描，它只能通過讀取全部數(shù)據(jù)塊進(jìn)行查找，極大增加了系統(tǒng)由于邏輯讀取進(jìn)行的消耗。在實(shí)際應(yīng)用中Rowid的值需要手動(dòng)獲取，因此并不可能在每個(gè)查詢條件中都根據(jù)Rowid進(jìn)行獲取，所以使用最多的是索引掃描對表進(jìn)行訪問。

1.2 表連接順序

當(dāng)有兩個(gè)或兩個(gè)以上的表進(jìn)行連接時(shí)，Oracle選擇一種合適的順序進(jìn)行連接。在Oracle數(shù)據(jù)庫中，無論進(jìn)行連接的表一共有多少個(gè)，每次進(jìn)行連接的表都是兩個(gè)，連接成一張表之后再與下一張表進(jìn)行連接，直至完成所有表的連接，所以每次連接的效率都只與當(dāng)前連接的兩個(gè)表有關(guān)。連接順序之所以會(huì)影響執(zhí)行效率是因?yàn)檫B接的兩個(gè)表有驅(qū)動(dòng)表和連接表之分。驅(qū)動(dòng)表是主表，是作為連接操作的外層循環(huán)的表。連接表是與驅(qū)動(dòng)表進(jìn)行連接的表，在連接中處于內(nèi)存循環(huán)的位置。CBO對驅(qū)動(dòng)表的每一行都對連接表進(jìn)行一次全掃描，所以O(shè)racle優(yōu)化器分別計(jì)算每個(gè)驅(qū)動(dòng)表和連接表的記錄條數(shù)，并選擇最小時(shí)間復(fù)雜度的順序在執(zhí)行計(jì)劃中被執(zhí)行。

1.3 表連接方式

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

1.4 查詢條件的篩選率

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

1.5 查詢條件的數(shù)據(jù)類型

優(yōu)化器根據(jù)查詢條件中傳入的值隱式轉(zhuǎn)換成表中字段所能匹配的類型。當(dāng)傳入的值和該字段在數(shù)據(jù)庫中創(chuàng)建時(shí)的類型不同時(shí)，Oracle嘗試進(jìn)行將其進(jìn)行類型轉(zhuǎn)換，如果轉(zhuǎn)換不成功則報(bào)錯(cuò)。所以O(shè)racle可以識別隱式和顯式的類型轉(zhuǎn)換，但每次的轉(zhuǎn)換都會(huì)耗費(fèi)一定的CPU和內(nèi)存資源。

1.6 排序操作

排序操作需消耗系統(tǒng)大量內(nèi)存和CPU，尤其是中間過程的排序，大部分都在內(nèi)存中直接排序并存儲(chǔ)中間結(jié)果，當(dāng)某個(gè)排序過程不是必須的或是可以被其他易操作的執(zhí)行步驟代替時(shí)，Oracle的CBO就會(huì)節(jié)省系統(tǒng)資源和時(shí)間的消耗使用其他方式代替。當(dāng)Oracle在執(zhí)行路徑中發(fā)現(xiàn)結(jié)果集已經(jīng)按照一定順序排好或者無需排序時(shí)就會(huì)省略排序過程直接下一步的操作，所以當(dāng)排序不是必須的選擇或已經(jīng)進(jìn)行過排序的結(jié)果集時(shí)，Oracle優(yōu)化器在選擇執(zhí)行計(jì)劃時(shí)忽略排序的步驟。

2 SQL語句優(yōu)化的建議

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

（1）將系統(tǒng)參數(shù)statistics—level設(shè)置為typica或all。這個(gè)參數(shù)的設(shè)置決定了收集的統(tǒng)計(jì)信息的內(nèi)容，typical或all級別收集的信息包含在ADDM所需的統(tǒng)計(jì)信息內(nèi)容中。

（2）獲取兩次快照的ID。當(dāng)數(shù)據(jù)庫中設(shè)置為自動(dòng)獲取快照時(shí)，此步驟可以省略，轉(zhuǎn)為直接使用數(shù)據(jù)庫對快照信息的分析結(jié)果。

（3）用戶授權(quán)。優(yōu)化建議的獲取需要advisor的權(quán)限，需確保當(dāng)前執(zhí)行的用戶擁有此權(quán)限。

（4）創(chuàng)建優(yōu)化任務(wù)。創(chuàng)建優(yōu)化任務(wù)是使用存儲(chǔ)過程完成的，將傳送過來的SQL語句和用戶名作為參數(shù)，調(diào)用DBMS-SQLTUNE包的CREATE-TUNING-TASK方法為該任務(wù)創(chuàng)建一個(gè)任務(wù)名，同時(shí)使用包屮的EXECUTE-TUNING-TASK方法執(zhí)行該任務(wù)。執(zhí)行完成后系統(tǒng)根據(jù)該SQL語句和統(tǒng)計(jì)信息生成優(yōu)化建議，并把執(zhí)行結(jié)果存放在任務(wù)日志中。

（5）根據(jù)任務(wù)名查看優(yōu)化建議。DBMS-SQLTUNE包提供了一個(gè)方法REPORT_TUNING_TASK （任務(wù)名）方法可以查看與任務(wù)名匹配的結(jié)果，結(jié)果中包含三部分。第一部分是此次優(yōu)化任務(wù)的基本信息，如任務(wù)名稱、執(zhí)行時(shí)問、范圍和執(zhí)行的語句等；第二部分是關(guān)于此次任務(wù)所找到的問題和優(yōu)化建議；第三部分是原有的執(zhí)行計(jì)劃以及采用優(yōu)化建議以后的執(zhí)行計(jì)劃的對比。當(dāng)該任務(wù)需要再次被運(yùn)行時(shí)，需要?jiǎng)h除在系統(tǒng)中存儲(chǔ)的該任務(wù)名相關(guān)的信息，DBMS_SQLTUNE包提供了一個(gè)DROP_TUNING-TASK （任務(wù)名）的方法除同名的任務(wù)，以便于下次執(zhí)行。

參考文獻(xiàn)

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

[2] 楊宇紅，石紅春.數(shù)據(jù)庫管理中SQL查詢優(yōu)化技術(shù)的應(yīng)用[J].信息與電腦，2011，1：108.

[3] 王躍.Oracle數(shù)據(jù)庫中SQL語句的優(yōu)化與分析[J].計(jì)算機(jī)光盤軟件與應(yīng)用，2011，8：162-163.