 |
|
Oracle Tips by Burleson |
Oracle 10g Automatic Workload Repository (AWR)
AWR is the main infrastructure that collects
in-memory statistics at regular intervals and makes them available
to the internal and external services or clients. External clients,
such as Oracle Enterprise Manager and SQL*plus sessions, can view
the AWR information through the data dictionary views. Internal
clients, such as the ADDM and other self-tuning components or
advisories, make use of the contents in the AWR.
General Benefits
Advantages of the new workload repository
include:
-
AWR is a record of all database in-memory
statistics historically stored. In the past, historical data
could be obtained manually using the ‘statspack’ utility. AWR
automatically collects more precise and granular information
than past methods.
-
With a larger data sample, more informed
decisions could be made. The self-tuning mechanism uses this
information for trend analysis.
-
The statistics survive database reboots and
crashes.
-
Another benefit is that AWR statistics are
accessible to external users, who can build their own performance
monitoring tools, routines, and scripts.
Oracle recommends that statspack users switch
to the Workload Repository in Oracle Database 10g.
Oracle 10g AWR Physical Structures
AWR is stored in tables owned by ‘SYS’ but
physically located on the SYSAUX tablespace. The Workload Repository
contains two types of tables:
-
Metadata Tables: These are used to
control, process, and describe the Workload Repository tables.
For example, Oracle uses the metadata tables to determine when
to perform snapshots, and what to capture to disk.
-
Historical
Statistics Tables: These tables store historical statistical
information about the database in the form of snapshots. Each
snapshot is a capture of the in–memory database statistics data
at a certain point in time. All names of the AWR tables are
prefixed with
-
WRx$ with x specifying the kind of table:
-
WRM$ tables store metadata information for the
Workload Repository.
-
WRH$ tables store historical data or snapshots.
-
WRI$ tables store data related to advisory
functions.
The WRx$ tables are organized into the
following categories: File Statistics, General System Statistics,
Concurrency Statistics, Instance Tuning Statistics, SQL Statistics,
Segment Statistics, Undo Statistics, Time– Model Statistics,
Recovery Statistics, and RAC Statistics. Fig 9.1 shows a graphical
view of the AWR table types.
Following is the list of WRM$ tables that
control all repository operations.
WRM$_BASELINE
WRM$_DATABASE_INSTANCE
WRM$_SNAPSHOT
WRM$_SNAP_ERROR
WRM$_WR_CONTROL
Dictionary views are also provided, making the
historical data available to users for query. Any view related to
the historical information in the AWR has the dba_hist_ prefix.
Figures 9.2 and 9.3 show the full list of the WR tables and the
dba_hist* tables respectively.
Oracle 10g AWR Collection Process
The collection process involves the capture of
in-memory statistics from the SGA and their transfer to the physical
tables located in the workload repository. The new background
process, MMON, does this. The frequency of the capture snapshot is
30 minutes by default, however it can be adjusted suitably.
You can control the interval and retention of
snapshot generation by the dbms_workload_repository.
modify_snapshot_settings procedure. For example:
EXEC
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS (43200, 15);
In this example, the retention period is
specified as 30 days (43200 min) and the interval between each
snapshot is 10 min.
The snapshots are used for computing the rate
of change of a statistic. This is mainly used for performance
analysis. A snapshot sequence numer (snap_id) identifies each
snapshot, which is unique within the Workload Repository.
Figure 9.4
shows the relation of AWR to other components.
Oracle 10g AWR Types of Data Collected
The collected statistics include:
-
New time model statistics that show the
amount of time spent on database activities.
-
Object Statistics
that determine both access and usage of the segments.
-
Some selected statistics collected in v$sysstat and v$sesstat
-
Some of the optimizer statistics that include
statistics for self-learning and tuning.
-
The ADDM Active Session History (ASH), which
represents the history of the recent session’s activity.
These statistics can be broadly categorized
into 5 groups based on their nature.
-
Base Statistics – This group represents raw
data, which are generally values from the start of the database,
e.g. total physical reads.
-
SQL Statistics – Important measurements
regarding the SQL statement. For example: Disk Read per SQL
statement.
-
Metrics – These are the secondary statistics,
and the most interesting ones from a tuning point of view. Metrics
track the rates of change of activities in the database. For
example, the average physical reads in the system in the last 30
min. is a metric.
-
Contents of Active Session History - For
example, db file sequential read wait for SID of 16, file# 12,
block# 1245, obj# 67, and time: 20000us.
-
Advisor Results – Results of the expert
analysis by the advisor framework.
Oracle 10g Using and Managing AWR
You can query base statistics and metrics from
the various fixed views provided. You can optionally create your own
snapshots and baselines using the dbms_workload_repository package.
dbms_workload_repository package procedures are
as follows:
-
modify_snapshot_settings: Procedure to modify the snapshot settings.
-
drop_baseline: Procedure to
drop a single baseline.
-
create_baseline:
Procedure to create a single baseline.
-
ddrop_snapshot_range:
Procedure to drop a range of snapshots.
-
ccreate_snapshot:
Procedure to create a manual snapshot immediately.
You can use the Oracle-provided SQL scripts to
generate reports to view the contents of AWR.
-
swrfrpt.sql: This script generates a report
showing information on the overall behavior of the system over a
time period. The script generates a text file.
-
swrfrpth.sql: This script gives the same
information as swrfrpt.sql, however, the generated output file uses
HTML format.
AWR is also used to track database usage metrics.
The usage metrics represent how you use the database features. The
database usage metrics are divided into two categories:
Database Feature Usage Statistics include
Advanced Replication, Oracle Streams, AQ, Virtual Private Database,
Audit options etc.
High Water Mark Statistics include the size of
the largest segment, the maximum number of sessions, the maximum
number of tables, the maximum size of the database, the maximum
number of data files, etc.
To view usage metric information, you can query
the following:
|
