Oracle SQL優化基本步驟

本空間日誌均為一種學習記錄。

                    ----------------該文章無法找到原創出自何處、若有侵範，請告知！

           最近有嘗試做SQL優化、一直不得要領，請逛到這裡的高人提供一些優化例項（最好是有優化歷程說明），先謝謝了！（這是很早以前看到的一篇文章，對我這種菜鳥來說挺有用的、分享如下：）

oracle 的優化器:   
oracle 有兩種優化器：基於規則的優化器(rbo/rule based optimizer)和基於代價的優化器(cbo/cost based optimizer).

較典型的問題有:
        有時、表明明建有索引,但查詢過程顯然沒有用到相關的索引,導致查詢過程耗時漫長,佔用資源巨大,問題到底出在哪兒呢？按照以下順序查詢,基本上能發現原因所在。
首先,我們要確定資料庫執行在何種優化模式下,相應的引數是：optimizer_mode。oracle v7 以來預設的設臵應是"choose"，即如果對已分析的表查詢的話選擇cbo，否則選擇rbo。如果該引數設為‚'rule‛，則不論表是否分析過，一概選用rbo，除非在語句中用hint 強制.
        其次、檢查被索引的列或組合索引的首列是否出現在pl/sql 語句的where子句中，這是執行計劃能用到相關索引的必要條件.
        第三、看採用了哪種型別的連線方式。oracle 的共有sort merge join（smj）、hash join（hj）和nested loop join（nl）。在兩張表連線，且內表的目標列上建有索引時，只有nested loop 才能有效地利用到該索引。smj 即使相關列上建有索引，最多隻能因索引的存在，避免資料排序過程。hj 由於須做hash 運算，索引的存在對資料查詢速度幾乎沒有影響.
       第四、看連線順序是否允許使用相關索引。假設表emp 的deptno 列上有索引，表dept 的列deptno 上無索引，where 語句有 emp.deptno=dept.deptno 條件。在做nl連線時，emp 做為外表，先被訪問，由於連線機制原因，外表的資料訪問方式是全表掃描，emp.deptno 上的索引顯然是用不上，最多在其上做索引全掃描或索引快速全掃描.
       第五、是否用到系統資料字典表或檢視。由於系統資料字典表都未被分析過，可能導致極差的'執行計劃‛。但是不要擅自對資料字典表做分析，否則可能導致死鎖，或系統效能下降。
       第六、索引列是否函式的引數。如是，索引在查詢時用不上。
       第七、是否存在潛在的資料型別轉換。如將字元型資料與數值型資料比較，oracle 會自動將字元型用to_number()函式進行轉換，從而導致第六種現象的發生。
       第八、是否為表和相關的索引蒐集足夠的統計資料。對資料經常有增、刪、改的表最好定期對錶和索引進行分析，可用sql 語句analyze table table_name compute statistics for all indexes;oracle 掌握了充分反映實際的統計資料，才有可能做出正確的選擇.
       第九、索引列的選擇性不高。我們假設典型情況，有表emp，共有一百萬行資料，但其中的emp.deptno 列，資料只有4 種不同的值，如10、20、30、40。雖然emp 資料行有很多，oracle 預設認定表中列的值是在所有資料行均勻分佈的，也就是說每種deptno 值各有25 萬資料行與之對應。
       假設sql 搜尋條件deptno=10，利用deptno 列上的索引進行資料搜尋效率，往往不比全表掃描的高，oracle理所當然對索引'視而不見‛，認為該索引的選擇性不高。但我們考慮另一種情況，如果一百萬資料行實際不是在4 種 deptno 值間平均分配，其中有 99 萬行對應著值10，5000 行對應值20，3000 行對應值30，2000 行對應值 40。在這種資料分佈圖案中對除值為10 外的其它deptno 值搜尋時，毫無疑問，如果索引能被應用，那麼效率會高出很多。我們可以採用對該索引列進行單獨分析，或用analyze 語句對該列建立直方圖，對該列蒐集足夠的統計資料，使oracle 在搜尋選擇性較高的值能用上索引。
        第十、索引列值是否可為空(null)，如果索引列值可以是空值，在sql 語句中那些需要返回null 值的操作，將不會用到索引，如 count（*），而是用全表掃描。這是因為索引中儲存值不能為全空。
        第十一、看是否有用到並行查詢（pqo）。並行查詢將不會用到索引。
        第十二、看 pl/sql 語句中是否有用到bind 變數。由於資料庫不知道bind 變數具體是什麼值，在做非相等連線時，如‚‚like等。oracle 將引用預設值，在某些情況下會對執行計劃造成影響。如果從以上幾個方面都查不出原因的話，我們只好用採用在語句中加hint 的方式強制oracle 使用最優的'執行計劃‛。hint 採用註釋的方式，有行註釋和段註釋兩種方式。 如我們想要用到a 表的ind_col1 索引的話，可採用以下方式：select /*+ index（a ind_col1）*/  * from a where col1 = xxx;注意，註釋符必須跟在select 之後，且註釋中的'+‛要緊跟著註釋起始符'/*‛或'--‛，否則hint 就被認為是一般註釋，對 pl/sql 語句的執行不產生任何影響。

兩種有效的跟蹤除錯方法:
       oracle 提供了兩種有效的工具來跟蹤除錯pl/sql 語句的執行計劃。
        一種是explain table 方式。使用者必須首先在自己的模式(schema)下，建立plan_table 表，執行計劃的每一步驟都將記錄在該表中，建表sql 指令碼為在${oracle_home}/rdbms/admin/ 下的utlxplan.sql。開啟sql*plus，輸入set autotrace on，然後執行待除錯的sql 語句。在給出查詢結果後，oracle 將顯示相應的'執行計劃‛，包括優化器型別、執行代價、連線方式、連線順序、資料搜尋路徑以及相應的連續讀、物理讀等資源代價。
        如果我們不能確定需要跟蹤的具體sql 語句，比如某個應用使用一段時間後，響應速度忽然變慢。我們這時可以利用oracle 提供的另一個有力工具tkprof，對應用的執行過程全程跟蹤.我們要先在系統檢視 v$session 中，可根據userid 或 machine，查出相應的sid 和serial#。以sys 或其他有執行dbms_system 程式包的使用者連線資料庫執行execute dbms_system.set_sql_trace_in_session(sid,serial#，true);。然後執行應用程式，這時在伺服器端，資料庫引數 user_dump_dest指示的目錄下，會生成ora__xxxx.trc 檔案，其中xxxx 為被跟蹤應用的作業系統程式號。應用程式執行完成後，用命令tkprof 對該檔案進行分析。
        命令示例:"tkprof tracefile outputfile explain=userid/password"。在作業系統oracle 使用者下，鍵入tkprof，會有詳細的命令幫助。分析後的輸出檔案outputfile 中，有每一條pl/sql 語句的'執行計劃‛、cpu 佔用、物理讀次數、邏輯讀次數、執行時長等重要資訊。根據輸出檔案的資訊，我們可以很快發現應用中哪條pl/sql 語句是問題的癥結所在。

        sql 語句編寫注意問題:下面就某些sql 語句的where 子句編寫中需要注意的問題作詳細介紹。在這些where 子句中,即使某些列存在索引，但是由於編寫了劣質的sql，系統在執行該sql 語句時也不能使用該索引，而同樣使用全表掃描，這就造成了響應速度的極大降低。
1)、is null 與 is not null
        不能用null 作索引，任何包含null 值的列都將不會被包含在索引中。任何在where 子句中使用is null 或is not null 的語句優化器是不允許使用索引的。
2)、語句中的函式語句中如有函式操作如(upper,substr 等)時，優化器是不會使用索引的，應儘量少用或不用。
3)、帶萬用字元（%）的 like 語句如果萬用字元（%）在搜尋詞首出現，oracle 系統不會使用索引。在很多情況下可能無法避免這種情況，然而當萬用字元出現在字串其他位臵時，優化器就能利用索引。在下面的查詢中索引得到了使用：select * from user_m where loginid like 'r%';
4)、order by 語句order by 語句決定了oracle 如何將返回的查詢結果排序。order by 語句對要排序的列沒有什麼特別的限制。但任何在order by 語句的非索引項或者有計算表示式都將降低查詢速度，應儘量少用。
5)、not 在查詢時經常在where 子句使用一些邏輯表示式，如大於、小於、等於以及不等於等等，也可以使用and（與）、or（或）以及 not（非）。not 可用來對任何邏輯運算子號取反。not 運算子包含在另外一個邏輯運算子中，這就是不等於（<>）運算子。換句話說，即使不在查詢where 子句中顯式地加入not 詞，not 仍在運算子中，見下例：select * from employee where salary<>3000; 對這個查詢，可以改寫為不使用not：select * from employee where salary<3000 or salary>3000; 雖然這兩種查詢的結果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許對salary 列使用索引，而第一種查詢則不能使用索引。
6)、in 和exists 在where 子句中使用子查詢。在where 子句中可以使用兩種格式的子查詢。

         第一種格式是使用in 操作符：第二種格式是使用exist 操作符：第二種格式要遠比第一種格式的效率高。應儘可能使用not exists 來代替not in。
7)、條件的順序問題，條件列和索引列的順序要保持對應。
8)、使用union、intersect、minus；消除對大型錶行資料的順序存取，對連線的列進行索引，還可以使用並集來避免順序存取。儘管在所有的檢查列上都有索引，但某些形式的where 子句強迫優化器使用順序存取。下面的查詢將強迫對orders 表執行順序操作：select  *from orders where (customer_num=104 and order_num>1001) or order_num=1008 雖然在customer_num 和order_num 上建有索引，但是在上面的語句中優化器還是使用順序存取路徑掃描整個表。因為這個語句要檢索的是分離的行的集合，所以應該改為如下語句： select  *from orders where customer_num=104 and order_num>1001 union select  *from orders where order_num=1008 這樣就能利用索引路徑處理查詢。

其他注意事項:

1)、精確查詢列及查詢條件, 禁用萬用字元＊；
2)、在滿足業務邏輯的情況下，分割事務大小，及時提交事務；
3)、及時釋放使用者鎖和資源，減少使用者鎖的使用；
4)、對外來鍵建立相應順序的索引；
5)、在可能的情況下修改不合理資料庫系統的結構；
6)、使用臨時表加速查詢

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24867586/viewspace-710895/，如需轉載，請註明出處，否則將追究法律責任。