ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle AWR(Automatic Workload Repository)使用

Oracle AWR(Automatic Workload Repository)使用

原创 Linux操作系统 作者:keeptrying 时间:2013-10-18 16:41:01 0 删除 编辑

 

一、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 without

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 to continue, otherwise enter an alternative.

 

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)、关闭AWRinterval设为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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-25

  • 博文量
    130
  • 访问量
    935630