Oracle數據庫SQL語句的性能優化

黃傳祿

SQL語句優化就是把性能低的SQL語句轉換成達到同樣目的的性能高的SQL語句。Oracle數據庫的性能最終是由SQL語句的執行效率來決定。本文介紹了SQL優化的一般原則，并詳細闡述了SQL語句性能優化的方法以及通過工具跟蹤PL/SQL執行計劃的方法達到優化SQL語句的目的。

【關鍵詞】SQL PL/SQL 性能 優化 方法

1 SQL語句優化的一般原則

Oracle為了執行每條SQL語句，首先要對該條語句確定系統實際執行SQL語句的方法。根據統計約80%的數據庫性能問題都是由執行效率低的SQL語句引起的，因此不管是軟件開發人員還是數據庫管理人員要編寫高質量的SQL語句都要知道SQL優化的一般原則：

（1）減少服務器資源的消耗；

（2）盡量依靠oracle的優化器，并為其提供條件；

（3）合理使用和優化索引；

（4）合理應用臨時表；

（5）避免寫過于復雜的SQL語句，盡量少使用嵌套語句，嵌套查詢會消耗大量的CPU資源；

（6）在不影響業務的前提下減少事務的粒度；

（7）在創建表的時候，應盡量創建主鍵，盡量根據實際應用的需要來調整表的PCTFREE和PCTUSED的存儲參數，優化插入，修改或刪除等操作；

（8）寫 SQL語句時盡量用大寫。

2 SQL語句優化的方法

2.1 Select 語句中盡量避免使用*

Oracle在解析使用帶有“*”的select查詢語句的過程中，需要到oracle的數據字典中去把“*”分別轉換成所有對應的列名，這個解析的過程需要花費時間，因此降低了SQL語句的執行效率。

2.2 使用Where字句代替Having子句

在使用select 語句時，可以通過使用where字句來過濾行，having 子句過濾分組。Having 過濾分組是在分組后才執行，所以是要花費一定的時間。所以盡量使用where字句來過濾行，這樣可以減少分組的行數，也就是減少分組的時間，提高了執行效率。

2.3 使用表連接而不是多個查詢

Oracle在執行每條語句時，在其內部完成很多工作，如解析SQL語句、估算索引的利用率、綁定變量以及讀取相關的數據塊等，這樣的話就需要花費很多時間。因此，查詢結果的時候盡量使用一次查詢獲得數據，不要經過兩次或多次查詢。

2.4 選擇最有效的表名順序

Select語句中的from子句后面可以接多張表，選擇哪張表放在最前面會影響到查詢的效率。因為在Oracle解析器中解析查詢語句時是按照從右往左的順序處理from子句中的表名，也就是放在from子句最后面的表是最先被執行的。所以在from后面進行多張表連接的時候，通常把記錄比較少的那張表作為基礎表，把基礎表放在最后面即最右邊。當oracle在處理from后面的表的時候，首先是掃描from最后面的那張表，也就是記錄比較少的那張表，這樣先執行記錄比較少的那張表就大大縮短了執行的時間。

2.5 where子句中的連接順序

帶有WHERE子句的查詢中，where子句后面可以帶多個條件。oracle在處理where子句后面的條件時是采用自右至左的順序，因此，在RBO優化器模式下，如果有多張表的連接，應該按照表中的數據量從大到小順序排列，然后按照數據量從左往右進行排列，這樣在進行表連接時，最右邊的表循環次數就會越少，執行的效率就會比較高。

2.6 用TRUNCATE代替DELETE刪除表數據

刪除表中的數據既可以使用delete語句也可以使用truncate語句。使用delete語句刪除數據時，數據被存放在回滾段中。如果沒有commit事務，oracle會將數據恢復到刪除之前的狀態。而使truncate語句用刪除數據時，回滾段不再存放任何可被恢復的信息，數據不能被恢復。因此很少的資源被調用，這樣的執行的時間也就變短了。

2.7 盡量多使用COMMIT

當用戶執行DML操作后，如果不使用commit命令進行提交操作，則oracle會在回滾段中記錄DML操作，以便用戶使用rollback命令對數據進行恢復。在使用rollback命令恢復時需要花費系統相應的時間和資源。所以，在程序中盡量多使用commit命令，這樣程序的性能會得到一定的提高，系統也會因為使用commit命令釋放如回滾段上用于恢復數據的信息、被程序語句獲得的鎖等資源而提高性能。

2.8 使用EXISTS代替IN

EXISTS只判斷子查詢返回行的存在行，而IN操作符是用于檢查一個值是否包含在列表中。使用IN操作符需要消耗更多的執行時間。

2.9 有效使用索引

Oracle 訪問表中的記錄可以通過全表掃描或ROWID的方式，采用索引實現了數據和存放數據的物理位置（ROWID）之間的聯系。在使用索引時，索引提供了快速訪問ROWID的方法，因此基于索引列的查詢就大大提高了查詢數據的效率。

3 跟蹤PL/SQL執行計劃的方法

3.1 采用Explain Table 的方式

采用Explain Table 的方式來獲得執行計劃的首先是必須先創建一張Plan_Table 表，然后把SQL語句的執行計劃的每一步驟都加載到創建的Plan_Table 表中。登錄SQL plus中使用@執行創建表的腳本：@${ORACLE_HOME}/rdbms/admin/utlxplan.sql，執行完后，這樣就創建了一張Plan_Table 表。然后是通過輸入SET AUTOTRACE ON 命令打開自動跟蹤的功能。最后是進行測試，把要測試的SQL語句進行運行，在查詢語句出來結果后，oracle就會顯示SQL語句的執行計劃，從執行計劃中我們可以看到的內容主要有優化器類型、執行代價、連接方式、連接順序、數據搜索路徑以及相應的連續讀、物理讀等資源代價。

3.2 使用工具TKPROF

假如有的時候某個應用使用一段時間后發現響應的速度突然變慢了，可能想到的是SQL查詢語句有沒有存在問題，但是又不確定具體是哪條語句出了問題，在這種情況下就可以使用TKPROF工具進行執行全過程的執行計劃跟蹤了。

（1）找到當前占用cpu資源最高的一個進程的PID號，然后在數據庫中根據PID號找到相應的sid號和serial#。

（2）使用dbms_system.set_sql_trace_in_session包來對這個session進行trace，然后到user_dump_dest定義的路徑下查找剛生成的trace文件，接著使用tkprof工具對生成的trace文件進行分析，tkprof得到的輸出文件主要有SQL語句本身、相關的診斷信息以及這個語句的執行計劃。根據輸出文件信息，就可以很快的判斷PL/SQL語句占用cpu時間、總共消耗的時間、讀取磁盤數量、邏輯讀的數量等相關信息，根據這些診斷信息進行快速的調整。

4 結語

SQL語言是一種靈活的語言，相同的功能可以使用不同的語句來實現，但是語句的執行效率往往有很大的區別。尤其對于海量數據，劣質SQL語句和優質SQL語句之間的速度差別可以達到上百倍，因此，要提高數據庫應用程序的性能必須精通SQL優化的一般原則和SQL語句的各種優化方法，寫出高質量的SQL語句，從而提高SQL語句的執行效率。

參考文獻

[1]Michael Rosenblum Paul Dorsey.Oracle PL/SQL性能調優訣竅與方法[M].北京：清華大學出版社，2015（11）.

[2]李偉等.Oracle 11g SQL和PL/SQL編程指南[M].北京：清華大學出版社，2014（08）.

[3]何明等.名師講壇—Oracle DBA入門與實戰經典[M].北京：清華大學出版社，2015（05）.

[4]李強.oracle 11g數據庫項目應用開發[M].北京：電子工業出版社，2013（01）.

作者單位

1.江西信息應用職業技術學院 江西省南昌市 330043

2.蛟潭中心小學 江西省景德鎮市 333416

3.景德鎮陶瓷大學 江西省景德鎮市 333403