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

相关文章

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

一个普通数据库用户所能查到的"意料之外"的信息(r2笔记98天)

有时候限于工作环境的情况,大多数开发人员只得到了一个权限收到限制的数据库用户。 可能你都不知道你所拥有的数据库用户都能查到哪些你想象不到的数据库信息,其实你知道...

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

通过shell脚本抓取awr报告中的问题sql(r6笔记第78天)

awr报告中的sql明细部分基本必看的部分,尤其是SQL Order by Elapsed time这个部分,能够很清晰的看到哪些sql语句占用了较多的DB t...

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

awr性能问题排查第一篇(r3笔记第42天)

对于awr,里面涵盖的内容比较杂,有时候看报告的时候总是不知道该怎么下手。时间长了,可能会有一些阅读习惯或者心得。今天在看大师chris lawson的一篇博文...

2734
来自专栏龙首琴剑庐

mybaits3整合spring总结

1、maven定义properties: <org.springframework.version>4.3.1.RELEASE</org.springframe...

3187
来自专栏数据和云

返璞归真:如何判断一个初始化参数是否来自默认设置

? 杨廷琨(yangtingkun) 云和恩墨 CTO 高级咨询顾问,Oracle ACE 总监,ITPUB Oracle 数据库管理版版主 Oracle初...

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

一个MySQL死锁问题的复现

很久之前有一个同事问我一个关于死锁的问题,一直在拖这个事情,总算找了空来看看。 这个环境的事务隔离级别是RR,仔细看了下问题描述和背景,发现还真不是一...

3769
来自专栏Jerry的SAP技术分享

ABAP OPEN SQL里OPEN CURSOR和SELECT的比较

After the OPEN CURSOR statement, the database cursor is positioned in front of t...

3659
来自专栏数据库新发现

Oracle诊断案例-Job任务停止执行

Last Updated: Saturday, 2004-11-20 12:47 Eygle

642
来自专栏乐沙弥的世界

Oracle expdp 时遭遇ORA-39125 ORA-04063

    数据库在使用DataPump导出时碰到了ORA-39125与ORA-04063。完整的ORA-39125提示是Worker unexpected fat...

781
来自专栏乐沙弥的世界

使用带dblink方式的datapump迁移Oracle 10g到11g

      对于从Oracle 10g下迁移数据库到Oracle 11g,除了使用RMAN方式之外,我们可以使用带dblink的datapump方式来实现基于逻...

531

扫码关注云+社区