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