分类目录归档：Oracle性能优化

相关参数

SQL> select * from v$version;

BANNER
---------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> show parameter optimizer_mode;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ----------------
optimizer_mode                       string                 ALL_ROWS

SQL> show parameter cursor_sharing;

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ----------------
cursor_sharing                       string                 EXACT

SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  /
Enter value for param: _optim_peek_user_binds
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_optim_peek_user_binds%')

NAME                             VALUE                    DESCRIPTION
-------------------------------- ------------------------ ----------------------------------
_optim_peek_user_binds           TRUE                     enable peeking of user binds

创建模拟表

SQL> create table t_xifenfei(id number,name varchar2(30));

Table created.

SQL>  begin
  2     for i in 1..100000 loop
  3          insert into t_xifenfei values(i,'xifenfei');
  4      end loop;
  5      commit;
  6    end;
  7    /

PL/SQL procedure successfully completed.

SQL> update t_xifenfei SET name='www.xifenfei.com' where mod(id,20000)=0;

5 row updated.

SQL> commit;

Commit complete.

SQL> create index i_xifenfei on t_xifenfei(name);

Index created.

默认收集统计信息,查看执行计划

SQL> exec  DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL> set autot trace exp 
SQL> select id from t_xifenfei where name='xifenfei';

Execution Plan
----------------------------------------------------------
Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 50000 |   683K|   103   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI | 50000 |   683K|   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME"='xifenfei')

SQL> select id from t_xifenfei where name='www.xifenfei.com';

Execution Plan
----------------------------------------------------------
Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 50000 |   683K|   103   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI | 50000 |   683K|   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME"='www.xifenfei.com')
--这里可以发现,对于这样少量的列的情况，没有选择一个合适的执行计划

准确收集统计信息

SQL> exec  DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE,
    2 method_opt => 'FOR ALL COLUMNS SIZE 254',estimate_percent => 100);

PL/SQL procedure successfully completed.

再次查看执行计划

SQL> select id from t_xifenfei where name='www.xifenfei.com';


Execution Plan
----------------------------------------------------------
Plan hash value: 1926396081

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    14 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    14 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("NAME"='www.xifenfei.com')


Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
        320  consistent gets
          0  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

SQL> select id from t_xifenfei where name='xifenfei';

99995 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 99999 |  1367K|   103   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI | 99999 |  1367K|   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME"='xifenfei')


Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
       6970  consistent gets
          0  physical reads
          0  redo size
    1455968  bytes sent via SQL*Net to client
      73745  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      99995  rows processed
--通过这里可以看出在完整的收集表和index包括直方图信息后,数据库执行计划正常
--也说明一点:在数据列分布不均匀的时候,依靠数据库自动收集直方图还是不怎么拷贝.

使用AUTOTRACE测试

SQL> set autot trace exp
SQL> var a varchar2(30);
SQL> exec :a := 'www.xifenfei.com';

PL/SQL procedure successfully completed.

SQL> select id from t_xifenfei where name=:a;

Execution Plan
----------------------------------------------------------
Plan hash value: 548923532

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 50000 |   683K|   103   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI | 50000 |   683K|   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("NAME"=:A)
--这里可以发现11g的Bind Variable Peeking 没有使用正确的执行计划,其实这个是AUTOTRACE本身的bug导致

收集下面sql执行计划(peeking测试需要)get_plan.sql脚本

SQL> select * from t_xifenfei where name='wwww.xifenfei.com' and id=100;

no rows selected

SQL> @get_plan.sql

Rollback complete.

Enter value for hash_value: 2708637417
select * from t_xifenfei where name='wwww.xifenfei.com' and id=100

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    14 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     3 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=100)
   2 - access("NAME"='wwww.xifenfei.com')
SQL>  select * from t_xifenfei where name='xifenfei' and id=100;

        ID NAME
---------- ------------------------------------------------------------
       100 xifenfei

1 row selected.

SQL> @get_plan.sql

Rollback complete.

Enter value for hash_value: 1355242984
 select * from t_xifenfei where name='xifenfei' and id=100

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   103 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    14 |   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("ID"=100 AND "NAME"='xifenfei'))
--这里可以看到,两个执行计划都我们希望的

测试peeking功能

SQL> alter system flush shared_pool;

System altered.

SQL> select * from t_xifenfei where name='xifenfei' and id=100;

        ID NAME
---------- ------------------------------------------------------------
       100 xifenfei

1 row selected.

SQL> @get_plan.sql

Rollback complete.

Enter value for hash_value: 2860562673
select * from t_xifenfei where name='xifenfei' and id=100

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   103 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    14 |   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("ID"=100 AND "NAME"='xifenfei'))
SQL> var b varchar2(30);
SQL> exec :b := 'www.xifenfei.com';

PL/SQL procedure successfully completed.

SQL> select * from t_xifenfei where name=:b and id=100;

no rows selected

SQL> @get_plan.sql

Rollback complete.

Enter value for hash_value: 4157424768
select * from t_xifenfei where name=:b and id=100

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |       |       |   103 (100)|          |
|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    14 |   103   (1)| 00:00:02 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("ID"=100 AND "NAME"=:B))

--重新硬解析
SQL> alter system flush shared_pool;

System altered.

SQL> var b varchar2(30);
SQL> exec :b := 'www.xifenfei.com';

PL/SQL procedure successfully completed.

SQL> select * from t_xifenfei where name=:b and id=100;

no rows selected

SQL> @get_plan.sql

Rollback complete.

Enter value for hash_value: 4157424768
select * from t_xifenfei where name=:b and id=100

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    14 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     6 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=100)
   2 - access("NAME"=:B)
SQL> var b varchar2(30);
SQL> exec :b := 'xifenfei';

PL/SQL procedure successfully completed.

SQL> select * from t_xifenfei where name=:b and id=100;

        ID NAME
---------- ------------------------------------------------------------
       100 xifenfei

1 row selected.

SQL> @get_plan.sql

Rollback complete.

Enter value for hash_value: 4157424768
select * from t_xifenfei where name=:b and id=100

------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    14 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     6 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"=100)
   2 - access("NAME"=:B)
--虽然oracle 11g宣称在在Bind Variable Peeking上增强了很多,
--但是这里的实验,依然证明他存在问题,导致执行计划不正确

通过整体实验过程,证明几个问题：
1.默认的的DBMS_STATS收集统计信息不一定使得所有执行计划均正确,特别在数据很不均匀分布时.
2.AUTOTRACE不能跟踪Bind Variable Peeking
3.Bind Variable Peeking是在硬解析时候生效,虽然11g进行了改善,但是有些时候效果还是不明显,如果数据很不均匀,在发现sql语句很多不合适的时候,建议先删除该sql的执行计划,让其再次硬解析,碰碰运气,如果一直效果不好,建议不适用绑定参数形式(正确的执行计划,更多的硬解析)
4._optim_peek_user_binds参数可以关闭Bind Variable Peeking功能,很不推荐.

本实验室为了说明sql profile的使用方法,不去研讨sql的执行效率.通过sql profile的方法使得一条本该使用index的sql该走全表扫描.
创建模拟表

SQL> select * from v$version;

BANNER
-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> create table t_xifenfei as select * from dba_objects;

表已创建。

SQL> create index i_xifenfei on t_xifenfei(object_id);

索引已创建。

SQL> EXEC DBMS_STATS.gather_table_stats(user,'T_XIFENFEI',CASCADE=>TRUE);

PL/SQL 过程已成功完成。

默认使用INDEX

SQL> SET AUTOT TRACE EXP
SQL> SELECT OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100;

执行计划
----------------------------------------------------------
Plan hash value: 1926396081

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)|Time     |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |            |     1 |    30 |     2   (0)|00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T_XIFENFEI |     1 |    30 |     2   (0)|00:00:01 |

|*  2 |   INDEX RANGE SCAN          | I_XIFENFEI |     1 |       |     1   (0)|00:00:01 |

------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=100)

使用hint实现全表扫描

SQL> SELECT /*+ FULL(T_XIFENFEI)*/OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=10
0;

执行计划
----------------------------------------------------------
Plan hash value: 548923532

--------------------------------------------------------------------------------

| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |            |     1 |    30 |   300   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   300   (1)| 00:00:04 |

--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

查找hint对应sql的sql_id

SQL> SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(T_XIFENFEI)*/%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------

0bbt69m5yhf3p
SELECT /*+ FULL(T_XIFENFEI)*/OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100

68r1cnxmn8fjk
SELECT SQL_ID,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(T_XIFENFEI)*/%'


ddmhrzhatfdyh
EXPLAIN PLAN SET STATEMENT_ID='PLUS570193' FOR SELECT /*+ FULL(T_XIFENFEI)*/OBJE

CT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------


bybs0sds8yu9c
SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT LIKE '%/*+ FULL(T_XIFENFEI)*/%'

获得对应Outline

SQL> SET PAGESIZE 10000
SQL> select * from table(dbms_xplan.display_cursor('0bbt69m5yhf3p',null,'outline'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  0bbt69m5yhf3p, child number 0
-------------------------------------
SELECT /*+ FULL(T_XIFENFEI)*/OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100

Plan hash value: 548923532

--------------------------------------------------------------------------------

| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |            |       |       |   300 (100)|          |

|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   300   (1)| 00:00:04 |

--------------------------------------------------------------------------------


Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T_XIFENFEI"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)


已选择33行。

创建sql profile

declare
 v_hints sys.sqlprof_attr;
 begin
 v_hints:=sys.sqlprof_attr(
      'BEGIN_OUTLINE_DATA',
      'IGNORE_OPTIM_EMBEDDED_HINTS',
      'OPTIMIZER_FEATURES_ENABLE(''11.2.0.3'')',
      'DB_VERSION(''11.2.0.3'')',
      'ALL_ROWS',
      'OUTLINE_LEAF(@"SEL$1")',
      'FULL(@"SEL$1" "T_XIFENFEI"@"SEL$1")',   --这个是由于hint产生,其实我们需要的就是这个
      'END_OUTLINE_DATA');
dbms_sqltune.import_sql_profile(
'SELECT OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100',
v_hints,'SQLPROFILE_XIFENFEI',                 --sql profile 名称
force_match=>true,replace=>true);
end;
/

验证sql profile

SQL> SELECT OBJECT_NAME FROM T_XIFENFEI WHERE OBJECT_ID=100;

执行计划
----------------------------------------------------------
Plan hash value: 548923532

--------------------------------------------------------------------------------

| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |            |     1 |    30 |   300   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T_XIFENFEI |     1 |    30 |   300   (1)| 00:00:04 |

--------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100)

Note
-----
   - SQL profile "SQLPROFILE_XIFENFEI" used for this statement

很多时候我们直接在客户机器上分析awr不太方便,需要通过收集客户awr信息到另一台机器上进行分析数据库性能等.这种情况下,就需要对客户的awr数据进行导出,然后导入到其他机器上,再进行深入分析.
导出awr数据

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
------------ ------------ ------------
* 1393262699 XIFENFEI     XIFENFEI-PC
  3753332923 FDJDB        ora1
  3753332923 FDJDB        ora2

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

输入 dbid 的值:  3753332923    <--需要输入

Using 3753332923 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
------------ --------- ------------------
FDJDB              906 23 4月  2012 00:00
                   907 23 4月  2012 01:00
                   908 23 4月  2012 02:00
                   909 23 4月  2012 03:00
                   910 23 4月  2012 04:00
                   911 23 4月  2012 05:00
                   912 23 4月  2012 06:00
                   913 23 4月  2012 07:00
                   914 23 4月  2012 08:00
                   915 23 4月  2012 09:00
                   916 23 4月  2012 10:00
                   917 23 4月  2012 11:00
                   918 23 4月  2012 12:00
                   919 23 4月  2012 13:00


Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:  906  <--需要输入
Begin Snapshot Id specified: 906

输入 end_snap 的值:  907    <--需要输入
End   Snapshot Id specified: 907


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

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

DATA_FILE_DIR                  E:\oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\
DATA_PUMP_DIR                  E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
LOG_FILE_DIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\log\
MEDIA_DIR                      E:\oracle\product\11.2.0\dbhome_1\demo\schema\product_media\
ORACLE_OCM_CONFIG_DIR          E:\oracle\product\11.2.0\dbhome_1\ccr\state
SS_OE_XMLDIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\ord er_entry\
SUBDIR                         E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep
XMLDIR                         E:\oracle\product\11.2.0\dbhome_1\rdbms\xml

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

输入 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_906_907.
To use this name, press <return> to continue, otherwise enter
an alternative.

输入 file_name 的值:  xifenfei_awr  <--需要输入

Using the dump file prefix: xifenfei_awr
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  The AWR extract dump file will be located
|  in the following directory/file:
|   E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   xifenfei_awr.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:
|   E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   xifenfei_awr.log
|  可以通过查看E:\oracle\product\11.2.0\dbhome_1\rdbms\log\xifenfei_awr.log
|  监控导出awr数据进度

End of AWR Extract

导入awr数据

SQL> @E:\oracle\product\11.2.0\dbhome_1\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_FILE_DIR                  E:\oracle\product\11.2.0\dbhome_1\demo\schema\sales_history\
DATA_PUMP_DIR                  E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
LOG_FILE_DIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\log\
MEDIA_DIR                      E:\oracle\product\11.2.0\dbhome_1\demo\schema\product_media\
ORACLE_OCM_CONFIG_DIR          E:\oracle\product\11.2.0\dbhome_1\ccr\state
SS_OE_XMLDIR                   E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\
SUBDIR                         E:\oracle\product\11.2.0\dbhome_1\demo\schema\order_entry\/2002/Sep
XMLDIR                         E:\oracle\product\11.2.0\dbhome_1\rdbms\xml

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

输入 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:

输入 file_name 的值:  awrdat_751_919 <--需要输入(文件后缀名一定要是.dmp)

Loading from the file name: awrdat_751_919.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.

输入 schema_name 的值:  XFF_AWR  <--需要输入(临时创建用户)

Using the staging schema name: XFF_AWR

Choose the Default tablespace for the XFF_AWR user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the XFF_AWR users's default tablespace.  This is the
tablespace in which the AWR data will be staged.

TABLESPACE_NAME                CONTENTS  DEFAULT TABLESPACE
------------------------------ --------- ------------------
EXAMPLE                        PERMANENT
SYSAUX                         PERMANENT *
USERS                          PERMANENT

Pressing <return> will result in the recommended default
tablespace (identified by *) being used.

输入 default_tablespace 的值:  EXAMPLE  <--需要输入

Using tablespace EXAMPLE as the default tablespace for the XFF_AWR


Choose the Temporary tablespace for the XFF_AWR user
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Choose the XFF_AWR 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.

输入 temporary_tablespace 的值:  TEMP  <--需要输入

Using tablespace TEMP as the temporary tablespace for XFF_AWR


... Creating XFF_AWR user  (临时用户创建)

|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   E:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   awrdat_751_919.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:\oracle\product\11.2.0\dbhome_1\rdbms\log\
|   awrdat_751_919.log
|
|  可以通过查看E:\oracle\product\11.2.0\dbhome_1\rdbms\log\awrdat_751_919.log
|  监控导出awr数据进度

... Dropping XFF_AWR user  (临时用户被删除)

End of AWR Load

查看awr报告

SQL> @?/RDBMS/admin/awrrpti.sql

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'
输入 report_type 的值:  html   <--需要输入

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
  3753332923        2 FDJDB        fdjdb2       ora2
  3753332923        1 FDJDB        fdjdb1       ora1
* 1393262699        1 XIFENFEI     xff          XIFENFEI-PC

输入 dbid 的值:   3753332923  <--需要输入
Using  3753332923 for database Id
输入 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.


输入 num_days 的值:  1  <--需要输入

Listing the last day's Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
fdjdb1       FDJDB              906 23 4月  2012 00:00     1
                                907 23 4月  2012 01:00     1
                                908 23 4月  2012 02:00     1
                                909 23 4月  2012 03:00     1
                                910 23 4月  2012 04:00     1
                                911 23 4月  2012 05:00     1
                                912 23 4月  2012 06:00     1
                                913 23 4月  2012 07:00     1
                                914 23 4月  2012 08:00     1
                                915 23 4月  2012 09:00     1
                                916 23 4月  2012 10:00     1
                                917 23 4月  2012 11:00     1
                                918 23 4月  2012 12:00     1
                                919 23 4月  2012 13:00     1



Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
输入 begin_snap 的值:  917  <--需要输入
Begin Snapshot Id specified: 917

输入 end_snap 的值:  918    <--需要输入
End   Snapshot Id specified: 918



Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_917_918.html.  To use this name,
press <return> to continue, otherwise enter an alternative.

输入 report_name 的值:xifenfei_awr.html  <--需要输入