資料庫性能優化方法「oracle性能調優總結」

Oracle在性能調優中提供了豐富的工具和報表支持，如何從眾多指標獲取有價值的調優信息則需要開發測試人員具有一定的基礎和經驗。本文主要對近幾年碰到頻率較高的幾類性能問題進行經驗總結，幫助開發、測試人員在調優過程中少走彎路，儘快定位、解決性能問題。

除去硬體故障和產品本身bug外，本文分為配置類，sql效率類、應用程序邏輯三大類進行歸納介紹。

1.配置類

1.1綁定變數

在聯機交易系統中，對於頻繁執行的SQL語句，如果所查數據分布較均勻、分區較均衡，建議使用綁定變數代替常量，以避免多次重複硬解析(Hard Parse)，節省時間、資源成本。

反例：

select * from user where userid=1;

select * from user where userid=2;

正例：

b1=1;

select * from user where userid= :b1;

b1=2;

select * from user where userid= :b1;

硬解析指標參考AWR報告中Load Profile–>Hard Parse/Sec（參考值：< 2 or 10），筆者一般會在大於1時用以下腳本查找可能需使用綁定變數優化的SQL。

1、利用force_matching_signature查詢可能可以使用綁定變數的語句數量

select v.force_matching_signature ,count(*) from v$sql v where FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATURE and PARSING_SCHEMA_NAME <> ‘SYS’ group by v.force_matching_signature having count(*)>&a order by 2;

2、查出疑似可使用綁定變數的SQL語句

select PARSING_SCHEMA_NAME,sql_text,sql_id from v$sql where force_matching_signature=’force_matching_signature_IDinStep1′;

3、 根據查詢結果與開發人員溝通，確認是否可用綁定變數的方式對SQL語句進行優化。

1）利用force_matching_signature查詢可能可以使用綁定變數的語句數量

select v.force_matching_signature ,count(*) from v$sql v where FORCE_MATCHING_SIGNATURE <> EXACT_MATCHING_SIGNATURE and PARSING_SCHEMA_NAME <> ‘SYS’ group by v.force_matching_signature having count(*)>&a order by 2;

2）查出疑似可使用綁定變數的SQL語句

select PARSING_SCHEMA_NAME,sql_text,sql_id from v$sql where force_matching_signature=’force_matching_signature_IDinStep1′;

3）根據查詢結果與開發人員溝通，確認是否可用綁定變數的方式對SQL語句進行優化。

綁定變數一定能優化性能嗎？

–小心”綁定偷窺”！！！

T1表包含100萬條記錄，status欄位含’A’，’C’兩種不同取值，其中：

status=’A’ 99萬9990條

status=’C’ 10條

SQL1：Select * from T1 where status=:b1

:b1 =’A’，則單表訪問路徑走全表掃描

SQL2：Select * from T1 where status=:b1

:b1 =’C’，則單表訪問路徑走索引範圍掃描

理想情況下，傳入不同變數的值，應該走不一樣的單表訪問路徑，但Oracle優化器還不夠智能。Oracle在第一次做硬解析（內存中沒有緩存執行計劃）的時候，會先”偷窺”一眼，變數的值傳入的是什麼，如果傳入的是”A”,則走全表掃描；並且把執行計劃緩存。

下一次執行的時候，由於執行計劃已經緩存，就不再”偷窺”變數的值了，而是直接沿用全表掃描的執行計劃。這個時候即使傳入的status變數為C，也走不上索引了。

這個現象稱為”綁定變數偷窺現象”。一條SQL語句適合採用何種解析方式需要衡量硬解析帶來的資源開銷和查詢計劃不準帶來的資源開銷，從而確定是否採用綁定變數。兩種解析適用場景總結如下：

1.2連接池

經常收到一些”怎麼查看應用到Oracle連接池是否夠用”，”系統tps很低，SQL也簡單，為啥資料庫伺服器cpu>10%？”

除了根據伺服器連接數或利用第三方工具，可從以下4個方面間接判斷連接池是否夠用：

1. 參考AWR報告中Load Profile–>Logons /Sec，參考值：< 2 or 10。

2. 參考ADDM中出現Session Connect and Disconnect–> Percent of Activity > 10。

3. top命令中cpu消耗排在前10位進程中含tnslsnr，或該進程消耗cpu > 10%。

4. alert.log中出現連接頻繁建立或斷開的告警。

若出現上述現象，應考慮適當增加連接池或檢查應用是否用到連接池。

1.3並行模式PARALLEL

並行模式適用於針對大數據量的操作，應用得當能大大縮短計算時間。但其劣勢在於：資源調度、合併結果集等比較消耗資源，不建議在系統超負荷運行的情況下使用。並行模式使用應注意以下幾項：

1.聯機交易往往並發較高，應避免使用並行。

2.聯機交易高峰時段，避免批量或報表使用並行。

3.並行查詢的優先順序為語句提示(hint)、表級定義、資料庫初始化參數。後兩者易造成響應時間慢、表掃描、會話阻塞等異常，不建議在應用運行時使用。

4.對於較大的數據量的查詢，可以使用提示(hint)來強制Oracle使用並行查詢。

5.建表、索引時如需使用PARALLEL，完成後切記關閉並行度，否則會造成後續使用該表、索引的SQL啟用了並行,佔用過多資源，導致其它會話等待，影響系統整體性能。

6.任務並行度不應大於伺服器CPU數，建議單個任務並行度應小於CPU數/2。

1.4統計信息缺乏或陳舊

開發測試環境往往缺乏統計信息更新機制，統計信息陳舊可能造成SQL查詢計劃有誤，查詢效率低下。大量的數據載入或更新後應及時收集統計信息。

1.5物化視圖

物化視圖是一種特殊的物理表，佔用實際的存儲空間，可用於讀寫分離，或者預先計算並保存表連接、嵌套或聚集等耗時較多的操作結果，在執行查詢時能避免這些耗時操作，從而快速得到結果。物化視圖主要用於數據倉庫和決策支持系統，使用物化視圖需注意：

1.對於高並發的聯機系統、基表數據頻繁更新且對數據實時性要求高的交易避免訪問物化視圖。

2.基表數據變更頻繁，一般不建議使用ON COMMIT數據刷新模式，推薦使用默認的ON DEMAND手工模式。

3. ON DEMAND模式下用到FAST增量刷新時，必須在創建有物化視圖日誌的情況下才能使用。

4.物化視圖日誌的大小直接會影響刷新速度。物化視圖長時間不刷新，或者基表的一次批量數據變更均會導致物化視圖日誌變得很大。

5.物化視圖日誌的高水位達到較高位置，即使物化視圖日誌中記錄很少甚至沒有，仍然會影響物化視圖的刷新速度。

2. SQL效率類

不合理的數據結構設計，SQL書寫不規範會導致笛卡爾積操作、全表掃描、索引跳掃、索引全掃、filter低效過濾等低效操作，從而導致SQL效率甚至應用性能大打折扣。本章節列出了常見的導致SQL低效的條例，實際開發測試過程中可能需要結合查詢計劃、統計信息、V$_*等進行調優驗證。

2.1表結構不合理

表結構不合理一般表現在：缺少主鍵、索引或索引設計不當，尤其是複合索引的選擇和排序上。表連接的時候恰當使用索引可以避免表掃和排序的發生。

2.2 SQL書寫較差

3. 應用程序邏輯

在性能測試測試中曾遇見因應用設計導致資料庫伺服器瓶頸，常見類型有：

1.高頻的SQL運行導致CPU繁忙。SQL語句平均執行時間很快，但通過對單筆交易運行的sql語句發現單筆交易運行相同SQL達100遍以上，需要結合業務邏輯考慮SQL設計的合理性。

2.高頻的記日誌導致IO等待。例如單筆普通查詢交易按照動賬類金融交易嚴格記錄日誌，查詢交易吞吐量較高時增加資料庫伺服器IO瓶頸。

3.欄位長度不滿足業務增長需求，導致鍵值衝突等異常。

4.未對用戶反覆提交查詢作出限制。尤其對於響應時間較長的SQL以及結果集可能比較大的SQL，如未防止用戶反覆點擊會對資料庫產生的嚴重影響。

5.客戶往往只關注的查詢排序後的部分結果集，為了控制輸出結果集大小，減少系統中IO，將結果儘快地返回給客戶端，開發上經常採用分頁查詢。