首頁 > 文章 > 資料庫 > Oracle AWR的管理與維護詳解

Oracle AWR的管理與維護詳解

小云云原創: 2017-12-11 15:10:461656瀏覽

AWR是Automatic Workload Repository的簡稱，中文叫著自動工作資料檔案庫。對於AWR的管理，主要是針對快照和基線的管理而言。例如設定快照的間隔，刪除快照，設定快照的保留時間。那對於基線而言則是基線的創建，刪除，自動創建等。本文主要描述這2部分的內容。本文主要為大家介紹了關於Oracle AWR（自動工作量資料檔案庫）管理與維護的相關資料，文中透過範例程式碼介紹的非常詳細，對大家的學習或工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧。

一、快照管理

預設情況下，Oracle資料庫每小時產生一次快照，並在工作負載庫中保留8天的統計資料。必要時，你可以使用dbms_workload_repository程式手動創建，刪除或修改快照等。可以使用OEM以及dbms_workload_repository套件管理快照。

1. 手動建立快照

使用下列程序手動建立快照，建立完成後可在資料字典DBA_HIST_SNAPSHOT查看所有快照相關資訊。

BEGIN
 DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

SELECT snap_id,
   dbid,
   begin_interval_time,
   end_interval_time
FROM  dba_hist_snapshot
ORDER BY end_interval_time DESC;

 SNAP_ID  DBID BEGIN_INTERVAL_TIME   END_INTERVAL_TIME
---------- ---------- ---------------------------- --------------------------
  164 41924548 14-APR-17 09.38.19.467 AM 14-APR-17 10.51.21.886 AM
  163 41924548 14-APR-17 09.00.10.470 AM 14-APR-17 09.38.19.467 AM
  162 41924548 14-APR-17 08.00.07.242 AM 14-APR-17 09.00.10.470 AM
  161 41924548 14-APR-17 07.00.04.120 AM 14-APR-17 08.00.07.242 AM

2. 刪除快照

# 以下程序將刪除snap_id從162到164的快照。在未指定41924548時，使用目前預設的資料庫

BEGIN
 DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 162,
       high_snap_id => 164, dbid => 41924548);
END;
/

3. 修改快照的保留間隔

--查看当前的保留策略，如下，为缺省值，采集间隔为1小时，保留8天
SQL> select * from dba_hist_wr_control;

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

--下面将其修改为保留5天，采集间隔为30分钟,topnsql为50条
BEGIN
 DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 10*24*60,
     interval => 30, topnsql => 50, dbid => 41924548);
END;
/

--查看修改后的结果
SQL> select * from dba_hist_wr_control;

  DBID SNAP_INTERVAL  RETENTION   TOPNSQL
---------- -------------------- ------------------ ----------
 41924548 +00000 00:30:00.0 +00010 00:00:00.0 50

二、基準管理

AWR基準可以手動建立和刪除，也可以自動建立和刪除。對於自動建立基線則是首先要建立基線樣本才能實現自動建立基線。主要描述如下。

1. 建立基準

基線的建立依賴快照，因此在建立之前應該得到所需時間範圍的snap_id，可以透過查詢資料字典DBA_HIST_SNAPSHOT取得，同時在基準建立完成後可以從資料字典DBA_HIST_BASELINE查詢基線相關資訊。

BEGIN
 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 160,
     end_snap_id => 161, baseline_name => 'peak_baseline',
     dbid => 41924548, expiration => 10);
END;
/

SQL> SELECT baseline_id,
 2   baseline_name,
 3   baseline_type,
 4   expiration,
 5   creation_time
 6 FROM dba_hist_baseline;

BASELINE_ID BASELINE_NAME   BASELINE_TYPE EXPIRATION CREATION_
----------- ----------------------- ------------- ---------- ---------
   1 peak_baseline   STATIC    10 14-APR-17
   0 SYSTEM_MOVING_WINDOW MOVING_WINDOW   07-APR-17

--基于特定时间创建基线，如下示例
BEGIN
 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (
  start_time  => TO_DATE ('2017-04-14 6:00:00', 'yyyy-mm-dd hh24:mi:ss'),
  end_time  => TO_DATE ('2017-04-14 8:00:00', 'yyyy-mm-dd hh24:mi:ss'),
  baseline_name => 'peak_baseline2',
  expiration  => 10);
END;
/

在上面的範例中，創建了一個範圍為160-161的，名字為peak_baseline的基線，保留時間為10天。在超過10天后，該基線會被刪除，相應的快照也被刪除。如果為指定失效期，則該基線以及相應的快照永久保留。

2. 刪除基線

BEGIN
 DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak_baseline',
     cascade => FALSE, dbid => 41924548);
END;
/

# 在上面的範例中將刪除名為peak_baseline的基線，同時指定了cascade為false，也是說在刪除基線的同時，不刪除對應的快照。

3. 基準重新命名

BEGIN
 DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (
     old_baseline_name => 'peak_baseline',
     new_baseline_name => 'peak_mondays',
     dbid => 41924548);
END;
/

--验证结果
SQL> SELECT baseline_id,
 2   baseline_name,
 3   baseline_type,
 4   expiration,
 5   creation_time
 6 FROM dba_hist_baseline;

BASELINE_ID BASELINE_NAME   BASELINE_TYPE EXPIRATION CREATION_
----------- ----------------------- ------------- ---------- ---------
   1 peak_mondays   STATIC    10 14-APR-17
   0 SYSTEM_MOVING_WINDOW MOVING_WINDOW   07-APR-17

# 在上面的範例中，將基線的名稱由peak_baseline修改成peak_mondays。

4. 修改預設移動視窗基線保留值

--查看缺省的window_size
SELECT baseline_name, baseline_type, moving_window_size
FROM dba_hist_baseline
WHERE baseline_name = 'SYSTEM_MOVING_WINDOW';

BASELINE_NAME   BASELINE_TYPE MOVING_WINDOW_SIZE
------------------------ ------------- ------------------
SYSTEM_MOVING_WINDOW  MOVING_WINDOW     8

BEGIN
 DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
     window_size => 7,
     dbid => 41924548);
END;
/

--window_size为天，只能够小于等于当前快照保留时间，否则报错，如下：

ERROR at line 1:
ORA-13541: system moving window baseline size (864000)
greater than retention (691200)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 686
ORA-06512: at line 2

5. 管理基準樣本

建立單一基準模板

BEGIN
 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
  start_time  => TO_DATE ('2017-04-14 17:00:00', 'yyyy-mm-dd hh24:mi:ss'),
  end_time  => TO_DATE ('2017-04-14 19:00:00', 'yyyy-mm-dd hh24:mi:ss'),
  baseline_name => 'baseline_140414',
  template_name => 'template_140414',
  expiration  => 10,
  dbid   => 41924548);
END;
/

--如果创建基线样本时间小于当前时间，则收到如下错误
ERROR at line 1:
ORA-13537: invalid input for create baseline template (end_time, end_time is less than SYSDATE)
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 768
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 818
ORA-06512: at line 2

在上面的範例中，我們建立了一個單一的基準樣本，並且指定了對應的時間範圍，基線的名稱及保留期限等。那麼在這個時間範圍內的相應的快照會被保留，同時這個基線可以用於後續在發現性能問題的時候進行比對。

建立重複基準樣本

重複的基準樣本指的是將來某個特定的時間範圍內，Oracle會參考這個設定的樣本自動為我們建立基準。例如，可以建立一個重複的基線樣本，使得在2017年每週一9:00-11:00自動產生基線。

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

BEGIN
 DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
  day_of_week   => 'monday',
  hour_in_day   => 9,
  duration    => 2,
  expiration    => 30,
  start_time    => '2017-04-14 09:00:00',
  end_time    => '2017-12-31 11:00:00',
  baseline_name_prefix => 'baseline_2017_mondays_',
  template_name   => 'template_2017_mondays',
  dbid     => 41924548);
END;
/

-- Author  : Leshami
-- Blog  : http://blog.csdn.net/leshami
-- QQ(Weixin) : 645746311

--查看已经创建的基线样本
SQL> select t.template_name,
 2  t.template_type,
 3  t.start_time,
 4  t.end_time,
 5  t.day_of_week,
 6  t.hour_in_day,
 7  t.duration
 8 from dba_hist_baseline_template t;

TEMPLATE_NAME   TEMPLATE_ START_TIME   END_TIME   DAY_OF_WE HOUR_IN_DAY DURATION
--------------------- --------- ------------------- ------------------- --------- ----------- --------
template_140414  SINGLE 2017-04-14 17:00:00 2017-04-14 19:00:00
template_2017_mondays REPEATING 2017-04-14 09:00:00 2017-12-31 11:00:00 MONDAY    17  3

在上面的範例中我們建立了一個重複從2017年4月14日起的每週一(day_of_week)會自動產生一個基線，其開始時間為9點(hour_in_day)，其持續時間為2小時(duration)，有效期為30天(expiration)，整個基線的起止時間範圍為：2017-04-14 09:00:00至2017-12- 31 11:00:00，同時也指定了基線樣本的名稱以及基線前綴名稱。

基線樣本的刪除

BEGIN
 DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (
     template_name => 'template_140414',
     dbid => 41924548);
END;
/

# 在上面的範例中我們刪除先前建立的基線樣本，透過指定樣本名稱即可。

三、AWR空間佔用

AWR快照的資料全部填入SYSAUX表空間。因此如果要保留的快照越久，代表需要佔用的SYSAUX空間越大。同時可以透過awrinfo.sql查看各個物件佔用SYSAUX的詳細資訊。

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

This script will report general AWR information
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrinfo.txt. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Using the report name awrinfo.txt
No errors.     -- Author : Leshami
No errors.     -- Blog : http://blog.csdn.net/leshami
~~~~~~~~~~~~~~~   -- QQ  : 645746311 
AWR INFO Report
~~~~~~~~~~~~~~~

Report generated at
19:48:53 on Apr 14, 2017 ( Friday ) in Timezone +08:00


Warning: Non Default AWR Setting!
--------------------------------------------------------------------------------
Snapshot interval is 60 minutes and Retention is 8 days


  DB_ID DB Name HOST_PLATFORM   INST STARTUP_TIME  LAST_ASH_SID PAR
------------ ------- ----------------------- ---- ----------------- ------------ ---
* 41924548 ORA11G ydq - Linux x86 64-bit  1 15:18:26 (04/07)  617410 NO

########################################################
(I) AWR Snapshots Information
########################################################

*****************************************************
(1a) SYSAUX usage - Schema breakdown (dba_segments)
*****************************************************
|
| Total SYSAUX size      617.4 MB ( 2% of 32,768.0 MB MAX with AUTOEXTEND ON )
|
| Schema SYS   occupies   206.6 MB ( 33.5% )
| Schema XDB   occupies   157.7 MB ( 25.5% )
| Schema APEX_030200 occupies    85.4 MB ( 13.8% )
| Schema MDSYS  occupies    73.9 MB ( 12.0% )

## 四、產生AWR報表

--單一實例下產生AWR報表

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

--RAC環境下產生AWR報告

SQL> @$ORACLE_HOME/rdbms/admin/awrgrpt.sql

--指定資料庫實例產生AWR報告

SQL> @$ORACLE_HOME/rdbms/admin/awrrpti.sql

--產生SQL語句AWR報告

SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql

--指定實例產生SQL語句AWR報告

SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpi.sql

#--產生比較的AWR報告

SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql

--RAC環境下產生比較的AWR報表

@$ORACLE_HOME/rdbms/admin/awrgdrpt.sql

五、AWR相關的重要視圖和資料字典

v$active_session_history : 顯示活躍的資料庫會話的活動，每秒採樣一次

v$metric和v$metric_history：

# 提供度量資料來追蹤系統性能。視圖被組織成好幾個群組，這些群組定義在v$metricgroup檢視中

DBA_HIST_ACTIVE_SESS_HISTORY

展示記憶體中活動會話歷史資訊

DBA_HIST_BASELINE
展示捕獲的基線的資訊

DBA_HIST_BASELINE_DETAILS

#DBA_HIST_BASELINE_TEMPLATE

基線模板相關資訊

DBA_HIST_DATABASE_INSTANCE

# 資料庫環境

##DBA_HIST_DB_CACHE_ADVICE#cache_ADVICE#c的物理讀取

DBA_HIST_DISPATCHER

每個snapshot下調度程序的資訊

DBA_HIST_DYN_REMASTER_STATS

動態動態作業的統計資訊##remastering程序的統計資料##dIO_STAT_Dv

以未見類型和功能來統計的歷史I/O資訊

DBA_HIST_SHARED_SERVER_SUMMARY

共享伺服器的統計資訊

DBA_HIST_SNAPSHOT

⃀

#DBA_HIST_SNAPSHOT

她問題訊息資訊資訊。

DBA_HIST_SQL_PLAN

執行計畫

DBA_HIST_WR_CONTROL

AWR控制資訊

Oracle AWR的管理與維護詳解

相關文章