1002      小米  小米6

15年老司機的DPM數據庫性能分析產品研發之路

本文根據DBAplus社群第87期線上分享整理而成。

鄒德裕

輕維軟件首席專家

• DBAplus社群聯合發起人，OraZ產品作者、Oracle OCM。

• 15年運維管理經驗，在數據庫診斷、故障排除、優化、架構設計等方麵具有豐富的經驗。

主題簡介：

1、運維中常見的場景及對應解決案例

2、解密DPM數據庫性能分析平台

本次我給大家帶來的主題分享為《15年老司機的DPM數據庫性能分析產品研發之路》。

我將通過Oracle在實際生產中常見的運維場景及問題處理案例，解析如何直擊、解決DBA的痛點，最後為大家揭秘DPM數據庫的台前幕後。

先簡單介紹下我的個人從業經曆。

大學期間，我曾給係裏為區政府做網站，當時正在推行“政府上網工程”， 一個暑假用asp就賺了一個學期的生活費。

畢業後在IT圈混了近10幾年，先後做了開發、銷售（賣的還是勞斯萊斯發電機）幹了快半年，實在賣不動又回去搞開發，並用Java給公司做了個“7號信令”的解碼軟件（現在再讓我回去寫那些算法，估計寫不出來了）。

再後來，我在開發中接觸了Oracle數據庫，便深深地喜歡上嚴謹的甲骨文，就去考了OCP，轉型DBA。一路磕磕碰碰走過來，回首過往，才發現這段開發經驗始終貫穿我工作的十幾年。

運維中常見場景及對應解決案例

現在的數據庫管理員必須擁有前所未有的淵博知識，最好具有設計、開發、係統管理背景，與數據庫有關的一切工作都可以看作是DBA的職責範圍。DBA隨時都要承受很大壓力，不僅要完成應用和數據庫的更改、快速解決問題，還要防止數據的丟失和損壞。

我們來了解下數據庫DBA的主要職責：

1. 保證數據的安全如備份方案，容災方案；

2. 數據庫的可用性；

3. 日常故障處理、問題診斷 ；

4. 性能分析處理；

5. 數據庫升級（打補丁）、改造、優化、補丁修複等。

下麵通過5個場景，從SQL優化、後台常見錯誤定位分析、鎖堵塞應急處理、如何規範完整地收集問題診斷信息，以及如何實現巡檢報告的工具化、平台化，逐步展開對DPM性能分析平台的了解。

為了消除故障、縮短故障曆時，提高設備運行性能，每月（或節假日）對數據庫的運行情況進行巡檢，以及時發現生產數據庫時已經存在或潛在的問題，同時提交係統巡檢報告，對係統存在的問題提出整改建議。而對於DBA來講，數據庫巡檢是件繁瑣的事情，但又不得不做。

以下為巡檢報告目錄，總共近65頁（還一半目錄未列出），以一個二線DBA為例，做一套庫最快也要半天，且該項工作重複且枯燥，以下為巡檢報告示例目錄：

1）通過DBAplus社群工具OraZ實現的深度巡檢，實現對係統配置、CRS、操作係統、補丁等軟件環境運行配置檢測、並提供原廠解決方案，該自動化巡檢功能整合到自動化運維平台，一鍵體檢實現主動掃描整個係統以及係統的操作係統、CRS、數據庫、高可用等層麵中的已知問題，提升係統的穩定性，檢查並提示環境變化是否違反最佳實踐，升級前後的驗證評估、環境檢測；

檢測結果： Oswatcher未安裝的明細：

平台將巡檢收集的係統性能數據、容量信息、配置信息收集保存在平台，為後續係統維護、升級、擴容提供決策數據支持，實現巡檢自動化。

作用：

1. 對於係統中存在的風險提供簡單化和合理化的診斷分析建議；

2. 對係統中存在的風險提供對應專業的解決方案；

3. 評估結果量化評分，直觀判斷健康度；

4. 檢測積累的最佳實踐應用是否啟用；

5. 數據入庫統計分析：高風險漏洞統計、安全趨勢分析、整體最佳實踐應用評估、分析問題出現趨勢、報告結果對比等。

2）DPM一鍵巡檢模塊

DPM巡檢模塊為Oraz巡檢的升級和延伸，增加360式的一鍵巡檢功能，支持數據庫一鍵式深度健康檢查，覆蓋麵更廣，包括了配置、性能、安全（包含最新的比特幣勒索檢測）、對象審核、集群等150多個指標，並提供按巡檢模板導出word功能，並生成巡檢報告。

Oracle數據庫的日常工作之一，當係統出現問題後盡快地定位問題，現場解決一部分常規數據庫問題。如出現些600或07445等內部錯誤或疑難雜症時，需向Oracle support請求專家深入分析，如需在MOS上開SR時提供規範、完整診斷問題的信息。

如何收集信息也是DBA一件頭疼的事，特別是開一級SR（故障當前持續中），此時在線專家會不時的要求現場提供對應的問題診斷信息，一來一往擠牙膏式的，往往耽誤問題解決，延長業務恢複的時間。

與數據庫打交道多年，處理過大量故障，也掌握了不少問題診斷的方法和工具，對每一類問題都可以大體歸類出一些診斷方法。無論問題多麼複雜，像扒洋蔥一樣，一層層去掉無關的，留下關鍵的，同時借助於一些診斷工具，層層深入，最後找到問題的核心。

首先按信息類型建立數據收集標準：

收集分類

• 標準Trace收集/Alert信息收集

RAC收集：

1. 每個節點的Alert.log

2. 每個節點的LMS[0|9] trace file

3. 每個節點的LCK trace file

4. 每個節點的LMON trace文件

5. 每個節點的LMD0 trace文件

6. 每個節點的DIAGA trace 文件

7. Alert log中提到的Trace 文件

非RAC收集：

1. 單Instance的Alert.log

2. Alert log中提到的Trace文件

性能影響：無，隻是簡單提取數據。

• OS日誌信息收集

操作係統對應相關信息如下：

1. Linux： /var/log/messages

2. Solaris： /var/adm/messages

3. HP-UX： /var/adm/syslog/syslog.log

4. Tru64： /var/adm/messages

5. AIX： 每個節點的"errpt" 和"errpt -a"命令輸出結果

6. Windows： 通過事件查看器導出係統日誌和應用日誌

性能影響：無，隻是簡單提取數據。

• RACDiag信息收集

運行racdiag.sql執行。

在相應的目前下執行這個腳本。

需要注意的這個腳本已經包含如下腳本：

-- 獲取Hang Analyze的trace，並會執行一會：

oradebug setmypid

oradebug unlimit

oradebug -g all hanganalyze 3

-- 下麵步驟將會執行時間比較長，需監控是否有對應文件生成：

oradebug -g all dump systemstate 267

若運行racdiag.sql這個腳本，就不再需要做sytemdump以及hanganalyze。

sqlplus “/ as sysdba”

sql> @racdiag.sql

性能影響：因為含有systemdump，做執行時間可能會相對較長.其執行時間受systemdump時間限製,之後去bdump以及udmp檢查執行時間點產生的文件。

注：篇幅有限，以下的就不一一列出了。

CRS日誌收集

1. HangAnalyze信息收集

2. SystemState信息收集

3. AWR/ASH信息收集

4. STATSPACK信息收集

5. Process相關信息收集

6. 診斷工具數據收集：OSWatcher、RDA、Procwatcher、11g ADRCLI、SQLT(SQLTXPLAIN)等工具

然後按故障類別梳理，整理對應故障所需提供診斷信息：

注：每種故障類型列出一個，其它不一一列出。

診斷RAC 問題：

• 實例剔除問題

報錯信息為：

INSTANCE EVICITON/ORA-29740： “EVICTED BY MEMBER %S,GROUPS INCARNATION”

Errors in file /opt/bdump/10gR2/bdump/v10gR21_lmon_121396.trc： ORA-29740： evicted by member 0, group incarnation 18 Mon Dec 8 01：52：25 2007 LMON： terminating instance due to error 2974

一個Instance報ORA-29740錯誤，可能是由於Oracle的軟件本身問題也有可能是由於硬件資源問題或者網絡問題，對於這種問題，需收集如下信息：標準日誌信息、CRS Logs信息、OS 信息、Procwaterh信息、OSW信息。

此外還有可能需要做CRS核心進程Debug（如Oracle後台專家建議）。

• Lmon進程中斷問題

報錯報錯為：

ORA-481： “LMON PROCESS TERMINATED WITH ERROR”

一般是由於 LMON在監視集群軟件本身時產生問題，出於結點本身一致性的需要，故需要LMON將此實例中斷。

Errors in file /opt/bdump/10gR2/bdump//v10gR21_lmon_9944.trc： ORA-481： LMON process terminated with error Thu Sep 25 03：46：56 2008 LMON： terminating instance due to error 481

需收集如下信息：標準日誌信息、CRS Logs信息、OS 信息。

• LCK進程中斷問題

• RAC節點自動重啟問題

• GES Potential blocker問題

• IPC Send Timed Out問題

診斷性能問題：

• 數據庫慢問題

根據數據庫的版本對應信息，一般情況下需收集信息如下：

1. ASH數據；

2. AWR：如果故障時間短，需要短時間頻率的AWR時間連續性報告多份，如故障時間長，需要長時間頻率的AWR 時間連續性報告多份；

3. OS信息、RDA信息；

4. Statspack信息(9i庫)；

5. 進程信息數據如10046 and Error stack and Process dump；

6. Hanganalyze和System State信息。

• 數據庫Hang/鎖的問題

• 內存4031問題

• 內存Latch問題

診斷非RAC問題

• ORA-600問題

• ORA-7445問題

ORA-07445 ： exception encountered： core dump [%s] [%s]

需收集如下信息：

1. 標淮的Alert文件及Alert log提到的Trace文件；

2. 如果生成很多文件，建議把最初的Trace文件提供，如果有生成不同的Trace文件，把每一個的Trace文件第一個上傳；

3. 在一些情況下，如果Trace文件沒有生成，如果這樣的話，core文件會對文件解釋有用，並且需要用操作係統的debug工具進提供。

• Instance Crash問題

• Listener問題

• 數據庫啟動問題

DPM平台診斷信息快速收集解決方案：

1）整合自動化運維平台的腳本管理模塊，通過腳本管理、SSH端口、遠程命令調用等功能將診斷信息收集標準化，並整合進平台，幫助DBA在處理相關問題時進行快速信息搜集，熟練的故障信息收集能夠減少故障處理的時間，這些必要的信息對於原因查找以及故障診斷和分析都是非常必要的。

2）故障出現時，通常DBA處理數據庫係統的性能問題時遇到最大的困難就是在現象發生的時候去收集必要的診斷信息。
 

首先，必要的診斷信息一般很少能被收集到，因為在問題發生時去定位問題、決定收集哪些診斷信息、考慮如何去收集這些診斷信息會花費一些時間。

更多的情況卻是，問題已經過去了，或者我們不得不關閉數據庫來解決這些問題。這就迫使用戶不得不等待問題再次發生時去快速收集信息。

DPM通過內部核心專業算法采集上百個內置指標和實時性能數據，已避免當數據庫發生性能問題的時候，我們並沒有機會來收集足夠的診斷信息，比如system state dump或者hang analyze，甚至問題發生的時候DBA根本不在場，我們才能在事後根據DPM收集的信息來分析問題的原因。

如何快速發現並解決ORA-XXX錯誤信息和分析思路、解決方法

數據庫後台日誌錯誤類型很多，這裏以ORA-00600數據庫內部致命錯誤來展示分析過程：

報錯原因

1）Ora-00600錯誤的發生，本質上是因為Oracle RDBMS程序代碼在運行過程中發生了程序意外(program exception)，它屬於Oracle的內部錯誤。

2）Ora-00600錯誤通常是由於Oracle BUG引發的，當然，其它一些情況也有可能引發，比如操作係統資源不夠，或者硬件出現問題時，或者不正確的操作也有可能引起。

報錯參數

1）在出現Ora-00600錯誤時，通常伴隨具體的報錯參數，每個參數被方括號包圍，格式如下：ORA-00600 internal error code, arguments： [string], [string], [string], [string], [string], [string], [string], [string]

2）第一個參數表示了發生意外的Oracle代碼的位置，它對定位問題起關鍵作用；剩下的參數提供更進一步的報錯信息。

解決手段

1）通常在出現ORA-00600時，都會在USER_DUMP_DEST或BACKGROUND_DUMP_DEST目錄下(11g的目錄結構不同)產生一些trace文件，同時在alert<SID>.log文件中也會產生報錯信息，這些信息和trace文件可以用來幫助分析錯誤原因；

2）還可以根據報錯前數據庫的日誌，以及詢問現場操作人員，分析數據庫在報錯之前進行了哪些操作，分析是不是因為不正確的操作引發了ORA-00600錯誤；

3）還可以通過MOS(My Oracle Support)來更深入的查找報錯信息的說明，MOS還提供了ORA-600/ORA-7445 Troubleshooter功能，可以查找相同ORA-00600錯誤的案例；

4）如果沒有關於您的報錯信息的說明，需向Oracle support請求支持，比如在MOS上開SR。

數據庫後台日誌實時告警

DPM“日誌分析”模塊通過大數據技術實時提取和分析數據庫後台日誌，DBA可以通過界麵形式直觀展示數據庫日誌：

場景四： “TX,TM,DX”鎖應急處理

現象描述

數據庫大量鎖異常等待，係統資源消耗高，cpu負載高 （針對大量'TX,TM,DX'等類型的鎖造成的大量異常等待）。

影響因素

多個事務爭用造成。

傳統解決方法

以下語句列出是誰造成了阻塞

 column event format a30 

  column sess format a20

  set linesize 250

  set pagesize 0

  break on id1 skip 1

select decode(request,0,'Holder：',' Waiter：') || s.inst_id || '：' || s.sid||','|| s.serial# sess,

      id1, id2, lmode, request, l.type, ctime, s.username,s.sql_id, s.event

-- ,s.service_name

  from gv$lock l, gv$session s

  where (id1, id2, l.type) in

    (select id1, id2, type from gv$lock where request>0

    )

   and l.sid=s.sid

   and l.inst_id=s.inst_id   

  order by id1, ctime desc, request

/

按照這個語句多查詢幾次，如果Holder持續不變，則跟開發商確認是否異常（如應用側無法回滾或提交，則數據庫端終止該進程以釋放鎖）。操作前記錄相關日誌。

DPM平台解決方案

通過DPM的“堵塞與等待”模塊分析，實現數據庫鎖阻塞的智能分析及一鍵式解鎖功能（涉及安全該功能暫時隱藏，後續修改成直接生成終止會話和進程的語句，由DBA自行判斷是否能終止），包括鎖的曆史情況均可回溯，堵塞源頭信息一目了然。

數據庫80%以上的問題都為性能問題，而SQL優化的本質就是：1、縮短響應時間；2、提升係統吞吐量；3、提升係統負載能力。

要使用多種手段，在提升係統吞吐量和增加係統負載能力、提高單個SQL效率之間尋求一種平衡。就是要盡量減少一條SQL需要訪問的資源總量，比如走索引更好，那就不要使用全表掃描。

SQL優化過程圖：

SQL優化步驟：

1、獲取SQL製定優化目標：從AWR、ASH、ORA工具、SQL CHECK SCRIPTS等主動發現有問題的SQL、用戶報告有性能問題DBA介入等，通過對SQL的執行情況進行了解，先初步製定SQL的優化目標。

2、檢查執行計劃：explain工具、sql*plus autotrace、dbms_xplan、10046、10053、awrsqrpt.sql等。

3、檢查統計信息：Oracle使用DBMS_STATS包對統計信息進行管理，涉及係統統計信息、表、列、索引、分區等對象的統計信息，統計信息是SQL能夠使用正確執行計劃的保證。

4、檢查高效訪問結構：重要的訪問結構，諸如索引、分區等能夠快速提高SQL執行效率。表存儲的數據本身，如碎片過多、數據傾斜嚴重、數據存儲離散度大，也會影響效率。

5、檢查影響優化器的參數：optimizer_mode、optimizer_index_cost_adj、optimizer_dynamic sampling、_optimizer_mjc_enabled、_optimizer_cost_based_transformation、hash_join_enable等對SQL執行計劃影響較大。

6、優化器新特性、BUG：如11g的ACS(自適應遊標共享）、automatic serial direct path（自動串行直接路徑讀）、extended statistics、SQL query result cache等。有的新特性會導致問題，所以需要謹慎使用。

7、SQL語句編寫問題：SQL語句結構複雜、使用了不合理的語法，比如UNION代替UNION ALL都可能導致性能低下。

8、優化器限製：無法收集或得到準確的統計信息、無法正確進行查詢轉換操作等，如semi join、anti join與or連用會走FILTER操作。

9、其他：主要涉及設計問題，如應用在業務高峰期運行，實際上可以放到較空閑狀態運行。表、索引、分區等設計不合理。

以上幾點，是我們進行優化時需要考慮的地方，可以逐步檢查。當然，80%到90%的純SQL性能調整，通過建立索引，收集正確統計信息，避免改寫優化器限製，就已經能夠解決了。

優化的示例這裏就不列出了，大家可以參考丁大師在社群分享過的《看了此文，Oracle SQL優化文章不必再看！”》一文。下麵我們來看看通過DPM的優化解決方案。

應用自動化工具DPM優化解決方案

通過上麵的SQL優化的過程和步驟看，從發現問題SQL、獲取執行計劃、檢查執行計劃、對比執行計劃、檢查統計信息、獲取訪問對象結構信息到檢查SQL語句編寫等問題，如以手工方式做優化分析，需要資深DBA優化專家才能熟練完成，且耗時耗力，下麵我們看看通過DPM工具快速發現問題並解決的案例。

整體實例性能診斷

通過數據庫內部核心專業算法，為每個數據庫構建一個單獨的指標繁忙度，通過這一個指標度量數據庫的健康情況，相比傳統數據庫監控數十個監控指標更為直觀，快速發現並跟蹤定位問題數據庫：

獲取和發現待優化SQL

1）第一種方法是通過DPM的慢SQL模塊消耗占比高SQL，可以從CPU時間、執行次數、IO讀、IO等待時間4個模塊匯總分析，通過消耗占比高的語句製定優化的SQL語句目標。

2）第二種方法是通過DPM的性能分析模塊，該模塊采集數據庫實時性能數據，構建數據庫性能運行趨勢圖，通過圖表的形式快速展示數據庫性能狀態，以及找到指定負載高時間段內消耗情況，點對點形式快速定位數據庫性能瓶頸，也可通過拖動中間的標尺查看對應時段的性能數據，來展示對應時段內的頂級活動事件、頂級活動SQL、頂級活動會話，選擇消耗百分比占比高的作為SQL優化目標。

獲取執行計劃

點擊“慢SQL”或“性能分析”模塊SQL_ID下鑽詳細對應SQL頁麵，直觀展示SQL語句文本、消耗情況、執行次數、曆史執行情況、執行計劃等：

訪問對象信息獲取

點擊“對象統計”獲取對應訪問結構涉及對象信息，若對應表或對象存在統計信息過舊問題（由“統計信息分析”模塊發現的統計信息過舊對象）則會自動標紅。

獲取優化建議

快速定位SQL性能原因，對於問題SQL提供一鍵式優化建議和解決方案，並評估解決方案的提升率，下圖為平台優化結果示例：

整體而言，我們提供的是企業級端到端的應用性能管理整體解決方案，擁有國內第一個真正實現了從應用層、中間件到數據庫事務級的自動化識別和自動關聯分析的APM應用性能管理平台，DMP既可以作為APM一個模塊，也可以獨立部署，作為數據庫性能管理工具使用。與此同時，APM、DPM也可以與我們的AMP自動化運維產品和Ivory大數據日誌分析平台深度集成，提供敏捷運維“產品+定製+服務”的企業級交付能力。

Q1：OraZ目前隻支持Oracle？

A1：是的，目前還隻支持Oracle。

Q2：目前支持的數據庫版本？

A2：除Oracle外還支持MySQL、DB2。其中，Oracle需要10g版本以上。

Q3：這些圖標可以下鑽到什麼程度？

A3：點擊sql_id後可以下鑽獲取SQL語句文本、消耗情況、執行次數、曆史執行情況、執行計劃，進一步可以切換查看訪問相關的統計信息和優化建議。

Q4：DPM模塊是內嵌在OraZ裏麵的嗎？

A4：DPM是全新開發的，與OraZ是獨立的，而且OraZ工具應該是麵向社區的工具，DPM是平台級，B/S架構的。

Q5：是否提供下載試用？上麵提到的，逐層鑽取，分析，對於一線DBA還是很有用的。

A5：暫未發出試用版，如需試用可以聯係我，可安排poc。

Q6：DPM指的是什麼？

A6：DPM是數據庫性能分析平台的簡稱。

Q7：新的補丁出來多久打合適？

A7：按Oracle官方工具Orachk檢查會是最近一個季度版本，具體可以從mos下載最新的檢測看報告結論。

Q8：對比SQL monitor，OraZ與DPM在SQL優化方麵有哪些優勢或者便利？

A8：SQL monitor更多是針對SQL實時監控，超過5秒的cpu/io時間就會被記錄，並分析，具體差別比較大，而OraZ是對全庫的ash數據采集分析，OraZ與DPM相似，最大區別是麵向個人和企業級。

原文發布時間為：2016-12-29

本文來自雲棲社區合作夥伴DBAplus

最後更新：2017-05-13 08:42:51

  上一篇：  選擇H5響應式網站建設的主要原因

  下一篇：  DBA呢？我的數據庫又雙叒叕連不上了！