探究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 条评论
登录 后参与评论

相关文章

来自专栏张高兴的博客

张高兴的 Windows 10 IoT 开发笔记:使用 Lightning 中的软件 PWM 驱动 RGB LED

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

dg broker配置的问题及分析 (r7笔记第22天)

今天在配置一个备库的时候碰到了一些问题,话说配置dg broker真没什么特别需要注意的细节了,本身已经给DBA省了很大的事儿了。 但是有时候就是会出现一些稀奇...

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

通过shell脚本得到数据库的基本信息(一)(r9笔记第89天)

今天写了个脚本,虽然实现的功能不多,但是个人感觉是一个好的开始,架子出来了,后面要补充的细节加进来就逐步完善了。 这个脚本的运行效果如下: OS Ver...

3304
来自专栏数据和云

DBA必备技能:RAC 如何安装新主机识别老存储恢复数据库

编辑手记:在 DBA 的日常工作中,经常会遇到 RAC 的一个节点失效的情况,或者通过存储迁移主机,如何恢复环境,加入集群是 DBA 的必备技能。 ? 张大朋...

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

归档问题导致的数据库无法启动 (80天)

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

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

oracle工具集初探(r4笔记第8天)

今天无意中看了下ORACLE_HOME/bin下面的东西,发现里面还是存在不少的东西。除了常用的sqlplus,tnsping,rman,exp/expdp,i...

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

数据库静默安装总结(r3笔记第58天)

在学习数据库的时候,不知道dbca的命令用了多少遍,但是越是安装也是觉得自己会的越少,因为图形界面的清晰直白反而不知道哪些准备工作是需要特别准备的,如果在远程支...

2768
来自专栏乐沙弥的世界

记一次奇怪的ORA-04028: cannot generate diana for object

      开发人员说新建了一个package,在编译的过程中出现了一些错误。提示为PL/SQL:ORA-00942: table or view does n...

341
来自专栏Objective-C

iOS-安装和使用 CocoaPods

3027
来自专栏沃趣科技

ASM 翻译系列第四十弹:理解ASM中 REQUIRED_MIRROR_FREE_MB和USABLE_FILE_MB的含义

原作者:Harald van Breederode 译者: 魏兴华 审核: 魏兴华 DBGeeK社区联合出品 原文链接:https://prutse...

34712

扫描关注云+社区