自動工作負載庫(Automatic Workload Repository,AWR)

swq618發表於2016-05-26
自動工作負載庫(Automatic Workload Repository,AWR)

AWR的由來:
    10g之前的oracle:使用者的連線將產生會話,當前會話記錄儲存在v$session中;處於等待狀態的會話會被複制一份放在v$session_wait中。當該連線
 斷開後,其原來的連線資訊在v$sessionv$session_wait中就會被刪除;oracle10g及之後保留下了v$session_wait中的這些資訊,並多了
v$active_session_history(ASH)檢視,記錄每個活動session在v$session_wait中最近10次的等待事件。

    ASH的取樣資料是儲存在記憶體中。而分配給ASH的記憶體空間是有限的,當所分配空間佔滿後,舊的記錄就會被覆蓋掉;而且資料庫重啟後,所有的這些
ASH資訊都會消失。這樣,對於長期檢測oracle的效能是不可能的。在Oracle10g中,提供了永久保留ASH資訊的方法,這就是AWR。
    
    由於全部儲存ASH中的資訊是非常耗費時間和空間的,AWR採用的策略是:每小時對v$active_session_history進行取樣一次,並將資訊儲存到磁碟
中,並且保留7天,7天后舊的記錄才會被覆蓋。這些取樣資訊被儲存在檢視wrh$_active_session_history中。而這個取樣頻率(1小時)和保留時間
(7天)是可以根據實際情況進行調整的,這就給DBA們提供了更加有效的系統監測工具。

1.AWR的啟用

    在預設情況下,Oracle啟用資料庫統計收集這項功能(即啟用AWR)。是否啟用AWR由初始化引數STATISTICS_LEVEL控制。透過SHOW PARAMETER命令

可以顯示STATISTICS_LEVEL的當前值:

     SQL> SHOW PARAMETER STATISTICS_LEVEL 

SQL語句的執行結果是:
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

如果STATISTICS_LEVEL的值為TYPICAL或者 ALL,表示啟用AWR;如果STATISTICS_LEVEL的值為BASIC,表示禁用AWR。

初始化引數statistics_level介紹:

AWR的行為受到引數STATISTICS_LEVEL的影響。這個引數有三個值:

*BASIC:awr統計的計算和衍生值關閉.只收集少量的資料庫統計資訊.

*TYPICAL:預設值.只有部分的統計收集.他們代表需要的典型監控oracle資料庫的行為.

*ALL : 所有可能的統計都被捕捉. 並且有作業系統的一些資訊.這個級別的捕捉應該在很少的情況下,比如你要更多的sql診斷資訊的時候才使用.


2.快照(SNAPSHOT)

   每隔一小時,記憶體監控程式(MMON)自動地採集一次統計資訊,並把這些資訊存放到負載庫中,一次取樣就是一個快照。為了節省空間,採集的資料在

7天后自動清除。快照的頻率和保留時間可以由使用者修改。

--檢視快照的頻率和保留時間(預設為每1小時取樣一次,取樣資訊保留時間為7天)
 select * from dba_hist_wr_control;
 select DBID, SNAP_INTERVAL, SNAPINT_NUM, RETENTION from wrm$_wr_control;
 
--修改 快照的頻率和保留時間(單位用分鐘)
exec dbms_workload_repository.modify_snapshot_settings(interval=>60, retention=>7*24*60);

使用者也可以使用下面的命令手工取樣(手工生成快照):
    BEGIN  
    DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();  
    END; 

手工刪除指定範圍的快照
  begin
  dbms_workload_repository.drop_snapshot_range(low_snap_id => 3965, high_snap_id => 3966, dbid => 3437504306);
  end;
    
 --檢視有多少個快照
 select count(1) from wrh$_active_session_history;
 select count(1) from dba_hist_active_sess_history;
 
透過查詢檢視DBA_HIST_SNAPSHOT,可以知道系統中產生了哪些快照。
select * from DBA_HIST_SNAPSHOT;

3.取樣資料存放位置

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

select table_name from dba_tables where table_name like 'WRH$%';

4. 設定基線
 4.1建立基線
    基線(baseline)是一種機制,這樣你可以在重要時間的快照資訊集做標記。一個基線定義在一對快照之間,快照透過他們的快照序列號識別.每個
基線有且只有一對快照。一次典型的效能調整實踐從採集量度的基準線集合、作出改動、然後採集另一個基準線集合開始。可以比較這兩個集合來檢查
所作的改動的效果。在 AWR 中,對現有的已採集的快照可以執行相同型別的比較。

    假定一個名稱為 apply_interest 上午 2:00 到 4:00 之間執行,對應快照 ID 4150 到 4151。我們可以為這些快照定義
一個名稱為 apply_interest_1 的基準線:

SQL> exec dbms_workload_repository.create_baseline(4150, 4151, 'apply_interest_1');

這一操作將快照從 4150 到 4151 編號,作為上面指定的基準線的一部分。檢視現有的基準線:
SQL> select *from dba_hist_baseline;
 
 DBID      BASELINE_ID  BASELINE_NAME        START_SNAP_ID  START_SNAP_TIME                  END_SNAP_ID END_SNAP_TIME
---------- ----------- -------------------- ------------- --------------------------------  ----------- -------------------------------
3437504306     1        apply_interest_1     4150          07-3月 -11 03.00.47.627 上午       4151        07-3月 -11 04.00.12.567 上午
 
SQL> select *from wrm$_baseline;
 
DBID        BASELINE_ID BASELINE_NAME                   START_SNAP_ID  END_SNAP_ID
---------- ----------- ------------------------------  -------------  -----------
3437504306      1       apply_interest_1                 4150             4151
 

在一些調整步驟之後,我們可以建立另一個基準線 — 假設名稱為 apply_interest_2(下午2點到4點),然後只為那些與這兩條基準線相關的快照比較量度。
SQL> exec dbms_workload_repository.create_baseline(4162, 4163, 'apply_interest_2');
 
像這樣把快照分隔在僅僅幾個集合中有助於研究調整對於效能量度的影響。

4.2 刪除基線
    分析之後使用 drop_baseline() 來刪除基準線;快照將保留(也可級聯刪除)。此外,當清除例程開始刪除舊的快照時,與基準線相關的快照不會
被清除,從而允許進行進一步的分析。
如果要刪除一個基準線:
SQL> exec dbms_workload_repository.drop_baseline(baseline_name=>'apply_interest_1', cascade=>false);
 
SQL> select *from wrh$_active_session_history where snap_id in (4150,4151);
 
SNAP_ID  DBID     INSTANCE_NUMBER  SAMPLE_ID SAMPLE_TIME                  SESSION_ID ...
4150 3437504306    1             14900840   07-3月 -11 02.55.02.038 上午   162       ...   
4150 3437504306    1             14900200   07-3月 -11 02.44.21.942 上午   165       ...  
....
4151 3437504306    1             14901980   07-3月 -11 03.14.02.213 上午  165        ...    
4151 3437504306    1             14901790   07-3月 -11 03.10.52.183 上午  165        ...  
4151 3437504306    1             14901490   07-3月 -11 03.05.52.138 上午  167        ... 
 
--級聯刪除(基線與快照一塊刪)
SQL> exec dbms_workload_repository.drop_baseline(baseline_name=>'apply_interest_2', cascade=>true);
 
SQL> select *from wrh$_active_session_history where snap_id in (4162,4163);
未選定行

5.產生AWR報告

    Oracle可以產生兩種型別的AWR報告:文字格式和HTML 格式。HTML格式的報告介面更加友好。AWR報告用於顯示兩個快照或者兩個時間點之間捕捉到

的資料。AWR報告其實就是一張資料庫健康體檢表,它顯示了資料庫健康的各項指標。

    有針對整個資料庫的AWR報告,有針對某個例項的AWR報告(在叢集環境),有針對單條SQL語句的AWR報告。透過執行Oracle自帶的SQL指令碼產生

AWR報告,只是產生不同的AWR報告,需要執行不同的指令碼。

    --產生整個資料庫的AWR報告,執行指令碼awrrpt.sql。

   @$ORACLE_HOME/rdbms/admin/awrrpt.sql 

    --產生某個例項的AWR報告,執行指令碼awrrpti.sql。

   @$ORACLE_HOME/rdbms/admin/awrrpti.sql 

    --產生某條SQL語句的AWR報告,執行指令碼awrsqrpt.sql。

   @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql 

--注: $ORACLE_HOME代表Oracle的主目錄。

示例:產生整個資料庫的AWR報告。本例中,Oracle的主目錄是 /oracle/product/10.2.0/db_1/rdbms/admin

執行指令碼awrrpt.sql。

    SQL> @/oracle/product/10.2.0/db_1/rdbms/admin/awrrpt.sql 

選擇報告的型別。

   1. 輸入 report_type 的值:  html 

本例中選擇HTML。

列出最近兩天產生的快照(主要是獲得快照ID)

   1. 輸入 num_days 的值:  2  

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
telemt       TELEMT            4123 06 Mar 2011 00:00      1
                               4124 06 Mar 2011 01:01      1
....
                               4160 07 Mar 2011 13:00      1
                               4161 07 Mar 2011 14:00      1

指定起始快照的ID和結束快照的ID。

   1. Specify the Begin and End Snapshot Ids  
   2. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  
   3. 輸入 begin_snap 的值:  4123
   4. Begin Snapshot Id specified: 4123 
   5.  
   6. 輸入 end_snap 的值:  4161 
   7. End   Snapshot Id specified: 4161

本例中起始快照的ID是4123,結束快照的ID是4161。

指定報告的名字。

    輸入 report_name 的值:  myreport.html 
    
注意:輸入報告名後回車出現 ORA-00904:: invalid identified value
解決辦法:用sys使用者來執行或者對使用者進行授權 grant all on dbms_workload_repository to perfstat;

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29674916/viewspace-2107422/,如需轉載,請註明出處,否則將追究法律責任。

相關文章