開發人員如何最佳化ORACLE執行語句

標籤：http   ar   os   使用   sp   檔案   資料   art   問題   

（原陳博）摘要：各個領域中，對資料庫的使用力度不斷增加：更多的企業級資料庫被使用，使用方式多樣化、資料庫容量增大化的趨勢明顯。與此同時，基於實際業務需要的應用也想資料庫提出了更高效率的要求：資料庫調優勢在必行。報告的作者具有多年大型系統開發和企業級資料庫維護的經驗，基於SQL語句在Oracle資料庫中的執行邏輯，在以下報告中提出多種資料庫最佳化的方法，從而達到提高Oracle資料庫效能、改善應用系統運行環境的目的。
關鍵詞：資料庫；共用池；掃描；索引
中圖分類號：NA　文獻標識碼：A　文章編號：1671－7597(2011)0220021－01
Oracle資料庫調優，是軟體工程師和資料庫管理員群體中永恒的話題。每一個技術人員，都基於自身對各自系統的瞭解，有著不同的調優方法。
作者從事航空結算領域的系統開發有六年之久，所接觸的系統均有著這一行業中系統的顯著特徵，即商務邏輯複雜、資料處理量大、資料處理效率要求高。另外，Team Dev人數眾多，且大多數情況是每個人分別實現不同業務模組。
以上環境因素，造成開發人員在實際開發工作中，對資料庫結構設計的自由度大大降低。因此，如何編寫高效的、實現具體商務邏輯的SOL語句，就顯得非常重要。技術人員通過對ORACLE資料庫中SOL語句執行過程的瞭解，可以有針對性地改善運行語句的步驟和處理邏輯，大幅度縮減SQL語句已耗用時間，從而提高單位時間內的資料處理量。
在ORACLE資料庫中，SOL語句的執行過程大致可以分為解析、執行和提取運行結果三步。那麼，如何在具體開發過程中最佳化執行語句呢?接下來我們將具體討論。
1、對解析過程的最佳化
SOL語句提交給ORACLE後，首先需要計算的是語句數值，同時尋找是否存在共用池中與此語句數值相同的其他語句，以及共用池中是否存在與此語句字元完全符合的語句。其中，第二種條件的優先順序高於第一種條件。也就是說，最理想的步驟，是在計算語句數值之後，找到共用池中值相同並且字元完全符合的語句後直接執行。如果ORACLE接受了一個與共用池中的語句相一致的語句，這個語句就會被重用。因此，在解析階段，最佳化的秘訣是使ORACLE接受一個已經在共用池中存在的語句，這樣啟動並執行效率，相比準備新的語句要高得多。
另外，Oracle為開發人員提供了充足的在資料庫中儲存代碼的能力。充分利用包括預存程序、觸發器、程式包、函數等在內的內部對象，使得資料庫可以直接存取共用池，而不使用外部資源。可以說，用資料庫中儲存的程式碼設計應用系統，資料庫的運行會更加高效。因此，在對現有系統進行維護性開發或最佳化工作的過程中，建議檢查所有的交易處理以及主要的通用過程，研究其處理常式是否可以轉換為資料庫中儲存的程式碼。如果可以用Oracle中的過程、程式包、函數、觸發器等來實現，將大大提高應用程式的運行速度。
尤其是在以金融、航空領域為代表的大型軟體，其應用於業務模組的時間動輒數年，而技術手段的更新日新月異，這就很可能造成同一套軟體由多種開發工具開發的情況。不同開發工具間的互動，往往造成了應用系統的低效率運行。如果能將主要邏輯運算方法在資料庫中編譯和儲存，將有利於系統整體運行效率的提升。
2、對語句執行和結果提取的最佳化
受Oracle內部機制影響，不同的SQL語句，其執行過程的組合也是多種多樣。因此，在最佳化SQL語句的過程中，有諸多因素需要考慮。
首先，是在開發過程中，需要避免無計劃的全表掃描。全表掃描，是在資料庫中，對無索引的表進行查詢。全表掃描，會消耗大量的伺服器資源，因為資料庫伺服器會搜尋表的每一條記錄，直到所有符合給定條件的記錄返回為止。尤其是在航空結算系統或者金融行業的系統，因為主要資料庫表的資料量動輒達到千萬級，一旦對這些表進行全表掃描，會引起資料庫資源被大量佔用，從而使得應用效能大幅下降。因此，在實際開發過程中，要基於業務需要，盡量設計好表的結構，避免沒有必要的全表查詢動作。
其次，開發人員要妥善管理好一類語句，即包含視圖的SQL語句。由於資料已經存在於實體表集合中，對於應用程式主要邏輯運算、系統提交物的產生，尤其是諸多報表的產生，需要建立大量視圖，以獲得兩張或兩張以上表的聯合資料集。在Oracle中運行包含視圖的SOL語句，最佳化器一般會有兩種執行查詢的方法：其一是在解決視圖後執行查詢，另外一種是把視圖文本本身整合到查詢裡去。第一種方法經常會導致查詢語句效能的下降，下降程度取決於所涉及表的相對大小，涉及表的結構越複雜、記錄越多，對效能的影響程度越大。如果是視圖整合到查詢中去，除了查詢的條件本身可以應用於視圖中，還可以直接使用中間結果集，也被稱為結果子集。這裡有一點需要注意的是，如果視圖本身包含集合的操作，例如Group by、SUM、COUNT或者DISTINCT等動作，則不適用於此類方法。
最後，是其他一些在實戰中積累的，可能提高資料庫效能的經驗。從本質上來講，資料庫效率變低來源於資料量的增加。有效地分流查詢資料量可以使效率得到顯著提升。這就要提到對資料進行必要的歸檔操作。一般根據具體業務需要和服務需求，線上資料的最長保留期限不會超過24個月，而線上長記錄的保留時限會更短。為提升系統整體運行效率，應及時將到期(超出業務要求的)線上長記錄從生產表中移除，轉而以短記錄的方式儲存在資料庫的曆史表中以便日後查詢，而這些線上短記錄，也應及時從線上資料庫中移出，最終根據具體業務要求，儲存在相應的檔案伺服器或磁碟陣列中。
將線上資料量穩定在一個固定的水平後，還需要為訪問頻繁且資料相對穩定的表建立充分並且符合業務需要的索引。Oracle會在語句運行前，尋找使消費最低的索引加以引用，從而避免更多的掃描時間。這裡特別需要指出的是，單單建立索引對於很多大型資料庫而言是不夠的，資料庫管理員還需要定期對索引進行重建。這是因為在複雜的系統操作過程中，表中的記錄會以很高的頻率被修改、刪除或者插入。這些操作(除了大量插入記錄外)雖然不會對錶本身的大小產生量級的影響，但其使用的索引空間使用量卻不會因為記錄的減少而降低，範圍會不斷增加。重建索引則解決了這一問題，那些曾被刪除記錄使用的空間被有效回收，進而提高了資料庫在空間上的利用率。

開發人員如何最佳化ORACLE執行語句