Oracle效能調整的三把利劍--ASH,AWR,ADDM

ASH (Active Session History)
ASH以V$SESSION為基礎，每秒取樣一次，記錄活動會話等待的事件。不活動的會話不會取樣，取樣工作由新引入的後臺程式MMNL來完成。
ASH buffers 的最小值為1MB,最大值不超過30MB。記憶體中記錄資料。期望值是記錄一小時的內容。

生成ASH報告：
SQLPLUS>@?/rdbms/ashrpt.sql

ASH記憶體記錄資料始終是有限的，為了儲存歷史資料，引入了自動負載資訊庫(Automatic Workload Repository ,AWR) 由後臺程式MMON完成。ASH資訊同樣被採集寫出到AWR負載庫中。由於記憶體不是足夠的，所以MMNL程式在ASH寫滿後會將資訊寫出到AWR負載庫中。ASH全部寫出是不可接受的，所以一般只寫入收集的10%的資料量，而且使用direct-path insert完成，儘量減少日誌的生成，從而最小化資料庫效能影響。

寫出到AWR負載庫的ASH資訊記錄在AWR的基礎表wrh$active_session_hist中，wrh$active_session_hist是一個分割槽表，Oracle會自動進行資料清理。

AWR(Automatic Workload Repository)自動工作負載資訊庫
AWR是Oracle 10g中的一個新特性，類似於10g以前的statspack。不過在使用上要比statspack簡單，提供的效能指標要比statspack多很多，能更好的幫助DBA來發現資料庫的效能瓶頸。
AWR是Oracle安裝好後自動啟動的，不需要特別的設定。收集的統計資訊儲存在SYSAUX表空間SYS模式下，以WRM$_*和WRH$_*的格式命名，預設會保留最近7天收集的統計資訊。每個小時將收集到的資訊寫到資料庫中，這一系列操作是由一個叫MMON的程式來完成的。

AWR儲存的資料分類：
WRM$表儲存AWR的後設資料（awrinfo.sql指令碼）
WRH$表儲存取樣快照的歷史資料（awrrpt.sql指令碼）
WRI$表儲存同資料庫建議功能相關的資料（ADDM相關資料）

一.生成AWR報告：
SQL>@?/rdbms/admin/awrrpt

根據嚮導來完成AWR報告的生成。需要注意的是，在選擇時間範圍的時候，中間不能有停機(如果顯示的時間中間有空白行，表示有停機情況)。在選擇報告型別的時候一般使用預設的HTML，方便檢視。

二.檢視資料庫的AWR的設定：
SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL
---------------------------------------------------------------------------
RETENTION
---------------------------------------------------------------------------
+00000 01:00:00.0(每小時收集一次)
+00007 00:00:00.0(保留7天)

三.修改預設設定：
begin
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 20,
retention => 2*24*60);
end;

修改成每20分鐘收集一次統計量，保留最近的2天統計量資訊。

四.手動收集一次資料庫的統計資訊：
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

我們還可以透過DBMS_WORKLOAD_REPOSITORY包完成對基線，預設設定的修改等操作。

ADDM (Automatic Database Diagnostic Monitor AWR)
是Oracle內部的一個顧問系統，能夠自動的完成最資料庫的一些最佳化的建議，給出SQL的最佳化，索引的建立，統計量的收集等建議。

ADDM報告生成：
SQLPLUS>@?/rdbms/addmrpt.sql

Oracle效能調整最重要的就是對最影響效能的SQL的調整。在一個應用中，能夠影響到資料庫的只有SQL，也只能是SQL。我們不能一味依靠增強硬體，修改系統、資料庫引數來提高資料庫的效能。更多的應該關注那些最影響效能的SQL語句。ASH報告、AWR報告、ADDM報告都能夠找出最影響效能的SQL的工具。在分析ASH報告、AWR報告的時候，最重要的就是關注SQL Statistics，SQL Statistics中最應該關注的是SQL ordered by Gets和SQL ordered by Reads兩個指標。大量的Gets(邏輯讀)會佔用大量的CPU時間。大量的Reads(物理讀)會引起IO的瓶頸出現。一般情況下，大量的Gets會伴隨著大量的Reads出現。當然，我們可以透過增大SGA的大小來減少Reads的量。透過這兩個指標找到了最影響效能的SQL，這是首要的，也是必要的。下一步就可以透過建立索引，調整SQL來提高SQL單獨執行時的效能。減少SQL執行時出現的高Gets,Reads。當然整體的效能影響還和excutions有關，如果這條SQL執行的次數過多，累加起來量還是很大的。那麼就可以考慮透過在應用上快取等手段來減少SQL執行的次數。另外還有一個需要注意的問題就是在開發過程中SQL一定要使用繫結變數，來減少硬解析(大量的硬解析也會消耗大量的CPU時間，佔用大量的Latch)。在開發過程中有個原則就是：小事務。操作完成及時的提交。

我們使用這麼多種方式、報告只有一個唯一的目的：找出最影響系統效能的SQL語句。找到SQL下一步就是對它進行調整了。

我們在監控資料庫時，如果是當前正在發生的問題，我們可以透過v$session+v$sqlarea來找出效能最差的SQL語句。如果在一個小時以內發生的我們可以透過生成ASH報告來找出SQL。如果是1小時以上或幾天我們可以透過AWR報告來找出幾小時，幾天以來最影響系統的SQL語句。ADDM報告基於AWR庫，預設可以儲存30天的ADDM報告。

我們也可以直接查詢試圖：
v$session                                      (當前正在發生)
v$session_wait                            (當前正在發生)
v$session_wait_history              (會話最近的10次等待事件)
v$active_session_history           (記憶體中的ASH採集資訊，理論為1小時)
wrh$_active_session_history    (寫入AWR庫中的ASH資訊，理論為1小時以上)
dba_hist_active_sess_history   (根據wrh$_active_session_history生成的檢視)

    ADDM會收集資料建議呼叫不同的Advisor進行資料庫最佳化，SQL相關的Advisor主要包括SQL Access Advisor和SQL Tuning Advisor。

        SQL Access Advisor和SQL Tuning Advisor之間的區別（）：

In a nutshell - the tuning advisor 
o suggests sql profiles 
o gathering more or stale statistics 
o indexes that might be VERY useful 
o query rewrites 

the access advisor 
o suggests indexes that might be useful (a possibly different set than the tuning advisor above) 
o materialized views 
o materialized view logs 
o partitions (in 11g on up only) 

   SQL Access Advisor可以透過DBMS_ADVISOR呼叫，SQL Tuning Advisor可以透過DBMS_SQLTUNE呼叫。

參考文章：
   《More about AWR》：http://blog.itpub.net/23135684/viewspace-1127938/

--end--