Oracle 10g New Features - AWR - Basical Conception

1.    What is AWR?

[@more@]

AWR stands for “Automatic Workload Repository“, which installs along with the database. Basically, AWR is an Oracle built-in tool that collects performance related statistics during database operation 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. To save space, the captured data is automatically purged after 7 days as default. Both the snapshot frequency and retention time can be modified by the user.

 

2.    How to see and modify AWR’s default settings?

To see the present settings:

   select snap_interval, retention from dba_hist_wr_control;

 

To modify the settings:

   begin dbms_workload_repository.modify_snapshot_settings(interval=>20,retention=>2*24*60);

   end;

(for snapshot intervals of 20 minutes and a retention period of 2 days)

Notice: Before you issue the statement above, there should be a schema named SWRF existing and enable.

 

3.    Where does AWR store and how to see the data?

Tables:

AWR uses several tables to store the collected statistics, all stored under the SYS schema in the new special tablespace named SYSAUX, and named in the format WRM$_ and WRH$_. The former type stores metadata information such as the database being examined and the snapshots being taken. The later type holds the actual collected statistics.

 

Views:

There are several views with the prefix DBA_HIST_ and the names directly relate to the tables. For example, the view DBA_HIST_SYSMETRIC_SUMMARY is built upon the table WRH$_SYSMETRIC_SUMMARY.

 

To get the complete list of AWR views:

   select view_name from user_views where view_name like ‘DBA_HIST_%’ escape ‘’;

 

An important view:

DBA_HIST_METRIC_NAME: defines the important metrics the AWR collects, the groups to which they belong, and the unit in which they are collected. For example:

DBID               : 4133493568

GROUP_ID           : 2

GROUP_NAME         : System Metrics Long Duration

METRIC_ID          : 2075

METRIC_NAME        : CPU Usage Per Sec

METRIC_UNIT        : CentiSeconds Per Second

It shows that a metric “CPU Usage Per Sec” is measured in units of “CentiSeconds Per Second” and belongs to a metric group “System Metrics Long Duration”. The record can be joined with other tables to get the activity, such as DBA_HIST_SYSMETRIC_SUMMARY.

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

转载于:http://blog.itpub.net/20294/viewspace-980870/