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:
(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?
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.
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/，如需转载，请注明出处，否则将追究法律责任。