EM13: Performance issue querying SYSMAN.GC_METRIC_STR_VALUES

[Rich J]

Hey all,

I have a Metric Extension in EM 13.2.0.0.0 whose values are strings.
But querying the SYSMAN.GC_METRIC_STR_VALUES view on the 12.2.0.1.0 OMS
is very slow, taking 2 minutes to return the first 500 rows from the
simple cursor:

SELECT * FROM SYSMAN.GC_METRIC_STR_VALUES

When trying to return metric values in past versions of EM, I've
filtered the queries with various columns like:

SELECT
*
FROM
sysman.gc_metric_str_values
WHERE
entity_type = 'oracle_database'
AND entity_name = 'myoradb'
AND entity_guid = '8C686DE17F33936EAFB49CA9AE1A0E6D'
AND metric_group_name = 'ME$myext'
AND metric_column_name = ('fun_text')
AND metric_column_guid = '6C80A9B8B5A3AD57D9412874F5B0BB2D'

Still no joy. Since Oracle locks the stats on the SYSMAN schema, I've
spent some time pulling apart the (many!) underlying views and tables,
but bypassing the view seems like it could a maintenance issue for EM
upgrades.

Has anyone else seen poor performance on this view?

TIA,
Rich

[Kellyn Pot'Vin-Gorman]

Hey Rich,
Do you have the execution plan for this one and can you tell me if you ran
fixed stats on this database after the upgrade?

Thanks,
Kellyn


[image: Kellyn Pot'Vin on about.me]

*Kellyn Pot'Vin-Gorman*
DBAKevlar Blog <http://dbakevlar.com>
President Denver SQL Server User Group <http://denversql.org/>
about.me/dbakevlar

Post by Rich J
Hey all,
I have a Metric Extension in EM 13.2.0.0.0 whose values are strings. But
querying the SYSMAN.GC_METRIC_STR_VALUES view on the 12.2.0.1.0 OMS is very
SELECT * FROM SYSMAN.GC_METRIC_STR_VALUES
When trying to return metric values in past versions of EM, I've filtered
SELECT
*
FROM
sysman.gc_metric_str_values
WHERE
entity_type = 'oracle_database'
AND entity_name = 'myoradb'
AND entity_guid = '8C686DE17F33936EAFB49CA9AE1A0E6D'
AND metric_group_name = 'ME$myext'
AND metric_column_name = ('fun_text')
AND metric_column_guid = '6C80A9B8B5A3AD57D9412874F5B0BB2D'
Still no joy. Since Oracle locks the stats on the SYSMAN schema, I've
spent some time pulling apart the (many!) underlying views and tables, but
bypassing the view seems like it could a maintenance issue for EM upgrades.
Has anyone else seen poor performance on this view?
TIA,
Rich

[Chris Taylor]

I don't know of a particular (specific) problem with that view but have
some things you could try:

1. Gather fixed_object_stats
2. Gather dictionary stats
3. Gather System Stats (either using interval or noworkload if the system
isn't regularly busy) (this is where the optimizer gets primary values for
many cost calculations)
4. If the repository database is 12.1.0.x make sure you disable
optimizer_adaptive_features

Chris

Post by Rich J
Hey all,
I have a Metric Extension in EM 13.2.0.0.0 whose values are strings. But
querying the SYSMAN.GC_METRIC_STR_VALUES view on the 12.2.0.1.0 OMS is very
SELECT * FROM SYSMAN.GC_METRIC_STR_VALUES
When trying to return metric values in past versions of EM, I've filtered
SELECT
*
FROM
sysman.gc_metric_str_values
WHERE
entity_type = 'oracle_database'
AND entity_name = 'myoradb'
AND entity_guid = '8C686DE17F33936EAFB49CA9AE1A0E6D'
AND metric_group_name = 'ME$myext'
AND metric_column_name = ('fun_text')
AND metric_column_guid = '6C80A9B8B5A3AD57D9412874F5B0BB2D'
Still no joy. Since Oracle locks the stats on the SYSMAN schema, I've
spent some time pulling apart the (many!) underlying views and tables, but
bypassing the view seems like it could a maintenance issue for EM upgrades.
Has anyone else seen poor performance on this view?
TIA,
Rich

[Rich J]

Post by Chris Taylor
1. Gather fixed_object_stats
2. Gather dictionary stats
3. Gather System Stats (either using interval or noworkload if the system isn't regularly busy) (this is where the optimizer gets primary values for many cost calculations)
4. If the repository database is 12.1.0.x make sure you disable optimizer_adaptive_features

Hey Chris,

The OMS repository is 12.2, so 1 and 2 are automagic now, as I'm running
the auto stats job. Number 3, however, is interesting. They're only
half collected (NW), so that could have some effect. I'll give it a
shot.

Thanks, Chris! And thanks to Kellyn, too!

Rich