Oracle數(shù)據(jù)庫SQL語句的性能優(yōu)化

黃傳祿

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

【關(guān)鍵詞】SQL PL/SQL 性能 優(yōu)化 方法

1 SQL語句優(yōu)化的一般原則

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

（1）減少服務(wù)器資源的消耗；

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

（3）合理使用和優(yōu)化索引；

（4）合理應(yīng)用臨時表；

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

（6）在不影響業(yè)務(wù)的前提下減少事務(wù)的粒度；

（7）在創(chuàng)建表的時候，應(yīng)盡量創(chuàng)建主鍵，盡量根據(jù)實際應(yīng)用的需要來調(diào)整表的PCTFREE和PCTUSED的存儲參數(shù)，優(yōu)化插入，修改或刪除等操作；

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

2 SQL語句優(yōu)化的方法

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

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

2.2 使用Where字句代替Having子句

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

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

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

2.4 選擇最有效的表名順序

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

2.5 where子句中的連接順序

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

2.6 用TRUNCATE代替DELETE刪除表數(shù)據(jù)

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

2.7 盡量多使用COMMIT

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

2.8 使用EXISTS代替IN

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

2.9 有效使用索引

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

3 跟蹤PL/SQL執(zhí)行計劃的方法

3.1 采用Explain Table 的方式

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

3.2 使用工具TKPROF

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

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

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

4 結(jié)語

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

參考文獻

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

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

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

[4]李強.oracle 11g數(shù)據(jù)庫項目應(yīng)用開發(fā)[M].北京：電子工業(yè)出版社，2013（01）.

作者單位

1.江西信息應(yīng)用職業(yè)技術(shù)學院 江西省南昌市 330043

2.蛟潭中心小學 江西省景德鎮(zhèn)市 333416

3.景德鎮(zhèn)陶瓷大學 江西省景德鎮(zhèn)市 333403