Oracle10g AWR及ASH詳解(final)

演示AWR安裝及生成報告:

SQL> conn / AS SYSDBA
SQL> @/u01/product/oracle/rdbms/admin/awrrpt.sql
輸入 report_type 的值:
輸入 num_days 的值: 2                          --- 現在到過去兩天時間內的snap id (可以檢視到).
輸入 begin_snap 的值: 2147                     --- 輸入的開始及結束的snap id 對應您要查詢的出現問題的時間段。 
輸入 end_snap 的值: 2182 
輸入 report_name 的值:
Report written to awrrpt_1_2177_2182.html
SQL> exit  

下載awrrpt_1_2177_2182.html並開啟檢視。

 

 

認識AWR 

Oracle 10g 開始提供了一個顯著改進的工具：自動工作負載資訊庫AWR (Automatic Workload Repository). Oracle 建議使用者用這個取代 Statspack。

AWR 是一個Oracle10g的內建工具，它採集與效能相關的統計資料，並從那些統計資料中匯出效能量度，以跟蹤潛在的問題。與 Statspack 不同，快照由一個稱為 MMON 的新的後臺程式及其從程式自動地每小時採集一次(在oracle10G中引進了兩個新的程式:mmon和mmnl，其中MMON承擔了大部分的工作)。為了節省空間，採集的資料在 7 天后自動清除。快照頻率和保留時間都可以由使用者修改。它產生兩種型別的輸出：文字格式（類似於 Statspack報表的文字格式但來自於 AWR 資訊庫）和預設的 HTML 格式（擁有到部分和子部分的所有超連結），從而提供了非常友好的使用者報表。 

AWR 用幾個表來儲存採集的效能統計資料，所有的表都儲存在 SYSAUX 表空間中的 SYS 模式下，並且以 WRM$_*(5個） 和 WRH$_*（94個）的格式名。前一種型別儲存後設資料資訊（如檢查的資料庫和採集的快照），後一種型別儲存實際採集的統計資料。H代表“歷史資料 (historical)”而 M 代表“後設資料 (metadata)”。在這些表上構建了幾種帶字首 DBA_HIST_ 的檢視，這些檢視可以用來編寫您自己的效能診斷工具。檢視的名稱直接與表相關；例如，檢視 DBA_HIST_SYSMETRIC_SUMMARY 是在WRH$_SYSMETRIC_SUMMARY 表上構建的。要使用AWR必須設定STATISTICS_LEVEL引數，共有三個：BASIC, TYPICAL,ALL。如果設為BASIC將禁用許多特性，如ADDM (Automatic Database Diagnostic Monitor ADDM)自動資料庫診斷監視器。安裝Oracle 10g後，預設值是TYPICAL。AWR的統計資料預設保留7天，並且每小時獲取一次快照。

 
一、 ASH(v$active_session_history)和AWR  

1.     10g之前使用者的連線將產生會話，當前會話記錄儲存在v$session中；處於等待狀態的會話會被複制一份放在v$session_wait中。當該連線斷開後，其原來的連線資訊在v$session和v$session_wait中就會被刪除。這是10g之前的狀況。

2.     v$session_wait_history與ASH若是一個普通的會話（我是指沒有大量地耗費資源），則對於效能調整來說無足輕重。但若該會話在活動時大量佔用了資源（比如：CPU，記憶體，I/O等），該會話資訊的丟失，將無法評測當時的系統瓶頸究竟是什麼。令DBA高興的是，oracle10g中保留下了v$session_wait中的這些資訊。在10g中新出現了一個檢視：v$session_wait_history。這個檢視儲存了每個活動session在v$session_wait中最近10次的等待事件(ASH預設每一秒收集一下v$session中活動會話的情況，記錄會話等待的事件，不活動的會話不會被取樣 ,間隔時間由 _ash_sampling_interval 引數確定)。但這對於一段時期內的資料效能狀況的監測是遠遠不夠的，為了解決這個問題，在10g中還新新增了一個檢視：v$active_session_history。這就是ASH（active session history）。典型的情況下,為了診斷當前資料庫的狀態，需要最近的五到十分鐘的詳細資訊。然而，由於記錄session的活動資訊是很費時間和空間的，ASH採用的策略是：儲存處於等待狀態的活動session的資訊，每秒從v$session_wait中取樣一次，並將取樣資訊儲存在記憶體中(ASH的取樣資料是儲存在記憶體中)。

3.     AWR注意，ASH的取樣資料是儲存在記憶體中。而分配給ASH的記憶體空間是有限的，當所分配空間佔滿後，舊的記錄就會被覆蓋掉；而且資料庫重啟後，所有的這些ASH資訊都會消失。這樣，對於長期檢測oracle的效能是不可能的。在Oracle10g中，提供了永久保留ASH資訊的方法，這就是AWR（automatic workload repository）。
      由於全部儲存ASH中的資訊是非常耗費時間和空間的，AWR採用的策略是：MMON程式每小時對ASH (v$active_session_history)進行取樣一次，並將資訊儲存到磁碟中，當ASH BUFFER滿2/3的話MMNL程式會寫，並保留7天，7天后舊的記錄才會被覆蓋。這些取樣資訊被儲存在表wrh$_active_session_history中。而這個取樣頻率（1小時）和保留時間（7天）是可以根據實際情況進行調整的，這就給DBA們提供了更加有效的系統監測工具。    AWR永久地儲存系統的效能診斷資訊，由SYS使用者擁有。一段時間後，你可能想清除掉這些資訊；有時候為了效能診斷，你可能需要自己定義取樣頻率來獲取系統快照資訊。Oracle 10g在包dbms_workload_repository中提供了很多過程，透過這些過程，你可以管理快照並設定基線(baselines：用於儲存指定時間段的歷史資料用於將來分析及對比 )。

4.   ASH儲存了系統最新的處於等待的會話記錄，可以用來診斷資料庫的當前狀態；而AWR中的資訊最長可能有1小時的延遲，所以其取樣資訊並不能用於診斷資料庫的當前狀態，但可以用來作為一段時期內資料庫效能調整的參考。

備註：  檢視dba_hist_active_sess_history是wrh$_active_session_history和其他幾個檢視的聯合展現，通常透過這個檢視進行歷史資料的訪問。

 

二、詳細介紹 AWR 

1.   ASH的採集資訊儲存在記憶體中，在舊的資訊被取樣到AWR中後，可被新採集的資訊覆蓋，重啟oracle後該資訊被清除。分配給ASH的記憶體大小可以查詢到：

SQL> select pool, name, bytes/1024/1024 From v$sgastat where name like '%ASH %';

POOL          NAME          BYTES/1024/1024
------------- ------------- ---------------
shared pool   ASH buffers                 6

2.   其實AWR記錄的資訊不僅是ASH，還可以收集到資料庫執行的各方面統計資訊和等待資訊，用以診斷分析。
AWR的取樣方式是，以固定的時間間隔為其所有重要的統計資訊和負載資訊執行一次取樣，並將取樣資訊儲存在AWR中。可以這樣說：ASH中的資訊被儲存到了AWR中的檢視wrh$_active_session_history中。ASH是AWR的真子集。

3.   mmon程式與mmnl程式快照由一個稱為 MMON 的新的後臺程式（及其從程式）以及MMNL後臺程式自動地每隔固定時間取樣一次。我們先來看一下這兩個新增加的後臺程式的介紹：
        MMON程式負責執行多種和管理相關（manageability-related）的後臺任務，例如：
當某個測量值（metrics）超過了預設的限定值（threshold value）後提交警告 建立新的 MMON 隸屬程式（MMON slave process）來進行快照（snapshot） 捕獲最近修改過的 SQL 物件的統計資訊
        MMNL程式負責執行輕量級的且頻率較高的和可管理性相關的後臺任務，例如捕獲會話歷史資訊，測量值計算等。AWR的取樣工作由MMON程式每個1小時執行一次，ASH資訊同樣會被取樣寫出到AWR負載庫中。雖然ASH buffer被設計為保留1小時的資訊，但很多時候這個記憶體是不夠的，當ASH buffer寫滿後，另外一個後臺程式MMNL將會主動將ASH資訊寫出。

4.   SYSAUX表空間這些取樣資料都儲存在SYSAUX表空間中，並且以WRM$_* 和 WRH$_*的格式命名。前一種型別儲存後設資料資訊(如檢查的資料庫和採集的快照)，後一種型別儲存實際採集的統計資料。

SQL> select table_name from dba_tables where table_name like 'WRM$%';

TABLE_NAME
-----------------------
WRM$_WR_CONTROL
WRM$_SNAP_ERROR
WRM$_SNAPSHOT
WRM$_DATABASE_INSTANCE
WRM$_BASELINE

當SYSAUX表空間滿後，AWR將自動覆蓋掉舊的資訊，並在警告日誌中記錄一條相關資訊：
ORA-1688: unable to extend table SYS.WRH$_ACTIVE_SESSION_HISTORY partition WRH$_ACTIVE_3533490838_1522 by 128 in tablespace SYSAUX 

5.  取樣頻率和保留時間可以透過查詢檢視dba_hist_wr_control或（wrm$_wr_control）來查詢AWR的取樣頻率和保留時間。預設為每1小時取樣一次，取樣資訊保留時間為7天。
SQL> select * from dba_hist_wr_control;

DBID SNAP_INTERVAL RETENTION   TOPNSQL
---- ------------- ----------- ----------
1148 +00000 00:1   +00007 00:0 DEFAULT

SQL> select DBID, SNAP_INTERVAL, SNAPINT_NUM, RETENTION from wrm$_wr_control;

      DBID SNAP_INTERVAL      SNAPINT_NUM RETENTION
---------- ------------------ ----------- --------------------
1160732652 +00000 01:00:00.0         3600 +00007 00:00:00.0

設定每半小時一次，並且保留5天 。
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);  

6.   取樣資料量由於資料量巨大，把所有ASH資料寫到磁碟上是不能接受的。一般是在寫到磁碟的時候過濾這個資料，寫出的資料佔取樣資料的10%，寫出時透過direct-path insert完成，儘量減少日誌生成，從而最小化資料庫效能的影響。

7.   初始化引數statistics_level， AWR的行為受到引數STATISTICS_LEVEL的影響。這個引數有三個值：
       BASIC：     awr統計的計算和衍生值關閉.只收集少量的資料庫統計資訊.
       TYPICAL：   預設值．只有部分的統計收集.他們代表需要的典型監控oracle資料庫的行為.
       ALL : 所有可能的統計都被捕捉. 並且有作業系統的一些資訊.這個級別的捕捉應該在很少的情況下,比如你要更多的sql診斷資訊的時候才使用.

 

三、使用AWR 

   AWR由ORACLE自動產生，不過也可以透過DBMS_WORKLOAD_REPOSITORY包來手工建立、刪除和修改。可以使用desc命令檢視該包中的過程。下面只介紹幾個常用的：

1.手工建立一個快照
SQL> select count(*) from wrh$_active_session_history;
  COUNT(*)
----------
     317  

SQL> begin
  2  dbms_workload_repository.create_snapshot();
  3  end;
  4  /

PL/SQL 過程已成功完成。
SQL> select count(*) from wrh$_active_session_history;
  COUNT(*)
----------
       320

2.手工刪除指定範圍的快照
SQL> select * from wrh$_active_session_history where snap_id = 96;
   SNAP_ID       DBID INSTANCE_NUMBER  SAMPLE_ID SAMPLE_TIME
---------- ---------- --------------- ---------- ----------------------------
        96 1160732652               1     236930 06-10月-07 11.26.04.562 上午
        96 1160732652               1     236930 06-10月-07 11.26.04.562 上午
        96 1160732652               1     236930 06-10月-07 11.26.04.562 上午

SQL> begin
  2  dbms_workload_repository.drop_snapshot_range(low_snap_id => 96, high_snap_id => 96, dbid => 1160732652);
  3  end;
  4  /
PL/SQL 過程已成功完成。
SQL> select * from wrh$_active_session_history where snap_id = 96;
未選定行

3.修改採集時間和統計資訊保留時間
PROCEDURE MODIFY_SNAPSHOT_SETTINGS
引數名稱                       型別                    輸入/輸出預設值?
------------------------------ ----------------------- ------ --------
RETENTION                      NUMBER                  IN     DEFAULT
INTERVAL                       NUMBER                  IN     DEFAULT
TOPNSQL                        NUMBER                  IN     DEFAULT
DBID                           NUMBER                  IN     DEFAULT

透過修改retention引數可以修改awr資訊的保留期限。預設的是七天,最小的值是一天。如果把retention設定為零,自動清除就關閉了.如果awr發現sysaux空間不夠,它透過刪除那些最老部分的快照來重新使用這些空間.同時,也會給dba發一條警告,告訴sysaux空間不夠了（在alert log中）.透過修改interval引數可以修改awr資訊的取樣頻率。最小的值是10分鐘,預設的是60分鐘.典型的值是10,20,30,60,120等等。把interval設為0則關閉自動捕捉快照.如將收集間隔時間改為30 分鐘一次。並且保留5天時間（注：單位都是為分鐘）：

SQL> select *from dba_hist_wr_control;
      DBID SNAP_INTERVAL      RETENTION          TOPNSQL
---------- ------------------ -------------------------- -----------
1160732652 +00000 01:00:00.0  +00007 00:00:00.0          DEFAULT

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);
PL/SQL 過程已成功完成。

SQL> SELECT *from dba_hist_wr_control;
      DBID SNAP_INTERVAL       RETENTION         TOPNSQL
---------- ------------------- ------------------------- -----------
1160732652 +00000 00:30:00.0   +00005 00:00:00.0         DEFAULT

SQL>

4.設定基線
基線（baseline）是一種機制,這樣你可以在重要時間的快照資訊集做標記。一個基線定義在一對快照之間,快照透過他們的快照序列號識別.每個基線有且只有一對快照。一次典型的效能調整實踐從採集量度的基準線集合、作出改動、然後採集另一個基準線集合開始。可以比較這兩個集合來檢查所作的改動的效果。在 AWR 中，對現有的已採集的快照可以執行相同型別的比較。
假定一個名稱為 apply_interest 的高度資源密集的程式在下午 1:00 到 3:00 之間執行，對應快照 ID 95 到 98。我們可以為這些快照定義一個名稱為

apply_interest_1 的基準線：
SQL> select *From dba_hist_baseline;

未選定行

SQL> select * from wrm$_baseline;

未選定行

SQL> exec dbms_workload_repository.create_baseline(95, 98, 'apply_interest_1');

PL/SQL 過程已成功完成。

這一操作將快照從 95 到 98 編號，作為上面指定的基準線的一部分。檢視現有的基準線：
SQL> select *from dba_hist_baseline;

      DBID BASELINE_ID BASELINE_NAME     START_SNAP_ID START_SNAP_TIME               END_SNAP_ID END_SNAP_TIME
---------- ----------- ------------------------- ------------- ------------------------------------- ----------- ------------
1160732652           1 apply_interest_1             95 06-10月-07 11.00.05.375 上午           98 06-10月-07 01.44.58.062 下午

SQL> select *from wrm$_baseline;

      DBID BASELINE_ID BASELINE_NAME        START_SNAP_ID END_SNAP_ID
---------- ----------- ---------------------------- ------------- -----------
1160732652           1 apply_interest_1                95          98

SQL>

在一些調整步驟之後，我們可以建立另一個基準線 — 假設名稱為 apply_interest_2，然後只為那些與這兩條基準線相關的快照比較量度。
SQL> exec dbms_workload_repository.create_baseline(92, 94, 'apply_interest_2');

PL/SQL 過程已成功完成。
像這樣把快照分隔在僅僅幾個集合中有助於研究調整對於效能量度的影響。您可以在分析之後使用 drop_baseline() 來刪除基準線；快照將保留(也可級聯刪除)。 此外，當清除例程開始刪除舊的快照時，與基準線相關的快照不會被清除，從而允許進行進一步的分析。

 

5.刪除基線

如果要刪除一個基準線:
SQL> exec dbms_workload_repository.drop_baseline(baseline_name=>'apply_interest_1', cascade=>false);

PL/SQL 過程已成功完成。

SQL> select *from wrh$_active_session_history where snap_id in (95,96,97,98);

   SNAP_ID       DBID INSTANCE_NUMBER  SAMPLE_ID SAMPLE_TIME
---------- ---------- --------------- ---------- -------------------------------
        95 1160732652               1     235360 06-10月-07 10.56.29.872 上午
        95 1160732652               1     235230 06-10月-07 10.54.19.857 上午
        95 1160732652               1     233130 06-10月-07 10.19.19.478 上午
        95 1160732652               1     232830 06-10月-07 10.14.18.859 上午
        95 1160732652               1     232250 06-10月-07 10.04.38.481 上午
        97 1160732652               1     238600 06-10月-07 12.33.08.420 下午
        97 1160732652               1     238600 06-10月-07 12.33.08.420 下午
        97 1160732652               1     238600 06-10月-07 12.33.08.420 下午
        97 1160732652               1     238600 06-10月-07 12.33.08.420 下午
        97 1160732652               1     238600 06-10月-07 12.33.08.420 下午
        97 1160732652               1     238600 06-10月-07 12.33.08.420 下午

   SNAP_ID       DBID INSTANCE_NUMBER  SAMPLE_ID SAMPLE_TIME
---------- ---------- --------------- ---------- -------------------------------
        97 1160732652               1     238420 06-10月-07 11.50.55.686 上午
        97 1160732652               1     238230 06-10月-07 11.47.45.687 上午
        98 1160732652               1     239140 06-10月-07 01.42.00.976 下午
        98 1160732652               1     239140 06-10月-07 01.42.00.976 下午
        98 1160732652               1     239140 06-10月-07 01.42.00.976 下午
        98 1160732652               1     239140 06-10月-07 01.42.00.976 下午
        98 1160732652               1     239140 06-10月-07 01.42.00.976 下午
        98 1160732652               1     239130 06-10月-07 01.27.04.161 下午
        98 1160732652               1     239130 06-10月-07 01.27.04.161 下午
        98 1160732652               1     239130 06-10月-07 01.27.04.161 下午

已選擇21行。

SQL> exec dbms_workload_repository.drop_baseline(baseline_name=>'apply_interest_2', cascade=>true);

PL/SQL 過程已成功完成。

SQL> select *from wrh$_active_session_history where snap_id in (92,93,94);
未選定行

SQL>

待續.....