ORACLE-SQL語句性能優(yōu)化(全)

1、ORACLEORACLE培訓(xùn)培訓(xùn)課程主要討論：課程主要討論： SQL語句執(zhí)行的過程、語句執(zhí)行的過程、ORACLE優(yōu)化器，表之間的關(guān)聯(lián)，如何得到優(yōu)化器，表之間的關(guān)聯(lián)，如何得到SQL執(zhí)行計劃，如何分析執(zhí)行計劃執(zhí)行計劃，如何分析執(zhí)行計劃等內(nèi)容，從而由淺到深的方式了解等內(nèi)容，從而由淺到深的方式了解SQL優(yōu)化的過程，使大家逐步掌握優(yōu)化的過程，使大家逐步掌握SQL優(yōu)化。優(yōu)化。n 優(yōu)化基礎(chǔ)知識優(yōu)化基礎(chǔ)知識n 性能調(diào)整綜述性能調(diào)整綜述n 有效的應(yīng)用設(shè)計有效的應(yīng)用設(shè)計n SQL語句的處理過程語句的處理過程n Oracle的優(yōu)化器的優(yōu)化器n Oracle的執(zhí)行計劃的執(zhí)行計劃n 注意事項注意事項n性能管理性能管理n

2、性能問題性能問題n調(diào)整的方法調(diào)整的方法nSQL優(yōu)化機制優(yōu)化機制n應(yīng)用的調(diào)整應(yīng)用的調(diào)整nSQL語句的處理過程語句的處理過程共享共享SQLSQL區(qū)域區(qū)域SQLSQL語句處理的階段語句處理的階段共享游標(biāo)共享游標(biāo)SQLSQL編碼標(biāo)準(zhǔn)編碼標(biāo)準(zhǔn)nOracle 優(yōu)化器介紹優(yōu)化器介紹nSQL Tunning Tipsn優(yōu)化優(yōu)化Tools 盡早開始盡早開始 設(shè)立合適目標(biāo)設(shè)立合適目標(biāo) 邊調(diào)整邊監(jiān)控邊調(diào)整邊監(jiān)控 相關(guān)人員進行合作相關(guān)人員進行合作 及時處理過程中發(fā)生的意外和變化及時處理過程中發(fā)生的意外和變化 80/2080/20定律定律隨著軟件技術(shù)的不斷發(fā)展，系統(tǒng)性能越來越重要。隨著軟件技術(shù)的不斷發(fā)展，系統(tǒng)性能越來越

3、重要。系統(tǒng)性能主要用：系統(tǒng)性能主要用：系統(tǒng)響應(yīng)時間和并發(fā)性來衡量。系統(tǒng)響應(yīng)時間和并發(fā)性來衡量。造成造成SQL語句性能不佳大致有兩個原因：語句性能不佳大致有兩個原因：開發(fā)人員只關(guān)注查詢結(jié)果的正確性，忽視查詢語句的效率。開發(fā)人員只關(guān)注查詢結(jié)果的正確性，忽視查詢語句的效率。開發(fā)人員只關(guān)注開發(fā)人員只關(guān)注SQL語句本身的效率，對語句本身的效率，對SQL語句執(zhí)行原理、影響語句執(zhí)行原理、影響SQL執(zhí)行效率的主要因素不清楚。執(zhí)行效率的主要因素不清楚。* 前者可以通過深入學(xué)習(xí)前者可以通過深入學(xué)習(xí)SQL語法及各種語法及各種SQL調(diào)優(yōu)技巧進行解決。調(diào)優(yōu)技巧進行解決。 SQL調(diào)優(yōu)是一個系統(tǒng)工程，熟悉調(diào)優(yōu)是一個系統(tǒng)工程

4、，熟悉SQL語法、掌握各種內(nèi)嵌函數(shù)、分語法、掌握各種內(nèi)嵌函數(shù)、分 析函數(shù)的用法只是編寫高效析函數(shù)的用法只是編寫高效SQL的必要條件。的必要條件。* 后者從分析后者從分析SQL語句執(zhí)行原理入手，指出語句執(zhí)行原理入手，指出SQL調(diào)優(yōu)應(yīng)在優(yōu)化調(diào)優(yōu)應(yīng)在優(yōu)化SQL解解 析和優(yōu)化析和優(yōu)化CBO上。上。調(diào)優(yōu)領(lǐng)域：調(diào)優(yōu)領(lǐng)域：應(yīng)用程序級調(diào)優(yōu)：應(yīng)用程序級調(diào)優(yōu)： * SQL語句調(diào)優(yōu)語句調(diào)優(yōu) * 管理變化調(diào)優(yōu)管理變化調(diào)優(yōu)2. 實例級調(diào)優(yōu)實例級調(diào)優(yōu) * 內(nèi)存內(nèi)存 * 數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)結(jié)構(gòu) * 實例配置實例配置3. 操作系統(tǒng)交互操作系統(tǒng)交互 * I/O * SWAP * Parameters本課程內(nèi)容只講解討論應(yīng)用程序級：本

5、課程內(nèi)容只講解討論應(yīng)用程序級：Oracle SQL語句調(diào)優(yōu)及管理變化調(diào)優(yōu)語句調(diào)優(yōu)及管理變化調(diào)優(yōu) 調(diào)整業(yè)務(wù)功能調(diào)整業(yè)務(wù)功能 調(diào)整數(shù)據(jù)設(shè)計調(diào)整數(shù)據(jù)設(shè)計 調(diào)整流程設(shè)計調(diào)整流程設(shè)計 調(diào)整調(diào)整SQL語句語句 調(diào)整物理結(jié)構(gòu)調(diào)整物理結(jié)構(gòu) 調(diào)整內(nèi)存分配調(diào)整內(nèi)存分配 調(diào)整調(diào)整I/O 調(diào)整內(nèi)存競爭調(diào)整內(nèi)存競爭 調(diào)整操作系統(tǒng)調(diào)整操作系統(tǒng)n開發(fā)人員不能只注重功能的實現(xiàn)，不開發(fā)人員不能只注重功能的實現(xiàn)，不管性能如何管性能如何n開發(fā)人員不能把開發(fā)人員不能把Oracle當(dāng)成一個黑盒當(dāng)成一個黑盒子，必須了解其結(jié)構(gòu)、處理子，必須了解其結(jié)構(gòu)、處理SQL和數(shù)和數(shù)據(jù)的方法據(jù)的方法n必需遵守既定的開發(fā)規(guī)范必需遵守既定的開發(fā)規(guī)范n未經(jīng)過

6、未經(jīng)過SQL語句優(yōu)化的模塊不要上線語句優(yōu)化的模塊不要上線n定位有問題的語句定位有問題的語句n檢查執(zhí)行計劃檢查執(zhí)行計劃n檢查執(zhí)行過程中優(yōu)化器的統(tǒng)計信息檢查執(zhí)行過程中優(yōu)化器的統(tǒng)計信息n分析相關(guān)表的記錄數(shù)、索引情況分析相關(guān)表的記錄數(shù)、索引情況n改寫改寫SQL語句、使用語句、使用HINT、調(diào)整索引、調(diào)整索引、表分析表分析n有些有些SQL語句不具備優(yōu)化的可能，需語句不具備優(yōu)化的可能，需要優(yōu)化處理方式要優(yōu)化處理方式n達到最佳執(zhí)行計劃達到最佳執(zhí)行計劃n盡量簡單，模塊化盡量簡單，模塊化n易讀、易維護易讀、易維護n節(jié)省資源節(jié)省資源內(nèi)存內(nèi)存CPU掃描的數(shù)據(jù)塊要少掃描的數(shù)據(jù)塊要少少排序少排序n不造成死鎖不造成死鎖

7、ORACLE將執(zhí)行過的將執(zhí)行過的SQL語句存放在內(nèi)存的共享語句存放在內(nèi)存的共享池池(shared buffer pool)中，可以被所有的數(shù)據(jù)庫用戶共享。中，可以被所有的數(shù)據(jù)庫用戶共享。當(dāng)你執(zhí)行一個當(dāng)你執(zhí)行一個SQL語句語句(有時被稱為一個游標(biāo)有時被稱為一個游標(biāo))時時,如果它和之前的執(zhí)行過的語句完全相同如果它和之前的執(zhí)行過的語句完全相同, ORACLE就能很快獲得已經(jīng)被解析的語句以及最好就能很快獲得已經(jīng)被解析的語句以及最好的的 執(zhí)行路徑執(zhí)行路徑. 這個功能大大地提高了這個功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用。的執(zhí)行性能并節(jié)省了內(nèi)存的使用。為了不重復(fù)解析相同的為了不重復(fù)解析相同的S

8、QL語句，在第一次解析之后，語句，在第一次解析之后，Oracle將將SQL語句存放在內(nèi)存中。這塊位于系統(tǒng)全局區(qū)域語句存放在內(nèi)存中。這塊位于系統(tǒng)全局區(qū)域SGA(systemglobal area)的共享池的共享池(shared buffer poo1)中的中的內(nèi)存可以被所有的數(shù)據(jù)庫用戶共享。因此，當(dāng)你執(zhí)行一個內(nèi)存可以被所有的數(shù)據(jù)庫用戶共享。因此，當(dāng)你執(zhí)行一個SQL語句語句(有時被稱為一個游標(biāo)有時被稱為一個游標(biāo))時，如果它和之前執(zhí)行過的語句完全時，如果它和之前執(zhí)行過的語句完全相同，相同，Oracle就能很快獲得已經(jīng)被解析的語句以及最好的執(zhí)行就能很快獲得已經(jīng)被解析的語句以及最好的執(zhí)行方案。方案。Or

9、acle的這個功能大大地提高了的這個功能大大地提高了SQL的執(zhí)行性能并節(jié)省的執(zhí)行性能并節(jié)省了內(nèi)存的使用。了內(nèi)存的使用?？上У氖牵上У氖?，Oracle只對簡單的表提供高速緩沖只對簡單的表提供高速緩沖(cache bufferiIlg)，這個功能并不適用于多表連接查詢。數(shù)據(jù)，這個功能并不適用于多表連接查詢。數(shù)據(jù)庫管理員必須在啟動參數(shù)文件中為這個區(qū)域設(shè)置合適的參數(shù)，庫管理員必須在啟動參數(shù)文件中為這個區(qū)域設(shè)置合適的參數(shù)，當(dāng)這個內(nèi)存區(qū)域越大，就可以保留更多的語句，當(dāng)然被共享的當(dāng)這個內(nèi)存區(qū)域越大，就可以保留更多的語句，當(dāng)然被共享的可能性也就越大了。當(dāng)向可能性也就越大了。當(dāng)向Oracle提交一個提交一個S

10、QL語句時，語句時，Oracle會首先在這塊內(nèi)存中查找相同的語句。會首先在這塊內(nèi)存中查找相同的語句。當(dāng)前被執(zhí)行的語句和共享池中的語句必須完全相同當(dāng)前被執(zhí)行的語句和共享池中的語句必須完全相同 （包括大小寫、空格、換行等）（包括大小寫、空格、換行等）兩個語句所指的對象必須完全相同兩個語句所指的對象必須完全相同 （同義詞與表是（同義詞與表是不同的對象）不同的對象）兩個兩個SQL語句中必須使用相同的名字的綁定變量語句中必須使用相同的名字的綁定變量(bind variables) 注意：注意：Oracle對兩者采取的是一種嚴(yán)格匹配策略，要達成共享。對兩者采取的是一種嚴(yán)格匹配策略，要達成共享。SQL語句必

11、語句必須完全相同須完全相同(包括空格、換行等包括空格、換行等)。能夠使用共享的語句必須滿足三個。能夠使用共享的語句必須滿足三個條件：條件： 字符級的比較。字符級的比較。當(dāng)前被執(zhí)行的語句和共享池中的語句必須完全相同。當(dāng)前被執(zhí)行的語句和共享池中的語句必須完全相同。例如：例如： SELECT * FROM ATABLE；和下面每一個；和下面每一個SQL語句都不同：語句都不同：SELECT *from ATABLESelect * From Atable； 語句所指對象必須完全相同語句所指對象必須完全相同 即兩條即兩條SQL語句操作的數(shù)據(jù)庫對象必須同一。語句操作的數(shù)據(jù)庫對象必須同一。語句中必須使用相同

12、命名的綁定變量。如：第一組的兩個語句中必須使用相同命名的綁定變量。如：第一組的兩個SQL語句是相同語句是相同的，可以共享；而第二組中兩個語句不同，即使在運行時賦予不同的綁定變的，可以共享；而第二組中兩個語句不同，即使在運行時賦予不同的綁定變量以相同的值：量以相同的值： 第一組第一組 select pin，name from people where pin = ：blk1.pin；select pin，name from people where pin =：blk1.pin；第二組第二組 select pin，name from people where pin =：blk1.ot_jnd；

13、select pin，name from people where pin = ：blk1.ov_jnd；共享共享SQL區(qū)域區(qū)域Sql 處理過程處理過程SQL PARSE與共享與共享SQL語句語句當(dāng)一個當(dāng)一個Oracle實例接收一條實例接收一條sql后后1、Create a Cursor 創(chuàng)建游標(biāo)創(chuàng)建游標(biāo)2、Parse the Statement 分析語句分析語句3、Describe Results of a Query 描述查詢的結(jié)果集描述查詢的結(jié)果集4、Define Output of a Query 定義查詢的輸出數(shù)據(jù)定義查詢的輸出數(shù)據(jù)5、Bind Any Variables 綁定變量綁

14、定變量6、Parallelize the Statement 并行執(zhí)行語句并行執(zhí)行語句7、Run the Statement 運行語句運行語句8、Fetch Rows of a Query 取查詢出來的行取查詢出來的行9、Close the Cursor 關(guān)閉游標(biāo)關(guān)閉游標(biāo) 為什么要為什么要bind variables?n字符級的比較字符級的比較:SELECT * FROM USER_FILES WHERE USER_NO = 10001234; 與與SELECT * FROM USER_FILES WHERE USER_NO = 10004321;n檢查：檢查：select name,exec

15、utionsfrom v$db_object_cachewhere name like select * from user_files%什么叫做重編譯問題什么叫做重編譯問題什么叫做重編譯？什么叫做重編譯？下面這個語句每執(zhí)行一次就需要在下面這個語句每執(zhí)行一次就需要在SHARE POOL 硬解析一硬解析一次，一百萬用戶就是一百萬次，消耗次，一百萬用戶就是一百萬次，消耗CPU和內(nèi)存，如果業(yè)務(wù)和內(nèi)存，如果業(yè)務(wù)量大，很可能導(dǎo)致宕庫量大，很可能導(dǎo)致宕庫如果綁定變量，則只需要硬解析一次，重復(fù)調(diào)用即可如果綁定變量，則只需要硬解析一次，重復(fù)調(diào)用即可select * from dConMsg where con

16、tract_no = 32013484095139綁定變量解決重編譯問題綁定變量解決重編譯問題未使用綁定變量的語句未使用綁定變量的語句sprintf(sqlstr, insert into scott.test1 (num1, num2) values (%d,%d),n_var1, n_var2);EXEC SQL EXECUTE IMMEDIATE :sqlstr ;EXEC SQL COMMIT; 使用綁定變量的語句使用綁定變量的語句 strcpy(sqlstr, insert into test (num1, num2) values (:v1, :v2);EXEC SQL PREPA

17、RE sql_stmt FROM :sqlstr;EXEC SQL EXECUTE sql_stmt USING :n_var1, :n_var2;EXEC SQL COMMIT; 綁定變量的注意事項綁定變量的注意事項注意：注意：1、不要使用數(shù)據(jù)庫級的變量綁定參數(shù)、不要使用數(shù)據(jù)庫級的變量綁定參數(shù)cursor_sharing來強來強制綁定，無論其值為制綁定，無論其值為 force 還是還是similar2、有些帶、有些帶 0性能優(yōu)于性能優(yōu)于select count(*)from tab；盡量少嵌套子查詢，這種查詢會消耗大量的盡量少嵌套子查詢，這種查詢會消耗大量的CPU資源；對于有比較多資源；對于

18、有比較多or運算的查詢，建議分成多個查詢，用運算的查詢，建議分成多個查詢，用union all聯(lián)結(jié)起來；多表查詢聯(lián)結(jié)起來；多表查詢的查詢語句中，選擇最有效率的表名順序。的查詢語句中，選擇最有效率的表名順序。Oracle解析器對表解析從解析器對表解析從右到左，所以記錄少的表放在右邊。右到左，所以記錄少的表放在右邊。 盡量多用盡量多用commit語句提交事務(wù)，可以及時釋放資源、解語句提交事務(wù)，可以及時釋放資源、解鎖、釋放日志空間、減少管理花費；在頻繁的、性能要求比較高的鎖、釋放日志空間、減少管理花費；在頻繁的、性能要求比較高的數(shù)據(jù)操作中，盡量避免遠程訪問，如數(shù)據(jù)庫鏈等，訪問頻繁的表可數(shù)據(jù)操作中，盡

19、量避免遠程訪問，如數(shù)據(jù)庫鏈等，訪問頻繁的表可以常駐內(nèi)存：以常駐內(nèi)存：alter tablecache； 在在Oracle中動態(tài)執(zhí)行中動態(tài)執(zhí)行SQL，盡量用，盡量用execute方式，不用方式，不用dbms_sql包。包。* SQL Tunning Tips *sql 語句的編寫原則和優(yōu)化語句的編寫原則和優(yōu)化 n隨著數(shù)據(jù)庫中數(shù)據(jù)的增加，系統(tǒng)的響隨著數(shù)據(jù)庫中數(shù)據(jù)的增加，系統(tǒng)的響應(yīng)速度就成為目前系統(tǒng)需要解決的最應(yīng)速度就成為目前系統(tǒng)需要解決的最主要的問題之一。系統(tǒng)優(yōu)化中一個很主要的問題之一。系統(tǒng)優(yōu)化中一個很重要的方面就是重要的方面就是SQLSQL語句的優(yōu)化。對于語句的優(yōu)化。對于大量數(shù)據(jù)，劣質(zhì)大量數(shù)據(jù)，

20、劣質(zhì)SQLSQL語句和優(yōu)質(zhì)語句和優(yōu)質(zhì)SQLSQL語語句之間的速度差別可以達到上百倍，句之間的速度差別可以達到上百倍，對于一個系統(tǒng)不是簡單地能實現(xiàn)其功對于一個系統(tǒng)不是簡單地能實現(xiàn)其功能就可，而是要寫出高質(zhì)量的能就可，而是要寫出高質(zhì)量的SQLSQL語句，語句，提高系統(tǒng)的可用性。提高系統(tǒng)的可用性。n在多數(shù)情況下，在多數(shù)情況下，OracleOracle使用索引來更使用索引來更快地遍歷表，優(yōu)化器主要根據(jù)定義的快地遍歷表，優(yōu)化器主要根據(jù)定義的索引來提高性能。如果在索引來提高性能。如果在SQLSQL語句的語句的wherewhere子句中寫的子句中寫的SQLSQL代碼不合理，就代碼不合理，就會造成優(yōu)化器刪去索

21、引而使用全表掃會造成優(yōu)化器刪去索引而使用全表掃描，一般就這種描，一般就這種SQLSQL語句就是所謂的語句就是所謂的劣劣質(zhì)質(zhì)SQLSQL語句語句。sql 語句的編寫原則和優(yōu)化語句的編寫原則和優(yōu)化 n在編寫在編寫SQLSQL語句時我們應(yīng)清楚優(yōu)化器根語句時我們應(yīng)清楚優(yōu)化器根據(jù)何種原則來使用索引，這有助于寫據(jù)何種原則來使用索引，這有助于寫出高性能的出高性能的SQLSQL語句。語句。nSQLSQL語句的編寫原則和語句的編寫原則和SQLSQL語句的優(yōu)化，語句的優(yōu)化，請跟我一起學(xué)習(xí)以下幾方面：請跟我一起學(xué)習(xí)以下幾方面：Tunning Tip的各個方面的各個方面1.不要讓不要讓Oracle做得太多；做得太多；

22、2.給優(yōu)化器更明確的命令；給優(yōu)化器更明確的命令； 3.減少訪問次數(shù)；減少訪問次數(shù)；4.細節(jié)上的影響；細節(jié)上的影響；1.不要讓不要讓Oracle做得太多做得太多避免復(fù)雜的多表關(guān)聯(lián)避免復(fù)雜的多表關(guān)聯(lián)select from user_files uf, df_money_files dm, cw_charge_record ccwhere uf.user_no = dm.user_noand dm.user_no = cc.user_noand and not exists(select )?很難優(yōu)化，隨著數(shù)據(jù)量的增加性能的風(fēng)很難優(yōu)化，隨著數(shù)據(jù)量的增加性能的風(fēng)險很大。險很大。避免使用避免使用 * 當(dāng)

23、你想在當(dāng)你想在SELECT子句中列出所有的子句中列出所有的COLUMN時時,使用動態(tài)使用動態(tài)SQL列引用列引用 * 是一個方便的方法是一個方便的方法.不不幸的是幸的是,這是一個非常低這是一個非常低效的方法效的方法. 實際上實際上,ORACLE在解析的過在解析的過程中程中, 會將會將* 依次轉(zhuǎn)依次轉(zhuǎn)換成所有的列名換成所有的列名, 這個工作是通過查詢這個工作是通過查詢數(shù)據(jù)字典完成的數(shù)據(jù)字典完成的, 這意這意味著將耗費更多的時間；味著將耗費更多的時間；只提取你所要使用的列；只提取你所要使用的列；使用別名能夠加快解析速度；使用別名能夠加快解析速度；避免使用耗費資源的操作避免使用耗費資源的操作帶有帶有D

24、ISTINCT,UNION,MINUS,INTERSECT,ORDER BY的的SQL語句會啟動語句會啟動SQL引擎執(zhí)行耗費資源的排序引擎執(zhí)行耗費資源的排序(SORT)功能功能. DISTINCT需要一次排序操作需要一次排序操作, 而其他的至少需要執(zhí)行兩次而其他的至少需要執(zhí)行兩次排序排序.例如例如,一個一個UNION查詢查詢,其中每個查詢都帶有其中每個查詢都帶有GROUP BY子句子句, GROUP BY會觸發(fā)嵌入排序會觸發(fā)嵌入排序(NESTED SORT) ; 這樣這樣, 每個每個查詢需要執(zhí)行一次排序查詢需要執(zhí)行一次排序, 然后在執(zhí)行然后在執(zhí)行UNION時時, 又一個唯一又一個唯一排序排序(

25、SORT UNIQUE)操作被執(zhí)行而且它只能在前面的嵌入操作被執(zhí)行而且它只能在前面的嵌入排序結(jié)束后才能開始執(zhí)行排序結(jié)束后才能開始執(zhí)行. 嵌入的排序的深度會大大影響查嵌入的排序的深度會大大影響查詢的效率詢的效率.通常通常, 帶有帶有UNION, MINUS , INTERSECT的的SQL語句都可以語句都可以用其他方式重寫用其他方式重寫.例如例如:低效低效: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO高效高效: SELECT DEPT_NO,DEPT_NAME FROM DEPT

26、D WHERE EXISTS ( SELECT X FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);用用EXISTS替換替換DISTINCT用用UNION-ALL 替換替換UNION ( if possible)當(dāng)當(dāng)SQL語句需要語句需要UNION兩個查詢結(jié)果集合時兩個查詢結(jié)果集合時,這兩個結(jié)果集合會以這兩個結(jié)果集合會以UNION-ALL的方式被合并的方式被合并, 然后在輸出最終結(jié)果前進行排序然后在輸出最終結(jié)果前進行排序.舉例舉例: 低效：低效： SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE

27、 TRAN_DATE = 31-DEC-95 UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = 31-DEC-95高效高效: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = 31-DEC-95 UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = 31-DEC-952. 給優(yōu)化器更明確的命令給

28、優(yōu)化器更明確的命令自動選擇索引自動選擇索引如果表中有兩個以上（包括兩個）索引，其中有一個唯一性如果表中有兩個以上（包括兩個）索引，其中有一個唯一性索引，而其他是非唯一性索引，而其他是非唯一性在這種情況下，在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯將使用唯一性索引而完全忽略非唯一性索引一性索引舉例舉例:SELECT ENAMEFROM EMPWHERE EMPNO = 2326 AND DEPTNO = 20 ;這里，只有這里，只有EMPNO上的索引是唯一性的，所以上的索引是唯一性的，所以EMPNO索索引將用來檢索記錄引將用來檢索記錄TABLE ACCESS BY ROWID ON

29、 EMP INDEX UNIQUE SCAN ON EMP_NO_IDX至少要包含組合索引的第一列至少要包含組合索引的第一列如果索引是建立在多個列上如果索引是建立在多個列上, 只有在它的第一個列只有在它的第一個列(leading column)被被where子句引用時子句引用時,優(yōu)化器才會選擇使用該索引優(yōu)化器才會選擇使用該索引. SQL create table multiindexusage ( inda number , indb number , descr varchar2(10);Table created.SQL create index multindex on multiind

30、exusage(inda,indb);Index created.SQL set autotrace traceonlySQL select * from multiindexusage where inda = 1;Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (BY INDEX ROWID) OF MULTIINDEXUSAGE 2 1 INDEX (RANGE SCAN) OF MULTINDEX (NON-UNIQUE)SQL select * from multiindexusage wher

31、e indb = 1;Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF MULTIINDEXUSAGE 很明顯很明顯, 當(dāng)僅引用索引的第二個列時當(dāng)僅引用索引的第二個列時,優(yōu)化器使用了全表掃描而忽略了索引優(yōu)化器使用了全表掃描而忽略了索引避免在索引列上使用函數(shù)避免在索引列上使用函數(shù)WHERE子句中，如果索引列是函數(shù)的一部分優(yōu)化器將不子句中，如果索引列是函數(shù)的一部分優(yōu)化器將不使用索引而使用全表掃描使用索引而使用全表掃描舉例舉例:低效：低效：SELECT FROM DEPTWHERE SAL *

32、 12 25000;高效高效:SELECT FROM DEPTWHERE SAL 25000/12;避免使用前置通配符避免使用前置通配符WHERE子句中子句中, 如果索引列所對應(yīng)的值的第一個字符由通如果索引列所對應(yīng)的值的第一個字符由通配符配符(WILDCARD)開始開始, 索引將不被采用索引將不被采用. SELECT USER_NO,USER_NAME,ADDRESSFROM USER_FILESWHERE USER_NO LIKE %109204421; 在這種情況下，在這種情況下，ORACLE將使用全表掃描將使用全表掃描.避免在索引列上使用避免在索引列上使用NOT通常，我們要避免在索引列上

33、使用通常，我們要避免在索引列上使用NOT, NOT會產(chǎn)生在和在會產(chǎn)生在和在索引列上使用函數(shù)相同的影響索引列上使用函數(shù)相同的影響. 當(dāng)當(dāng)ORACLE”遇到遇到”NOT,他他就就會停止使用索引轉(zhuǎn)而執(zhí)行全表掃描會停止使用索引轉(zhuǎn)而執(zhí)行全表掃描.舉例舉例: 低效低效: (這里這里,不使用索引不使用索引) SELECT FROM DEPT WHERE DEPT_CODE NOT = 0; 高效高效: (這里這里,使用了索引使用了索引) SELECT FROM DEPT WHERE DEPT_CODE 0;避免在索引列上使用避免在索引列上使用 IS NULL和和IS NOT NULL避免在索引中使用任何可以

34、為空的列，避免在索引中使用任何可以為空的列，ORACLE將無法使用該將無法使用該索引索引 對于單列索引，如果列包含空值，索引中將不存在此記對于單列索引，如果列包含空值，索引中將不存在此記錄錄. 對于復(fù)合索引，如果每個列都為空，索引中同樣不存在此對于復(fù)合索引，如果每個列都為空，索引中同樣不存在此記錄記錄.如果至少有一個列不為空，則記錄存在于索引中如果至少有一個列不為空，則記錄存在于索引中如果唯一性索引建立在表的如果唯一性索引建立在表的A列和列和B列上列上, 并且表中存在一條記并且表中存在一條記錄的錄的A,B值為值為(123,null) , ORACLE將不接受下一條具有相同將不接受下一條具有相同

35、A,B值（值（123,null）的記錄）的記錄(插入插入). 然而如果所有的索引列都為然而如果所有的索引列都為空，空，ORACLE將認為整個鍵值為空而空不等于空將認為整個鍵值為空而空不等于空. 因此你可以因此你可以插入插入1000條具有相同鍵值的記錄條具有相同鍵值的記錄,當(dāng)然它們都是空當(dāng)然它們都是空!因為空值不存在于索引列中因為空值不存在于索引列中,所以所以WHERE子句中對索引列進行子句中對索引列進行空值比較將使空值比較將使ORACLE停用該索引停用該索引.任何在任何在where子句中使用子句中使用is null或或is not null的語句優(yōu)化器是的語句優(yōu)化器是不允許使用索引的。不允許使

36、用索引的。避免出現(xiàn)索引列自動轉(zhuǎn)換避免出現(xiàn)索引列自動轉(zhuǎn)換當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時當(dāng)比較不同數(shù)據(jù)類型的數(shù)據(jù)時, ORACLE自動對列進行簡單自動對列進行簡單的類型轉(zhuǎn)換的類型轉(zhuǎn)換.假設(shè)假設(shè)EMP_TYPE是一個字符類型的索引列是一個字符類型的索引列.SELECT USER_NO,USER_NAME,ADDRESSFROM USER_FILESWHERE USER_NO = 109204421這個語句被這個語句被ORACLE轉(zhuǎn)換為轉(zhuǎn)換為:SELECT USER_NO,USER_NAME,ADDRESSFROM USER_FILESWHERE TO_NUMBER(USER_NO) = 10920442

37、1 因為內(nèi)部發(fā)生的類型轉(zhuǎn)換因為內(nèi)部發(fā)生的類型轉(zhuǎn)換, 這個索引將不會被用到這個索引將不會被用到! 在查詢時盡量少用格式轉(zhuǎn)換在查詢時盡量少用格式轉(zhuǎn)換n如用如用 WHERE a.order_no = b.order_no n不用不用 WHERE TO_NUMBER (substr(a.order_no, instr(b.order_no, .) - 1) = TO_NUMBER (substr(a.order_no, instr(b.order_no, .) - 1)3.減少訪問次數(shù)減少訪問次數(shù)減少訪問數(shù)據(jù)庫的次數(shù)減少訪問數(shù)據(jù)庫的次數(shù)當(dāng)執(zhí)行每條當(dāng)執(zhí)行每條SQL語句時語句時, ORACLE在內(nèi)在內(nèi)部執(zhí)

38、行了許多工作部執(zhí)行了許多工作: 解析解析SQL語句語句, 估算索引的利用率估算索引的利用率, 綁定綁定變量變量 , 讀數(shù)據(jù)塊等等讀數(shù)據(jù)塊等等. 由此可見由此可見, 減少訪問數(shù)據(jù)庫的次數(shù)減少訪問數(shù)據(jù)庫的次數(shù) , 就能就能實際上減少實際上減少ORACLE的工作量的工作量.類比，工程實施類比，工程實施使用使用DECODE來減少處理時間來減少處理時間例如例如: SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0020 AND ENAME LIKESMITH%; SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO

39、 = 0030 AND ENAME LIKESMITH%;你可以用你可以用DECODE函數(shù)高效地得到相同結(jié)果函數(shù)高效地得到相同結(jié)果SELECT COUNT(DECODE(DEPT_NO,0020,X,NULL) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,X,NULL) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,NULL) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,NULL) D0030_SALFROM EMP WHERE ENAME LIKE SMITH%;減少對表的查詢減少對表的查

40、詢在含有子查詢的在含有子查詢的SQL語句中語句中,要特別注意減少對表的查詢要特別注意減少對表的查詢.例如例如: 低效低效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) ANDDB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) 高效高效 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_N

41、AME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604) 4. 細節(jié)上的影響細節(jié)上的影響WHERE子句中的連接順序子句中的連接順序ORACLE采用自下而上的順序解析采用自下而上的順序解析WHERE子句子句,根據(jù)這個原根據(jù)這個原理理, 當(dāng)在當(dāng)在WHERE子句中有多個表聯(lián)接時，子句中有多個表聯(lián)接時，WHERE子句中排子句中排在最后的表應(yīng)當(dāng)是返回行數(shù)可能最少的表，有過濾條件的子在最后的表應(yīng)當(dāng)是返回行數(shù)可能最少的表，有過濾條件的子句應(yīng)放在句應(yīng)放在WHERE子句中的最后。子句中的最后。n如：設(shè)從如：設(shè)從emp表查到的數(shù)據(jù)比較少或該表的過濾條件比較確定，能大表查到

42、的數(shù)據(jù)比較少或該表的過濾條件比較確定，能大大縮小查詢范圍，則將最具有選擇性部分放在大縮小查詢范圍，則將最具有選擇性部分放在WHERE子句中的最后：子句中的最后：select * from emp e,dept d where d.deptno 10 and e.deptno =30 ; n如果如果dept表返回的記錄數(shù)較多的話，上面的查詢語句會比下面的查詢表返回的記錄數(shù)較多的話，上面的查詢語句會比下面的查詢語句響應(yīng)快得多。語句響應(yīng)快得多。select * from emp e,dept d where e.deptno =30 and d.deptno 10 ;WHERE子句子句 函數(shù)、表達式

43、使用函數(shù)、表達式使用n最好不要在最好不要在WHERE子句中使用函或表子句中使用函或表達式，如果要使用的話，最好統(tǒng)一使達式，如果要使用的話，最好統(tǒng)一使用相同的表達式或函數(shù)，這樣便于以用相同的表達式或函數(shù)，這樣便于以后使用合理的索引。后使用合理的索引。Order by語句語句 nORDER BY語句決定了語句決定了Oracle如何將返回的查詢?nèi)绾螌⒎祷氐牟樵兘Y(jié)果排序。結(jié)果排序。Order by語句對要排序的列沒有什么語句對要排序的列沒有什么特別的限制，也可以將函數(shù)加入列中（象聯(lián)接或特別的限制，也可以將函數(shù)加入列中（象聯(lián)接或者附加等）。者附加等）。任何在任何在Order by語句的非索引項或語句的非

44、索引項或者有計算表達式都將降低查詢速度。者有計算表達式都將降低查詢速度。n仔細檢查仔細檢查order by語句以找出非索引項或者表達語句以找出非索引項或者表達式，它們會降低性能。解決這個問題的辦法就是式，它們會降低性能。解決這個問題的辦法就是重寫重寫order by語句以使用索引，也可以為所使用語句以使用索引，也可以為所使用的列建立另外一個索引，同時應(yīng)絕對避免在的列建立另外一個索引，同時應(yīng)絕對避免在order by子句中使用表達式。子句中使用表達式。聯(lián)接列聯(lián)接列 對于有聯(lián)接的列，即使最后的聯(lián)接值為一個靜態(tài)值，優(yōu)化器對于有聯(lián)接的列，即使最后的聯(lián)接值為一個靜態(tài)值，優(yōu)化器是不會使用索引的。是不會使用

45、索引的。 select * from employss where first_name|last_name =Beill Cliton; 系統(tǒng)優(yōu)化器對基于系統(tǒng)優(yōu)化器對基于last_name創(chuàng)建的索引沒有使用。創(chuàng)建的索引沒有使用。 當(dāng)采用下面這種當(dāng)采用下面這種SQL語句的編寫，語句的編寫，Oracle系統(tǒng)就可以采用基系統(tǒng)就可以采用基于于last_name創(chuàng)建的索引。創(chuàng)建的索引。 select * from employee where first_name =Beill and last_name =Cliton; 帶通配符（帶通配符（%）的）的like語句語句 通配符（通配符（%）在搜尋詞首

46、出現(xiàn)，）在搜尋詞首出現(xiàn)，Oracle系統(tǒng)不使用系統(tǒng)不使用last_name的索引。的索引。select * from employee where last_name like %cliton%; 在很多情況下可能無法避免這種情況，但是一定要心中有底在很多情況下可能無法避免這種情況，但是一定要心中有底，通配符如此使用會降低查詢速度。然而當(dāng)通配符出現(xiàn)在字，通配符如此使用會降低查詢速度。然而當(dāng)通配符出現(xiàn)在字符串其他位置時，優(yōu)化器就能利用索引。在下面的查詢中索符串其他位置時，優(yōu)化器就能利用索引。在下面的查詢中索引得到了使用：引得到了使用： select * from employee where l

47、ast_name like c%; 用用Where子句替換子句替換HAVING子句子句避免使用避免使用HAVING子句子句, HAVING 只會在檢索出所有記錄之后才對結(jié)果只會在檢索出所有記錄之后才對結(jié)果集進行過濾集進行過濾. 這個處理需要排序這個處理需要排序,總計等操作總計等操作. 如果能通過如果能通過WHERE子句限子句限制記錄的數(shù)目制記錄的數(shù)目,那就能減少這方面的開銷那就能減少這方面的開銷.例如例如: 低效低效: SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION != SYDNEY

48、 AND REGION != PERTH 高效高效 SELECT REGION，AVG(LOG_SIZE) FROM LOCATION WHERE REGION REGION != SYDNEY AND REGION != PERTH GROUP BY REGION 順序順序 WHERE GROUP HAVING用用NOT EXISTS 替代替代 NOT IN在子查詢中在子查詢中,NOT IN子句將執(zhí)行一個內(nèi)部的排序和合并子句將執(zhí)行一個內(nèi)部的排序和合并. 無論在哪種情況無論在哪種情況下下,NOT IN都是最低效的都是最低效的 (因為它對子查詢中的表執(zhí)行了一個全表遍歷因為它對子查詢中的表執(zhí)行了一

49、個全表遍歷). 使用使用NOT EXISTS 子句可以有效地利用索引。盡可能使用子句可以有效地利用索引。盡可能使用NOT EXISTS來代替來代替NOT IN，盡管二者都使用了，盡管二者都使用了NOT（不能使用索引而降低速度），（不能使用索引而降低速度），NOT EXISTS要比要比NOT IN查詢效率更高。查詢效率更高。例如例如:語句語句1 SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp); 語句語句2 SELECT dname, deptno FROM dept WHERE NOT EXI

50、STS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno); 2 2要比要比1 1的執(zhí)行性能好很多。的執(zhí)行性能好很多。因為因為1 1中對中對empemp進行了進行了full table scan,full table scan,這是很浪費時間的操作。而且這是很浪費時間的操作。而且1 1中中沒有用到?jīng)]有用到empemp的的indexindex， 因為沒有因為沒有wherewhere子句。而子句。而2 2中的語句對中的語句對empemp進行的是進行的是縮小范圍的查詢?？s小范圍的查詢。用索引提高效率用索引提高效率索引是表的一個概念部分索引是

51、表的一個概念部分,用來提高檢索數(shù)據(jù)的效率，用來提高檢索數(shù)據(jù)的效率，ORACLE使使用了一個復(fù)雜的自平衡用了一個復(fù)雜的自平衡B-tree結(jié)構(gòu)結(jié)構(gòu). 通常通常,通過索引查詢數(shù)據(jù)比全表通過索引查詢數(shù)據(jù)比全表掃描要快掃描要快. 當(dāng)當(dāng)ORACLE找出執(zhí)行查詢和找出執(zhí)行查詢和Update語句的最佳路徑時語句的最佳路徑時, ORACLE優(yōu)化器將使用索引優(yōu)化器將使用索引. 同樣在聯(lián)結(jié)多個表時使用索引也可以同樣在聯(lián)結(jié)多個表時使用索引也可以提高效率提高效率. 另一個使用索引的好處是另一個使用索引的好處是,它提供了主鍵它提供了主鍵(primary key)的唯一性驗證。的唯一性驗證。通常通常, 在大型表中使用索引特

52、別有效在大型表中使用索引特別有效. 當(dāng)然當(dāng)然,你也會發(fā)現(xiàn)你也會發(fā)現(xiàn), 在掃描小在掃描小表時表時,使用索引同樣能提高效率使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提雖然使用索引能得到查詢效率的提高高,但是我們也必須注意到它的代價但是我們也必須注意到它的代價. 索引需要空間來存儲索引需要空間來存儲,也需要也需要定期維護定期維護, 每當(dāng)有記錄在表中增減或索引列被修改時每當(dāng)有記錄在表中增減或索引列被修改時, 索引本身也索引本身也會被修改會被修改. 這意味著每條記錄的這意味著每條記錄的INSERT , DELETE , UPDATE將為將為此多付出此多付出4 , 5 次的磁盤次的磁盤I/O

53、. 因為索引需要額外的存儲空間和處理因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應(yīng)時間變慢那些不必要的索引反而會使查詢反應(yīng)時間變慢.。定期的重構(gòu)索引。定期的重構(gòu)索引是有必要的。是有必要的。避免在索引列上使用計算避免在索引列上使用計算WHERE子句中，如果索引列是函數(shù)的一部分優(yōu)子句中，如果索引列是函數(shù)的一部分優(yōu)化器將不化器將不使用索引而使用全表掃描使用索引而使用全表掃描 低效：低效： SELECT FROM DEPT WHERE SAL * 12 25000; 高效高效: SELECT FROM DEPT WHERE SAL 25000/12;用用= 替代替代 如果如果DEP

54、TNO上有一個索引。上有一個索引。 高效高效: SELECT * FROM EMP WHERE DEPTNO =4 低效低效: SELECT * FROM EMP WHERE DEPTNO 3通過使用通過使用=、=等，避免使用等，避免使用NOT命令命令n例子：例子：select * from employee where salary 3000; n對這個查詢，可以改寫為不使用對這個查詢，可以改寫為不使用NOT：select * from employee where salary3000; 雖然這兩種查詢的結(jié)果一樣，但是第二種查詢方案會比第雖然這兩種查詢的結(jié)果一樣，但是第二種查詢方案會比第一

55、種查詢方案更快些。第二種查詢允許一種查詢方案更快些。第二種查詢允許Oracle對對salary列列使用索引，而第一種查詢則不能使用索引。使用索引，而第一種查詢則不能使用索引。如果有其它辦法，不要使用子查如果有其它辦法，不要使用子查詢。詢。外部聯(lián)接外部聯(lián)接+的用法的用法 n外部聯(lián)接外部聯(lián)接+按其在按其在=的左邊或右邊分左聯(lián)接和右聯(lián)接。若不帶的左邊或右邊分左聯(lián)接和右聯(lián)接。若不帶+運算符的表中的一個行不直接匹配于帶運算符的表中的一個行不直接匹配于帶+預(yù)算符的表中的任何行，預(yù)算符的表中的任何行，則前者的行與后者中的一個空行相匹配并被返回。利用外部聯(lián)接則前者的行與后者中的一個空行相匹配并被返回。利用外部

56、聯(lián)接+，可以替代效率十分低下的可以替代效率十分低下的 not in not in 運算，大大提高運行速度。例如，運算，大大提高運行速度。例如，下面這條命令執(zhí)行起來很慢：下面這條命令執(zhí)行起來很慢：select a.empno from emp a where a.empno not in(select empno from emp1 where job=SALE);n利用外部聯(lián)接，改寫命令如下利用外部聯(lián)接，改寫命令如下: : select a.empno from emp a ,emp1 b where a.empno=b.empno(+) and b.empno is null and b.j

57、ob=SALE;這樣運行速度明顯提高這樣運行速度明顯提高. .盡量多使用盡量多使用COMMIT 事務(wù)是消耗資源的，大事務(wù)還容易引起事務(wù)是消耗資源的，大事務(wù)還容易引起死鎖死鎖 COMMIT所釋放的資源所釋放的資源: 回滾段上用于恢復(fù)數(shù)據(jù)的信息回滾段上用于恢復(fù)數(shù)據(jù)的信息. 被程序語句獲得的鎖被程序語句獲得的鎖 redo log buffer 中的空間中的空間 ORACLE為管理上述為管理上述3種資源中的內(nèi)部種資源中的內(nèi)部花費花費用用TRUNCATE替代替代DELETE當(dāng)刪除表中的記錄時當(dāng)刪除表中的記錄時,在通常情況下在通常情況下, 回滾段回滾段(rollback segments ) 用來存放可以

58、被恢復(fù)的信息用來存放可以被恢復(fù)的信息. 如果你沒如果你沒有有COMMIT事務(wù)事務(wù),ORACLE會將數(shù)據(jù)恢復(fù)到刪除之前的會將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)狀態(tài)(準(zhǔn)準(zhǔn)確地說是恢復(fù)到執(zhí)行刪除命令之前的狀況確地說是恢復(fù)到執(zhí)行刪除命令之前的狀況)而當(dāng)運用而當(dāng)運用TRUNCATE時時, 回滾段不再存放任何可回滾段不再存放任何可被恢復(fù)的被恢復(fù)的信息信息.當(dāng)命令運行后當(dāng)命令運行后,數(shù)據(jù)不能被恢復(fù)數(shù)據(jù)不能被恢復(fù).因此很少的資因此很少的資源被調(diào)用源被調(diào)用,執(zhí)行時間也會很短執(zhí)行時間也會很短.計算記錄條數(shù)計算記錄條數(shù)和一般的觀點相反和一般的觀點相反, count(*) 比比count(1)稍快稍快 , 當(dāng)然如果可當(dāng)然如果可

59、以通過索引檢索以通過索引檢索,對索引列的計數(shù)仍舊是對索引列的計數(shù)仍舊是最快的最快的. 例如例如 COUNT(EMPNO)比如有的表比如有的表PHONE_NO字段是字段是CHAR型型,而且創(chuàng)建有索引，而且創(chuàng)建有索引，但在但在WHERE條件中忘記了加引號，就條件中忘記了加引號，就不會用到索引。不會用到索引。WHERE PHONE_NOHERE PHONE_NO符型字段的引號字符型字段的引號優(yōu)化優(yōu)化EXPORT和和IMPORT使用較大的使用較大的BUFFER(比如比如10MB , 10,240,000)可以提高可以提高EXPORT和和IMPORT的

60、速度的速度;ORACLE將盡可能地獲取你所指定的內(nèi)將盡可能地獲取你所指定的內(nèi)存大小存大小,即使在內(nèi)存即使在內(nèi)存不滿足不滿足,也不會報錯也不會報錯.這個值至少要和表這個值至少要和表中最大的列相當(dāng)中最大的列相當(dāng),否則否則列值會被截斷列值會被截斷;* 優(yōu)化優(yōu)化 Tools *SQL 語句的執(zhí)行步驟語句的執(zhí)行步驟語法分析語法分析 ，分析語句的語法是否符合規(guī)范，衡量語句中各表達式的，分析語句的語法是否符合規(guī)范，衡量語句中各表達式的意義。意義。 語義分析語義分析 ，檢查語句中涉及的所有數(shù)據(jù)庫對象是否存在，且用戶，檢查語句中涉及的所有數(shù)據(jù)庫對象是否存在，且用戶有相應(yīng)的權(quán)限。有相應(yīng)的權(quán)限。 視圖轉(zhuǎn)換，將涉及視