 |
|
Oracle Tips by Burleson |
dba_hist_sysmetric_history
The dba_hist_sysmetric_history view collects history for all
system-wide metrics belonging to such metric groups as System
Metrics Long Duration and System Metrics Short Duration. This view
stores snapshots of the v$sysmetric_history dynamic view as shown
below:
SQL> desc
DBA_HIST_SYSMETRIC_HISTORY
Name Null? Type
----------------------------------------- -------- ------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
BEGIN_TIME NOT NULL DATE
END_TIME NOT NULL DATE
INTSIZE NOT NULL NUMBER
GROUP_ID NOT NULL NUMBER
METRIC_ID NOT NULL NUMBER
METRIC_NAME NOT NULL VARCHAR2(64)
VALUE NOT NULL NUMBER
METRIC_UNIT NOT NULL VARCHAR2(64)
This view also contains important metrics for database time-series
tuning approach such as:
* Buffer Cache Hit Ratio
* Database CPU Time Ratio
* Database Time Per Sec
* Database Wait Time Ratio
* Physical Reads Per Sec
* Response Time Per Txn
* SQL Service Response Time.
Note that this view also contains a metric_unit column that helps
identify measure units for every metric.
dba_hist_sysmetric_summary
The dba_hist_sysmetric_summary view shows a history for system-wide
metrics that belong to the System Metrics Long Duration metric
group. This view stores snapshots for the v$sysmetric_summary
dynamic view as shown below:
SQL> desc
DBA_HIST_SYSMETRIC_SUMMARY
Name Null? Type
----------------------------------------- -------- ------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
BEGIN_TIME NOT NULL DATE
END_TIME NOT NULL DATE
INTSIZE NOT NULL NUMBER
GROUP_ID NOT NULL NUMBER
METRIC_ID NOT NULL NUMBER
METRIC_NAME NOT NULL VARCHAR2(64)
METRIC_UNIT NOT NULL VARCHAR2(64)
NUM_INTERVAL NOT NULL NUMBER
MINVAL NOT NULL NUMBER
MAXVAL NOT NULL NUMBER
AVERAGE NOT NULL NUMBER
STANDARD_DEVIATION NOT NULL NUMBER
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 |
