Re: EM13: Performance issue querying SYSMAN.GC_METRIC_STR_VALUES

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

On Thu, Aug 23, 2018 at 11:54 AM Rich J <rjoralist3_at_society.servebeer.com> wrote:

> 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
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l