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之間的區別（http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1794009000346753857）：

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調用。

@?rdbms/admin/awrrpt.sql是以前statspack的擴充，收集資訊更詳細，查看長期的資料庫情況。
@?rdbms/admin/ashrpt.sql查看當前的資料庫情況，因為ash是每秒從v$session進行進行取樣，awr收集的資料要比ash多得多。
一般收集資料庫資訊的話要結合awr和ash。
@?rdbms/admin/addmrpt .sql相當於是駐留在oracle裡的一位專家，是一個自我診斷引擎。產生symptom，problem，infomation，提供解決問題的建議，並自動修複一些具體的故障。
@?rdbms/admin/awrinfo.sql顯示的都是awr的相關資訊，包括快照資訊、sysaux空間使用、awr組件、ash等資訊。
=============================================================================
                                                     簡   單   總    結
=============================================================================
awr與ash的最主要的區別在於：awr是平面的，全面的，ash是立體的，更側重於session的event跟蹤，
由於業務量大的資料庫的event wait是瞬息萬變，awr很可能會監控不到，為了彌補這個不足，ash才可以對session的event進行跟蹤。

ash與addm的區別在於：addm偶重於基於對當據庫目前狀態的分析，對存在的問題提供指導性的意見，可以說ash，addm是awr的補充，
awr全面地收集資料庫的狀態，但ash/addm是側重要對收集的資料進行分析，並提供一些有益的建議。

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

Oracle效能調整ASH,AWR,ADDM