1、ORACLEORACLE培訓培訓-SQL-SQL性能優化性能優化老方塊內部培訓班使用老方塊內部培訓班使用課程主要討論：課程主要討論： SQL語句執行的過程、語句執行的過程、ORACLE優化器優化器，表之間的關聯，如何得到，表之間的關聯，如何得到SQL執行執行計劃，如何分析執計劃，如何分析執行計劃等內容，從而由淺到深的方式行計劃等內容，從而由淺到深的方式了解了解SQL優化的過優化的過程，使大家逐步掌握程，使大家逐步掌握SQL優化。優化。1. 優化基礎知識優化基礎知識2. 性能調整綜述性能調整綜述3. 有效的應用設計有效的應用設計4. SQL語句的處理過程語句的處理過程5. Oracle的優化器的

2、優化器6. Oracle的執行計劃的執行計劃7. 注意事項注意事項n性能管理性能管理n性能問題性能問題n調整的方法調整的方法nSQL優化機制優化機制n應用的調整應用的調整nSQL語句的處理過程語句的處理過程共享共享SQLSQL區域區域SQLSQL語句處理的階段語句處理的階段共享游標共享游標SQLSQL編碼標準編碼標準nOracle 優化器介紹優化器介紹nSQL Tunning Tipsn優化優化Tools 盡早開始盡早開始 設立合適目標設立合適目標 邊調整邊監控邊調整邊監控 相關人員進行合作相關人員進行合作 及時處理過程中發生的意外和變化及時處理過程中發生的意外和變化 80/2080/20定律

3、定律隨著軟件技術的不斷發展，系統性能越來越重要。隨著軟件技術的不斷發展，系統性能越來越重要。系統性能主要用：系統性能主要用：系統響應時間和并發性來衡量。系統響應時間和并發性來衡量。造成造成SQL語句性能不佳大致有兩個原因：語句性能不佳大致有兩個原因：1. 開發人員只關注查詢結果的正確性，忽視查詢語句的效率。開發人員只關注查詢結果的正確性，忽視查詢語句的效率。2. 開發人員只關注開發人員只關注SQL語句本身的效率，對語句本身的效率，對SQL語句執行原理、影響語句執行原理、影響SQL執行效率的主要因素不清楚。執行效率的主要因素不清楚。* 前者可以通過深入學習前者可以通過深入學習SQL語法及各種語法

4、及各種SQL調優技巧進行解決。調優技巧進行解決。 SQL調優是一個系統工程，熟悉調優是一個系統工程，熟悉SQL語法、掌握各種內嵌函數、分語法、掌握各種內嵌函數、分 析函數的用法只是編寫高效析函數的用法只是編寫高效SQL的必要條件。的必要條件。* 后者從分析后者從分析SQL語句執行原理入手，指出語句執行原理入手，指出SQL調優應在優化調優應在優化SQL解解 析和優化析和優化CBO上。上。調優領域：調優領域：1. 應用程序級調優：應用程序級調優： * SQL語句調優語句調優 * 管理變化調優管理變化調優2. 實例級調優實例級調優 * 內存內存 * 數據結構數據結構 * 實例配置實例配置3. 操作系

5、統交互操作系統交互 * I/O * SWAP * Parameters本課程內容只講解討論應用程序級：本課程內容只講解討論應用程序級：Oracle SQL語句調優及管理變化調優語句調優及管理變化調優1. 調整業務功能調整業務功能2. 調整數據設計調整數據設計3. 調整流程設計調整流程設計4. 調整調整SQL語句語句5. 調整物理結構調整物理結構6. 調整內存分配調整內存分配7. 調整調整I/O8. 調整內存競爭調整內存競爭9. 調整操作系統調整操作系統n開發人員不能只注重功能的實現，不開發人員不能只注重功能的實現，不管性能如何管性能如何n開發人員不能把開發人員不能把Oracle當成一個黑盒當成

6、一個黑盒子，必須了解其結構、處理子，必須了解其結構、處理SQL和數和數據的方法據的方法n必需遵守既定的開發規范必需遵守既定的開發規范n未經過未經過SQL語句優化的模塊不要上線語句優化的模塊不要上線n定位有問題的語句定位有問題的語句n檢查執行計劃檢查執行計劃n檢查執行過程中優化器的統計信息檢查執行過程中優化器的統計信息n分析相關表的記錄數、索引情況分析相關表的記錄數、索引情況n改寫改寫SQL語句、使用語句、使用HINT、調整索引、調整索引、表分析表分析n有些有些SQL語句不具備優化的可能，需語句不具備優化的可能，需要優化處理方式要優化處理方式n達到最佳執行計劃達到最佳執行計劃n盡量簡單，模塊化盡

7、量簡單，模塊化n易讀、易維護易讀、易維護n節省資源節省資源內存內存CPU掃描的數據塊要少掃描的數據塊要少少排序少排序n不造成死鎖不造成死鎖 ORACLE將執行過的將執行過的SQL語句存放在內存的共享語句存放在內存的共享池池(shared buffer pool)中，可以被所有的數據庫用戶共享。中，可以被所有的數據庫用戶共享。當你執行一個當你執行一個SQL語句語句(有時被稱為一個游標有時被稱為一個游標)時時,如果它和之前的執行過的語句完全相同如果它和之前的執行過的語句完全相同, ORACLE就能很快獲得已經被解析的語句以及最好就能很快獲得已經被解析的語句以及最好的的 執行路徑執行路徑. 這個功能

8、大大地提高了這個功能大大地提高了SQL的執行性能并節省了內存的使用。的執行性能并節省了內存的使用。為了不重復解析相同的為了不重復解析相同的SQL語句，在第一次解析之后，語句，在第一次解析之后，Oracle將將SQL語句存放在內存中。這塊位于系統全局區域語句存放在內存中。這塊位于系統全局區域SGA(systemglobal area)的共享池的共享池(shared buffer poo1)中的中的內存可以被所有的數據庫用戶共享。因此，當你執行一個內存可以被所有的數據庫用戶共享。因此，當你執行一個SQL語句語句(有時被稱為一個游標有時被稱為一個游標)時，如果它和之前執行過的語句完全時，如果它和之前

9、執行過的語句完全相同，相同，Oracle就能很快獲得已經被解析的語句以及最好的執行就能很快獲得已經被解析的語句以及最好的執行方案。方案。Oracle的這個功能大大地提高了的這個功能大大地提高了SQL的執行性能并節省的執行性能并節省了內存的使用。了內存的使用。可惜的是，可惜的是，Oracle只對簡單的表提供高速緩沖只對簡單的表提供高速緩沖(cache bufferiIlg)，這個功能并不適用于多表連接查詢。數據，這個功能并不適用于多表連接查詢。數據庫管理員必須在啟動參數文件中為這個區域設置合適的參數，庫管理員必須在啟動參數文件中為這個區域設置合適的參數，當這個內存區域越大，就可以保留更多的語句，

10、當然被共享的當這個內存區域越大，就可以保留更多的語句，當然被共享的可能性也就越大了。當向可能性也就越大了。當向Oracle提交一個提交一個SQL語句時，語句時，Oracle會首先在這塊內存中查找相同的語句。會首先在這塊內存中查找相同的語句。當前被執行的語句和共享池中的語句必須完全相同當前被執行的語句和共享池中的語句必須完全相同 （包括大小寫、空格、換行等）（包括大小寫、空格、換行等）兩個語句所指的對象必須完全相同兩個語句所指的對象必須完全相同 （同義詞與表是（同義詞與表是不同的對象）不同的對象）兩個兩個SQL語句中必須使用相同的名字的綁定變量語句中必須使用相同的名字的綁定變量(bind var

11、iables) 注意：注意：Oracle對兩者采取的是一種嚴格匹配策略，要達成共享。對兩者采取的是一種嚴格匹配策略，要達成共享。SQL語句必語句必須完全相同須完全相同(包括空格、換行等包括空格、換行等)。能夠使用共享的語句必須滿足三個。能夠使用共享的語句必須滿足三個條件：條件： 字符級的比較。字符級的比較。當前被執行的語句和共享池中的語句必須完全相同。當前被執行的語句和共享池中的語句必須完全相同。例如：例如： SELECT * FROM ATABLE；和下面每一個；和下面每一個SQL語句都不同：語句都不同：SELECT *from ATABLESelect * From Atable； 語句所

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

13、=：blk1.pin；第二組第二組 select pin，name from people where pin =：blk1.ot_jnd；select pin，name from people where pin = ：blk1.ov_jnd；共享共享SQL區域區域Sql 處理過程處理過程SQL PARSE與共享與共享SQL語句語句當一個當一個Oracle實例接收一條實例接收一條sql后后1、Create a Cursor 創建游標創建游標2、Parse the Statement 分析語句分析語句3、Describe Results of a Query 描述查詢的結果集描述查詢的結果集4

14、、Define Output of a Query 定義查詢的輸出數據定義查詢的輸出數據5、Bind Any Variables 綁定變量綁定變量6、Parallelize the Statement 并行執行語句并行執行語句7、Run the Statement 運行語句運行語句8、Fetch Rows of a Query 取查詢出來的行取查詢出來的行9、Close the Cursor 關閉游標關閉游標 為什么要為什么要bind variables?n字符級的比較字符級的比較:SELECT * FROM USER_FILES WHERE USER_NO = 10001234; 與與SEL

15、ECT * FROM USER_FILES WHERE USER_NO = 10004321;n檢查：檢查：select name,executionsfrom v$db_object_cachewhere name like select * from user_files%什么叫做重編譯問題什么叫做重編譯問題什么叫做重編譯？什么叫做重編譯？下面這個語句每執行一次就需要在下面這個語句每執行一次就需要在SHARE POOL 硬解析一硬解析一次，一百萬用戶就是一百萬次，消耗次，一百萬用戶就是一百萬次，消耗CPU和內存，如果業務和內存，如果業務量大，很可能導致宕庫量大，很可能導致宕庫如果綁定變量，

16、則只需要硬解析一次，重復調用即可如果綁定變量，則只需要硬解析一次，重復調用即可select * from dConMsg where contract_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(

17、sqlstr, insert into test (num1, num2) values (:v1, :v2);EXEC SQL PREPARE sql_stmt FROM :sqlstr;EXEC SQL EXECUTE sql_stmt USING :n_var1, :n_var2;EXEC SQL COMMIT; 綁定變量的注意事項綁定變量的注意事項注意：注意：1、不要使用數據庫級的變量綁定參數、不要使用數據庫級的變量綁定參數cursor_sharing來強來強制綁定，無論其值為制綁定，無論其值為 force 還是還是similar2、有些帶、有些帶 0性能優于性能優于select co

18、unt(*)from tab；盡量少嵌套子查詢，這種查詢會消耗大量的盡量少嵌套子查詢，這種查詢會消耗大量的CPU資源；對于有比較多資源；對于有比較多or運算的查詢，建議分成多個查詢，用運算的查詢，建議分成多個查詢，用union all聯結起來；多表查詢聯結起來；多表查詢的查詢語句中，選擇最有效率的表名順序。的查詢語句中，選擇最有效率的表名順序。Oracle解析器對表解析從解析器對表解析從右到左，所以記錄少的表放在右邊。右到左，所以記錄少的表放在右邊。 盡量多用盡量多用commit語句提交事務，可以及時釋放資源、解語句提交事務，可以及時釋放資源、解鎖、釋放日志空間、減少管理花費；在頻繁的、性能要

19、求比較高的鎖、釋放日志空間、減少管理花費；在頻繁的、性能要求比較高的數據操作中，盡量避免遠程訪問，如數據庫鏈等，訪問頻繁的表可數據操作中，盡量避免遠程訪問，如數據庫鏈等，訪問頻繁的表可以常駐內存：以常駐內存：alter tablecache； 在在Oracle中動態執行中動態執行SQL，盡量用，盡量用execute方式，不用方式，不用dbms_sql包。包。* SQL Tunning Tips *sql 語句的編寫原則和優化語句的編寫原則和優化 n隨著數據庫中數據的增加，系統的響隨著數據庫中數據的增加，系統的響應速度就成為目前系統需要解決的最應速度就成為目前系統需要解決的最主要的問題之一。系統

20、優化中一個很主要的問題之一。系統優化中一個很重要的方面就是重要的方面就是SQLSQL語句的優化。對于語句的優化。對于大量數據，劣質大量數據，劣質SQLSQL語句和優質語句和優質SQLSQL語語句之間的速度差別可以達到上百倍，句之間的速度差別可以達到上百倍，對于一個系統不是簡單地能實現其功對于一個系統不是簡單地能實現其功能就可，而是要寫出高質量的能就可，而是要寫出高質量的SQLSQL語句，語句，提高系統的可用性。提高系統的可用性。n在多數情況下，在多數情況下，OracleOracle使用索引來更使用索引來更快地遍歷表，優化器主要根據定義的快地遍歷表，優化器主要根據定義的索引來提高性能。如果在索引

21、來提高性能。如果在SQLSQL語句的語句的wherewhere子句中寫的子句中寫的SQLSQL代碼不合理，就代碼不合理，就會造成優化器刪去索引而使用全表掃會造成優化器刪去索引而使用全表掃描，一般就這種描，一般就這種SQLSQL語句就是所謂的語句就是所謂的劣劣質質SQLSQL語句語句。sql 語句的編寫原則和優化語句的編寫原則和優化 n在編寫在編寫SQLSQL語句時我們應清楚優化器根語句時我們應清楚優化器根據何種原則來使用索引，這有助于寫據何種原則來使用索引，這有助于寫出高性能的出高性能的SQLSQL語句。語句。nSQLSQL語句的編寫原則和語句的編寫原則和SQLSQL語句的優化，語句的優化，請

22、跟我一起學習以下幾方面：請跟我一起學習以下幾方面：Tunning Tip的各個方面的各個方面1.不要讓不要讓Oracle做得太多；做得太多；2.給優化器更明確的命令；給優化器更明確的命令； 3.減少訪問次數；減少訪問次數；4.細節上的影響；細節上的影響；1.不要讓不要讓Oracle做得太多做得太多避免復雜的多表關聯避免復雜的多表關聯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

23、exists(select )?很難優化，隨著數據量的增加性能的風很難優化，隨著數據量的增加性能的風險很大。險很大。避免使用避免使用 * 當你想在當你想在SELECT子句中列出所有的子句中列出所有的COLUMN時時,使用動態使用動態SQL列引用列引用 * 是一個方便的方法是一個方便的方法.不不幸的是幸的是,這是一個非常低這是一個非常低效的方法效的方法. 實際上實際上,ORACLE在解析的過在解析的過程中程中, 會將會將* 依次轉依次轉換成所有的列名換成所有的列名, 這個工作是通過查詢這個工作是通過查詢數據字典完成的數據字典完成的, 這意這意味著將耗費更多的時間；味著將耗費更多的時間；只提取你所

24、要使用的列；只提取你所要使用的列；使用別名能夠加快解析速度；使用別名能夠加快解析速度；避免使用耗費資源的操作避免使用耗費資源的操作帶有帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的的SQL語句會啟動語句會啟動SQL引擎執行耗費資源的排序引擎執行耗費資源的排序(SORT)功能功能. DISTINCT需要一次排序操作需要一次排序操作, 而其他的至少需要執行兩次而其他的至少需要執行兩次排序排序.例如例如,一個一個UNION查詢查詢,其中每個查詢都帶有其中每個查詢都帶有GROUP BY子句子句, GROUP BY會觸發嵌入排序會觸發嵌入排序(NESTED SORT

25、) ; 這樣這樣, 每個每個查詢需要執行一次排序查詢需要執行一次排序, 然后在執行然后在執行UNION時時, 又一個唯一又一個唯一排序排序(SORT UNIQUE)操作被執行而且它只能在前面的嵌入操作被執行而且它只能在前面的嵌入排序結束后才能開始執行排序結束后才能開始執行. 嵌入的排序的深度會大大影響查嵌入的排序的深度會大大影響查詢的效率詢的效率.通常通常, 帶有帶有UNION, MINUS , INTERSECT的的SQL語句都可以語句都可以用其他方式重寫用其他方式重寫.例如例如:低效低效: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP

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

27、例舉例: 低效：低效： SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE 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

28、_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = 31-DEC-952. 給優化器更明確的命令給優化器更明確的命令自動選擇索引自動選擇索引如果表中有兩個以上（包括兩個）索引，其中有一個唯一性如果表中有兩個以上（包括兩個）索引，其中有一個唯一性索引，而其他是非唯一性索引，而其他是非唯一性在這種情況下，在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯將使用唯一性索引而完全忽略非唯一性索引一性索引舉例舉例:SELECT ENAMEFROM EMPWHERE EMPNO = 2326 AND DEPTNO = 20 ;這里，只有這里，只有EMPNO上

29、的索引是唯一性的，所以上的索引是唯一性的，所以EMPNO索索引將用來檢索記錄引將用來檢索記錄TABLE ACCESS BY ROWID ON EMP INDEX UNIQUE SCAN ON EMP_NO_IDX至少要包含組合索引的第一列至少要包含組合索引的第一列如果索引是建立在多個列上如果索引是建立在多個列上, 只有在它的第一個列只有在它的第一個列(leading column)被被where子句引用時子句引用時,優化器才會選擇使用該索引優化器才會選擇使用該索引. SQL create table multiindexusage ( inda number , indb number , d

30、escr varchar2(10);Table created.SQL create index multindex on multiindexusage(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

31、 SCAN) OF MULTINDEX (NON-UNIQUE)SQL select * from multiindexusage where indb = 1;Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF MULTIINDEXUSAGE 很明顯很明顯, 當僅引用索引的第二個列時當僅引用索引的第二個列時,優化器使用了全表掃描而忽略了索引優化器使用了全表掃描而忽略了索引避免在索引列上使用函數避免在索引列上使用函數WHERE子句中，如果索引列是函數的一部分優化器將不子句中，如果索引列是函

32、數的一部分優化器將不使用索引而使用全表掃描使用索引而使用全表掃描舉例舉例:低效：低效：SELECT FROM DEPTWHERE SAL * 12 25000;高效高效:SELECT FROM DEPTWHERE SAL 25000/12;避免使用前置通配符避免使用前置通配符WHERE子句中子句中, 如果索引列所對應的值的第一個字符由通如果索引列所對應的值的第一個字符由通配符配符(WILDCARD)開始開始, 索引將不被采用索引將不被采用. SELECT USER_NO,USER_NAME,ADDRESSFROM USER_FILESWHERE USER_NO LIKE %109204421;

33、 在這種情況下，在這種情況下，ORACLE將使用全表掃描將使用全表掃描.避免在索引列上使用避免在索引列上使用NOT通常，我們要避免在索引列上使用通常，我們要避免在索引列上使用NOT, NOT會產生在和在會產生在和在索引列上使用函數相同的影響索引列上使用函數相同的影響. 當當ORACLE”遇到遇到”NOT,他他就就會停止使用索引轉而執行全表掃描會停止使用索引轉而執行全表掃描.舉例舉例: 低效低效: (這里這里,不使用索引不使用索引) SELECT FROM DEPT WHERE DEPT_CODE NOT = 0; 高效高效: (這里這里,使用了索引使用了索引) SELECT FROM DEPT

34、 WHERE DEPT_CODE 0;避免在索引列上使用避免在索引列上使用 IS NULL和和IS NOT NULL避免在索引中使用任何可以為空的列，避免在索引中使用任何可以為空的列，ORACLE將無法使用該將無法使用該索引索引 對于單列索引，如果列包含空值，索引中將不存在此記對于單列索引，如果列包含空值，索引中將不存在此記錄錄. 對于復合索引，如果每個列都為空，索引中同樣不存在此對于復合索引，如果每個列都為空，索引中同樣不存在此記錄記錄.如果至少有一個列不為空，則記錄存在于索引中如果至少有一個列不為空，則記錄存在于索引中如果唯一性索引建立在表的如果唯一性索引建立在表的A列和列和B列上列上,

35、并且表中存在一條記并且表中存在一條記錄的錄的A,B值為值為(123,null) , ORACLE將不接受下一條具有相同將不接受下一條具有相同A,B值（值（123,null）的記錄）的記錄(插入插入). 然而如果所有的索引列都為然而如果所有的索引列都為空，空，ORACLE將認為整個鍵值為空而空不等于空將認為整個鍵值為空而空不等于空. 因此你可以因此你可以插入插入1000條具有相同鍵值的記錄條具有相同鍵值的記錄,當然它們都是空當然它們都是空!因為空值不存在于索引列中因為空值不存在于索引列中,所以所以WHERE子句中對索引列進行子句中對索引列進行空值比較將使空值比較將使ORACLE停用該索引停用該索

36、引.任何在任何在where子句中使用子句中使用is null或或is not null的語句優化器是的語句優化器是不允許使用索引的。不允許使用索引的。避免出現索引列自動轉換避免出現索引列自動轉換當比較不同數據類型的數據時當比較不同數據類型的數據時, ORACLE自動對列進行簡單自動對列進行簡單的類型轉換的類型轉換.假設假設EMP_TYPE是一個字符類型的索引列是一個字符類型的索引列.SELECT USER_NO,USER_NAME,ADDRESSFROM USER_FILESWHERE USER_NO = 109204421這個語句被這個語句被ORACLE轉換為轉換為:SELECT USER_

37、NO,USER_NAME,ADDRESSFROM USER_FILESWHERE TO_NUMBER(USER_NO) = 109204421 因為內部發生的類型轉換因為內部發生的類型轉換, 這個索引將不會被用到這個索引將不會被用到! 在查詢時盡量少用格式轉換在查詢時盡量少用格式轉換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, .) -

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

39、0 AND ENAME LIKESMITH%; SELECT COUNT(*)，SUM(SAL) FROMEMP WHERE DEPT_NO = 0030 AND ENAME LIKESMITH%;你可以用你可以用DECODE函數高效地得到相同結果函數高效地得到相同結果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,

40、0030,SAL,NULL) D0030_SALFROM EMP WHERE ENAME LIKE SMITH%;減少對表的查詢減少對表的查詢在含有子查詢的在含有子查詢的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) 高效高

41、效 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER) FROM TAB_COLUMNS WHERE VERSION = 604) 4. 細節上的影響細節上的影響WHERE子句中的連接順序子句中的連接順序ORACLE采用自下而上的順序解析采用自下而上的順序解析WHERE子句子句,根據這個原根據這個原理理, 當在當在WHERE子句中有多個表聯接時，子句中有多個表聯接時，WHERE子句中排子句中排在最后的表應當是返回行數可能最少的表，有過濾條件的子在最后的表應當是返回行數可能最少的表，有過濾

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

43、from emp e,dept d where e.deptno =30 and d.deptno 10 ;WHERE子句子句 函數、表達式使用函數、表達式使用n最好不要在最好不要在WHERE子句中使用函或表子句中使用函或表達式，如果要使用的話，最好統一使達式，如果要使用的話，最好統一使用相同的表達式或函數，這樣便于以用相同的表達式或函數，這樣便于以后使用合理的索引。后使用合理的索引。Order by語句語句 nORDER BY語句決定了語句決定了Oracle如何將返回的查詢如何將返回的查詢結果排序。結果排序。Order by語句對要排序的列沒有什么語句對要排序的列沒有什么特別的限制，也可以將

44、函數加入列中（象聯接或特別的限制，也可以將函數加入列中（象聯接或者附加等）。者附加等）。任何在任何在Order by語句的非索引項或語句的非索引項或者有計算表達式都將降低查詢速度。者有計算表達式都將降低查詢速度。n仔細檢查仔細檢查order by語句以找出非索引項或者表達語句以找出非索引項或者表達式，它們會降低性能。解決這個問題的辦法就是式，它們會降低性能。解決這個問題的辦法就是重寫重寫order by語句以使用索引，也可以為所使用語句以使用索引，也可以為所使用的列建立另外一個索引，同時應絕對避免在的列建立另外一個索引，同時應絕對避免在order by子句中使用表達式。子句中使用表達式。聯接列

45、聯接列 對于有聯接的列，即使最后的聯接值為一個靜態值，優化器對于有聯接的列，即使最后的聯接值為一個靜態值，優化器是不會使用索引的。是不會使用索引的。 select * from employss where first_name|last_name =Beill Cliton; 系統優化器對基于系統優化器對基于last_name創建的索引沒有使用。創建的索引沒有使用。 當采用下面這種當采用下面這種SQL語句的編寫，語句的編寫，Oracle系統就可以采用基系統就可以采用基于于last_name創建的索引。創建的索引。 select * from employee where first_name

46、 =Beill and last_name =Cliton; 帶通配符（帶通配符（%）的）的like語句語句 通配符（通配符（%）在搜尋詞首出現，）在搜尋詞首出現，Oracle系統不使用系統不使用last_name的索引。的索引。select * from employee where last_name like %cliton%; 在很多情況下可能無法避免這種情況，但是一定要心中有底在很多情況下可能無法避免這種情況，但是一定要心中有底，通配符如此使用會降低查詢速度。然而當通配符出現在字，通配符如此使用會降低查詢速度。然而當通配符出現在字符串其他位置時，優化器就能利用索引。在下面的查詢中索符

47、串其他位置時，優化器就能利用索引。在下面的查詢中索引得到了使用：引得到了使用： select * from employee where last_name like c%; 用用Where子句替換子句替換HAVING子句子句避免使用避免使用HAVING子句子句, HAVING 只會在檢索出所有記錄之后才對結果只會在檢索出所有記錄之后才對結果集進行過濾集進行過濾. 這個處理需要排序這個處理需要排序,總計等操作總計等操作. 如果能通過如果能通過WHERE子句限子句限制記錄的數目制記錄的數目,那就能減少這方面的開銷那就能減少這方面的開銷.例如例如: 低效低效: SELECT REGION，AVG(

48、LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION REGION != SYDNEY 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子句將執行一個內部的排序和合并子句將執行一個內部的排序和合并.

49、無論在哪種情況無論在哪種情況下下,NOT IN都是最低效的都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷因為它對子查詢中的表執行了一個全表遍歷). 使用使用NOT EXISTS 子句可以有效地利用索引。盡可能使用子句可以有效地利用索引。盡可能使用NOT EXISTS來代替來代替NOT IN，盡管二者都使用了，盡管二者都使用了NOT（不能使用索引而降低速度），（不能使用索引而降低速度），NOT EXISTS要比要比NOT IN查詢效率更高。查詢效率更高。例如例如:語句語句1 SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELEC

50、T deptno FROM emp); 語句語句2 SELECT dname, deptno FROM dept WHERE NOT EXISTS (SELECT deptno FROM emp WHERE dept.deptno = emp.deptno); 2 2要比要比1 1的執行性能好很多。的執行性能好很多。因為因為1 1中對中對empemp進行了進行了full table scan,full table scan,這是很浪費時間的操作。而且這是很浪費時間的操作。而且1 1中中沒有用到沒有用到empemp的的indexindex， 因為沒有因為沒有wherewhere子句。而子句。而2

51、 2中的語句對中的語句對empemp進行的是進行的是縮小范圍的查詢。縮小范圍的查詢。用索引提高效率用索引提高效率索引是表的一個概念部分索引是表的一個概念部分,用來提高檢索數據的效率，用來提高檢索數據的效率，ORACLE使使用了一個復雜的自平衡用了一個復雜的自平衡B-tree結構結構. 通常通常,通過索引查詢數據比全表通過索引查詢數據比全表掃描要快掃描要快. 當當ORACLE找出執行查詢和找出執行查詢和Update語句的最佳路徑時語句的最佳路徑時, ORACLE優化器將使用索引優化器將使用索引. 同樣在聯結多個表時使用索引也可以同樣在聯結多個表時使用索引也可以提高效率提高效率. 另一個使用索引的

52、好處是另一個使用索引的好處是,它提供了主鍵它提供了主鍵(primary key)的唯一性驗證。的唯一性驗證。通常通常, 在大型表中使用索引特別有效在大型表中使用索引特別有效. 當然當然,你也會發現你也會發現, 在掃描小在掃描小表時表時,使用索引同樣能提高效率使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提雖然使用索引能得到查詢效率的提高高,但是我們也必須注意到它的代價但是我們也必須注意到它的代價. 索引需要空間來存儲索引需要空間來存儲,也需要也需要定期維護定期維護, 每當有記錄在表中增減或索引列被修改時每當有記錄在表中增減或索引列被修改時, 索引本身也索引本身也會被修改會被修改. 這意

53、味著每條記錄的這意味著每條記錄的INSERT , DELETE , UPDATE將為將為此多付出此多付出4 , 5 次的磁盤次的磁盤I/O . 因為索引需要額外的存儲空間和處理因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢那些不必要的索引反而會使查詢反應時間變慢.。定期的重構索引。定期的重構索引是有必要的。是有必要的。避免在索引列上使用計算避免在索引列上使用計算WHERE子句中，如果索引列是函數的一部分優子句中，如果索引列是函數的一部分優化器將不化器將不使用索引而使用全表掃描使用索引而使用全表掃描 低效：低效： SELECT FROM DEPT WHERE SAL

54、* 12 25000; 高效高效: SELECT FROM DEPT WHERE SAL 25000/12;用用= 替代替代 如果如果DEPTNO上有一個索引。上有一個索引。 高效高效: 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

55、 where salary3000; 雖然這兩種查詢的結果一樣，但是第二種查詢方案會比第雖然這兩種查詢的結果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許一種查詢方案更快些。第二種查詢允許Oracle對對salary列列使用索引，而第一種查詢則不能使用索引。使用索引，而第一種查詢則不能使用索引。如果有其它辦法，不要使用子查如果有其它辦法，不要使用子查詢。詢。外部聯接外部聯接+的用法的用法 n外部聯接外部聯接+按其在按其在=的左邊或右邊分左聯接和右聯接。若不帶的左邊或右邊分左聯接和右聯接。若不帶+運算符的表中的一個行不直接匹配于帶運算符的表中的一個行不直接匹配于帶+預算符的表中

56、的任何行，預算符的表中的任何行，則前者的行與后者中的一個空行相匹配并被返回。利用外部聯接則前者的行與后者中的一個空行相匹配并被返回。利用外部聯接+，可以替代效率十分低下的可以替代效率十分低下的 not in not in 運算，大大提高運行速度。例如，運算，大大提高運行速度。例如，下面這條命令執行起來很慢：下面這條命令執行起來很慢：select a.empno from emp a where a.empno not in(select empno from emp1 where job=SALE);n利用外部聯接，改寫命令如下利用外部聯接，改寫命令如下: : select a.empno f

57、rom emp a ,emp1 b where a.empno=b.empno(+) and b.empno is null and b.job=SALE;這樣運行速度明顯提高這樣運行速度明顯提高. .盡量多使用盡量多使用COMMIT 事務是消耗資源的，大事務還容易引起事務是消耗資源的，大事務還容易引起死鎖死鎖 COMMIT所釋放的資源所釋放的資源: 回滾段上用于恢復數據的信息回滾段上用于恢復數據的信息. 被程序語句獲得的鎖被程序語句獲得的鎖 redo log buffer 中的空間中的空間 ORACLE為管理上述為管理上述3種資源中的內部種資源中的內部花費花費用用TRUNCATE替代替代DE

58、LETE當刪除表中的記錄時當刪除表中的記錄時,在通常情況下在通常情況下, 回滾段回滾段(rollback segments ) 用來存放可以被恢復的信息用來存放可以被恢復的信息. 如果你沒如果你沒有有COMMIT事務事務,ORACLE會將數據恢復到刪除之前的會將數據恢復到刪除之前的狀態狀態(準準確地說是恢復到執行刪除命令之前的狀況確地說是恢復到執行刪除命令之前的狀況)而當運用而當運用TRUNCATE時時, 回滾段不再存放任何可回滾段不再存放任何可被恢復的被恢復的信息信息.當命令運行后當命令運行后,數據不能被恢復數據不能被恢復.因此很少的資因此很少的資源被調用源被調用,執行時間也會很短執行時間也

59、會很短.計算記錄條數計算記錄條數和一般的觀點相反和一般的觀點相反, count(*) 比比count(1)稍快稍快 , 當然如果可當然如果可以通過索引檢索以通過索引檢索,對索引列的計數仍舊是對索引列的計數仍舊是最快的最快的. 例如例如 COUNT(EMPNO)比如有的表比如有的表PHONE_NO字段是字段是CHAR型型,而且創建有索引，而且創建有索引，但在但在WHERE條件中忘記了加引號，就條件中忘記了加引號，就不會用到索引。不會用到索引。WHERE PHONE_NOHERE PHONE_NO符型字段的引號字符型字段的引號優化優化EXPORT

60、和和IMPORT使用較大的使用較大的BUFFER(比如比如10MB , 10,240,000)可以提高可以提高EXPORT和和IMPORT的速度的速度;ORACLE將盡可能地獲取你所指定的內將盡可能地獲取你所指定的內存大小存大小,即使在內存即使在內存不滿足不滿足,也不會報錯也不會報錯.這個值至少要和表這個值至少要和表中最大的列相當中最大的列相當,否則否則列值會被截斷列值會被截斷;* 優化優化 Tools *SQL 語句的執行步驟語句的執行步驟語法分析語法分析 ，分析語句的語法是否符合規范，衡量語句中各表達式的，分析語句的語法是否符合規范，衡量語句中各表達式的意義。意義。 語義分析語義分析 ，檢