[Oracle Module] New sysmetric metricset

[andresrc]

Create a new sysmetric metricset to complement the current performance one.

Problems

• Elastic metricbeat module is running queries and pulling metrics every 30 or 60 seconds, these queries take a significant amount of time as well which results in stale statistics and inaccurate aggregations.
• Another problem with the queries used by the metricbeat module is that they don’t account for the rack installations. If there is a rack-installation, metricbeat is not going to provide correct results, because nobody knows whether the data reported is from the current node that you connected to or is an average across all nodes in the rack.

Solution

• What Oracle DBAs usually do is actually pull the pre-calculated metrics from the V$SYSMETRIC table (called System View).
• It provides about 200+ different metrics for a short duration (15 seconds) and a long duration (60 seconds).
• DBAs usually need around 10 out of these 200+ metrics and Oracle will keep adding more metrics to this table in the future, so, there has to be a way to dynamically filter in or query only those that are suggested by the user with the help of a keyword or Regex.
• These metrics must be queried for long-duration because DBAs need this data for historical data analysis and querying for 15 seconds duration will overload the system and may lead to performance issues.

User Stories

• As an oracle DBA Elastic user, I would like to be able to access the system metrics from Oracle System View through the metricbeat module so that I could be sure that the data is not stale and the aggregations are as precise as possible.
• As an Oracle DBA Elastic user, I would like to filter through metrics by providing keyword or wildcard regex to the query so that metricbeat collects only a subset of the metrics when needed and does not overburden the system.

Open points

• Define the ways to filter the available metrics.
• The metrics are dynamic, based on the content of the table. How are mappings defined?
• What happens if we want to have different metrics with different collection periods? Can we instantiate the metricset twice?

[jsoriano]

User Stories

• As an oracle DBA Elastic user, I would like to be able to access the system metrics from Oracle System View through the metricbeat module so that I could be sure that the data is not stale and the aggregations are as precise as possible.
• As an Oracle DBA Elastic user, I would like to filter through metrics by providing keyword or wildcard regex to the query so that metricbeat collects only a subset of the metrics when needed and does not overburden the system.

I wonder if we should try to cover these advanced use cases with the sql module and a bit of documentation for the specific use case of this System View. We already have examples for similar use cases in the docs: https://www.elastic.co/guide/en/beats/metricbeat/8.2/metricbeat-module-sql.html#_example_capture_innodb_related_metrics.

A configuration like this one could cover these use cases:

- module: sql
  metricsets:
    - query
  period: 10s
  hosts: [...]
  driver: "oracle"
  sql_query: "SELECT metric_name, value FROM V$SYSMETRIC WHERE metric_name LIKE 'Logical Reads%'"
  sql_response_format: variables

We may need to normalize the names, I think they are only lowercased now, but we should avoid spaces problematic characters as dots and so on. Processors might also help here.

For well-known use cases we may provide different curated metricsets, that collect sets of metrics that are meaningful to investigate a given problem. These metricsets would be disabled by default, but users could enable them. We could provide dashboards for these specific use cases, and optimized field mappings, dimensions and so on, what would help on saving disk space.

A somehow generic sysmetric metricset may be a middle ground between them, but it will overlap with the sql module, it can be complex to support good dynamic mappings and so on, and it won't be as flexible as the sql module, nor as curated as specific metricsets.
I think it'd be better to focus on addressing the limitations of the sql module to cover advanced use cases, and on metricsets for specific sets of variables or use cases that may be relevant to not so advanced users.

[akshay-saraswat]

I wonder if we should try to cover these advanced use cases with the sql module and a bit of documentation for the specific use case of this System View. We already have examples for similar use cases in the docs: https://www.elastic.co/guide/en/beats/metricbeat/8.2/metricbeat-module-sql.html#_example_capture_innodb_related_metrics.

There are two reasons for making these changes in the Oracle module and not the SQL module right now

1. Customers who asked for these enhancements cannot wait longer. SQL module changes would increase the scope and time to market.
2. This curation is only for the additional fields requested from the same table that is used to collect the default fields. Imagine I configured the Oracle module, analyzed OOTB dashboards, and after a while realized that I need another couple of metrics to complete my report. In such a scenario, I would fiddle with the same Oracle integration and would not think about configuring another integration. We cannot ask a user to create a new integration altogether just for a couple of additional metrics. And such a request is really frequent in the case of SQL databases.

I agree that this functionality should be shared across all the SQL database integrations using the generic SQL input. But I would be concerned as a customer advocate if we provided this functionality only in the SQL module but not in the Oracle module.

I believe it's okay to provide this curation as a separate metricset within the existing Oracle module for intuitive discovery and easier use-case resolution.

[jsoriano]

SGTM, thanks for the clarifications!