可以通过dba_hist_wr_control查看当前的配置情况,当前awr为每1小时做一次数据快照,保留时间为7天。
SYS@PENG SQL> select * from dba_hist_wr_control;
---------- --------------------------- ------------------------------------ ----------
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;
---------- --------------------------- ------------------------------------ ----------
Elapsed: 00:00:00.02
SYS@PENG SQL>
除自动建立快照以外,还可以在需要的时候手工创建快照,数据和自动创建的一样使用。
SYS@PENG SQL> select dbms_workload_repository.create_snapshot() from dual;
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()
------------------------------------------
Elapsed: 00:00:01.00
SYS@PENG SQL> select dbms_workload_repository.create_snapshot() from dual;
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT()
------------------------------------------
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.
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
Rem
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
column inst_name heading "Instance"
column db_name
column dbid
prompt
prompt Current Instance
prompt ~~~~~~~~~~~~~~~~
select d.dbid
@@awrrpti
undefine num_days;
undefine report_type;
undefine report_name;
undefine begin_snap;
undefine end_snap;
--
-- End of file
其中一段的结果为
select d.dbid
---------- --------- ---------- ----------------
然后调用了awrrpti.sql,其内容较多,以后找机会在专门分析。
这里我们直接执行awrrpt.sql。
SYS@PENG SQL> @/oracle/product/10g/rdbms/admin/awrrpt.sql
Current Instance
~~~~~~~~~~~~~~~~
----------- ------------ -------- ------------
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:
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
------------ -------- ------------ ------------ ------------
* 620748923
Using
Using
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.
specifying a number lists all completed snapshots.
Enter value for num_days: 2
Listing the last 2 days of Completed Snapshots
Instance
------------ ------------ --------- ------------------ -----
peng
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 64
Begin Snapshot Id specified: 64
Enter value for end_snap: 70
End
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_64_70.txt.
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