Automatic Workload Repository (AWR)

Basically, AWR is an Oracle built-in tool that collects performance related statistics and derives performance metrics from them to track a potential problem. Unlike Statspack, snapshots are collected automatically every hour by a new background process called MMON and its slave processes. Be default, the collected data is  automatically purged after 7 days. Both the snapshot frequency and retention time can be modified by the user. To see the present settings, you could use:

 

set lines 200

col SNAP_INTERVAL format a30;

col RETENTION format a30;

select * from dba_hist_wr_control;

 

SNAP_INTERVAL                  RETENTION

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

+00000 01:00:00.0              +00007 00:00:00.0

+00000 01:00:00.0              +00007 00:00:00.0

 

To change snapshots’ interval & retention parameter setting:

 

BEGIN

  DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(

    retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.

    interval => 60);           -- Minutes (= 1 hour). Current value retained if NULL.

END;

/

 

To check Snapshot ID information  can be queried from the  DBA_HIST_SNAPSHOT view:

 

select * from DBA_HIST_SNAPSHOT order by BEGIN_INTERVAL_TIME;

 

Existing snapshots can be removed by:

 

BEGIN

  DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (

    low_snap_id  => 6491,

    high_snap_id => 6499);

END;

/

 

Extra snapshots can be taken:

 

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

 

Workload Repository Reports:

 

Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows:

 

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

@$ORACLE_HOME/rdbms/admin/awrrpti.sql

 

The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

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

转载于:http://blog.itpub.net/22473175/viewspace-614269/