July 21, 2009 Leave a comment
When we think of mining data in EM Grid Control (GC), we first pay attention to 3 main tables in the repository owned by SYSMAN, MGMT_METRIC_RAW, MGMT_METRIC_1HOUR, MGMT_METRIC_1DAY. These tables hold RAW metric data, one-hour aggregated data, one-day aggregated data respectively.
With default installation, Grid Control agents upload raw data in EM repository which are stored in MGMT_METRIC_RAW table for 7 days. Then, raw data are aggregated by Grid Control based on a predefined policy into 1-hour records, stored in MGMT_METRIC_1HOUR. At this poin, the raw data older than 7 days are eligible for purging. After 31 days or a month, 1-hour records in MGMT_METRIC_1HOUR are subject for further aggregation that rolls upthem into 1-day records. These 1-day records are stored in MGMT_METRIC_1Day for 365 days or 1 year before purged by Grid Control.
As one way to avoid losing details during the aggregation process, we can change the retention policy. New retention periods for these 3 tables can be defined in MGMT_PARAMETERS tables. These no-default parameters do not exist in mgmt_parameters tables during the standard installation. We need to insert the right value into the table.
Table Name Retention Parameter Retention Days
MGMT_METRICS_RAW mgmt_raw_keep_window 7
MGMT_METRICS_1HOUR mgmt_hour_keep_window 31
MGMT_METRICS_1DAY mgmt_day_keep_window 365
INSERT INTO SYSMAN.MGMT_PARAMETERS (PARAMETER_NAME, PARAMETER_VALUE,PARAMETER_COMMENT)
VALUES (‘mgmt_raw_keep_window’,’31′,’Keep raw data for 31 days’);
Not surprising, these 3 main tables are Index-Oraganized tables, partitioned given that e.g. the partitioning option is enabled during the installation.
MGMT_METRICS_RAW is range-partitioned per hour, whereas MGMT_METRICS_1HOUR and MGMT_METRICS_1DAY are range-partitioned per day.
CREATE TABLE “SYSMAN”.”MGMT_METRICS_1DAY“
( ”TARGET_GUID” RAW(16),
“KEY_VALUE” VARCHAR2(256 BYTE) DEFAULT ‘ ‘,
“SAMPLE_COUNT” NUMBER DEFAULT 0,
“VALUE_AVERAGE” NUMBER DEFAULT 0,
“VALUE_MINIMUM” NUMBER DEFAULT 0,
“VALUE_MAXIMUM” NUMBER DEFAULT 0,
“VALUE_SDEV” NUMBER DEFAULT 0,
CONSTRAINT “MGMT_METRICS_1DAY_PK_INT” PRIMARY KEY (“TARGET_GUID”, “METRIC_GUID”, “KEY_VALUE”, “ROLLUP_TIMESTAMP”) ENABLE
) ORGANIZATION INDEX COMPRESS 3
PARTITION BY RANGE (“ROLLUP_TIMESTAMP”)
(PARTITION “2008-11-11 00:00″ VALUES LESS THAN (TO_DATE(‘ 2008-11-11 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))
PARTITION “2008-11-12 00:00″ VALUES LESS THAN (TO_DATE(‘ 2008-11-12 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)) …
During maintenance, GC jobs truncate data in the partitions but leave the partition untouched, because dropping partitions would invalidate stored procedures. To drop old partitions, we can manually run “exec emd_maintenance.partition_maintenance;” after bring down OEM Grid Control processes.
Querying the data in these 3 tables, we need to know and refer TARGET_GUID and METRIC_GUID, instead of target names or metric names. For easier access using names, we can use 3 views MGMT$METRIC_DETAILS, MGMT$METRIC_HOURLY, and MGMT$METRIC_DAILY. These views owned by SYSMAN are based on these 3 main tables but having extra columns like ‘target_name’, ‘metric_name’ for easier referrence.