Oracle AWR(Automatic Workload Repository)使用
一、AWR說明
AWR(Automatic Workload Repository)是Oracle的一個內建工具,它採集與效能相關的統計資料,並從那些統計資料中匯出效能量度,以跟蹤潛在的問題。
快照由一個稱為MMON的後臺程式及其從程式自動地每小時採集一次。為了節省空間,採集的資料在7天后自動清除。快照頻率和保留時間都可以由使用者修改。它產生兩種型別的輸出:文字格式和預設的HTML格式,HTML格式擁有到部分和子部分的所有超連結,從而提供了非常友好的使用者報表。
AWR使用幾個表來儲存採集的統計資料。所有的表都儲存在SYSAUX表空間中的SYS模式下,並且以WRM$_*和WRH$_*的格式命名。WRM$_*型別的表儲存後設資料資訊(如檢查的資料庫和採集的快照),WRH$_*型別的表儲存實際採集的統計資料。M代表metadata,後設資料;H代表historaical,歷史資料。
在這些表上構建了幾種帶字首 DBA_HIST_ 的檢視,這些檢視可以用來編寫您自己的效能診斷工具。檢視的名稱直接與表相關,如:檢視DBA_HIST_SYSMETRIC_SUMMARY是在WRH$SYSMETRIC_SUMMARY表上構建的。
statistics_level引數:
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中禁用的功能如下:
STATICTICS_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 TYICAL 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 STATICTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality,including:
l Automatic Workload Repository(AWR) Snapshots
l Automatic Database Diagnostic Monitor(ADDM)
l All server-generated alerts
l Automatic SGA Memory Management
l Automatic optimizer statistics collection
l Object level statics
l End to End Application Tracing (V$CLIENT_STATS)
l Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)
l Service level statistics
l Buffer cache advisory
l MTTR advisory
l Shared pool sizing advisory
l Segment level statistics
l PGA Target advisory
l Timed statistics
l Monitorying of statistics
二、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
例:
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
823279453 RACDB 1 RACDB1
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'
Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 823279453 1 RACDB RACDB1 rac1
823279453 2 RACDB RACDB2 rac2
823279453 3 RACDB RACDB3 rac3
Using 823279453 for database Id
Using 1 for instance number
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
specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
RACDB1 RACDB 112 16 Oct 2013 09:56 1
113 16 Oct 2013 11:00 1
114 16 Oct 2013 12:00 1
115 16 Oct 2013 13:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 113
Begin Snapshot Id specified: 113
Enter value for end_snap: 115
End Snapshot Id specified: 115
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_113_115.html. To use this name,
press
Enter value for report_name: /home/oracle/awrrpt_1_113_115.html
Report written to /home/oracle/awrrpt_1_113_115.html
三、AWR操作
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
---------- -------------------- -------------------- ----
823279453 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT
以上結果表示,每小時產生一個snapshot,保留7天。
2、調整AWR配置
AWR配置都是通過dbms_workload_repository包進行配置。
(1)、調整AWR產生snapshot的頻率和保留策略。
如,將收集間隔時間改為30分鐘一次,並且保留5天時間(單位都是分鐘):
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>30,retention=>5*24*60);
PL/SQL procedure successfully completed.
(2)、關閉AWR,把interval設為0則關閉自動捕捉快照
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval=>0);
PL/SQL procedure successfully completed.
(3)、手工建立一個快照
SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
(4)、檢視快照
SQL> select * from sys.wrh$_active_session_history;
(5)、手工刪除指定範圍的快照
SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id => 973, high_snap_id => 999,dbid => 1354067853);
(6)、建立baseline,儲存這些資料用於將來分析和比較
SQL> exec dbms_workload_repository.create_baseline(start_snap_id => 70, end_snap_id => 80,baseline_name => 'oltp_peakload_b1');
檢視baseline:
select * from dba_hist_baseline
(7)、刪除baseline
SQL> exec dbms_workload_repository.drop_baseline(baseline_name => 'oltp_peakload_b1',cascade => false);
cascade指定為false,只刪除該baseline;
cascade指定為true,刪除該baseline的同時,刪除和該baseline相關的snapshots。
(8)、將AWR資料匯出並遷移到其它資料庫以便以後分析
SQL> exec dbms_swrf_internal.awr_extract(dmpfile => 'awr_data.dmp', dmpdir => 'DATA_PUMP_DIR', bid =
> 70, eid => 80);
其中,dmpdir指定的目錄為目錄物件。
檢視目錄物件:select * from dba_directories;
建立目錄物件:
SYS@ tsid > create directory dump_dir1 as 'E:\oracle\product\10.2.0\db_1\RDBMS\log';
Directory created.
刪除目錄物件:
SYS@ tsid > drop directory dump_dir1;
Directory dropped.
(9)、遷移AWR資料檔案到其它資料庫
SQL> exec dbms_swrf_internal.awr_load(schema => ‘AWR_TEST’, dmpfile => ‘awr_data.dmp’, dmpdif => ‘DATA_PUMP_DIR’);
把AWR資料轉移到SYS模式中:
SQL> exec dbms_swrf_internal.move_to_awr(schema => ‘TEST’);
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25744374/viewspace-774618/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Automatic Diagnostic Repository (ADR) with Oracle Net for 11gOracle
- [20210926]使用dbms_workload_repository.add_colored_sql.txtSQL
- Automatic Diagnostic Repository (ADR) in Oracle Database 11g Release 1 (ADRCI)OracleDatabase
- [20231017]使用dbms_workload_repository.add_colored_sql之2.txtSQL
- Oracle 12.2 使用Database Link優化Standby Database WorkloadOracleDatabase優化
- ORACLE AWROracle
- 2.10.3 使用 Oracle Automatic Storage Management (Oracle ASM) 克隆資料庫OracleASM資料庫
- Oracle 12c Automatic ReoptimizationOracle
- oracle工具 awr formatOracleORM
- 【AWR】Oracle資料庫建立awr基線Oracle資料庫
- 【AWR】Oracle批量生成awr報告指令碼Oracle指令碼
- oracle之 AWR固定基線Oracle
- 【AWR】Oracle awr相關檢視及體系介紹Oracle
- oracle 10G特性之awrOracle 10g
- Oracle 客戶端生成AWR方法Oracle客戶端
- Oracle 11g DG新特性--Automatic block repairOracleBloCAI
- 如何使用 Repository 模式模式
- Oracle生成awr報告操作步驟Oracle
- oracle awr快照點不記錄問題Oracle
- Oracle 11g關閉開啟AWROracle
- Oracle AWR無法生成快照(ORA-32701)Oracle
- Oracle 12.2 physical standby備庫收集AWR報告Oracle
- 詳解Oracle AWR執行日誌分析工具Oracle
- Oracle 11.2.0.4 awr過期快照無法自動清理Oracle
- 9. Oracle常用分析診斷工具——9.1. AWROracle
- 【效能調優】Oracle AWR報告指標全解析Oracle指標
- 【BUG】Oracle12c tablespace io statistics missing from awr reportOracle
- ASM(Automatic Storage Management)ASM
- 效能優化之達夢AWR使用優化
- OAM 深入解讀:使用 OAM 定義與管理 Kubernetes 內建 Workload
- ORACLE AWR效能報告和ASH效能報告的解讀Oracle
- [轉]Oracle資料庫ASH和AWR的簡單介紹Oracle資料庫
- Cannot retrieve repository metadata (repomd.xml) for repositoryXML
- Automatic Storage Management (ASM)(轉)ASM
- SAP QM Automatic Defect Recording
- React18 Automatic batchingReactBAT
- Bundle the repository
- 從Workload中優雅隔離Pod
- Cannot retrieve repository metadata (repomd.xml) for repository: baseXML