1. 首頁 > 生活 > >

oracle性能調整與優化 oracle性能優化

小知識oracle性能優化



文章插圖
oracle性能調整與優化 oracle性能優化

文章插圖

Oracle在性能調優中提供了豐富的工具和報表支持，如何從眾多指標獲取有價值的調優信息則需要開發測試人員具有一定的基礎和經驗 。本文主要對近幾年碰到頻率較高的幾類性能問題進行經驗總結，幫助開發、測試人員在調優過程中少走彎路，盡快定位、解決性能問題 。
除去硬件故障和產品本身bug外，本文分為配置類，sql效率類、應用程序邏輯三大類進行歸納介紹 。
1.配置類
1.1綁定變量
在聯機交易系統中，對于頻繁執行的SQL語句，如果所查數據分布較均勻、分區較均衡，建議使用綁定變量代替常量，以避免多次重復硬解析(Hard Parse)，節省時間、資源成本 。
反例：
select * from user where userid=1;
select * from user where userid=2;
【oracle性能調整與優化 oracle性能優化】正例：
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，將結果盡快地返回給客戶端，開發上經常采用分頁查詢 。