Database Reference
In-Depth Information
Collection of Statistics Using AWR
Oracle 10g has two new background processes. Memory
Monitor (MMON) and Memory Monitor Light (MMNL). The
MMON does most of the work. By default it wakes up after
every hour and gathers information from data dictionary views,
dynamic performance views, optimizer and then stores this
information in the SYSAUX tablespace. The tables that contain
this information are called the Automatic Workload Repository
(AWR). These tables are stored under SYS schema. Any table
name starting with “WR” belongs to AWR. The AWR tables are
divided into three categories: table names starting with WRM$
represents AWR metadata tables, table names starting with
WRH$ contains historical data whereas AWR tables relating to
advisory functions begin with WRI$.
The statistics collected in AWR are related to system and
session, time-model (i.e. database response time), wait event (i.e.
time a session has to wait for an event to get completed)
statistics, active session history (ASH i.e. every active session
with Oracle Server is sampled every second) statistics and top
load SQL (i.e. SQL statements taking most of the overall system
resources and adding a prominently extra burden on the server)
statistics.
How to activate AWR?
In order to activate AWR, you first have to set the
PFILE/SPFILE parameter STATISTICS_LEVEL to one the
following values.
BASIC: Disables the AWR. Few database statistics are gathered
at each collection interval when operating the Oracle instance in
this mode.
TYPICAL: Setting this value will enable the AWR. This is the
default value and is good for most environments.
Search WWH ::




Custom Search