 |
|
Oracle Tips by Burleson |
Metric dba_hist Views
Metric dba_hist data dictionary views are organized into several
groups such as system, session, service, file, tablespace, and event
metrics. All available metric groups can be found by using the
v$metricgroup dynamic view as follows:
SQL> select * from V$METRICGROUP;
GROUP_ID NAME INTERVAL_SIZE MAX_INTERVAL
------- ---------------------------------------------- ----------
0 Event Metrics 6000 1
1 Event Class Metrics 6000 60
2 System Metrics Long Duration 6000 60
3 System Metrics Short Duration 1500 12
4 Session Metrics Long Duration 6000 60
5 Session Metrics Short Duration 1500 1
6 Service Metrics 6000 60
7 File Metrics Long Duration 60000 6
9 Tablespace Metrics Long Duration 6000 0
All available metrics for which the Automatic Workload Repository
(AWR) keeps history are in the dba_hist_metric_name view that stores
snapshots for v$metric_name view. There are approximately 184
different metrics available.
Metrics are actively used by sophisticated automation features of
Oracle10g such as Automatic Memory Management (AMM), Sequential data
Access via Metadata (SAM) and advisory engines such as the Automatic
Database Diagnostic Monitor (ADDM), SQL Advisor, etc. For example,
ADDM uses a goal-based algorithm designed to minimize DB time. This
metric is computed as a cumulative time spent.
SQL> select METRIC_NAME from
DBA_HIST_METRIC_NAME;
The following is a list of several metrics computed by the AWR
snapshot engine; however, the list is not exhaustive:
METRIC_NAME
--------------------------------------
Average File Read Time (Files-Long)
Average File Write Time (Files-Long)
Average Users Waiting Counts
Background Checkpoints Per Sec
Blocked User Session Count
Branch Node Splits Per Sec
Branch Node Splits Per Txn
Buffer Cache Hit Ratio
CPU Time (Session)
CPU Time Per User Call
CPU Usage Per Sec
The following sections provide a general overview of selected metric
dba_hist views that are available and a short description of
information they provide.
The above book excerpt is from:
Oracle
Tuning Power Scripts
With 100+ High Performance
SQL Scripts
Oracle In-Focus Series
ISBN
0-9744486-7-2
Mike Ault, Donald K.
Burleson. Harry Conway
http://www.rampant-books.com/book_2005_1_power_tuning.htm |
