为了系统化的梳理 AWR 的知识体系，我们整理了一个系列文章，希望从原理、使用到 AWR 报告的解读，给读者展示全面的 AWR 知识体系，本文是这个系列文章的第三篇。
Oracle提供了两个脚本，用于AWR数据的导出和转移导入，以方便远程的分析和工作协同。
awrextr.sql 脚本用于导出；awrload.sql 用户AWR数据的加载；而加载后的数据可以通过awrddrpi.sql来生成报告。也是非常方便高效的。
查看这两个脚本，可以看到其中主要调用了dbms_swrf_internal 包来完成数据的卸载和加载工作。
awrextr.sql的核心内容如下：
begin /* call PL/SQL routine to extract the data */ dbms_swrf_internal.set_awr_dbid(-2); dbms_swrf_internal.awr_extract(dmpfile => :dmpfile, dmpdir => :dmpdir, bid => :bid, eid => :eid, dbid => :dbid); dbms_swrf_internal.clear_awr_dbid; end; /
dbms_swrf_internal.awr_extract过程通过指定相关参数（包括导出文件名、Directory路径名、开始snapid、结束的snapid，dbid）来实现数据转储。
awrload.sql的核心内容如下：
begin /* call PL/SQL routine to load the data into the staging schema */ dbms_swrf_internal.set_awr_dbid(-2); dbms_swrf_internal.awr_load(schname => :schname, dmpfile => :dmpfile, dmpdir => :dmpdir); dbms_swrf_internal.clear_awr_dbid; end; / begin /* call PL/SQL routine to move the data into AWR */ dbms_swrf_internal.set_awr_dbid(-2); dbms_swrf_internal.move_to_awr(schname => :schname); dbms_swrf_internal.clear_awr_dbid; end; /
这个脚本通过dbms_swrf_internal.awr_load过程向数据库加载数据，其中需要定义一个临时的Schema名称，加载完成之后，再通过dbms_swrf_internal.move_to_awr将数据从临时Schema转移到正式Schema中。
了解这两个脚本的核心内容之后，其使用就一目了然了。
awrextr.sql 脚本非常易用，以下是一个测试输出：
[oracle@dbrac1 admin]$ sqlplus "/ as sysdba" @?/rdbms/admin/awrextr.sql SQL*Plus: Release 10.2.0.4.0 - Production on Sun Aug 1 23:39:37 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Disclaimer: This SQL/Plus script should only be called under the guidance of Oracle Support. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
执行的说明注释部分：
~~~~~~~~~~~~~ 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 ------------ ------------ ------------ * 3368702614 NAPDB dbrac1 * 3368702614 NAPDB dbrac2 The default database id is the local one: '3368702614'. To use this database id, press <return> to continue, otherwise enter an alternative. Enter value for dbid: Using 3368702614 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:
列举快照信息：
Listing all Completed Snapshots DB Name Snap Id Snap Started ------------ --------- ------------------ NAPDB 22492 01 Aug 2010 10:00 22493 01 Aug 2010 11:00 22494 01 Aug 2010 12:00 22495 01 Aug 2010 13:00 22496 01 Aug 2010 14:00 22497 01 Aug 2010 15:00 22498 01 Aug 2010 16:00 22499 01 Aug 2010 17:00 22500 01 Aug 2010 18:00 22501 01 Aug 2010 19:00 22502 01 Aug 2010 20:00 22503 01 Aug 2010 21:00 22504 01 Aug 2010 22:00 22505 01 Aug 2010 23:00
定义需要导出的快照范围：
Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 22338 Begin Snapshot Id specified: 22338 Enter value for end_snap: 22505 End Snapshot Id specified: 22505 定义DataPUMP需要的路径： Specify the Directory Name ~~~~~~~~~~~~~~~~~~~~~~~~~~ Directory Name Directory Path ------------------------------ ------------------------------------------------- ADMIN_DIR /opt/oracle/product/10.2.0/db/md/admin DUMP /opt/oracle/backup/ WORK_DIR /opt/oracle/product/10.2.0/db/work Choose a Directory Name from the above list (case-sensitive). Enter value for directory_name: DUMP Using the dump directory: DUMP
定义导出文件的名称：
Specify the Name of the Extract Dump File ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The prefix for the default dump file name is awrdat_22338_22505. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for file_name: Using the dump file prefix: awrdat_22338_22505 | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | The AWR extract dump file will be located | in the following directory/file: | /opt/oracle/backup/ | awrdat_22338_22505.dmp | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
完成所有条件定义之后，AWR开始Extract数据，抽取数据的实质是通过一个Query条件来限制访问的数据：
| *** 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:
| /opt/oracle/backup/
| awrdat_22338_22505.log
|
WHERE (dbid, snap_id) IN (SELECT dbid, snap_id FROM SYS.WRM$_SNAPSHOT WHERE DBID = 3368702614 AND SNAP_ID >=
22338 AND SNAP_ID <= 22505 AND STATUS = 0 AND BL_MOVED = 1)
Table List String Length: 2212
('WRM$_WR_CONTROL', 'WRM$_DATABASE_INSTANCE', 'WRM$_SNAPSHOT', ....'WRH$_ACTIVE_SESSION_HISTORY_BL')
Starting "SYS"."SYS_EXPORT_TABLE_01":
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 125.4 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SYS"."WRH$_SQL_PLAN" 1.425 MB 3969 rows
. . exported "SYS"."WRH$_WAITCLASSMETRIC_HISTORY" 5.969 MB 88231 rows
......................
. . exported "SYS"."WRR$_FILTERS" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/opt/oracle/backup/awrdat_22338_22505.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 23:41:54完成数据抽取后，我们就可以获得一个转储的导出文件。比如将生产库的采样数据导入测试库，就可以比较两者或多者之间的性能差异或变化，是非常有效的采样数据利用手段。
将导出文件转移到目标数据库或性能分析数据库，可以通过执行awrload.sql导入到数据库中。在导入之前，首先创建一个目录指向包含导出文件的路径，如：
SQL> create or replace directory eygle as 'E:\AWRData'; Directory created.
然后调用awrload.sql脚本执行导入：
SQL> @?/rdbms/admin/awrload ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Disclaimer: This SQL/Plus script should only be called under the guidance of Oracle Support. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~ 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 D:\oracle\admin\enmo\dpdump\ EYGLE E:\AWRData ORACLE_OCM_CONFIG_DIR D:\oracle\10.2.0\ccr\state Choose a Directory Name from the list above (case-sensitive). Enter value for directory_name: EYGLE Using the dump directory: EYGLE
首先列出的是目录名，选择我们之前建好，指向包含导出文件的路径。
然后需要导入的文件名称，注意输入文件名不需要包含后缀，否则会报错：
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: awrdat_46145_46197 Loading from the file name: awrdat_46145_46197.dmp
然后有一个关键步骤是创建一个中间转换Schema，缺省的名称是AWR_STAGE，这个选择缺省即可，转换完成之后会自动删除：
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 ------------------------------ --------- ------------------ EYGLE PERMANENT SYSAUX PERMANENT * USERS 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 ------------------------------ --------- ----------------------- TEMP TEMPORARY * Pressing <return> will result in the database's default temporary tablespace (identified by *) being used. Enter value for temporary_tablespace: Using tablespace TEMP as the temporary tablespace for AWR_STAGE
在选择了缺省表空间及临时表空间之后，创建用户和导入的进程自动开始：
... Creating AWR_STAGE user | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | Loading the AWR data from the following | directory/file: | E:\AWRData | awrdat_46145_46197.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: | E:\ AWRData | awrdat_46145_46197.log
导入的基本原则是和原来的数据不产生冲突，所以大量的判断子句被加入进来：
WHERE (DBID, SNAP_ID) NOT IN (SELECT DBID, SNAP_ID FROM AWR_STAGE.WRH$_SERVICE_STAT) WHERE (DBID, SNAP_ID) NOT IN (SELECT DBID, SNAP_ID FROM AWR_STAGE.WRH$_SERVICE_STAT_BL) WHERE (DBID, SNAP_ID) NOT IN (SELECT DBID, SNAP_ID FROM AWR_STAGE.WRH$_SYSMETRIC_HISTORY) WHERE (DBID, SNAP_ID) NOT IN (SELECT DBID, SNAP_ID FROM AWR_STAGE.WRH$_FILEMETRIC_HISTORY) WHERE (DBID, SNAP_ID) NOT IN (SELECT DBID, SNAP_ID FROM AWR_STAGE.WRH$_WAITCLASSMETRIC_HISTORY) WHERE (DBID, SNAP_ID) NOT IN (SELECT DBID, SNAP_ID FROM AWR_STAGE.WRH$_TABLESPACE_STAT) WHERE (DBID, SNAP_ID) NOT IN (SELECT DBID, SNAP_ID FROM AWR_STAGE.WRH$_TABLESPACE_STAT_BL) WHERE (DBID, SNAP_ID) NOT IN (SELECT DBID, SNAP_ID FROM AWR_STAGE.WRH$_LOG)
最后Oracle通过INSERT APPEND的方式向SYS对象追加数据，然后删除临时用户，完成导入工作：
INSERT /*+ APPEND */ INTO SYS.WRM$_SNAP_ERROR (SNAP_ID, DBID, INSTANCE_NUMBER, TABLE_NAME, ERROR_NUMBER) SELECT SNAP_ID, DBID, INSTANCE_NUMBER, TABLE_NAME, ERROR_NUMBER FROM AWR_STAGE.WRM$_SNAP_ERROR WHERE (DBID, SNAP_ID, INSTANCE_NUMBER, TABLE_NAME) NOT IN (SELECT DBID, SNAP_ID, INSTANCE_NUMBER, TABLE_NAME FROM SYS.WRM$_SNAP_ERROR) UPDATE SYS.WRM$_SNAPSHOT s1 SET status = 0, error_count = (SELECT count(*) FROM SYS.wrm$_snap_error s2 WHERE s1.dbid = s2.dbid AND s1.snap_id = s2.snap_id AND s1.instance_number = s2.instance_number) WHERE status = 1 AND BITAND(snap_flag, 2) != 0 AND (dbid, snap_id) IN (SELECT dbid, snap_id FROM AWR_STAGE.WRM$_SNAPSHOT) Finished MOVE_TO_AWR procedure ... Dropping AWR_STAGE user End of AWR Load
注意：以上这个过程在Oracle 10.2.0.4及10.2.0.5中存在问题，数据导入非常缓慢，几百M的文件导入可能就需要数个小时的时间，在11g中及10.2.0.4之前的版本中不存在这个问题。10g的AWR数据可以导入到11g的数据库中，以下是一个导入范例：
SQL> create or replace directory dadi as 'E:\AWRStat'; SQL> @?/rdbms/admin/awrload ~~~~~~~~~~ Specify the Directory Name ~~~~~~~~~~~~~~~~~~~~~~~~~~ Directory Name Directory Path ------------------------------ ------------------------------------------------- DADI E:\AWRStat DATA_PUMP_DIR D:\oracle\admin\eyglee\dpdump\ Choose a Directory Name from the list above (case-sensitive). Enter value for directory_name: DADI Using the dump directory: DADI Enter value for file_name: awrdat_49018_50602 Loading from the file name: awrdat_49018_50602.dmp Enter value for schema_name: Using the staging schema name: AWR_STAGE TABLESPACE_NAME CONTENTS DEFAULT TABLESPACE ------------------------------ --------- ------------------ SYSAUX PERMANENT * USERS PERMANENT 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 ------------------------------ --------- ----------------------- TEMP TEMPORARY * Pressing <return> will result in the database's default temporary tablespace (identified by *) being used. Enter value for temporary_tablespace: Using tablespace TEMP as the temporary tablespace for AWR_STAGE ... Creating AWR_STAGE user | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | Loading the AWR data from the following | directory/file: | E:\AWRStat | awrdat_49018_50602.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: | E:\AWRStat | awrdat_49018_50602.log | ... Dropping AWR_STAGE user End of AWR Load
导入的AWR数据，同样可以生成AWR报告，通过调用awrrpti.sql脚本即可，这个脚本会要求输入DBID和实例号，定义导入数据库相应的信息即可生成相应的报告。
SQL> @?/rdbms/admin/awrrpti 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: Type Specified: html Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ 3944144691 1 CCICDB ccicdb ccicdbsrv5 * 572103006 1 ENMO enmo EYGLEE Enter value for dbid: 3944144691 Using 3944144691 for database Id Enter value for inst_num: 1 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 <return> without specifying a number lists all completed snapshots. Enter value for num_days: 2 Listing the last 2 days of Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level ------------ ------------ --------- ------------------ ----- ccicdb CCICDB 46421 19 Oct 2010 00:00 1 46422 19 Oct 2010 00:15 1 46423 19 Oct 2010 00:30 1 46424 19 Oct 2010 00:45 1
对于RAC集群环境，也可以通过调用awrrpti.sql脚本，按照不同的实例号来生成不同实例的AWR报告。
如果希望了解AWR数据的存储和分布情况，可以调用awrinfo.sql来生成关于AWR数据的报告，根据报告里的snap_id分布情况，你可以选择通过手工来删除一定范围的采样数据：
SQL> select min(snap_id), max(snap_id) 2 from dba_hist_snapshot 3 where dbid = 3944144691; MIN(SNAP_ID) MAX(SNAP_ID) ------------ ------------ 45189 47802 SQL> exec dbms_workload_repository.drop_snapshot_range(45189, 47802, 3944144691) PL/SQL procedure successfully completed. SQL> select * from dba_hist_snapshot where dbid = 3944144691; no rows selected
这个操作很快可以完成，如果检查数据字典可以发现，Oracle仅仅修改了对应SNAPSHOT的状态，而并没有真正删除快照（在11gR2最新的版本中，会执行直接删除操作）：
SQL> select dbid, status, count(*) 2 from wrm$_snapshot 3 group by dbid, status; DBID STATUS COUNT(*) -------------- ---------------- -------------------- 3812548755 0 89 96312462 0 50 3944144691 2 2614
检查DBA_HIST_SNAPSHOT视图可以发现，这个视图的定义是只显示STATUS为0的记录：
SQL> select text 2 from dba_views 3 where view_name = 'DBA_HIST_SNAPSHOT'; TEXT -------------------------------------------------------------------------------- select snap_id, dbid, instance_number, startup_time, begin_interval_time, end_interval_time, flush_elapsed, snap_level, error_count from WRM$_SNAPSHOT where status = 0
如果需要彻底清除某个实例的AWR数据，可以通如下命令指定DBID，则该数据库的所有采样都将被清除：
SQL> exec dbms_swrf_internal.unregister_database(3944144691) PL/SQL procedure successfully completed.
最后修改时间：2019-08-30 11:25:23
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
