一. AWR 說明

            Oracle 10g之前對資料庫做效能檢測使用statspack工具。 關於statspack的說明，參考我的Blog：

            statspack安裝使用 和 report 分析

            http://blog.csdn.net/tianlesoftware/archive/2009/10/16/4682329.aspx

 

            Oracle Database 10g 提供了一個新的工具：(AWR:Automatic Workload Repository)。Oracle 建議使用者用這個取代 Statspack。AWR 實質上是一個 Oracle 的內建工具，它採集與效能相關的統計資料，並從那些統計資料中匯出效能量度，以跟蹤潛在的問題。

            與 Statspack 不同，快照由一個稱為 MMON 的新的後臺程式及其從程式自動地每小時採集一次。為了節省空間，採集的資料在 7 天后自動清除。快照頻率和保留時間都可以由使用者修改。它產生兩種型別的輸出：文字格式（類似於 Statspack 報表的文字格式但來自於 AWR 資訊庫）和預設的 HTML 格式（擁有到部分和子部分的所有超連結），從而提供了非常使用者友好的報表。

 

            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)

在Oracle 11gR2裡禁用的功能如下：

STATISTICS_LEVEL specifies the level of collection for database and operating system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions.

The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.

When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.

Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:

• Automatic Workload Repository (AWR) Snapshots

• Automatic Database Diagnostic Monitor (ADDM)

• All server-generated alerts

• Automatic SGA Memory Management

• Automatic optimizer statistics collection

• Object level statistics

• End to End Application Tracing (V$CLIENT_STATS)

• Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)

• Service level statistics

• Buffer cache advisory

• MTTR advisory

• Shared pool sizing advisory

• Segment level statistics

• PGA Target advisory

• Timed statistics

• Monitoring of statistics

二.  AWR使用

SQL>@?/rdbms/admin/awrrpt.sql

 

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Would you like an HTML report, or a plain text report?

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

輸入 report_type 的值: 

 

Type Specified:  html

 

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing without

specifying a number lists all completed snapshots.

 

輸入 num_days 的值:  1

 

Listing the last day's Completed Snapshots

                                                        Snap

Instance     DB Name        Snap Id    Snap Started    Level

------------ ------------ --------- ------------------ -----

orcl10g      ORCL10G            142 03 7月  2009 08:11     1

                                143 03 7月  2009 09:00     1

                                144 03 7月  2009 10:00     1

                                145 03 7月  2009 11:00     1

                                146 03 7月  2009 12:01     1

 

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

輸入 begin_snap 的值:  142

Begin Snapshot Id specified: 142

輸入 end_snap 的值:  146

End   Snapshot Id specified: 146

 

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is awrrpt_1_142_146.html.  To use this name,

press to continue, otherwise enter an alternative.

輸入 report_name 的值:  D:/awrrpt_1_142_146.html

 

Report written to D:/awrrpt_1_142_146.html

 

 

三.  AWR 操作

 

3.1. 檢視當前的AWR儲存策略

 

SQL> col SNAP_INTERVAL format a20

SQL> col RETENTION format a20

SQL> select * from dba_hist_wr_control;

      DBID SNAP_INTERVAL        RETENTION            TOPNSQL

---------- -------------------- -------------------- ----------

262089084 +00000 01:00:00.0    +00007 00:00:00.0    DEFAULT

以上結果表示,每小時產生一個SNAPSHOT，保留7天。

 

3.2. 調整AWR配置

 

AWR配置都是透過dbms_workload_repository包進行配置。

 

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

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

 

3.2.2 關閉AWR，把interval設為0則關閉自動捕捉快照

SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);

 

3.2.3 手工建立一個快照

SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

 

3.2.4 檢視快照

SQL> select * from sys.wrh$_active_session_history

 

3.2.5 手工刪除指定範圍的快照

SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 973, high_snap_id => 999, dbid => 262089084);

 

3.2.6 建立baseline，儲存這些資料用於將來分析和比較

SQL> exec dbms_workload_repository.create_baseline(start_snap_id => 1003, end_snap_id => 1013, 'apply_interest_1');

 

3.2.7 刪除baseline

SQL> exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name => 'apply_interest_1', cascade => FALSE);

 

3.2.8 將AWR資料匯出並遷移到其它資料庫以便於以後分析

SQL> exec DBMS_SWRF_INTERNAL.AWR_EXTRACT(dmpfile => 'awr_data.dmp', mpdir => 'DIR_BDUMP', bid => 1003, eid => 1013);

 

3.2.9 遷移AWR資料檔案到其他資料庫

SQL> exec DBMS_SWRF_INTERNAL.AWR_LOAD(SCHNAME => 'AWR_TEST', dmpfile => 'awr_data.dmp', dmpdir => 'DIR_BDUMP');

把AWR資料轉移到SYS模式中：

SQL> exec DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME => 'TEST');

 

 

四. AWR 報告分析

 

這部分內容，可以參考statspack，這2個內容都差不多。

            statspack安裝使用 和 report 分析

            http://blog.csdn.net/tianlesoftware/archive/2009/10/16/4682329.aspx

 

 

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 SQ