ORACLE_SQL性能優(yōu)化(這個很全的)

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

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

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

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

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

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

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

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

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

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

11、必語句必須完全相同須完全相同(包括空格、換行等包括空格、換行等)。能夠使用共享的語句必須滿足三個。能夠使用共享的語句必須滿足三個條件：條件： 字符級的比較。字符級的比較。當前被執(zhí)行的語句和共享池中的語句必須完全相同。當前被執(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語句語句當一個當一個Oracle實例接收一條實例接收一條sql后后1、Create a Cursor 創(chuàng)建游標創(chuàng)建游標2、Parse the Statement 分析語句分析語句3、Describe Results of a Query 描述查詢的結果集描述查詢的結果集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 關閉游標關閉游標 為什么要為什么要bind variables? 字符級的比較字符級的比較: SELECT * FROM USER_FILES WHERE USER_NO = 10001234; 與與 SELECT * FROM USER_FILES WHERE USER_NO = 10004321; 檢查：檢查： select name,

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

16、re 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(sqlstr, insert into test (num1, num2) values (:v1, :v2);EXEC SQL

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

19、據(jù)操作中，盡量避免遠程訪問，如數(shù)據(jù)庫鏈等，訪問頻繁的表可以常駐內存：以常駐內存：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)的響應速度就成為目前系統(tǒng)需要解決的最主要的問題之一。系統(tǒng)優(yōu)化中一個很重要統(tǒng)需要解決的最主要的問題之一。系統(tǒng)優(yōu)化中一個很重要的方面就是的方面就是SQLSQL語句的優(yōu)化。對于大量數(shù)據(jù)，劣質語句的優(yōu)化。對

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

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

22、；做得太多；2.給優(yōu)化器更明確的命令；給優(yōu)化器更明確的命令； 3.減少訪問次數(shù)；減少訪問次數(shù)；4.細節(jié)上的影響；細節(jié)上的影響；1.不要讓不要讓Oracle做得太多做得太多避免復雜的多表關聯(liá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ù)量的增加性能的風險很大。很難優(yōu)化，隨著數(shù)據(jù)量的增加性能的風險很大。避免使用避免

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

24、作帶有帶有DISTINCT,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í)行而且它只能在前面的嵌入排序結束后才能開始執(zhí)行排序結束后才能開始執(zhí)行. 嵌入的排序的深度會大大影響查嵌入的排序的深度會大大影響查詢的效率詢的效率.通常通常, 帶有帶有UNION, MINUS , INTERSECT的的SQL語句都可以語句都可以用其他方式重寫用其他方式重寫.用用EXISTS替換替換DISTINCT例如例如:低效低效: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO高效高效: SELECT DE

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

27、 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_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 RO

29、WID ON 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 mu

30、ltiindexusage(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 multiindexusag

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

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

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

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

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

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

37、9204421 因為內部發(fā)生的類型轉換因為內部發(fā)生的類型轉換, 這個索引將不會被用到這個索引將不會被用到! 在查詢時盡量少用格式轉換在查詢時盡量少用格式轉換 如用如用 WHERE a.order_no = b.order_no 不用不用 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ù)當執(zhí)行每條當執(zhí)行每條SQL語句時語句時, ORACLE在

38、內部執(zhí)在內部執(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 DE

39、PT_NO = 0030 AND ENAME LIKESMITH%;你可以用你可以用DECODE函數(shù)高效地得到相同結果函數(shù)高效地得到相同結果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

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

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

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

44、句的非索引項或者有計算表達式都將降低查詢速度。有計算表達式都將降低查詢速度。 仔細檢查仔細檢查order by語句以找出非索引項或者表達式，語句以找出非索引項或者表達式，它們會降低性能。解決這個問題的辦法就是重寫它們會降低性能。解決這個問題的辦法就是重寫order by語句以使用索引，也可以為所使用的列建語句以使用索引，也可以為所使用的列建立另外一個索引，同時應絕對避免在立另外一個索引，同時應絕對避免在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)建的索引沒有使用。 當采用下面這種當采用下面這種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%; 在很多情況下可能無法避免這種情況，但是一定要心中有底在很多情況下可能無法避免這種情況，但是一定要心中有底，通配符如此使用會降低查詢速度。然而當通配符出現(xiàn)在字，通配符如此使用會降低查詢速度。然而當通配符出現(xiàn)在字符串其他位置時，優(yōu)化器就能利用索引。在下面的查詢中索符串其他位置時，優(yōu)化器就能利用索引。在下面的查詢中索引得到了使用：引得到了使用： select * from employee where

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

48、EY 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í)行一個內部的排序和合并子句將執(zhí)行一個內部的排序和合并. 無論在哪種情況無論在哪種情況下下,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

50、EXISTS (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中中沒有用到沒有用到empemp的的indexindex， 因為沒有因為沒有wherewhere子句。而子句。而2 2中的語句對中的語句對empemp進行的是進行的是縮小范圍的查詢?？s小范圍的查詢。用索引提高效率用索引提高效率索引是表的一個概念部分

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

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

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

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

55、會比第一種查詢方案更快些。第二種查詢允許一種查詢方案更快些。第二種查詢允許Oracle對對salary列使列使用索引，而第一種查詢則不能使用索引。用索引，而第一種查詢則不能使用索引。如果有其它辦法，不要使用子查如果有其它辦法，不要使用子查詢。詢。外部聯(lián)接外部聯(lián)接+的用法的用法 外部聯(lián)接外部聯(lián)接+按其在按其在=的左邊或右邊分左聯(lián)接和右聯(lián)接。若不帶的左邊或右邊分左聯(lián)接和右聯(lián)接。若不帶+運算符的表中的一個行不直接匹配于帶運算符的表中的一個行不直接匹配于帶+預算符的表中的任何行，預算符的表中的任何行，則前者的行與后者中的一個空行相匹配并被返回。利用外部聯(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);利用外部聯(lián)接，改寫命令如下利用外部聯(lián)接，改寫命令如下: : select a.empno from emp a ,emp1 b where a.empno=b.empno(+) and b.empno is null and

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

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

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

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