How to purge metric values from dropped metric extension in EM13c?

[Rich J]

Hey all,

In EM13cR2, I had a metric extension (ME) that collected roughly 28M
values. As it was causing major performance issues in EM, I undeployed
it from all targets, exported the ME, and dropped it. That's fine,
except all of the collected values were not also dropped, but cannot now
be easily reported on, either. That's fine, as I don't need them, but
how does one go about purging those values? As the collected values are
strings, they take up the majority of my EM repository, and likely will
for the next two years.

Searching MOS, but I haven't found anything yet. Anyone?

TIA!
Rich

[Chris Taylor]

Have you looked at the SYSMAN MGMT_METRICS tables.

There's a METRIC_GUID (but perhaps it got removed as well?). I would
think you'd be able to manually do either:

1. Delete the mgmt collected metrics from the SYSMAN schema in the
repository
2. Save the metrics you want to keep to a temp table, truncate the metrics
table(s) and reinsert the saved values

OEM would need to be down while you did that though. Purging data manually
from the SYSMAN tables is pretty straight forward once you have the
METRIC_GUID and find all the child tables that also have that GUID.

Chris

Post by Rich J
Hey all,
In EM13cR2, I had a metric extension (ME) that collected roughly 28M
values. As it was causing major performance issues in EM, I undeployed it
from all targets, exported the ME, and dropped it. That's fine, except all
of the collected values were not also dropped, but cannot now be easily
reported on, either. That's fine, as I don't need them, but how does one
go about purging those values? As the collected values are strings, they
take up the majority of my EM repository, and likely will for the next two
years.
Searching MOS, but I haven't found anything yet. Anyone?
TIA!
Rich

[Rich J]

Post by Chris Taylor
Have you looked at the SYSMAN MGMT_METRICS tables.
1. Delete the mgmt collected metrics from the SYSMAN schema in the repository
2. Save the metrics you want to keep to a temp table, truncate the metrics table(s) and reinsert the saved values
OEM would need to be down while you did that though. Purging data manually from the SYSMAN tables is pretty straight forward once you have the METRIC_GUID and find all the child tables that also have that GUID.

Hey Chris,

I have looked and it's gone, which is exactly what I would expect from
dropping the ME. However, in troubleshooting performance in getting
these metric values out of EM, I do have the ME's Metric Group ID. I
can grab all 27M+ of the metric values using:

SELECT
k.*
FROM
sysman.em_metric_items_e i,
sysman.em_metric_string_latest_e v,
sysman.em_metric_keys_e k
WHERE
i.metric_item_id = v.metric_item_id
AND i.metric_key_id = k.metric_key_id
AND i.metric_group_id = 7541;

While I'm sure I could come up with a fun way to drop all of those
manually via SQL, I'd much prefer a supported method. There's a lot to
consider, like after deleting the ~27M rows (1M less than I thought
before) from EM_METRIC_STRING_LATEST_E, there'll be only 5K rows left.
And around 8K rows left from EM_METRIC_ITEMS_E. If this was any
"normal" data system, I'd be rebuilding those tables instead of deleting
the rows. And since I don't have a comprehensive list of what tables
would need to be rebuilt, I'm much less comfortable doing that.

Thanks!
Rich

[Chris Taylor]

Well, depending on the version of the repository db, I guess you could
delete the 27m and then run a shrink space on the table?

Of course, searching for that table in Metalink returns 0 rows so no
maintenance notes available :/

Chris

Post by Chris Taylor
Have you looked at the SYSMAN MGMT_METRICS tables.
There's a METRIC_GUID (but perhaps it got removed as well?). I would
1. Delete the mgmt collected metrics from the SYSMAN schema in the repository
2. Save the metrics you want to keep to a temp table, truncate the metrics
table(s) and reinsert the saved values
OEM would need to be down while you did that though. Purging data
manually from the SYSMAN tables is pretty straight forward once you have
the METRIC_GUID and find all the child tables that also have that GUID.
Hey Chris,
I have looked and it's gone, which is exactly what I would expect from
dropping the ME. However, in troubleshooting performance in getting these
metric values out of EM, I do have the ME's Metric Group ID. I can grab
SELECT
k.*
FROM
sysman.em_metric_items_e i,
sysman.em_metric_string_latest_e v,
sysman.em_metric_keys_e k
WHERE
i.metric_item_id = v.metric_item_id
AND i.metric_key_id = k.metric_key_id
AND i.metric_group_id = 7541;
While I'm sure I could come up with a fun way to drop all of those
manually via SQL, I'd much prefer a supported method. There's a lot to
consider, like after deleting the ~27M rows (1M less than I thought before)
from EM_METRIC_STRING_LATEST_E, there'll be only 5K rows left. And around
8K rows left from EM_METRIC_ITEMS_E. If this was any "normal" data system,
I'd be rebuilding those tables instead of deleting the rows. And since I
don't have a comprehensive list of what tables would need to be rebuilt,
I'm much less comfortable doing that.
Thanks!
Rich