811
技術社區[雲棲]
【雲和恩墨大講堂】SQL玩轉AWR裸數據
編輯手記：Oracle線上嘉年華第四講，SQL玩轉AWR裸數據，教你真正利用AWR中的裸數據對係統性能進行分析並調優。
作者簡介：
羅海雄
雲和恩墨優化專家
ITPUB論壇數據庫管理版版主，2012 ITPUB全國SQL大賽冠軍得主，他還是資深的架構師和性能優化專家，對 SQL 優化和理解尤其深入；從開發到性能管理，他有著超過10年的企業級係統設計和優化經驗。曾經服務於甲骨文公司，組織和主講過多次《甲骨文技術開發人員日》和《Oracle圓桌會議》，並具備豐富的製造行業係統架構經驗。
今天的主題是SQL玩轉AWR裸數據。
AWR相信DBA們都不陌生。Automatic Workload Repository，自動負載信息庫，就是Oracle把數據庫中比較重要的性能視圖裏的信息，定期從內存保存到數據庫裏麵。默認情況下，Oracle 會每個一個小時保存一次。另外，Oracle也有機製保證信息庫的大小不至於無限增長，所以一般信息庫隻保留7天的數據。默認是保存在SYSAUX表空間中。主要的信息涵蓋了Oracle較重要的性能相關信息。
底層存儲為WRH$* 表， 通常可通過DBA_HIST_*訪問，總共有100多張表。
對於AWR, 常規的用法是生成AWR或者ASH報告。
下麵列舉了幾個最常見的方法。
@?/rdbms/admin/awrrpt.sql -- 標準報告，特定時間段內總體性能報告
@?/rdbms/admin/awrddrpt.sql -- 對比報告，兩個時間段內性能對比
@?/rdbms/admin/ashrpt.sql -- ASH報告，特定時間段內曆史會話性能報告
@?/rdbms/admin/awrsqrpt.sql -- SQL報告，特定時間段內SQL性能報告
AWR/ASH報告很不錯，但也有一些缺陷。
-
首先，AWR反應的是點對點的數據。比如說，我生成一個今天9:00到12:00的AWR報告，那麼，我看到的，就是12:00和9:00兩個時間點的變化。但是，9:00-10:00, 10:00-11:00,11:-12:00 分別是什麼樣的，我們看不到。
-
另外一個問題，AWR把數據都羅列出來，但卻缺乏數據間的聯係.
-
AWR混入大量無用數據, 導致生成AWR報告需要30秒到幾分鍾的時間，所以，如果我們有裸數據，其實可以更高效，更深入的挖掘Oracle數據庫的性能信息。在正式接觸裸數據前，我們需要先了解AWR在數據庫裏存的是什麼樣的數據。
在裸數據裏麵，記錄的各種指標主要有4類
最多的一種是"累計值"舉個例子 dba_hist_sysstat 裏會記錄數據庫的邏輯讀。記錄的不是這一個小時產生的邏輯讀，而是從數據庫啟動到產生快照的時候的總的邏輯讀。這就叫累計值，大多數的指標的是累計值。
也有部分數據記錄的是"當前值"
比如說，數據庫當前的PGA使用量，數據庫的會話數等，還有比較特殊的，會記錄兩次快照之間的變化值。我們可以認為，這是一種預計算，最常見的記錄變化值的兩類數據，分別是SQL相關統計信息，以及段(segment)相關統計信息，當然，SQL/Segment記錄變化值的同時，也記錄了累計值。
還有一類，記錄的是”統計值“
就是把一段時間內的數據，做了統計之後保存了起來，這些主要是METRIC類的數據。比如說，每秒CPU, 每秒最大等待時間等。
對於DBA來說，最關心的一般是變化值，
兩次快照之間的變化量。這是一個簡單的SQL, 獲取數據庫的曆史性能信息裏的redo size 信息
select SNAP_ID,STAT_NAME,VALUE from DBA_HIST_SYSSTAT
where STAT_NAME=‘redo size’ order by snap_id;
我們現在看到的，就是累計值。那麼，怎麼方便的獲取變化值呢？
1、要取得變化值，需要取出後麵的記錄，減去前麵的記錄。
如果僅僅是兩個時間點，最簡單的方法就是訪問這個表兩次，然後相減。
select a.value - b.value from DBA_HIST_SYSSTAT A,DBA_HIST_SYSSTAT B
where A.STAT_NAME=‘redo size’and A.STAT_NAME = B.STAT_NAME and a.snap_id = 123 and b.snap_id = 122
這樣得到是兩個點之間的差值，但是對我們來說，玩玩是不夠的。
2、有時候，我們希望得到一個時間段內，每兩個連續快照之間的變化值。比如說，9:00-21:00, 我們希望獲得 9:00-10:00, 10:-11:00... 20:00-21:00, 每個時間段分別的變化值。
這裏就涉及到Oracle的分析函數了分析函數
Oracle的分析函數提供了在一個結果集內，跨行訪問數據的能力。分析函數裏麵的LEAD/LAG正是跨行獲取數據的利器
LAG : 同一組內，排在當前行之前的數據
LEAD : 同一組內，排在當前行之後的數據
如圖所示，可以看到，我們要的是拿當前value 減去 lag value。
select snap_id,stat_name,
value-lag(value) over
(partition by stat_name order by snap_id)
from dba_hist_sysstat
where stat_name = 'redo size'
order by snap_id;
這就是分析函數LAG的完整語法。
3、我們一般不會滿足獲取一個指標的變化值的，下麵的表，才是我們希望獲得的。
這裏又引入了進階SQL的另一個寫法：行列轉換。
具體我就不細講了，大家可以體會一下，如何使用sum(case when .. then .. end )或者max(case when .. then .. end )的形式的形式來進行行列轉換
，但用Case when來寫行列轉換，很容易使SQL冗長，而且容易出錯。
Oracle 11g中，提供了更方便的方式進行行列轉換
大家可以看到，標黃大寫的PIVOT， 正是Oracle 11g中引入的行列轉換利器。使用PIVOT, 增減指標極其簡單：
很輕鬆就加了兩個指標，如果覺得列名不好看，也可以自己指定。
其實，我們可以很輕鬆的就把AWR報告中的"Load Profile"部分通過行列轉換給取出來，而且，是多個連續變化的值。
把跑的結果拷到Excel, 很容易就出來一個漂亮的趨勢圖。
但是，這個圖是有問題的：圖裏的REDO Size是以byte為單位的，值太大，把別的指標統統壓到和0差不多，多個指標要到同一個圖，還能看出各自的趨勢，對於多指標關聯的分析很有作用。
這時候，又有一個分析函數出來了。沒錯，因為我們是在對Oracle的性能數據進行分析，所以，需要大量的使用”分析函數“
分析函數： Ratio_To_Report 求當前行數據在所有同組數據內占的比例。比如說，我的結果集裏有3行，分別是1,3,6. 那麼1對應的那一行，占總數據(1+3+6)的10%, 出來的結果就是0.1（10%）.
select * from (
select snaptime,RATIO_TO_REPORT(value) over(partition by stat_name) value,stat_name,snap_id
from (… )) PIVOT (sum(value) for stat_name in (
…))order by snap_id;
在這個圖裏麵，大家就都平等了，也更方便的去看各個指標之間是否存在關聯
再給大家看另一個SQL, 還是ratio_to_report, 這次，我們拿到的結果，其實是AWR報告裏另一個非常重要的數據:Top Timed Events
我把每個時間段的CPU時間和非空閑事件給放在一起，然後計算每個事件(含CPU)在每個時間段占的百分比，就得到 Top Timed Events，而且是連續的多個時間的數據。
今天的分享到此結束，更多精彩，盡在Oracle線上嘉年華。敬請關注！
文章轉自數據和雲公眾號，原文鏈接
最後更新：2017-07-18 20:36:27