【技術(shù)開發(fā) 評(píng)論】

    編者按：工欲善其事，比先利其器

    4月2日-3日，中國首屆數(shù)據(jù)庫技術(shù)大會(huì)在北京盛大召開。在這一面向數(shù)據(jù)庫及商業(yè)智能技術(shù)專業(yè)人群的技術(shù)盛會(huì)上，各路DBA、數(shù)據(jù)庫開發(fā)，商業(yè)智能技術(shù)高手云集，分享各自在數(shù)據(jù)庫技術(shù)領(lǐng)域深入研究的體會(huì)和最佳實(shí)踐。

    在4月3日上午進(jìn)行的“Oracle實(shí)踐案例應(yīng)用專場”演講中，來自于ITPUB社區(qū)的資深版主李軼楠給大家分享了題為“Oracle優(yōu)化工具中的七種武器”的演講。李軼楠在社區(qū)中的賬號(hào)為Ora-600。他擅長Oracle DBA的相關(guān)技能，有豐富的Oracle架構(gòu)設(shè)計(jì)、實(shí)施部署、故障診斷、性能優(yōu)目前已獲得8i/9i/10g OCP認(rèn)證，同時(shí)還得到了Oracle官方授予的Oracle ACE稱號(hào)。

ITPUB社區(qū)的資深版主李軼楠 講座現(xiàn)場

    當(dāng)前，為滿足業(yè)務(wù)的運(yùn)行要求，高性能要求是目前IT系統(tǒng)普遍面臨的最棘手問題，尤其是客戶面對(duì)著目前越來越龐大系統(tǒng)和數(shù)據(jù)，系統(tǒng)整合、數(shù)據(jù)大集中似乎成了趨勢，而對(duì)我們來說，則充滿了壓力和挑戰(zhàn)。
   
    李軼楠在演講中談到，在此背景下，數(shù)據(jù)庫性能優(yōu)化面臨諸多挑戰(zhàn)，包括：架構(gòu)和業(yè)務(wù)的設(shè)計(jì)與變更；熟悉各種數(shù)據(jù)庫參數(shù)、系統(tǒng)參數(shù)；應(yīng)用邏輯與SQL代碼實(shí)現(xiàn)；如何選擇合適的存儲(chǔ)方式；存儲(chǔ)盤陣、存儲(chǔ)模式、存儲(chǔ)參數(shù)、存儲(chǔ)表空間、存儲(chǔ)對(duì)象等；面對(duì)復(fù)雜的網(wǎng)絡(luò)配置等等諸多方面。

    同時(shí)，他指出，Oracle數(shù)據(jù)庫性能調(diào)優(yōu)工具也在不斷變遷，從8i中的快速進(jìn)化，到9i中的逐漸完善（更精細(xì)的收集 - Session tuning using 10046 SQL traces；更加全面的STATSPACK；智能化、自動(dòng)化開始初現(xiàn)）；到10g中的日趨完美――基于數(shù)據(jù)庫自動(dòng)化基礎(chǔ)的更完美的優(yōu)化（包括自動(dòng)化收集；更加廣泛的收集；保留一段時(shí)間的歷史；提供了：ASH, AWR, ADDM, EM等功能調(diào)用；形成了越來越完善的性能優(yōu)化診斷工具）

講座現(xiàn)場，聽眾提問互動(dòng)

 

    七種調(diào)整數(shù)據(jù)庫性能的必殺武器

    武器一：碧玉刀 — 動(dòng)態(tài)性能視圖

    刀是最大眾化的武器，小到刮刀、折刀、剃刀、西瓜刀、切菜刀、剔骨刀，大到柳葉刀、圓月彎刀、武士刀、青龍偃月刀。。。不論大小長短、不論古今中外，刀是最常見的武器。不過再普通的刀，到了高手的手中，也會(huì)成為神兵利器，刀如此，Oracle的動(dòng)態(tài)性能視圖也如此，無論各種性能問題，根源皆可尋究于此。

    這些試圖包括：V$SYSSTAT   V$SESSION   V$SESSTAT   V$SGASTAT

    V$FILESTAT   V$UNDOSTAT   V$ROLLSTAT  V$WAITSTAT ；V$LOCK   V$LATCH   V$SQL   V$SQLAREA   V$SQLTEXT；V$PROCESS ；V$LIBRARYCACHE； V$ROWCACHE等等。

    李軼楠談到，不是每個(gè)問題，都那么清楚的擺在我們面前，細(xì)致的察看，仔細(xì)的分析，利器才是利器 

 

    武器二：長生劍 — 等待事件

    劍，輕靈、快速、靈敏，甚至于詭異。談笑間，輕松快意時(shí)尋出敵人弱點(diǎn)，以閃電般的速度刺入敵人最虛弱的部位，一擊破敵。
性能優(yōu)化的核心是什么，快速準(zhǔn)確的定位，不需要華麗的顯示，不需要冗長的信息，需要的僅僅是一個(gè)準(zhǔn)確的定位，等待事件就是此中利器。

    競爭即等待，關(guān)注等待事件：v$system_event / v$session_event / v$session_wait 有助于我們發(fā)現(xiàn)系統(tǒng)的性能瓶頸。比如：
從v$system_event中，我們能發(fā)現(xiàn)系統(tǒng)問題；從v$session_event中發(fā)現(xiàn)會(huì)話問題； 從v$session_wait的參數(shù)中可以找到競爭對(duì)象。

    熟悉各種主要的等待事件,快速定位問題所在 ，如圖

    常用等待事件：

    • buffer busy waits(數(shù)據(jù)高速緩存忙等待)

    • db file scattered read(數(shù)據(jù)文件離散讀取)

    • db file sequential read(數(shù)據(jù)文件順序讀)

    • direct path read(直接路徑讀取)

    • direct path write(直接路徑寫出)

    • enqueue(隊(duì)列)

    • free buffer waits(空閑緩沖區(qū)等待)

    • latch free(鎖存器空閑)

    • log buffer space(日志緩沖區(qū)空間分配)

    • log file switch(archiving needed)

    • log file switch(checkpoint incomplete)

    • log file sync(日志文件同步)


 

    武器三：霸王槍 — statspack

    槍中之霸王，膽氣之結(jié)晶。槍具有劍的輕靈，又有棍的霸道，不論是快速定位，還是全面分析，都是statspack所能勝任的。有了全面的信息收集，分析問題變得簡單了。

    Statspack輸出包含豐富的信息，如數(shù)據(jù)庫和實(shí)例名稱；獲取快照的時(shí)間；當(dāng)前高速緩存的大?。回?fù)載概覽；實(shí)例效率百分比；前五個(gè)等待事件；等待事件的完整列表；共享池中SQL語句的信息；實(shí)例活動(dòng)統(tǒng)計(jì)；表空間和文件I/O；緩沖區(qū)統(tǒng)計(jì)信息；回退段或還原段統(tǒng)計(jì)信息；字典高速緩存統(tǒng)計(jì)信息；庫高速緩存統(tǒng)計(jì)；SGA統(tǒng)計(jì)；Init.ora參數(shù)的啟動(dòng)值等。

    Statspack內(nèi)容：

 

    武器四：孔雀翎 — ash、awr、addm、addr

    是一種暗器，但又不是暗器。悄然，自動(dòng)，不動(dòng)聲色間，一切皆在握。Oracle在10g開始，推出了一系列自動(dòng)化、智能化的工具，雖然這些工具在以前或多或少都有相似的影子，但功能的增強(qiáng)、理念的增強(qiáng)，造就了這些以前所不具備的新工具。 

    Active Session History（ASH）-活動(dòng)會(huì)話歷史，每秒鐘自動(dòng)從內(nèi)存中抓取樣例的活動(dòng)會(huì)話信息，比如，通過以下語句： 

    select a.sql_text from v$sql a

    where sql_id in (select sql_id from v$active_session_history where session_id=157);

    • 信息直接從內(nèi)存結(jié)構(gòu)中獲取，并不保存，僅在系統(tǒng)運(yùn)行中有效 

    • 可以得到 

    • SID 

    • SQL ID

    • Program

    • Wait event# 

    • Object, File, Block

    • actual wait time (if captured while waiting) 

    通過ashrpt.sql可以產(chǎn)生ash分析報(bào)告，發(fā)現(xiàn)某個(gè)時(shí)段的TOP (Top Events/ Top SQL/ Top Sessions/ Top Objects/Files/Latches)
通過活動(dòng)會(huì)話歷史信息，可以追溯到性能問題的根源，找出什么資源在競爭？向下追溯到哪個(gè)程序帶來了競爭？以及哪個(gè)SQL帶來了競爭？

    用戶可以找出活動(dòng)會(huì)話信息中的TOP，如通過TOP SQL可以進(jìn)一步發(fā)現(xiàn)問題。

    Automatic Workload Repository-自動(dòng)負(fù)載信息庫 (AWR)

    • 10g的數(shù)據(jù)庫中內(nèi)置了工作負(fù)載信息庫

    • AWR是Oracle10g數(shù)據(jù)庫自動(dòng)化管理的基礎(chǔ)架構(gòu)

    • 自動(dòng)捕獲工作負(fù)載數(shù)據(jù)

    • 默認(rèn)情況下，每隔  60 分鐘保存一次，或者手動(dòng)保存 7 天的數(shù)據(jù)

    • 存儲(chǔ)在新SYSAUX 表空間內(nèi)

    • 服務(wù)器自動(dòng)管理空間要求

    • 自動(dòng)清除舊數(shù)據(jù)

    • 存儲(chǔ)不同類別的數(shù)據(jù)：

    • 基本統(tǒng)計(jì)，例如物理讀取

    • SQL 統(tǒng)計(jì)，例如磁盤讀?。總€(gè) sql 語句）

    • 量度，例如，物理讀取數(shù)量/秒

    • 通過awrrpt.sql可以產(chǎn)生與statspack類似的性能差異報(bào)告

    Automatic Database Diagnostic Monitor -自動(dòng)數(shù)據(jù)庫診斷監(jiān)控(ADDM) ：

    • AWR收集完信息后自動(dòng)調(diào)用，為數(shù)據(jù)庫提供性能診斷分析報(bào)告 

    • 分析依賴于AWR收集的性能信息快照，對(duì)比兩次收集快照的性能差異，提供分析建議 

    • 由Oracle自動(dòng)調(diào)用，也可以手動(dòng)調(diào)用 

    • 可以分析當(dāng)前的，最近一次收集的，也可以分析之前還存在的AWR快照

    • 對(duì)RAC 架構(gòu)同樣適用 

    • 分析結(jié)果在數(shù)據(jù)庫的相應(yīng)字典表中存儲(chǔ)，可以通過dbms_advisor包的get_task_report過程來獲取已經(jīng)分析的結(jié)果，也可以通過addmrpt.sql腳本對(duì)特定的快照進(jìn)行分析。

    Automatic Database difference Report - AWR數(shù)據(jù)對(duì)比報(bào)告(ADDR)：

    •  對(duì)AWR報(bào)告的一種補(bǔ)充

    • 基于基線的理念，對(duì)比比單純的報(bào)告更能夠說明問題

    • 比基線更靈活，產(chǎn)生報(bào)告時(shí)隨意選擇對(duì)比基線

    • 通過awrddrpt.sql可以獲取性能異常時(shí)間與正常時(shí)間段AWR報(bào)告的對(duì)比值，能夠快速發(fā)現(xiàn)性能差異，從而定位問題 

    性能調(diào)優(yōu)工具關(guān)聯(lián)圖示：

 

    武器五多情環(huán) — sql tuning advisor/sql access advisor

    多情環(huán)似乎是一個(gè)情種，誰擁有它似乎都會(huì)產(chǎn)生感情，從而對(duì)許多江湖中的事看的很淡。在Oracle應(yīng)用中，誰對(duì)性能影響最大，不言而喻，是SQL，準(zhǔn)確地說是SQL語句的算法，可以說，80%以上的性能問題都可以通過調(diào)整SQL來解決或者緩解，擁有調(diào)優(yōu)SQL性能的能力，基本上可以算作一個(gè)DBA高手咯。

    SQL 調(diào)整以前的手段包括：檢查系統(tǒng)使用情況；查看等待事件；查看數(shù)據(jù)庫分散讀取上的等待事件；通過以下方法識(shí)別 SQL（難以操作）；識(shí)別具有大量數(shù)據(jù)庫分散讀取等待事件的會(huì)話并跟蹤它們，或者在 OEM 中查看最突出的會(huì)話；獲得解釋計(jì)劃；檢查被訪問的對(duì)象（大小/基數(shù)）；查看 SQL 統(tǒng)計(jì)信息和/或與對(duì)象統(tǒng)計(jì)信息相比較 (v$sql) （難以操作）；識(shí)別問題；聯(lián)系打包應(yīng)用程序的供應(yīng)商；為供應(yīng)商提供測試方案；供應(yīng)商提供補(bǔ)丁/升級(jí)；安裝在客戶的下一個(gè)維護(hù)周期中的補(bǔ)丁/升級(jí)；

    在Oracle 10g以后，我們可以：

    通過查看 ADDM 建議；或者根據(jù)鏈接來運(yùn)行自動(dòng) SQL 調(diào)整；還可以接受來自 SQL 調(diào)整的 SQL 描述文件建議。

    SQL Tuning & Access Advisors能夠?qū)ο到y(tǒng)中的SQL語句提供優(yōu)化指導(dǎo) ，從多個(gè)不同的方向?yàn)镾QL提供優(yōu)化建議，建議包括了：統(tǒng)計(jì)信息的重新收集，創(chuàng)建/刪除索引，創(chuàng)建/刪除物化視圖，是否需要物化視圖日志，SQL語句的書寫以及固化執(zhí)行計(jì)劃的SQL Profiling；通過存儲(chǔ)在Oracle內(nèi)部的SQL Profiling 能夠在不改變SQL代碼的基礎(chǔ)上強(qiáng)制執(zhí)行計(jì)劃。


 

    武器六離別鉤 —提示(hints)

    Oracle很強(qiáng)大的工具，優(yōu)化SQL的利器，能夠強(qiáng)制SQL的執(zhí)行算法，確保SQL執(zhí)行按照我們希望的執(zhí)行計(jì)劃。鉤，用的好傷人，用不好傷己，hints也如此。非高手者，非思路清晰者，且忌亂用，用不好的話，你會(huì)很受傷的。。。

    一些典型的hints：首選用于測試執(zhí)行計(jì)劃；其次可用于在需求確定時(shí)，固化執(zhí)行計(jì)劃

    常用的hints：

    • FIRST_ROWS, ALL_ROWS ,RULE

    • FULL(tab) 

    • INDEX( tab index ) 

    • NO_INDEX ( tab index ) 

    • USE_NL(tab)

    • USE_MERGE(tab..) 

    • USE_HASH(tab1 tab2)

    • PARALLEL ( table, <degree> [, <instances>] )

 

    武器七：拳頭

    沒有武器就是有武器，有武器就是沒有武器。最后一種武器-拳頭，就是對(duì)整個(gè)體系的全面理解，無形的武器勝于有形的武器，就像太極，沒有招數(shù)就是最好的招數(shù)。

    作為一個(gè)DBA，或者更高一些，作為一個(gè)架構(gòu)管理員，能夠理解整個(gè)業(yè)務(wù)系統(tǒng)，對(duì)數(shù)據(jù)庫、存儲(chǔ)、網(wǎng)絡(luò)、系統(tǒng)、應(yīng)用軟件、業(yè)務(wù)流程都非常清楚，甚至于對(duì)使用者的使用習(xí)慣都非常清楚，優(yōu)化就不再是什么高難度了。天地之大皆裝于我胸中，萬物皆為我之神兵。如果真有那么一天一切都在你的掌握之中，優(yōu)化也許會(huì)比吃飯更容易一些咯

    最后，他總結(jié)道：優(yōu)化的工具有千千萬，找到適合的最關(guān)鍵；精通一、兩個(gè)工具，比什么工具都“會(huì)”使更有用；工具就是工具，最終優(yōu)化人來定；工具是可以換的，人“才”是換不來的；優(yōu)化應(yīng)該在系統(tǒng)中整體貫穿，需要我們用優(yōu)化工具的時(shí)候似乎已經(jīng)有點(diǎn)晚。