基于ORACLE數據庫的SQL性能優化

岳彩云 賴曉風

摘要：數據庫系統是任何信息系統最重要的組成部分，它涉及信息系統運行效率，影響系統性能。隨著現代信息技術的進步，數據庫的規模越來越龐大，對數據處理的操作也越來越復雜。在Oracle數據庫系統中，查詢操作是最基本、最復雜、最頻繁的操作，SQL的查詢語句的效率直接影響數據庫的整體性能。該文主要介紹了SOL的語句所用優化技術，簡要分析了數據庫邏輯結構的優化、數據庫物理存儲結構的優化、使用分區。同時深入研究SOL性能分析及優化，其中，手動進行SOLprofile綁定主要涉及要執行的SOL文本、計劃出問題后的表現、采用sql lprofile綁定;索引調優涉及要執行的SOL文本、SQL執行的相關統計信息、SQL執行計劃、創建索引等。經過研究得出，若想將ORACLE數據庫性能提高，必須多角度優化SQL語句。

關鍵詞：數據庫;SQL;索引;查詢

中圖分類號：TP311 文獻標識碼：A

文章編號：1009-3044（2020）10-0017-03

1數據庫結構優化

1.1數據庫邏輯結構的優化

邏輯數據庫設計不合理往往易產生數據冗余、更新異常、插入異常、刪除異常等問題，所以邏輯數據庫設計至少應滿足規范化BC范式或第三范式。

為降低數據冗余、減少用于存儲數據的頁，可遵循高級別的范式來減少每張表的列數，但這將產生更多表，且表間關系會更復雜，這樣會降低系統的性能，特別是查詢性能。從某種意義上說，非規范化能提高系統效率，非規范化過程可以結合性能考慮用多種手段實現，所以進行數據庫邏輯結構設計時應綜合考慮數據冗余和基于連接的查詢性能問題。

1.2數據庫物理存儲結構的優化

因為數據文件和日志文件的位置、分布直接影響到數據庫系統性能，所以數據庫設計應遵循：一是將序列訪問的文件和數據文件分別存放在不同磁盤上，一般，序列訪問文件宜存儲于高速專用磁盤上，數據文件分散存儲到不同的磁盤上而實現并行I/O，從而提高訪問速度;二是數據類型應盡量使用所需的最小存儲空間，特別是索引列，如能使用Smallint類型的就不用Int型，這樣數據頁就能存放更多的數據行，以減少I/O操作。

1.3使用分區

對于數據量超過PB級、TB級甚至更大的大型數據庫，某些單表的記錄數往往多達億條，巨大的數據量將嚴重影響數據庫的運行效率和運維難度。為解決這一問題，可對表進行合理分區。把大表分為多個更小、更容易管理的部分，充分利用數據庫系統中的多個CPU或多個磁盤子系統，以改善數據庫系統的運行效率?？梢园凑諛I務數據本身性質進行表分區，也可按時間進行表分區，或其他業務的維度進行分區。

2SQL性能分析及優化

不同的業務場景、數據庫類型、數據邏輯結構及不同的網絡、服務器等硬件環境，實驗結果或有微小差異。以下所有實驗結果都是針對某市某業務信息系統后臺數據庫進行的實驗，且所用數據庫為Oracle 12C版本，相關SQL處理后的效果只是一個大致的結果。

2.1手動進行SQL profile綁定

當Oracle面對執行計劃失效或跑偏時，執行效率就會大大降低，影響數據庫的查詢性能甚至整個數據庫性能，此時，最好的優化手段就是進行SQL profile人工綁定。例如：

2.1.1要執行的SQL文本

2.1.2計劃出問題后的表現sql單次執行時間需要消耗204秒，單次運行產生邏輯讀消耗995k塊次，物理讀323塊次，按照標準塊8k計算，需要消耗邏輯讀7.9G，物理讀2.5G。

系統消耗主要表現在ID=6，ac82_110共約6.4億行數據，idex_ac82 110_7的distinct值為509。

2.1.3采用sql profile綁定

綁定后，SQL執行時間由207秒縮短至4秒，執行效率提高約50倍;物理讀消耗由324k減少至4k，物理讀消耗減少約80倍;邏輯讀消耗由1M塊次減少至108k，邏輯讀減少約10倍。

2.2索引調優

能否有效使用索引是數據庫是否取得高性能的關鍵。因為查詢主要性能開銷是磁盤I/O，而全表掃描會產生大量的磁盤I/O，而使用索引直接指向數據存放位置，則只需少量的磁盤讀取操作，避免了全表掃描帶來的性能開銷，從而加速數據的查詢過程。但是，索引也會使數據庫在執行增、刪、改等操作時增加額外的系統開銷，并且索引本身也會占用數據庫的空間。因此，索引并不是越多越好，只有建立合理有效的索引才有助于改善數據庫性能。合理有效的索引是建立在對各種業務場景熟悉，科學的查詢分析和預測基礎上的。

2.2.1要執行的SQL文本

2.2.2 SQL執行的相關統計信息

平均邏輯讀達87k塊次，采樣期為兩天，兩天執行18799次，屬高執行頻次，致使總邏輯讀達1.6G塊次。

2.2.3 SQL執行計劃

2.2.4創建索引

從上述執行計劃可看到，執行計劃中存在索引跳躍掃描，而該索引得前導列yaz040的基數達15萬多，這樣的跳躍掃描很消耗性能，所以建議在AAZ288列上單獨創建索引。創建索引語句如下：

2.2.5 SQL創建后的效果

通過創建索引后，執行時間只有0.01s，平均邏輯讀只有幾百塊次甚至更低。

2.3 SQL語句本身優化

在數據庫系統中，使用SQL不能僅僅關注執行結果的正確性，更應關注在不同的軟、硬件及網絡環境和業務場景下存在的性能差異，這種性能差異在大型甚至超大型數據庫環境中尤為明顯。本人在工作和學習的實踐中發現，性能低下的SQL往往來自濫用索引、連接的誤用和無法優化的where子句。通過避免以上問題，可以明顯提高SQL運行效率。

2.3.1要執行的SQL文本

2.3.2 SQL執行的相關統計信息

SQL在統計單次執行時間約40分鐘，單次邏輯讀8.9M塊次，按標準塊8k計算，需要消耗約70G的邏輯讀。

2.3.3執行計劃

注意到id=8，10，7，id=8通過id=9即KB05K1的自鏈接條件返回了851條結果集，然后作為驅動表同id=10做了一個filter，這意味著要對KB05K1做約800次的掃描。性能消耗就出在這里。

2.3.4 SQL語句優化調整

2.3.5 SQL語句優化后的執行效果

將源語句標亮部分造成的多次大表掃描，用分析函數替代，可只走一次掃描。邏輯讀由原來的8900k塊次變為137k塊次，縮小65倍。查詢由原來的40分鐘變為3秒。

3結束語

文章對Oracle數據庫性能調整和優化進行了簡要的分析和研究，對數據庫設計、SQL的性能等的優化進行了探討。但在實際工作中，針對不同的數據量級、不同的軟件、硬件環境以及網絡環境，需要綜合考慮各種方法和制定多種措施。Oracle數據庫性能優化是一項系統工程，需要對數據庫系統的運行狀態做出全面的評估并根據工作實際情況系統地動態調整數據庫以得到最優的性能。