1 导出awr数据
很多时候我们直接在客户机器上分析awr不太方便,需要通过收集客户awr信息到另一台机器上进行分析数据库性能等。这种情况下,就需要对客户的awr数据进行导出,然后导入到其他机器上,再进行深入分析。
1.1 查看所有awr脚本
[oracle@rac1 admin]$ cd $ORACLE_HOME/rdbms/admin
[oracle@rac1 admin]$ ls -l awr*
-rw-r--r-- 1 oracle oinstall 1148 Dec 1 2006 awrblmig.sql
-rw-r--r-- 1 oracle oinstall 20892 May 23 2005 awrddinp.sql
-rw-r--r-- 1 oracle oinstall 7450 Jul 25 2011 awrddrpi.sql
-rw-r--r-- 1 oracle oinstall 2005 May 27 2005 awrddrpt.sql
-rw-r--r-- 1 oracle oinstall 11082 Mar 24 2009 awrextr.sql
-rw-r--r-- 1 oracle oinstall 16457 Mar 13 2008 awrgdinp.sql
-rw-r--r-- 1 oracle oinstall 7393 Jul 25 2011 awrgdrpi.sql
-rw-r--r-- 1 oracle oinstall 1897 Apr 29 2009 awrgdrpt.sql
-rw-r--r-- 1 oracle oinstall 7440 Mar 13 2008 awrginp.sql
-rw-r--r-- 1 oracle oinstall 6444 Jul 25 2011 awrgrpti.sql
-rw-r--r-- 1 oracle oinstall 1523 Apr 29 2009 awrgrpt.sql
-rw-r--r-- 1 oracle oinstall 49166 Sep 1 2004 awrinfo.sql
-rw-r--r-- 1 oracle oinstall 2462 Jan 5 2005 awrinpnm.sql
-rw-r--r-- 1 oracle oinstall 8603 Mar 3 2006 awrinput.sql
-rw-r--r-- 1 oracle oinstall 10368 Jul 15 2009 awrload.sql
-rw-r--r-- 1 oracle oinstall 7704 Jul 25 2011 awrrpti.sql
-rw-r--r-- 1 oracle oinstall 1999 Oct 24 2003 awrrpt.sql
-rw-r--r-- 1 oracle oinstall 6803 Jul 25 2011 awrsqrpi.sql
-rw-r--r-- 1 oracle oinstall 1469 Jan 5 2005 awrsqrpt.sql
1.2 使用sys用户或DBA角色用户登录sqlplus
[oracle@rac1 admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Dec 16 10:43:02 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
1.3 执行awrextr.sql脚本导出awr数据
SQL> @?/rdbms/admin/awrextr
~~~~~~~~~~~~~
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 ~
1.4 按提示输入dbid
Databases in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id DB Name Host
------------ ------------ ------------
* 831572945 RACDB rac1
* 831572945 RACDB rac2
The default database id is the local one: ' 831572945'. To use this
database id, press to continue, otherwise enter an alternative.
Enter value for dbid: 831572945
Using 831572945 for Database ID
1.5 输入天数
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 without
specifying a number lists all completed snapshots.
Enter value for num_days: 7
Listing the last 7 days of Completed Snapshots
DB Name Snap Id Snap Started
------------ --------- ------------------
RACDB 1 12 Dec 2013 08:00
2 12 Dec 2013 09:00
3 14 Dec 2013 06:03
4 15 Dec 2013 09:47
5 15 Dec 2013 20:08
6 15 Dec 2013 21:00
7 15 Dec 2013 22:00
8 15 Dec 2013 23:00
9 16 Dec 2013 00:00
10 16 Dec 2013 01:00
11 16 Dec 2013 02:00
DB Name Snap Id Snap Started
------------ --------- ------------------
RACDB 12 16 Dec 2013 03:00
13 16 Dec 2013 04:00
14 16 Dec 2013 05:01
15 16 Dec 2013 06:00
16 16 Dec 2013 07:00
17 16 Dec 2013 08:00
18 16 Dec 2013 09:00
19 16 Dec 2013 10:00
1.6 输入开始和结束快照id
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 16
Begin Snapshot Id specified: 16
Enter value for end_snap: 19
End Snapshot Id specified: 19
1.7 输入生成AWR数据文件路径
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR /u01/app/oracle/product/11.2.0/dbhome_2/rdbms/log
/
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_2/ccr/state
XMLDIR /u01/app/oracle/product/11.2.0/dbhome_2/rdbms/xml
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
1.8 输入文件名
Specify the Name of the Extract Dump File
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The prefix for the default dump file name is awrdat_16_19.
To use this name, press to continue, otherwise enter
an alternative.
Enter value for file_name: myawrdata
Using the dump file prefix: myawrdata
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| The AWR extract dump file will be located
| in the following directory/file:
| /u01/app/oracle/product/11.2.0/dbhome_2/rdbms/log/
| myawrdata.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:
| /u01/app/oracle/product/11.2.0/dbhome_2/rdbms/log/
| myawrdata.log
|
End of AWR Extract
1.9 连接sftp将生成的dmp文件复制到本机
sftp> lcd d:/AWR_data/
sftp> get /u01/app/oracle/product/11.2.0/dbhome_2/rdbms/log/myawrdata.dmp
Downloading myawrdata.dmp from /u01/app/oracle/product/11.2.0/dbhome_2/rdbms/log/myawrdata.dmp
100% 7660KB 1532KB/s 00:00:05
/u01/app/oracle/product/11.2.0/dbhome_2/rdbms/log/myawrdata.dmp: 7843840 bytes transferred in 5 seconds (1532 KB/s)
2 导入awr数据
将生成的myawrdata.dmp文件导入到自己的数据库中。
2.1 登录本地数据库执行awrload.sql脚本
[oracle@ENMOEDU admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 26 14:08:20 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @?/rdbms/admin/awrload
~~~~~~~~~~
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 ~
2.2 输入路径dmp文件存放路径
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name Directory Path
------------------------------ -------------------------------------------------
DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
ma/sales_history/
DATA_PUMP_DIR /u01/app/oracle/admin/ENMOEDU/dpdump/
LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
ma/product_media/
MEDIA_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
ma/product_media/
ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
Directory Name Directory Path
------------------------------ -------------------------------------------------
QUEST_SOO_ADUMP_DIR /u01/app/oracle/admin/ENMOEDU/adump/
QUEST_SOO_BDUMP_DIR /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/
QUEST_SOO_CDUMP_DIR /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/cdump/
QUEST_SOO_UDUMP_DIR /u01/app/oracle/diag/rdbms/enmoedu/ENMOEDU/trace/
SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
ma/order_entry/
SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/sche
ma/order_entry//2002/Sep
XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
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
2.3 将dmp文件拷贝到DATA_PUMP_DIR路径下
sftp> lcd d:/AWR_data
sftp> cd /u01/app/oracle/admin/ENMOEDU/dpdump
sftp> put myawrdata.dmp
Uploading myawrdata.dmp to /u01/app/oracle/admin/ENMOEDU/dpdump/myawrdata.dmp
100% 7660KB 7660KB/s 00:00:00
d:/AWR_data/myawrdata.dmp: 7843840 bytes transferred in 0 seconds (7660 KB/s)
2.4 输入文件名
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: myawrdata
Loading from the file name: myawrdata.dmp
2.5 输入临时用户名(默认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 to continue, otherwise enter
an alternative.
Enter value for schema_name:
Using the staging schema name: AWR_STAGE
2.6 输入默认表空间（默认SYSAUX）
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
------------------------------ --------- ------------------
EXAMPLE PERMANENT
MGMT_AD4J_TS PERMANENT
MGMT_ECM_DEPOT_TS PERMANENT
MGMT_TABLESPACE PERMANENT
SYSAUX PERMANENT *
TEST PERMANENT
USERS PERMANENT
Pressing 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
2.7 输入临时表空间（默认TEMP）
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 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
2.8 等待导入完成
... Creating AWR_STAGE user
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Loading the AWR data from the following
| directory/file:
| /u01/app/oracle/admin/ENMOEDU/dpdump/
| myawrdata.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:
| /u01/app/oracle/admin/ENMOEDU/dpdump/
| myawrdata.log
|
... Dropping AWR_STAGE user
End of AWR Load
3 查看awr报告
3.1 执行awrrpti.sql脚本查看awr报告
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
3.2 输入dbid
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ -------- ------------ ------------ ------------
* 87396644 1 ENMOEDU ENMOEDU ENMOEDU
831572945 1 RACDB RACDB1 rac1
Enter value for dbid: 831572945
Using 831572945 for database Id
3.3 输入实例号
Enter value for inst_num: 1
Using 1 for instance number
3.4 输入日期（不填显示全部）
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 without
specifying a number lists all completed snapshots.
Enter value for num_days:
Listing all Completed Snapshots
3.5 输入快照开始和结束id
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
RACDB1 RACDB 16 16 Dec 2013 07:00 1
17 16 Dec 2013 08:00 1
18 16 Dec 2013 09:00 1
19 16 Dec 2013 10:00 1
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 16
Begin Snapshot Id specified: 16
Enter value for end_snap: 19
End Snapshot Id specified: 19
3.6 输入将生成的awr报告名称
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_16_19.html. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name: awr_racdb1.html
Using the report name awr_racdb1.html
…….(此处略)
Report written to awr_racdb1.html
3.7 将生成的awr拷贝到本机
sftp> cd /u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin
sftp> lcd d:/AWR_data
sftp> ls *.html
awr_racdb1.html
sftp> get awr_racdb1.html
Downloading awr_racdb1.html from /u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/awr_racdb1.html
100% 522KB 522KB/s 00:00:00
/u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/awr_racdb1.html: 535105 bytes transferred in 0 seconds (522 KB/s)
附：DBMS_XPLAN中的display_awr函数
DBMS_XPLAN包包括一系列函数，主要是用于显示特定SQL语句的执行计划, 对于awr中的执行计划，则是用display_awr函数显示存储在AWR历史数据的执行计划。
例如：select *from table(DBMS_XPLAN. display_awr(SQL_ID, PLAN_HASH_VALUE, DB_ID, FORMAT));
DISPLAY_AWR函数提示：要正常调用DISPLAY_AWR参数，必须对以下视图有权限：DBA_HIST_SQL_PLAN和DBA_HIST_SQLTEXT的SELECT。
参数描述：
SQL_ID：所显示执行计划的SQL语句的ID。该ID可以从DBA_HIST_SQL_PLAN.SQL_ID或DBA_HIST_SQLTEXT.SQL_ID获得，该参数必须指定非空值，没有默认值；
PLAN_HASH_VALUE：执行计划的哈希值。我们之前提到，每个执行计划都有一个哈希值。通过该值，可以显示SQL语句的特定执行计划。如果该参数未指定或为NULL，则会显示语句的所有执行计划；
DB_ID：指定显示哪个数据库的执行计划，默认为本地数据库ID；
提示：我们可以将其他数据库的AWR数据导入本地数据库进行分析。
FORMAT：格式化控制字符串。与DISPLAY的相同选项类似。
DBA_建瑾
2013.12.26
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29324876/viewspace-1064768/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/29324876/viewspace-1064768/
307
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
