awr导出分析数据

有些时候，我们分析客户的数据库的性能，需要请客户将AWR数据导出，然后导入本机数据库进行分析。下面简单介绍下怎样用awr导出分析数据。

一、在导出数据之前，一般需要创建以下路径

DATA_FILE_DIR

DATA_PUMP_DIR

EXP

IMP

 

SQL> create directory exp as 'd:\exp';

 

SQL> create directory data_file_dir as 'd:\dir';

SQL> create directory exp as 'd:\exp';

二、导出

SQL> @?/rdbms/admin/awrextr  --运行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
------------ ------------ ------------
* 1520519778 STREAM       STREAM
  2400249746 CNDERPDB     p5a1
  2400249746 CNDERPDB     p5b1

The default database id is the local one: '1520519778'.  To use this
database id, press <return> to continue, otherwise enter an alternative.

输入 dbid 的值:  1520519778

Using 1520519778 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.

输入 num_days 的值:  1     ---输入要导出数据的天数

Listing the last day's Completed Snapshots

DB Name        Snap Id    Snap Started
------------ --------- ------------------
STREAM               7 23 6月  2011 09:30
                     8 23 6月  2011 11:00
                     9 23 6月  2011 12:00
                    10 23 6月  2011 13:00

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:  7
Begin Snapshot Id specified: 7

输入 end_snap 的值:  10
End   Snapshot Id specified: 10

Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~

Directory Name                 Directory Path
------------------------------ -------------------------------------------------

DATA_FILE_DIR                  D:\dir
DATA_PUMP_DIR                  D:\oracle\admin\stream\dpdump\
EXP                            d:\exp
IMP                            d:\imp
ORACLECLRDIR                   D:\oracle\product\11.2.0\dbhome_1\bin\clr
ORACLE_OCM_CONFIG_DIR          D:\oracle\product\11.2.0\dbhome_1\ccr\state
XMLDIR                         D:\oracle\product\11.2.0\dbhome_1\rdbms\xml

Choose a Directory Name from the above list (case-sensitive).

输入 directory_name 的值:  DATA_FILE_DIR  --注意一定要大写

Using the dump directory: DATA_FILE_DIR

Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_7_10.
To use this name, press <return> to continue, otherwise enter
an alternative.

输入 file_name 的值:  awr_7_10  --导出文件的名字，不要加后缀

Using the dump file prefix: awr_7_10
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   D:\dir
|   awr_7_10.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:
|   D:\dir
|   awr_7_10.log
|
End of AWR Extract
SQL>

 

三、下面是部分导出的日志

启动 "SYS"."SYS_EXPORT_TABLE_01": 
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 375.3 MB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
     已完成 128 TABLE 个对象, 用时 11 秒
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
     已完成 121 CONSTRAINT 个对象, 用时 1 秒
处理对象类型 TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
     已完成 1 REF_CONSTRAINT 个对象, 用时 1 秒
. . 导出了 "SYS"."WRH$_SQL_PLAN"                       1.829 MB    5318 行
. . 导出了 "SYS"."WRH$_SQLTEXT"                        208.9 KB     202 行
. . 导出了 "SYS"."WRH$_ACTIVE_SESSION_HISTORY":"WRH$_ACTIVE_2400249746_0"  31.83 KB       0 行
. . 导出了 "SYS"."WRH$_LATCH":"WRH$_LATCH_2400249746_0"  10.99 KB       0 行
. . 导出了 "SYS"."WRH$_SQLSTAT":"WRH$_SQLSTA_2400249746_0"  36.64 KB       0 行
. . 导出了 "SYS"."WRH$_SEG_STAT":"WRH$_SEG_ST_2400249746_0"  22.76 KB       0 行
. . 导出了 "SYS"."WRH$_LATCH_MISSES_SUMMARY":"WRH$_LATCH__2400249746_0"  7.820 KB       0 行
. . 导出了 "SYS"."WRH$_PARAMETER":"WRH$_PARAME_2400249746_0"  7.406 KB       0 行
. . 导出了 "SYS"."WRH$_SEG_STAT_OBJ"                   44.17 KB     365 行
. . 导出了 "SYS"."WRH$_SYSSTAT":"WRH$_SYSSTA_2400249746_0"  6.593 KB       0 行
. . 导出了 "SYS"."WRH$_SYSMETRIC_SUMMARY"              59.72 KB     632 行
. . 导出了 "SYS"."WRH$_SQL_BIND_METADATA"              104.5 KB    1721 行
. . 导出了 "SYS"."WRH$_SYSTEM_EVENT":"WRH$_SYSTEM_2400249746_0"  8.632 KB       0 行

. . 导出了 "SYS"."WRM$_SNAP_ERROR"                         0 KB       0 行
已成功加载/卸载了主表 "SYS"."SYS_EXPORT_TABLE_01"
******************************************************************************
SYS.SYS_EXPORT_TABLE_01 的转储文件集为:
  D:\DIR\AWR_7_10.DMP
作业 "SYS"."SYS_EXPORT_TABLE_01" 已于 13:45:08 成功完成