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 DEFAULT

Elapsed: 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
再查看修改后的效果：

Elapsed: 00:00:00.02
SYS@PENG SQL>

除自动建立快照以外，还可以在需要的时候手工创建快照，数据和自动创建的一样使用。

SYS@PENG SQL> select dbms_workload_repository.create_snapshot() from dual;
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()
------------------------------------------
68

Elapsed: 00:00:01.00
SYS@PENG SQL> select dbms_workload_repository.create_snapshot() from dual;
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()
------------------------------------------
69

Elapsed: 00:00:00.60
SYS@PENG SQL>

这里存放了很多sql脚本，有时间可以打开阅读，对提高水平有作用。这里我们用到awrrpt.sql。

可以先打开看一下内容。

[oracle@localhost admin]$ cat awrrpt.sql

Rem $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 snapshots

set 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
fr

Oracle AWR报告生成与查看(一)