羅海雄：僅僅使用AWR做報(bào)告? 性能優(yōu)化還未入門（含PPT）

AWR/ASH報(bào)告很不錯(cuò)，但也有一些缺陷。

• 首先，AWR反應(yīng)的是點(diǎn)對(duì)點(diǎn)的數(shù)據(jù)。 比如說(shuō)，我生成一個(gè)今天9:00到12:00的AWR報(bào)告，那么，我看到的，就是12:00和9:00兩個(gè)時(shí)間點(diǎn)的變化。但是，9:00-10:00, 10:00-11:00,11:-12:00 分別是什么樣的，我們看不到。

• 另外一個(gè)問(wèn)題，AWR把數(shù)據(jù)都羅列出來(lái)，但卻缺乏數(shù)據(jù)間的聯(lián)系.

• AWR混入大量無(wú)用數(shù)據(jù) , 導(dǎo)致生成AWR報(bào)告需要30秒到幾分鐘的時(shí)間，所以，如果我們有裸數(shù)據(jù)，其實(shí)可以更高效，更深入的挖掘Oracle數(shù)據(jù)庫(kù)的性能信息。

在裸數(shù)據(jù)里面，記錄的各種指標(biāo)主要有4類

select SNAP_ID,STAT_NAME,VALUE from DBA_HIST_SYSSTAT

where STAT_NAME=‘redo size’ order by snap_id;

我們現(xiàn)在看到的，就是累計(jì)值。那么，怎么方便的獲取變化值呢？

1、 要取得變化值 ，需要取出后面的記錄，減去前面的記錄。

如果僅僅是兩個(gè)時(shí)間點(diǎn)，最簡(jiǎn)單的方法就是訪問(wèn)這個(gè)表兩次，然后相減。 

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

這樣得到是兩個(gè)點(diǎn)之間的差值，但是對(duì)我們來(lái)說(shuō)，玩玩是不夠的。

2、有時(shí)候，我們希望得到一個(gè)時(shí)間段內(nèi)，每?jī)蓚€(gè)連續(xù)快照之間的 變化值 。比如說(shuō)，9:00-21:00, 我們希望獲得 9:00-10:00, 10:-11:00... 20:00-21:00, 每個(gè)時(shí)間段分別的變化值。

這里就涉及到Oracle的分析函數(shù)了 分析函數(shù)

如圖所示，可以看到，我們要的是拿當(dāng)前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;

這就是分析函數(shù)LAG的完整語(yǔ)法。

3、 我們一般不會(huì)滿足獲取一個(gè)指標(biāo)的變化值的，下面的表，才是我們希望獲得的。

這里又引入了進(jìn)階SQL的另一個(gè)寫法 ：行列轉(zhuǎn)換。

大家可以體會(huì)一下，如何使用sum(case when .. then .. end )或者max(case when .. then .. end )的形式的形式來(lái)進(jìn)行行列轉(zhuǎn)換 ，但用Case when來(lái)寫行列轉(zhuǎn)換，很容易使SQL冗長(zhǎng)，而且容易出錯(cuò)。

Oracle 11g中，提供了更方便的方式進(jìn)行行列轉(zhuǎn)換

大家可以看到，標(biāo)黃大寫的PIVOT， 正是Oracle 11g中引入的行列轉(zhuǎn)換利器。 使用PIVOT, 增減指標(biāo)極其簡(jiǎn)單：

很輕松就加了兩個(gè)指標(biāo)，如果覺(jué)得列名不好看，也可以自己指定。

其實(shí)，我們可以很輕松的就把AWR報(bào)告中的"Load Profile"部分通過(guò)行列轉(zhuǎn)換給取出來(lái)，而且，是多個(gè)連續(xù)變化的值。

把跑的結(jié)果拷到Excel, 很容易就出來(lái)一個(gè)漂亮的趨勢(shì)圖。

但是，這個(gè)圖是有問(wèn)題的： 圖里的REDO Size是以byte為單位的，值太大，把別的指標(biāo)統(tǒng)統(tǒng)壓到和0差不多，多個(gè)指標(biāo)要到同一個(gè)圖，還能看出各自的趨勢(shì)，對(duì)于多指標(biāo)關(guān)聯(lián)的分析很有作用。

這時(shí)候，又有一個(gè)分析函數(shù)出來(lái)了。沒(méi)錯(cuò)，因?yàn)槲覀兪窃趯?duì)Oracle的性能數(shù)據(jù)進(jìn)行分析，所以，需要大量的使用”分析函數(shù)“

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;

在這個(gè)圖里面，大家就都平等了，也更方便的去看各個(gè)指標(biāo)之間是否存在關(guān)聯(lián)

再給大家看另一個(gè)SQL, 還是ratio_to_report, 這次，我們拿到的結(jié)果，其實(shí)是AWR報(bào)告里另一個(gè)非常重要的數(shù)據(jù): Top Timed Events

我把每個(gè)時(shí)間段的CPU時(shí)間和非空閑事件給放在一起，然后計(jì)算每個(gè)事件(含CPU)在每個(gè)時(shí)間段占的百分比，就得到 Top Timed Events，而且是連續(xù)的多個(gè)時(shí)間的數(shù)據(jù)。

在看AWR時(shí)，有幾個(gè)區(qū)域是必看的。

• 第一個(gè)是LOAD PROFILE.

參考前面用來(lái)演示lag() 函數(shù)的部分：

select * from (select snap_id,STAT_NAME, 

value-lag(value) over(partition by STAT_NAME 

order by snap_id) value 

 from dba_hist_sysstat where stat_name in (

‘redo size’,‘execute count’,‘DB time’,‘physical reads‘

) ) PIVOT (sum(value) for stat_name in (

'redo size','execute count','DB time','physical reads‘

))order by snap_id;

把stat_name里面的部分，加上LOAD PROFILE的其他指標(biāo)，就是個(gè)完整的load profile了。

通過(guò)load profile, 大家可以對(duì)系統(tǒng)的總體負(fù)載有個(gè)準(zhǔn)確的認(rèn)識(shí)。

• 第二個(gè)部分，是Top timed events, 最耗時(shí)間的等待事件（包括CPU)的部分。

通過(guò)這個(gè)部分，大家可以了解整個(gè)系統(tǒng)的性能瓶頸:

select snap_id,event,pct||'%' PCT,time from (

select snap_id,event,round(time)time,

round(RATIO_TO_REPORT(TIME) over(partition by snap_id)*100,1) pct

from( select 'CPU Time' EVENT,snap_id,value/100 - LAG(value)over(partition by stat_name order by snap_id)/100 TIME

from DBA_HIST_SYSSTAT where stat_name = 'CPU used by this session'

union all select event_name,snap_id, time_waited_micro/1e6 - 

LAG(time_waited_micro) over(partition by event_name order by snap_id)/1e6 

from DBA_HIST_SYSTEM_EVENT where wait_class!='Idle'

)where time>0) where pct>1 order by snap_id,time desc

• 通常來(lái)說(shuō)，知道了系統(tǒng)負(fù)載，系統(tǒng)瓶頸，我們還需要了解的是第三個(gè)部分： Top SQL
  

通過(guò)Top SQL, 我們可以了解系統(tǒng)運(yùn)行過(guò)哪些主要的語(yǔ)句。

但是，傳統(tǒng)的AWR報(bào)告中的Top SQL是有缺陷的。最主要的問(wèn)題，它的信息是分散的。

在對(duì)SQL進(jìn)行判斷時(shí)，我會(huì)結(jié)合多個(gè)指標(biāo)。執(zhí)行時(shí)間(elapsed Time)、CPU時(shí)間(CPU Time)、邏輯讀(Buffer gets)、物理讀(disk reads)、執(zhí)行次數(shù)（executions）、返回行數(shù)（rows_processed）,但是，傳統(tǒng)的awr報(bào)告，這些指標(biāo)分布在不同位置?？雌饋?lái)很不方便。 比如說(shuō)這個(gè)，有執(zhí)行時(shí)間，執(zhí)行次數(shù)，CPU時(shí)間。但缺乏邏輯讀，物理讀，返回行數(shù)等，有時(shí)候，還得專門去找。

所以呢，我經(jīng)常訪問(wèn)裸數(shù)據(jù)，使用SQL, 直接從數(shù)據(jù)庫(kù)里取出包含完整信息的Top SQL.

另外，根據(jù)不同的情況，我們可能關(guān)心的點(diǎn)也不一樣。 比如說(shuō)，系統(tǒng)CPU消耗嚴(yán)重，我們更關(guān)心SQL order by CPU, I/O嚴(yán)重時(shí)，關(guān)心的則是物理讀。所以我用的SQL, 可以支持同時(shí)取出按不同指標(biāo)的排序的Top N SQL.

比如說(shuō)， Top 10 by elapsed time, Top 10 by CPU, Top 10 by disk reads.

大家都知道，傳統(tǒng)的order by + rownum < N 的方式只支持對(duì)其中一個(gè)指標(biāo)進(jìn)行排名，顯然是不夠的。而分析函數(shù)，又再次發(fā)揮了作用。

select sql.*, (select SQL_TEXT from dba_hist_sqltext t

 where t.sql_id = sql.sql_id and rownum=1 ) SQLTEXT

from (select a.* ,

RANK() over( order by els desc) as r_els, 

RANK() over( order by phy desc) as r_phy, 

RANK() over( order by get desc) as r_get, 

RANK() over( order by exe desc) as r_exe, 

RANK() over( order by CPU desc) as r_cpu

 from (

 select sql_id,sum(executions_delta) exe,round(sum(elapsed_time_delta ) / 1e6, 2) els

,round(sum(cpu_time_delta) / 1e6, 2) cpu,

round(sum(iowait_delta) / 1e6, 2) iow,sum(buffer_gets_delta) get,

sum(disk_reads_delta) phy,sum(rows_processed_delta) RWO,

round(sum(elapsed_time_delta) / greatest(sum(executions_delta), 1) / 1e6,4) elsp,

round(sum(cpu_time_delta) / greatest(sum(executions_delta), 1) / 1e6, 4) cpup,

round(sum(iowait_delta) / greatest(sum(executions_delta), 1) / 1e6, 4) iowp,

round(sum(buffer_gets_delta) / greatest(sum(executions_delta), 1), 2) getp,

round(sum(disk_reads_delta) / greatest(sum(executions_delta), 1), 2) phyp,

round(sum(rows_processed_delta) / greatest(sum(executions_delta), 1), 2) ROWP

from dba_hist_sqlstat s

--where snap_id between ... and ...

group by sql_id  ) a

)SQL where r_els <= 10 or r_phy <=10 or r_cpu<=10 order by els desc

大家可以看到，這里面用到了 RANK() 函數(shù)。這個(gè)函數(shù)可以得出根據(jù)某個(gè)指標(biāo)排序的排名。然后再通過(guò)最后的 r_els <= 10 or r_phy <=10 or r_cpu<=10 的過(guò)濾條件，就可以獲取按照多個(gè)指標(biāo)排序的Top N了。

有時(shí)候，我會(huì)把這個(gè)結(jié)果想辦法做成HTML, 就變成這個(gè)效果了。

在分析SQL中，還有很重要的信息。

第一個(gè)是執(zhí)行計(jì)劃。

select * from table(dbms_xplan.display_awr('&SQLID'))

除了執(zhí)行計(jì)劃，還有一個(gè)信息不可或缺，就是綁定變量。

我碰到的SQL問(wèn)題里面，有一個(gè)典型分類，就是SQL本來(lái)執(zhí)行好好的，突然變差。這時(shí)候，在分析時(shí)，需要很關(guān)注的，就是歷史綁定變量。Oracle在AWR裸數(shù)據(jù)中也保留了綁定變量:

DBA_HIST_SQLSTAT.BIND_DATA 這個(gè)欄位里面，保存了綁定變量

通過(guò)以下SQL, 可以獲取歷史綁定變量:

select snap_id,sq.sql_id,bm.position, dbms_sqltune.extract_bind(sq.bind_data,bm.position).value_string value_string 

from dba_hist_sqlstat sq ,dba_hist_sql_bind_metadata bm

where sq.sql_id = bm.sql_id --and sq.sql_id = '&sql'

出來(lái)的是行格式的，讀起來(lái)不方便。用PIVOT 做一個(gè)行列轉(zhuǎn)換就漂亮了。

select * from ( select snap_id, to_char(sn.begin_interval_time,'MM/DD-HH24:MI') snap_time, sq.sql_id,bm.position, dbms_sqltune.extract_bind(bind_data,bm.position).value_string value_string from dba_hist_snapshot sn natural join dba_hist_sqlstat sq ,dba_hist_sql_bind_metadata bm

where sq.sql_id = bm.sql_id and sq.sql_id = '&sql'

) PIVOT (max(value_string) for position in (1,2,3,4,5,6,7,8,9,10))

order by snap_id

完美的取出不同時(shí)間段的歷史綁定變量值.

對(duì)于“SQL本來(lái)執(zhí)行好好的，突然變差”的問(wèn)題，有一個(gè)比較簡(jiǎn)潔的解決方式，就是嘗試讓SQL走回以前的執(zhí)行計(jì)劃。

Select plan_hash_value ,Sum(Elapsed_time_Delta) /greatest(Sum(Executions_Delta),1),sum(Executions_Delta) From dba_hist_sqlstat where sql_id = '&SQLID' group by plan_hash_value ;

通過(guò)以上SQL, 可以快速獲取某個(gè)SQL多個(gè)執(zhí)行計(jì)劃的執(zhí)行效果。然后再想辦法應(yīng)用其執(zhí)行計(jì)劃，往往可以收到奇效。綁定執(zhí)行計(jì)劃的方法有多種，SPM/SQL Profile/SQL Patch等，具體我就不展開(kāi)了。

不知道大家有沒(méi)有碰到過(guò)這樣的情況, 有時(shí)候，明明性能瓶頸在SQL,但Top SQL中DB Time(%)指標(biāo)卻很低,前10個(gè)加起來(lái)也不足20%.

像這個(gè)AWR, Top SQL by elapsed Time才記錄了2%. 也就是說(shuō)，你只能看到2%的性能相關(guān)的SQL.

其中一個(gè)主要原因是由于Shared Pool大小限制以及非綁定變量問(wèn)題，導(dǎo)致SQL可能會(huì)被漏記, 這種情況下，怎么辦呢？

其實(shí)，有個(gè)地方不會(huì)被漏記。就是Top Segments. 通常，如果Top SQL中找不到太多信息，我們可以去看看Top Segments:

這是摘自同一個(gè)AWR的信息。 Top segments 告訴我們，對(duì)表的訪問(wèn)集中在前面3個(gè)，我們可以專注于這幾個(gè)表的問(wèn)題。

當(dāng)然， 同樣可以通過(guò)SQL直接訪問(wèn)裸數(shù)據(jù)獲取相關(guān)信息:

Select begin_interval_time,seg.snap_id,PHYSICAL_READS_DELTA, object_name,subobject_name 

from DBA_HIST_SEG_STAT SEG ,DBA_HIST_SEG_STAT_OBJ O , dba_hist_snapshot snap

where o.obj# = seg.obj# and o.dataobj# = seg.dataobj# and PHYSICAL_READS_DELTA > 1e5 and seg.snap_id = snap.snap_id

and begin_interval_time > sysdate - 4/24

order by PHYSICAL_READS_DELTA desc

這是一個(gè)常用的AWR裸數(shù)據(jù)的列表:

多數(shù)的AWR分析可以從這些裸數(shù)據(jù)開(kāi)始。Load Profile, Top Timed Event, Top SQL, SQL Plan, SQL 綁定變量, Top Segments,相關(guān)的SQL 陸陸續(xù)續(xù)都貼出來(lái)了.

AWR裸數(shù)據(jù)如此的重要，對(duì)于關(guān)心數(shù)據(jù)庫(kù)性能的DBA們，我們需要好好的保護(hù)好它們~

1. 系統(tǒng)保存時(shí)間,默認(rèn)7天遠(yuǎn)遠(yuǎn)不足，建議改到30天以上，跨過(guò)一個(gè)月結(jié)周期

2. 需要的時(shí)候，我們可以對(duì)裸數(shù)據(jù)進(jìn)行離線備份

@?/rdbms/admin/awrextr

3. 甚至，我們可以把裸數(shù)據(jù)專門找個(gè)數(shù)據(jù)庫(kù)存起來(lái)，作為一個(gè)資料庫(kù)使用。

@?/rdbms/admin/awrload

4. 有時(shí)候，也可以針對(duì)特定的表進(jìn)行備份。比如說(shuō)，我剛剛貼的這個(gè)列表

以上分享主要內(nèi)容是：

 1.  AWR的分析辦法：  Load Profile, Top Timed Event, Top SQL, SQL Plan, SQL 綁定變量,Top Segments

2.  一些高級(jí)SQL用法:  分析函數(shù) Lag/Rank/Ratio_to_report, 行列轉(zhuǎn)換 PIVOT