oracle awr 设定,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

再查看修改后的效果：

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 DEFAULT

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>

[oracle@localhost admin]$ pwd

/oracle/product/10g/rdbms/admin

这里存放了很多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

from v$database d,

v$instance i;

@@awrrpti

undefine 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.sql

Current 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: text

Type Specified: text

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB

Id Inst Num DB

Name Instance Host

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

*

620748923 1

PENG peng localhost

Using 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

without

specifying a number lists all completed snapshots.

Enter value for num_days: 2

Listing 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