486
基於裸數據的異地數據庫性能診斷與優化
影響數據庫性能的因素有很多，從大的方麵可以分為硬件和軟件。硬件包括CPU、內存、存儲、網絡設備等，軟件方麵包括操作係統版本、操作係統參數、數據庫版本、數據庫參數、數據庫架構、運行的SQL代碼等。
以上因素中，運行的SQL代碼可單獨歸為一類，這部分內容多變，可控性較低，與業務強關聯，動態影響，難以準確捕獲，問題此消彼長難以根除。通過我們處理的故障類型統計，80%的性能問題來自於不良的SQL語句編寫。
生產環境常做訪問控製，管理生產環境DBA忙於日常事務無法顧及數據庫性能。本文介紹一次性從生產庫上獲取分析性能SQL相關的數據，拿到本地環境分析診斷生產性能問題。
較詳細分析一個SQL的性能，需要的內容包括執行計劃信息、表的基礎信息、索引基礎信息、SQL寫法問題等等。這些內容都存放在數據字典中。
1、創建相關的表，語句參考：
注：（第三條、第四條紅框處，沒有* 是因為這兩個視圖裏麵有long類型，不支持create as ct操作，實際操作過程中，未獲取long類型的數據，隻選取了必要的列）
2、通過數據泵導出上述創建的表
3、導出AWR裸數據
$ORACLE_HOME/rdbms/admin/awrextr.sql
4、本地導入創建的表
5、通過數據泵導入AWR裸數據
$ORACLE_HOME/rdbms/admin/awrload.sql
1、執行時段為10~12點，15~17點，平均執行時長超過1秒的SQL統計。多個采樣期間都有執行的，取執行次數最多的采樣期間。
2、執行結果部分展示如下：
3、生成這獲取這此SQL的SQLAWR數據腳本(取前20)
4、生成結果放入命令窗口執行
注：紅框為格式化操作
5、生成結果展示如下
案例解析
NEW_TOP_PHYSICAL_16_awr_sqlrpt_dqdx4x39x2x7m.html
SQL文本
SELECT COUNT(1)
FROM GPCXXXXXXXX A
WHERE A.VALIDDATE < :B1
AND A.SUBMITDATE < :B1
AND A.SUBMITDATE >SYSDATE - 40
AND A.FEETYPE IN ('307')
AND A.PLANSTATUS = 'N'
AND ROWNUM = 1;
- 小時內還未執行完一次，但占用整個采樣期間8.21%的物理讀，並伴有嚴重的IO等待，對采樣期間數據庫整體性能有較大影響
- 執行計劃中存在全表掃描操作
- 語句簡單易懂
表基礎信息
近3億行，未分區，平均行長149，理論占用空間大小為 296815739*149*1.17/1024/1024/1024=48G，實際占用約50G空間（從MY_DBA_SEGMENTS中獲取），知此表碎片並不嚴重或不存在碎片。
SQL綁定變量分析
結合綁定變量和條件看，大範圍上，隻查詢40天以內的數據。
條件列數據分布情況
回顧下SQL條件：
WHERE A.VALIDDATE < :B1
AND A.SUBMITDATE < :B1
AND A.SUBMITDATE > SYSDATE - 40
AND A.FEETYPE IN ('307')
AND A.PLANSTATUS = 'N'
AND ROWNUM = 1;
結合條件和上述查詢結果，分析如下：
FEETYPE,PLANSTATUS是等值關聯，VALIDDATE是開區間範圍關聯，SUBMITDATE是閉區間範圍關聯。已知此表中SUBMITDATE保留3年數據，在數據分布平均的情況下，此SQL查詢的數據量約為(296815739/3/365)*40/25/2=21.7W，約占整個表的0.07%。理論上適合使用索引，不必要全表掃描。
索引情況分析
- 此表當前存在3個組合索引4個單列索引
- 其中前三個索引實則過濾性極差，索引的NDV值僅2個或者3個，除非值嚴重分布不均，同時又經常選取值少的部分，不然這類索引沒有存在的必要
- 結合本例子SQL，涉及的列上均沒有索引，建立FEETYPE, SUBMITDATE兩列組合索引，理應提升SQL性能
- 建立FEETYPE, SUBMITDATE組合索引，執行SQL執行時長縮短到10S以內
- 表按SUBMITDATE分區，數據按月存放數據
最後更新：2017-11-15 14:05:54