【恩墨學院】基於裸資料的異地資料庫效能診斷與最佳化

楊江

楊江， 6年Oracle工作經驗，4年Oracle資料庫專業服務經驗，擅長效能最佳化、效能問題診斷、故障排查、GOLDENGATE。

影響資料庫效能的因素有很多，從大的方面可以分為硬體和軟體。硬體包括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

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;

1.  小時內還未執行完一次，但佔用整個取樣期間8.21%的物理讀，並伴有嚴重的IO等待，對取樣期間資料庫整體效能有較大影響

2.  執行計劃中存在全表掃描操作

3.  語句簡單易懂

表基礎資訊

近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%。理論上適合使用索引，不必要全表掃描。

索引情況分析

1.  此表當前存在3個組合索引4個單列索引

2.  其中前三個索引實則過濾性極差，索引的NDV值僅2個或者3個，除非值嚴重分佈不均，同時又經常選取值少的部分，不然這類索引沒有存在的必要

3.  結合本例子SQL，涉及的列上均沒有索引，建立FEETYPE, SUBMITDATE兩列組合索引，理應提升SQL效能

解決方案

a.  建立FEETYPE, SUBMITDATE組合索引，執行SQL執行時長縮短到10S以內

b.  表按SUBMITDATE分割槽，資料按月存放資料

恩墨學院隸屬於雲和恩墨（北京）資訊科技有限公司，致力於提供專業高水準的與大資料培訓服務，挖掘培養大資料與資料庫人才。恩墨學院提供包括個人實戰技能培訓、個人認證培訓、企業內訓在內的全方位大資料和資料庫技術培訓。ACE級別超強師資，配備專業實驗室，沉浸式學習與訓練，專業實驗室、配備專業助教指導訓練。能迅速融入專家圈子，業內資源豐富，迅速積累職場人脈。課程包括：班、Oracle 、Oracle OCP考試等。