 |
|
Oracle
Manageability Infrastructure
Oracle Tips by Burleson Consulting |
This
is an excerpt from "Oracle 10g New Features for Administrators" by
Ahmed Baraka.
Types of Oracle Statistics - Cumulative Statistics
Cumulative statistics are the accumulated total
value of a particular statistic since instance startup.
Database Metrics
Database metrics are the statistics that measure the
rate of change in a cumulative performance statistic.
The background process MMON (Manageability Monitor)
updates metric data on a minute-by-minute basis, after collecting
the necessary fresh base statistics.
Sample Data
The new Automatic Session History (ASH) feature now
automatically collects session sample data, which represents a
sample of the current state of the active sessions.
Baseline Data
The statistics from the period where the database
performed well are called baseline data.
MMON process takes snapshots of statistics and
save them into disks.
The Manageability Monitor Light (MMNL) process
performs:
o computing metrics
o capturing session history information for the
Automatic Session History (ASH) feature under some
circumstances. For example, the MMNL process will flush ASH
data to disk if the ASH memory buffer fills up before the one
hour interval that would normally cause MMON to flush it.
The Automatic Workload Repository (AWR)
Its task is the automatic collection of performance
statistics in the database.
AWR provides performance statistics in two distinct
formats:
• A temporary in-memory collection of statistics in
the SGA, accessible by (V$) views.
• A persistent type of performance data in the form
of regular AWR snapshots, accessible by (DBA_*) views.
Using the DBMS_WORKLOAD_REPOSITORY Package to Manage
AWR Snapshots
To manually creating a snapshot:
dbms_workload_repository.create_snapshot()
To drop a range of snapshots:
dbms_workload_repository.drop_snapshot_range
(low_snap_id => 40,high_snap_id => 60, dbid => 2210828132)
To modify a AWR setting:
DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention =>
43200, interval => 30, dbid =>3310949047)
In this example, the retention period is specified
as 43200 minutes (30 days) and the interval between each snapshot is
specified as 30 minutes.
Note: If you set the value of the RETENTION
parameter to zero, you disable the automatic purging of the AWR.
If you set the value of the INTERVAL parameter to
zero, you disable the automatic capturing of AWR snapshots.
Creating and Deleting AWR Snapshot Baselines
Whenever you create a baseline by defining it over
any two snapshots (identified by their snap IDs), the AWR retains
the snapshots indefinitely (it won’t purge these snapshots after the
default period of seven days), unless you decide to drop the
baseline itself.
To create a new snapshot baseline:
dbms_workload_repository.create_baseline
(start_snap_id => 125, end_snap_id => 185,
baseline_name => 'peak_time baseline', dbid =>2210828132)
To drop a snapshot baseline:
dbms_workload_repository.drop_baseline
(baseline_name => 'peak_time baseline', cascade
=> FALSE, dbid => 2210828132)
By setting CASCADE parameter to TRUE, you can drop
the actual snapshots as well.
Note: If AWR does not find room in the SYSAUX
tablespace, Oracle will start deleting oldest snapshot regardless of
values of INTERVAL and RETENTION.
Creating AWR Reports
Use the script awrrpt.sql to generate summary
reports about the statistics collected by the AWR facility.
Note: You must have the SELECT ANY DICTIONARY
privilege in order to run the awrrpt.sql script.
AWR Statistics Data Dictionary Views
DBA_HIST_SNAPSHOT shows all snapshots saved in the
AWR.
DBA_HIST_WR_CONTROL displays the settings to control
the AWR.
DBA_HIST_BASELINE shows all baselines and their
beginning and ending snap ID numbers.
 |
If you like Oracle tuning, see the
book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
