ITPub博客

首页 > Linux操作系统 > Linux操作系统 > AWR基本管理

AWR基本管理

原创 Linux操作系统 作者:lsq_008 时间:2008-12-14 16:20:32 0 删除 编辑

   通过DBMS_WORKLOAD_REPOSITORY包,可以对AWR进行基本的管理。在默认的情况下,oracle是每一小时采集一次快照,下面简要介绍一下手动管理:

1.手动创建快照:

SQL> /
 
   SNAP_ID       DBID BEGIN_INTERVAL_TIME            END_INTERVAL_TIME
---------- ---------- ------------------------------ ------------------------------
         1 2168572286 12-DEC-08 08.32.41.000 PM      12-DEC-08 09.00.49.645 PM
         3 2168572286 12-DEC-08 10.00.51.498 PM      12-DEC-08 11.00.53.571 PM
         2 2168572286 12-DEC-08 09.00.49.645 PM      12-DEC-08 10.00.51.498 PM
         4 2168572286 13-DEC-08 04.45.19.000 AM      13-DEC-08 04.51.49.972 AM
         5 2168572286 13-DEC-08 04.51.49.972 AM      13-DEC-08 05.30.28.483 AM
         6 2168572286 13-DEC-08 05.30.28.483 AM      13-DEC-08 06.00.29.593 AM
 
6 rows selected.
 
SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/
  2    3    4 
 
PL/SQL procedure successfully completed.

SQL> a  order by 1
  1* select snap_id,dbid,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot order by 1
SQL> /
 
   SNAP_ID       DBID BEGIN_INTERVAL_TIME            END_INTERVAL_TIME
---------- ---------- ------------------------------ ------------------------------
         1 2168572286 12-DEC-08 08.32.41.000 PM      12-DEC-08 09.00.49.645 PM
         2 2168572286 12-DEC-08 09.00.49.645 PM      12-DEC-08 10.00.51.498 PM
         3 2168572286 12-DEC-08 10.00.51.498 PM      12-DEC-08 11.00.53.571 PM
         4 2168572286 13-DEC-08 04.45.19.000 AM      13-DEC-08 04.51.49.972 AM
         5 2168572286 13-DEC-08 04.51.49.972 AM      13-DEC-08 05.30.28.483 AM
         6 2168572286 13-DEC-08 05.30.28.483 AM      13-DEC-08 06.00.29.593 AM
         7 2168572286 14-DEC-08 04.10.24.000 PM      14-DEC-08 04.21.31.407 PM
         8 2168572286 14-DEC-08 04.21.31.407 PM      14-DEC-08 04.27.46.179 PM
 
8 rows selected.

2.删除快照,可以指定一个快照的范围进行删除。

SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 2,
                           high_snap_id => 3);
END;
/
  2    3    4    5 
 
PL/SQL procedure successfully completed.
 
SQL> SQL> select snap_id,dbid,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME from dba_hist_snapshot order by 1;
 
   SNAP_ID       DBID BEGIN_INTERVAL_TIME            END_INTERVAL_TIME
---------- ---------- ------------------------------ ------------------------------
         1 2168572286 12-DEC-08 08.32.41.000 PM      12-DEC-08 09.00.49.645 PM
         4 2168572286 13-DEC-08 04.45.19.000 AM      13-DEC-08 04.51.49.972 AM
         5 2168572286 13-DEC-08 04.51.49.972 AM      13-DEC-08 05.30.28.483 AM
         6 2168572286 13-DEC-08 05.30.28.483 AM      13-DEC-08 06.00.29.593 AM
         7 2168572286 14-DEC-08 04.10.24.000 PM      14-DEC-08 04.21.31.407 PM
         8 2168572286 14-DEC-08 04.21.31.407 PM      14-DEC-08 04.27.46.179 PM
 
6 rows selected.

3.修改快照设置。可以更改快照自动产生的时间间隔和快照保留时

SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200,
                 interval => 30);
END;
/  2    3    4    5 
 
PL/SQL procedure successfully completed.

4.创建和删除baseline。baseline就是保留一个典型的时间段内(业务高峰期)的快照,用作性能基准,在以后出现性能问题时,可以进行对比。

创建:

SQL> BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 6,
                   end_snap_id => 8, baseline_name => 'peak baseline');
END;
/  2    3    4    5 
 
PL/SQL procedure successfully completed.

删除:

SQL> BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',
                  cascade => FALSE);
END;
/  2    3    4    5 
 
PL/SQL procedure successfully completed.

 

5.查看awr报告

SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
 
Current Instance
~~~~~~~~~~~~~~~~
 
   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
 2168572286 EBANK               1 ebank
 
 
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
------------ -------- ------------ ------------ ------------
* 2168572286        1 EBANK        ebank        localhost.lo
                                                caldomain
 
Using 2168572286 for database Id
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 without
specifying a number lists all completed snapshots.
 
 
Enter value for num_days: 1
 
Listing the last day's Completed Snapshots
 
                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
ebank        EBANK                7 14 Dec 2008 16:21      1
                                  8 14 Dec 2008 16:27      1
 
 
 
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 7    
Begin Snapshot Id specified: 7
 
Enter value for end_snap: 8
End   Snapshot Id specified: 8
 
 
 
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_7_8.html.  To use this name,
press to continue, otherwise enter an alternative.
 
Enter value for report_name:

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-511826/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 值夜班
请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1241597