教你如何成為Oracle 10g OCP - 第十五章 自動化管理

15.1  自動化管理概述

1. 自動化管理的元件

A. AWR自動工作負載資訊庫(Automatic Workload Repository)
   AWR由多個schema物件組成，包含大約200多個表和多個程式包。在預設情況下每
   1個小時，Oracle伺服器會對整個資料庫生成一個快照(snapshot),並把快照資訊
   存入AWR相應的表裡(為了節省空間，採集的資料在 7 天后自動清除)。捕獲下來的
   資料可以由內建的自動化元件進行分析，也可以由DBA來分析。 AWR使用多個表來存
   儲收集的統計資料，它們都被放置在SYS這個方案中，儲存在一個新的特殊表空間
   SYSAUX上，並且以WRM$_*和WRH$_*的格式儲存。前面一種格式的表儲存諸如資料庫
   檢查和採集的快照等後設資料訊息，而後面一種格式的表儲存了實際收集的統計資料
  （M表示“Metadata後設資料”，H表示“Historical歷史”）。  另外還有多個以
   “DBA_HIST_”為字首，由這些表構造出的檢視。比如相關儲存時間的資訊可以從
    DBA_HIST_WR_CONTROL檢視中獲得。

B. 自動化任務： 透過分析AWR裡的資訊，自動化元件可以確定是否需要進行一些
   日常維護工作，比如每天進行備份，或者每天收集並重新整理最佳化器統計資訊等。備註
   一下，Oracle9i中必須手工設定table monitoring, 才能透過判斷table統計資訊
   是否過期從而進行統計分析，但是Oracle10g中透過設定statistics_level=typical
   或all 就可以自動收集統計資訊(在10g中表監控是啟用的)。

C. 伺服器告警資訊(Server Alert): 對於那些不能自動解決而需要DBA介入的工作，
   自動化元件會生成伺服器告警資訊，從而通知DBA .

D. 顧問(Advisor): 自動化元件裡包含多個顧問，比如有負責判斷記憶體大小的顧問，
   最佳化SQL的顧問等。這些顧問會自動分析AWR裡記錄的資訊，從中找出潛在的問題，
   並得出改進的意見。

E. 自動資料庫診斷監控(Automatic Database Diagnostic Monitor, ADDM),每次收
   集完AWR快照之後，都會啟動ADDM程式。ADDM程式負責對最近兩個快照(也就是最
   近1個小時)的資料庫的活動情況進行分析，判斷最近1小時是否出現潛在的問題，
   如果發現問題，會根據型別呼叫各個相關顧問，對發現的問題提出建議。

備註： 結合EM和AWR是非常方便的。

 

2. 自動化管理的功能由statistics_level引數決定，可以為該引數設定3個值。
  
Oracle10g, 11g 預設情況下 statistics_level 的值為 TYPICAL 。

SQL> show   parameter  statistics_level 

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

A. typical -- 預設值，啟用所有的自動化功能，並會為此收集資料庫裡所需要
   的相關資訊。收集的資訊包括： Buffer Cache Advice, MTTR Advice,Timed
   Statistics, Segment Level Statistics , PGA Advice .....等等，可以通
   過 select statistics_name , activation_level from v$statistics_level
   order by 2 ; 來查詢收集的資訊 。 Oracle建議使用預設值typical 。

B. all -- 如果設定為all, 那麼除了typical之外，還會收集額外的資訊，包括
   plan execution statistics 和Timed OS statistics(參考A中的SQL查詢語
   句).   在該設定下，可能為了收集診斷資訊而消耗過多伺服器資源。

C. basic -- 關閉所有自動化功能。

 

3. 自動化相關檢視 v$statistics_level檢視不同的設定能收集的不同資訊
   (以下例子是Oracle11g中得出的)。

SQL> select statistics_name , activation_level from v$statistics_level order by 2 ;

STATISTICS_NAME                                                  ACTIVAT
---------------------------------------------------------------- -------
Timed OS Statistics                                              ALL
Plan Execution Statistics                                        ALL
Timed Statistics                                                 TYPICAL
Segment Level Statistics                                         TYPICAL
PGA Advice                                                       TYPICAL
Shared Pool Advice                                               TYPICAL
Modification Monitoring                                          TYPICAL
Longops Statistics                                               TYPICAL
Bind Data Capture                                                TYPICAL
Ultrafast Latch Statistics                                       TYPICAL
Threshold-based Alerts                                           TYPICAL
Global Cache Statistics                                          TYPICAL
Active Session History                                           TYPICAL
Undo Advisor, Alerts and Fast Ramp up                            TYPICAL
Streams Pool Advice                                              TYPICAL
Time Model Events                                                TYPICAL
Plan Execution Sampling                                          TYPICAL
Automated Maintenance Tasks                                      TYPICAL
SQL Monitoring                                                   TYPICAL
Adaptive Thresholds Enabled                                      TYPICAL
V$IOSTAT_* statistics                                            TYPICAL
Buffer Cache Advice                                              TYPICAL 
MTTR Advice                                                      TYPICAL

 

參考資料： 
------------------------------------------------------------------------------

在 Oracle9i 中，您可以透過開啟表監視選項 (ALTER TABLE ...MONITORING) 然後
檢查這些表的檢視DBA_TAB_MODIFICATIONS，檢查表中的資料是否已明顯更改過，備註
：This view is populated only for tables with the MONITORING attribute。

在 10g 中，已不再使用 MONITORING 語句了。而透過設定初始化引數 STATISTIC_LEVEL
為 TYPICAL 或 ALL，就可以自動收集統計資訊。（預設值為 TYPICAL，因此可以隨即啟
用自動收集統計資訊的功能。）Oracle 資料庫 10g 具有一個預定義的排程程式作業，
名稱為 GATHER_STATS_JOB，它由 STATISTIC_LEVEL 引數的適當數值所啟用。

statistics_level 預設是typical，在10g中表監控是啟用的(預設建立table就是
monitoring屬性，9i下預設建立table是nomonitoring屬性)，強烈建議在10g中此
引數的值是typical。如果STATISTICS_LEVEL設定為basic，不僅不能監控表，而且
將禁掉如下一些10g的新功能：

ASH(Active Session History)
ASSM(Automatic Shared Memory Management)
AWR(Automatic Workload Repository)
ADDM(Automatic Database Diagnostic Monitor)

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

 

4.  透過自動分析AWR中的資料，資料庫能夠判斷是否進行一些日常的維護，比如
    收集最佳化器統計資訊等，Oracle10g中透過使用自動維護任務框架(automated
    maintenance tasks infrastructure) 來完成自動化任務。排程器使用預先
    定義的維護視窗來執行這些任務，需要定期進行維護的情況包括：

A. 收集最佳化器統計資訊

   SQL執行時，透過最佳化器對SQL進行解析得到最佳的執行計劃。最佳化器在生成
   執行計劃過程中，必須參考SQL語句引用到的表及列的資訊，這些資訊用於
   描述表中的資料的分佈情況，包括表中包含的資料行數，表中包含的資料
   塊的個數，列上有哪些索引等，這些資訊也叫做最佳化器統計資訊。統計資訊
   是存放在資料字典裡的靜態的資料，他們不會隨著表中的資料的變化而變化，
   只有收集完畢統計資訊後，它們才會被更新，從而體現出收集完統計資訊的
   那一個時間點上表裡資料的分佈情況。

B. 段顧問(segment advicor) 
  
   該顧問用來找出哪些segment佔用的空閒空間過多，從而可以透過執行shrink
   來釋放可用空間。

C. 自動備份任務 

 

15.2  AWR體系結構
    AWR是Oracle10g資料庫中的一個基礎架構，提供了收集資料庫診斷資訊，
對潛在問題進行分析和自我調整的功能。透過使用AWR, 資料庫伺服器提供了一
個自動收集歷史統計資訊的功能。

詳細可以參考： http://space.itpub.net/35489/viewspace-609528

在AWR中包括以下兩個部分：

1. 在記憶體中透過讀取動態效能檢視和資料字典中的資料，收集資料庫工作負載
資訊，資訊包括以下幾大類：

A. 基本統計資訊，比如最佳化器統計資訊、作業系統統計資訊如dba_hist_osstat
   檢視(來自v$osstat)等。
B. 指標值：衡量資料庫活動的各個指標值。
C. ASH (Active Session History): 記錄了歷史上session的活動情況，ASH非常
   有價值，後面將會談到。
D. 顧問得出的建議: 針對各個問題，顧問得出的結論也存放在AWR中。
E. 快照資訊：每隔一段時間(預設是60分鐘)，Oracle會對整個資料庫生成一個快
   照，快照中的資料來自於動態效能檢視和資料字典，這些快照資料存放在相應
   的表裡。

2. 每隔一段時間，透過一個名為MMON (Manageability Monitor)的後臺程式，將
   記憶體中的資料寫入到磁碟上的表空間中。AWR包含了上百個表， 都位於sys用
   戶下，並存放在sysaux表空間下。 不建議透過SQL直接訪問AWR相關表中的資料，
   而是透過Database Control或者dbms_workload_repository包來訪問AWR中資料。

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

備註： Oracle10g新後臺程式MMON及MMNL程式

在Oracle不同的文件中，對這兩個程式的解釋存在歧義。
MMON 應該是 Memory Monitor 的縮寫，但是在有的文件中被記錄為Manageability
Monitor ，這應當是10g早期版本中的稱呼，只不過後來發生了變更。

ASH的取樣資料是儲存在記憶體中。而分配給ASH的記憶體空間是有限的，當所分配
空間佔滿後，舊的記錄就會被覆蓋掉；而且資料庫重啟後，所有的這些ASH資訊
都會消失。這樣，對於長期檢測oracle的效能是不可能的。在Oracle10g中，提
供了永久保留ASH資訊的方法，這就是AWR（automatic workload repository）。

由於全部儲存ASH中的資訊是非常耗費時間和空間的，AWR採用的策略是：
MMON程式每小時對ASH (v$active_session_history)進行取樣一次，並將資訊儲存
到磁碟中，當ASH BUFFER滿的話MMNL程式會寫，並保留7天，7天后舊的記錄才會
被覆蓋。這些取樣資訊被儲存在表wrh$_active_session_history中。

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資訊寫出。

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

    每隔一段時間(預設60分鐘)，AWR都會收集一次快照。快照描述的是在一個特定時間
點上所捕獲的資料庫相關的統計資訊，由於Oracle庫中，幾乎所有的統計資訊體現的都
是截止到到目前的累計值，因此我們每隔一段時間就獲取一個時間點上的統計資訊，然
後將兩個時間點上的統計資訊相減，得到在這段時間裡，發生的資料庫活動的情況，這
為資料庫診斷提供了指導。 快照預設儲存一週。

    每個快照都會由一個唯一的快照ID(snap_id)標識，預設情況下，AWR每隔60分鐘收集
一次快照，該時間間隔可以根據需要進行調整。 由於Oracle自動化元件中的各個顧問
都會訪問快照中的資料，因此不同的時間間隔可能會影響顧問在診斷時的精確性。比如
如果收集快照間隔為3小時，可能時間間隔過長，從而丟失某些問題。所有快照的snap_id
以及相關屬性(快照建立時間等)可以從dba_hist_snapshot檢視中顯示出來。

備註： 兩次快照之間不能有資料庫的開關機，否則收集資訊無效。
Oracle提供以下命令可以修改快照收集時間間隔和儲存時間: 
execute dbms_workload_repository.modify_snapshot_settings(interval => 120,retention => 20160);
上述命令修改快照的收集間隔為2小時，儲存時間為兩週 (其中單位為分鐘)。

我們可以手工呼叫程式包來收集快照，比如呼叫儲存過程：
SQL > begin 
      dbms_workload_repository.create_snapshot();
      end;  /
我們也可以使用命令刪除指定的快照：
dbms_workload_repository.drop_snapshot_range(low_snap_id=>41,high_snap_id=>46);

 

AWR中的資料量 ---  

AWR中儲存的資料都在sysaux表空間中，AWR中收集的統計資訊的資料量取決於資料庫中
活動的session個數。假設，始終有10個活動session的系統來說，如果預設儲存7天，那麼
AWR儲存的資料大致為200M到300M .  比如我們一個active session保持在300個左右，
目前sysaux 表空間用到了2.5G左右。

我們可以使用rdbms/admin/utlsyxsz.sql 來顯示當前sysaux表空間裡各個元件的大小
尺寸。 我們還可以透過awrinfo.sql來生成一個報表，該報表用來顯示sysaux表空間
裡不同逐漸的增長率 。當然，如果AWR檢測到syaux表空間中的可用空間不足時，會
自動刪除那些最早生成的快照，併發出告警資訊。

我們可以透過指定收集快照的時間間隔和對快照設定保留策略來控制AWR中的資料量，
收集時間間隔越長，AWR中的資料量相對就少(因為是累計的) ， 預設保留7天資料，
MMON程式會在每天晚上定期刪除7天前的資料。

 

AWR系統表 --- 
AWR快照存放在以WR開頭的sys使用者下的系統表中,其中WR表示workload repository,
包括 WRI$_, WRH$_, WRM$_ , 其中I表示Internal, H 表示History, M表示Metadata.
這些表的資料都透過 DBA_HIST_* 檢視展現出來。

快照集(snapshot set) ---
如果將快照納入到快照集中，則快照不會被MMON程式自動刪除。快照集是讓我們保留
重要時間段的快照資料的一種方法 。 快照集通常用於保留重要的快照資料(比如作為
效能比較，作為基準線的快照)，可以一直保留直到快照集被刪除，建立的快照集可以
從dba_hist_baseline 檢視中顯示出來。透過執行如下命令建立快照集：

SQL> begin
       dbms_workload_repository.create_baseline(
        start_snap_id=>29 ,
        end_snap_id=>36 ,
        baseline_name=>'my_baseline'); 
     end ;
     /

刪除指定的快照集：

SQL> begin
       dbms_workload_repository.drop_baseline(
        baseline_name=>'my_baseline'); 
     end ;
     /