探究AWR 第一篇

statspack相比awr算是比较通用,而且免费,可以在标准版,企业版中使用,awr是新企业版本中才有的,算是statspack的一个升级版,而且代码不公开。但是实现的功能和数据的采集要更丰富。 awr算是dba工作的必备工具。自己总结了一下,大概由以下几个方面来说明一下。 1.snapshot的管理 2.基线的管理 3.所需空间开销和设置 4.awr数据的迁移 5.生成awr相关的报告 6.awr相关的视图和基表 1.snapshot的管理 先来看看snapshot,这是列出近几天的snapshot,可以看出如果数据库重启过,在两个snap的交集处会有一个分隔的回车。

Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
TEST01       TEST01            15 13 Mar 2014 17:51      1

                                         16 15 Mar 2014 04:01      1
                                         17 15 Mar 2014 05:00      1
                                         18 15 Mar 2014 07:03      1
                                         19 15 Mar 2014 08:00      1
                                         20 16 Mar 2014 19:41      1
SQL> select to_char(startup_time,'yyyy-mm-dd hh24:mi:ss')from gv$instance;
TO_CHAR(STARTUP_TIM
-------------------
2014-03-15 03:50:27

要生成快照。可以直接用对应的Pl/sql包来实现。

SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.

可以看到,生成了一个新的快照。21:00的时候刚自动创建了一个快照,在一分钟之后手工创建了一个快照。

Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
TEST01       TEST01              16 15 Mar 2014 04:01      1
                                 17 15 Mar 2014 05:00      1
                                 18 15 Mar 2014 07:03      1
                                 19 15 Mar 2014 08:00      1
                                 20 16 Mar 2014 19:41      1
                                 21 16 Mar 2014 21:00      1
                                 22 16 Mar 2014 21:01      1

要删除快照可以使用如下的存储过程

PROCEDURE DROP_SNAPSHOT_RANGE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOW_SNAP_ID                    NUMBER                  IN
 HIGH_SNAP_ID                    NUMBER                  IN
 DBID                                  NUMBER                  IN     DEFAULT

这样会删除两个快照,21和22. SQL> exec dbms_workload_repository.drop_snapshot_range(low_snap_id=>21,high_snap_id=>22); PL/SQL procedure successfully completed. 重新创建一个快照,快照会从23开始。

SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
                                。。。。
                                 20 16 Mar 2014 19:41      1
                                 23 16 Mar 2014 21:06      1

2.基线

baseline(基线)的设置也是一个亮点,比如设定一个基准,然后隔了几个月之后,比如数据库突然出现了一些问题,然后领导肯定会问之前为什么好好的,这时候可以拿出之前的基准数据,比如这几个月数据量增长了多少,增长的情况,系统负载的增长,都可以很数字化的体现出来,会更有说服力。

基线的设定,可以使用如下的存储过程。

PROCEDURE CREATE_BASELINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 START_SNAP_ID                  NUMBER                  IN
 END_SNAP_ID                      NUMBER                  IN
 BASELINE_NAME                  VARCHAR2                IN
 DBID                                  NUMBER                  IN     DEFAULT
 EXPIRATION                        NUMBER                  IN     DEFAULT
SQL> exec dbms_workload_repository.create_baseline( START_SNAP_ID=>17,end_snap_id=>23,baseline_name=>'test_baseline');
PL/SQL procedure successfully completed.

3.所需空间开销和设置

awr采集的数据和指标对于排查问题还是很有帮助的,根据Oracle的描述,默认保留7天的awr数据来算。并发10个session需要大概200-300M的空间,对于一些性能敏感的系统,建议还是保留awr的时间要长一些。

默认awr的保留时间是8天,每一个小时生成一个快照。

SQL> col snap_interval for a20
SQL> col retention format a20
SQL> l
  1* select *from dba_hist_wr_control
SQL> /
      DBID             SNAP_INTERVAL        RETENTION            TOPNSQL
---------- -------------------- -------------------- ----------
3645037571 +00000 01:00:00.0+00008 00:00:00.0    DEFAULT

比如修改为30分钟采样一次,数据保留31天,可以这样设置

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

4.awr数据的迁移

可以把awr的数据迁移到别的库中做相关的诊断。迁移可以基本分为两部分。

1)数据的导出。

SQL> @?/rdbms/admin/awrextr.sql
~~~~~~~~~~~~~
AWR EXTRACT
~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will extract the AWR data for a range of snapshots  ~
~  into a dump file.  The script will prompt users for the         ~
~  following information:                                          ~
~     (1) database id                                              ~
~     (2) snapshot range to extract                                ~
~     (3) name of directory object                                 ~
~     (4) name of dump file                                        ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id     DB Name      Host
------------ ------------ ------------
* 3645037571 TEST01       rac1
The default database id is the local one: '3645037571'.  To use this
database id, press <return> to continue, otherwise enter an alternative.
Enter value for dbid: 
Using 3645037571 for Database ID
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 <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 5
Listing the last 5 days of Completed Snapshots
DB Name        Snap Id    Snap Started
------------ --------- ------------------
TEST01            15 13 Mar 2014 17:51
                    16 15 Mar 2014 04:01
                    17 15 Mar 2014 05:00
                    18 15 Mar 2014 07:03
                    19 15 Mar 2014 08:00
                    20 16 Mar 2014 19:41
                    23 16 Mar 2014 21:06
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 15
Begin Snapshot Id specified: 15
Enter value for end_snap: 23
End   Snapshot Id specified: 23
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR                  /u03/ora11g/product/11.2.0/dbhome_1/rdbms/log/
ORACLE_OCM_CONFIG_DIR          /u03/ora11g/product/11.2.0/dbhome_1/ccr/state
Choose a Directory Name from the above list (case-sensitive).
Enter value for directory_name: DATA_PUMP_DIR
Using the dump directory: DATA_PUMP_DIR
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_15_23.
To use this name, press <return> to continue, otherwise enter
an alternative.
Enter value for file_name: awrextr_TEST01_RECENT_5DAYS.dmp
Using the dump file prefix: awrextr_TEST01_RECENT_5DAYS.dmp
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   /u03/ora11g/product/11.2.0/dbhome_1/rdbms/log/
|   awrextr_TEST01_RECENT_5DAYS.dm.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Extract Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR extract operation can be
|  monitored in the following directory/file:
|   /u03/ora11g/product/11.2.0/dbhome_1/rdbms/log/
|   awrextr_TEST01_RECENT_5DAYS.dm.log
|
End of AWR Extract
-------------------------------------

数据加载

SQL> @?/rdbms/admin/awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object                            ~
~     (2) name of dump file                                   ~
~     (3) staging schema name to load AWR data into           ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR                  /u03/ora11g/product/11.2.0/dbhome_1/rdbms/log/
ORACLE_OCM_CONFIG_DIR          /u03/ora11g/product/11.2.0/dbhome_1/ccr/state
Choose a Directory Name from the list above (case-sensitive).
Enter value for directory_name: DATA_PUMP_DIR
Using the dump directory: DATA_PUMP_DIR
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
Enter value for file_name: awrextr_TEST01_RECENT_5DAYS.dm.dmp
Loading from the file name: awrextr_TEST01_RECENT_5DAYS.dm.dmp.dmp
Staging Schema to Load AWR Snapshot Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The next step is to create the staging schema
where the AWR snapshot data will be loaded.
After loading the data into the staging schema,
the data will be transferred into the AWR tables
in the SYS schema.
The default staging schema name is AWR_STAGE.
To use this name, press <return> to continue, otherwise enter
an alternative.
Enter value for schema_name:         
Using the staging schema name: AWR_STAGE
Choose the Default tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE users's default tablespace.  This is the
tablespace in which the AWR data will be staged.
TABLESPACE_NAME                CONTENTS  DEFAULT TABLESPACE
------------------------------ --------- ------------------
SYSAUX                         PERMANENT *
TEST_DATA1                     PERMANENT
TEST_DATA2                     PERMANENT
Pressing <return> will result in the recommended default
tablespace (identified by *) being used.
Enter value for default_tablespace: 
Using tablespace SYSAUX as the default tablespace for the AWR_STAGE
Choose the Temporary tablespace for the AWR_STAGE user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the AWR_STAGE user's temporary tablespace.
TABLESPACE_NAME                CONTENTS  DEFAULT TEMP TABLESPACE
------------------------------ --------- -----------------------
TEMPTS1                        TEMPORARY *
Pressing <return> will result in the database's default temporary
tablespace (identified by *) being used.
Enter value for temporary_tablespace: 
Using tablespace TEMPTS1 as the temporary tablespace for AWR_STAGE
... Creating AWR_STAGE user
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   /u03/ora11g/product/11.2.0/dbhome_1/rdbms/log/
|   awrextr_TEST01_RECENT_5DAYS.dm.dmp
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Load Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR load operation can be
|  monitored in the following directory/file:
|   /u03/ora11g/product/11.2.0/dbhome_1/rdbms/log/
|   awrextr_TEST01_RECENT_5DAYS.dm.log
|

它会创建一个临时的schema,把数据都放到那个schema下,然后再放入sys下面。完成后再删除临时的scema.

5.生成awr相关的报告

当然了以上只是基本的知识点,在实际中需要用awr来生成一些相关的报告。

@?/rdbms/admin/awrrpt.sql --最常见的生成awr报告的方式

@?/rdbms/admin/awrsqrpt.sql --生成awr报告中指定sql_id的执行计划

@?/rdbms/admin/awrddrpt.sql --比较两个awr报告

@?/rdbms/admin/awrrpti.sql --适用于rac环境等

6.awr相关的视图和基表

dba_hist_snapshot 显示历史的snapshot信息

dba_hist_sql_plan显示sql的执行计划

dba_hist_wr_control显示awr的一些基本信息

还有很多基表在$wrh开头的表中,里面还是有很多的关联。

如下是导出的日志。

。。。。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-03-18

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Oracle Net Services - Tracing and Logging at a Glance

    Oracle Net trace 用于跟踪或调试oracle连接故障,连接异常断开或者连接超时等情形,通过产生详细的跟踪信息来进行分析和诊断Oracle...

523
来自专栏乐沙弥的世界

Oracle 表空间时点恢复(TSPITR)

表空间时点恢复,是Oracle在基于冷备,热备恢复以外的一种以表空间为粒度的,不完全恢复的形式来将表空间恢复到过去某个特定的时间点的一种恢复方式。它整合了RMA...

662
来自专栏杨建荣的学习笔记

ASM无法启动的问题分析(一)(r7笔记第87天)

最近碰到了一个关于ASM无法启动的案例,当然这个案例比较长,准备分两篇来写。 问题的背景如下: 目前存在一套standalone的环境,采用了ASM作为存储管理...

40916
来自专栏杨建荣的学习笔记

一个Oracle bug的手工修复(r6笔记第59天)

在上周五的时候,本来一个例行巡检,想扩充一些表空间,结果弄巧成拙,因为一个drop datafile的操作直接导致了一主两备的两个备库MRP直接抛出了ORA-6...

2635
来自专栏数据和云

诊断案例:从实例挂起到归档失败和内存管理的蝴蝶效应

杨廷琨(yangtingkun) 云和恩墨 CTO 高级咨询顾问,Oracle ACE 总监,ITPUB Oracle 数据库管理版版主 编辑手记:在很多数据...

2819
来自专栏乐沙弥的世界

自定义 RMAN 显示的日期时间格式

      缺省情况下,使用RMAN备份与恢复界面仅仅显示的是日期,而没有具体的时间。有时候需要查看具体的时间,如查看当天多个Incarnation的情形。对于...

864
来自专栏杨建荣的学习笔记

11g rac配置scan ip(r6笔记第30天)

如果是从10g转战11g rac就会发现很多不同之处,其中一个比较大的改变就是在11g中有了一个新特性scan,其实这是一个简称,完整的名称为:SCAN(Si...

2444
来自专栏杨建荣的学习笔记

11g Dataguard中的snapshot standby特性(r8笔记第49天)

11g中的ADG特性本身已经非常有特色,促使很多对于10g中不太灵便的备库升级到11g,对于DBA是一大福利,那么还有一个福利就是snapshot standb...

2715
来自专栏乐沙弥的世界

ORA-02409:超时:分布式事务处理等待锁定ORA-02063

ORA-02409:超时:分布式事务处理等待锁定ORA-02063 一、错误现象与环境     前端应用程序运行时出现下面的错误提示: 事件添加失败:O...

832
来自专栏杨建荣的学习笔记

海量数据迁移之外部表加载(100天)

本地有一个小的环境,今天照例登上sqlplus,突然发现报了如下的错误。一看原来归档满了。我记得前几天做一个批量操作临时把temp文件resize了很大,限于本...

3188

扫码关注云+社区