Oracle AWR报告生成与查看

可以通过dba_hist_wr_control查看当前的配置情况，当前awr为每1小时做一次数据快照，保留时间为7天。SYS@PENG SQL> select * from dba_hist_wr_control;
      DBID SNAP_INTERVAL               RETENTION                             TOPNSQL
---------- --------------------------- ------------------------------------ ----------
620748923 +00000 01:00:00.0           +00007 00:00:00.0                     DEFAULTElapsed: 00:00:00.16
SYS@PENG SQL>调整配置数据：SYS@PENG SQL> execute dbms_workload_repository.modify_snapshot_settings（interval=>30,retention=>14000）;PL/SQL procedure successfully completed.Elapsed: 00:00:00.17
再查看修改后的效果：SYS@PENG SQL> select * from dba_hist_wr_control;
      DBID SNAP_INTERVAL               RETENTION                             TOPNSQL
---------- --------------------------- ------------------------------------ ----------
620748923 +00000 00:30:00.0           +00009 17:20:00.0                     DEFAULTElapsed: 00:00:00.02
SYS@PENG SQL>
除自动建立快照以外，还可以在需要的时候手工创建快照，数据和自动创建的一样使用。SYS@PENG SQL> select dbms_workload_repository.create_snapshot（） from dual;
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT（）
------------------------------------------
                                        68Elapsed: 00:00:01.00
SYS@PENG SQL> select dbms_workload_repository.create_snapshot（） from dual;
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT（）
------------------------------------------
                                        69Elapsed: 00:00:00.60
SYS@PENG SQL>
[Oracle@localhost admin]$ pwd
/oracle/product/10g/rdbms/admin这里存放了很多sql脚本，有时间可以打开阅读，对提高水平有作用。这里我们用到awrrpt.sql。可以先打开看一下内容。[oracle@localhost admin]$ cat awrrpt.sqlRem $Header: awrrpt.sql 24-oct-2003.12:04:53 pbelknap Exp $
Rem
Rem awrrpt.sql
Rem
Rem Copyright （c） 1999, 2003, Oracle Corporation. All rights reserved.
Rem
Rem    NAME
Rem      awrrpt.sql
Rem
Rem    DESCRIPTION
Rem      This script defaults the dbid and instance number to that of the
Rem      current instance connected-to, then calls awrrpti.sql to produce
Rem      the Workload Repository report.
Rem
Rem    NOTES
Rem      Run as select_catalog privileges.
Rem      This report is based on the Statspack report.
Rem
Rem      If you want to use this script in an non-interactive fashion,
Rem      see the "customer-customizable report settings" section in
Rem      awrrpti.sql
Rem
Rem    MODIFIED   （MM/DD/YY）
Rem    pbelknap    10/24/03 - swrfrpt to awrrpt
Rem    pbelknap    10/14/03 - moving params to rpti
Rem    pbelknap    10/02/03 - adding non-interactive mode cmnts
Rem    mlfeng      09/10/03 - heading on
Rem    aime        04/25/03 - aime_going_to_main
Rem    mlfeng      01/27/03 - mlfeng_swrf_reporting
Rem    mlfeng      01/13/03 - Update comments
Rem    mlfeng      07/08/02 - swrf flushing
Rem    mlfeng      06/12/02 - Created
Rem--
-- Get the current database/instance information - this will be used
-- later in the report along with bid, eid to lookup snapshotsset echo off heading on underline on;
column inst_num heading "Inst Num" new_value inst_num format 99999;
column inst_name heading "Instance" new_value inst_name format a12;
column db_name   heading "DB Name"   new_value db_name   format a12;
column dbid      heading "DB Id"     new_value dbid      format 9999999999 just c;prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
from v$database d,
       v$instance i;@@awrrptiundefine num_days;
undefine report_type;
undefine report_name;
undefine begin_snap;
undefine end_snap;
--
-- End of file
其中一段的结果为
select d.dbid            dbid
     , d.name            db_name
     , i.instance_number inst_num
     , i.instance_name   inst_name
from v$database d,
       v$instance i;      DBID DB_NAME     INST_NUM INST_NAME
---------- --------- ---------- ----------------
620748923 PENG               1 peng
然后调用了awrrpti.sql，其内容较多，以后找机会在专门分析。这里我们直接执行awrrpt.sql。SYS@PENG SQL> @/oracle/product/10g/rdbms/admin/awrrpt.sqlCurrent Instance
~~~~~~~~~~~~~~~~   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
620748923 PENG                1 peng
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: textType Specified: text
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 620748923         1 PENG         peng         localhostUsing 620748923 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 <return> without
specifying a number lists all completed snapshots.
Enter value for num_days: 2Listing the last 2 days of Completed Snapshots                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
peng         PENG                58 12 Dec 2010 01:11      1
                                 59 12 Dec 2010 02:01      1
                                 60 12 Dec 2010 03:01      1
                                 61 12 Dec 2010 04:02      1
                                 62 12 Dec 2010 05:02      1                                 63 13 Dec 2010 01:00      1                                 64 13 Dec 2010 02:00      1
                                 65 13 Dec 2010 03:02      1
                                 66 13 Dec 2010 04:01      1
                                 67 13 Dec 2010 04:54      1
                                 68 13 Dec 2010 04:59      1
                                 69 13 Dec 2010 05:00      1
                                 70 13 Dec 2010 05:30      1 Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 64
Begin Snapshot Id specified: 64Enter value for end_snap: 70
End   Snapshot Id specified: 70 Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_64_70.txt. To use this name,
press <return> to continue, otherwise enter an alternative.Enter value for report_name: /tmp/awrrpt_1_64_70.txt生成文件存放在指定的/tmp下。文件包含一下内容：

Report Summary
Wait Events Statistics
SQL Statistics
Instance Activity Statistics
IO Stats
Buffer Pool Statistics
Advisory Statistics
Wait Statistics
Undo Statistics
Latch Statistics
Segment Statistics
Dictionary Cache Statistics
Library Cache Statistics
Memory Statistics
Streams Statistics
Resource Limit Statistics
init.ora Parameters

Oracle 10g Data Guard 环境缺少log_archive_config 参数导致归档路径被禁用Oracle 数据库11g版本dataguard创建的简单方法相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）