AWR: Automatic Workload Repository

from:
http://blog.csdn.net/tianlesoftware/article/details/4682300

 

Oracle 10g之前對資料庫做效能檢測使用statspack工具。

Oracle Database 10g 提供了一個新的工具：(AWR:Automatic Workload Repository)。

AWR簡稱Automatic Workload Repository,它作為SYSAUX表空間中的一組表和其他物件而存在。

統計資訊在記憶體中(也就是在SGA內的資料結構中)累積。因為統計資訊只反映例項所做的操作，所以並不影響例項的效能.

將計資訊定期的(預設是60分鐘)寫入磁碟，也就是寫入AWR。這稱為一次AWR快照。
將統計資訊寫入磁碟的操作由後臺程式MMON(可管理性監視器)完成。預設情況下，在重寫前，快照會保留8天。

與 Statspack 不同，快照由一個稱為 MMON 的新的後臺程式及其從程式自動地每小時採集一次。
只要生成快照，MMON程式就會自動執行ADDM(Automatic Database Diagnostic)。

AWR 使用幾個表來儲存採集的統計資料，所有的表都儲存在新的名稱為 SYSAUX 的特定表空間中的 SYS 模式下，
並且以 WRM$_* 和 WRH$_* 的格式命名。
前一種型別儲存後設資料資訊（如檢查的資料庫和採集的快照），後一種型別儲存實際採集的統計資料。
H 代表“歷史資料 (historical)”而 M 代表“後設資料 (metadata)”。

在這些表上構建了幾種帶字首 DBA_HIST_ 的檢視，這些檢視可以用來編寫您自己的效能診斷工具。
檢視的名稱直接與表相關；例如，檢視 DBA_HIST_SYSMETRIC_SUMMARY 是在WRH$_SYSMETRIC_SUMMARY 表上構建的。

statistics_level 預設是typical，在10g中表監控是啟用的，強烈建議在10g中此引數的值是typical。如果STATISTICS_LEVEL設定為basic，不僅不能監控表，而且將禁掉如下一些10g的新功能：
            ASH(Active Session History)
            ASSM(Automatic Shared Memory Management)
            AWR(Automatic Workload Repository)
            ADDM(Automatic Database Diagnostic Monitor)

/* AWR使用 */
SQL>@?/rdbms/admin/awrrpt.sql

/* 檢視當前的AWR儲存策略 */
SQL> col SNAP_INTERVAL format a20
SQL> col RETENTION format a20
SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
2432893466 +00000 01:00:00.0    +00008 00:00:00.0    DEFAULT

/* 調整AWR配置 */
AWR配置都是通過dbms_workload_repository包進行配置。

/* 調整AWR產生snapshot的頻率和保留策略 ,
如將收集間隔時間改為30 分鐘一次。並且保留5天時間（單位都是分鐘）：*/

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);
BEGIN dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60); END;

*
ERROR at line 1:
ORA-13541: system moving window baseline size (691200) greater than retention
(432000)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 174
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 222
ORA-06512: at line 1

SQL> select 5*24*60*60 as sec from dual;

       sec
----------
    432000

/* 檢查當前的移動視窗基線大小 */
col BASELINE_NAME for a30
SELECT dbid, baseline_name, baseline_type, moving_window_size from dba_hist_baseline;

   DBID BASELINE_NAME                  BASELINE_TYPE MOVING_WINDOW_SIZE
---------- ------------------------------ ------------- ------------------
2432893466 SYSTEM_MOVING_WINDOW           MOVING_WINDOW                  8

SQL> exec dbms_workload_repository.modify_baseline_window_size(5);

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);

PL/SQL procedure successfully completed.

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
2432893466 +00000 00:30:00.0    +00005 00:00:00.0    DEFAULT

/* 關閉AWR，把interval設為0則關閉自動捕捉快照 */
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);

/* 手工建立一個快照 */
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

PL/SQL procedure successfully completed.

/* 檢視快照 */

SQL> select count(*) from sys.wrh$_active_session_history;

  COUNT(*)
----------
       599

/* 檢視 10天以前的快照snap_id */

select distinct snap_id,sample_time from sys.wrh$_active_session_history where sample_time < sysdate - 10;

/* 手工刪除指定範圍的快照 */
SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 339, high_snap_id => 353, dbid => 2432893466);

/* 建立baseline，儲存這些資料用於將來分析和比較 */
SQL> exec dbms_workload_repository.create_baseline(start_snap_id => 373,end_snap_id => 376,baseline_name => 'bl_test');

PL/SQL procedure successfully completed.

/* 檢視基線*/
SQL> col BASELINE_NAME for a10
SQL> select baseline_id,baseline_name,baseline_type,start_snap_id,end_snap_id,creation_time
from dba_hist_baseline;

BASELINE_ID BASELINE_N BASELINE_TYPE START_SNAP_ID END_SNAP_ID CREATION_TIME
----------- ---------- ------------- ------------- ----------- ------------------
          2 bl_test    STATIC                  373         376 24-NOV-13
          0 SYSTEM_MOV MOVING_WINDOW           359         389 15-MAY-13
            ING_WINDOW

/* 刪除baseline , cascade引數選擇是否將其關聯的Snapshots級別進行刪除*/

SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name => 'bl_test',cascade => false);

PL/SQL procedure successfully completed.

 

4.1 SQL ordered by Elapsed Time

            記錄了執行總和時間的TOP SQL(請注意是監控範圍內該SQL的執行時間總和，而不是單次SQL執行時間 Elapsed Time = CPU Time + Wait Time)。
 

            Elapsed Time(S): SQL語句執行用總時長，此排序就是按照這個欄位進行的。注意該時間不是單個SQL跑的時間，而是監控範圍內SQL執行次數的總和時間。單位時間為秒。Elapsed Time = CPU Time + Wait Time

            CPU Time(s): 為SQL語句執行時CPU佔用時間總時長，此時間會小於等於Elapsed Time時間。單位時間為秒。

            Executions: SQL語句在監控範圍內的執行次數總計。

            Elap per Exec(s): 執行一次SQL的平均時間。單位時間為秒。

            % Total DB Time: 為SQL的Elapsed Time時間佔資料庫總時間的百分比。

            SQL ID: SQL語句的ID編號，點選之後就能導航到下邊的SQL詳細列表中，點選IE的返回可以回到當前SQL ID的地方。

            SQL Module: 顯示該SQL是用什麼方式連線到資料庫執行的，如果是用SQL*Plus或者PL/SQL連結上來的那基本上都是有人在除錯程式。一般用前臺應用連結過來執行的sql該位置為空。

            SQL Text: 簡單的sql提示，詳細的需要點選SQL ID。

4.2 SQL ordered by CPU Time:

            記錄了執行佔CPU時間總和時間最長的TOP SQL(請注意是監控範圍內該SQL的執行佔CPU時間總和，而不是單次SQL執行時間)。
 

4.3 SQL ordered by Gets:

            記錄了執行佔總buffer gets(邏輯IO)的TOP SQL(請注意是監控範圍內該SQL的執行佔Gets總和，而不是單次SQL執行所佔的Gets)。

4.4 SQL ordered by Reads:

            記錄了執行佔總磁碟物理讀(物理IO)的TOP SQL(請注意是監控範圍內該SQL的執行佔磁碟物理讀總和，而不是單次SQL執行所佔的磁碟物理讀)。

 
4.5 SQL ordered by Executions:

            記錄了按照SQL的執行次數排序的TOP SQL。該排序可以看出監控範圍內的SQL執行次數。

4.6 SQL ordered by Parse Calls:

            記錄了SQL的軟解析次數的TOP SQL。說到軟解析(soft prase)和硬解析(hard prase)，就不能不說一下Oracle對sql的處理過程。
 

4.7 SQL ordered by Sharable Memory:

            記錄了SQL佔用library cache的大小的TOP SQL。Sharable Mem (b)：佔用library cache的大小，單位是byte。

4.8 SQL ordered by Version Count:

            記錄了SQL的開啟子游標的TOP SQL。

4.9 SQL ordered by Cluster Wait Time:

            記錄了叢集的等待時間的TOP SQL

 

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