[Metricbeat][Oracle] Field oracle.tablespace.space.total.bytes is missing

[kush-elastic]

Description

The field oracle.tablespace.space.total.bytes is absent from the Table Space Metric set.

Cause

The query used to retrieve the Table Space metric for other than TEMP space is missing the calculation for Total bytes based on the collected bytes of read and write operations. This calculation is present for TEMP spaces but not for other spaces.
Query: Link to the query
Performing an aggregation (SUM) on the field BYTES will provide us with the total bytes for the tablespace.

[niraj-elastic]

There is some mismatch in between Oracle metricbeat module logic and Oracle integration logic

Metricbeat module Logic

Currently in oracle there are two different kind of queries written to fetch space.total.bytes, space.free.bytes, space.used.bytes based on TEMP and NON-TEMP tablespaces.

• TEMP tablespace

Space.free.bytes :- Collected from FREE_SPACE field of DBA_TEMP_FREE_SPACE table.
Space.used.bytes :- Collected from ALLOCATED_SPACE field of DBA_TEMP_FREE_SPACE table.
Space.total.bytes :- Collected from TABLESPACE_SIZE field of DBA_TEMP_FREE_SPACE table.

• NON-TEMP tablespace

Space.free.bytes :- Collected as SUM(bytes) of DBA_FREE_SPACE table.
Space.used.bytes :- Collected as SUM(bytes) of DBA_DATA_FILES table.
Space.total.bytes :- Not collected

Integration Logic

Now currently to collect this same fields there are different queries in integration.

• TEMP tablespace

Space.free.bytes :- Collected from FREE_SPACE field of DBA_TEMP_FREE_SPACE table.
Space.used.bytes :- Collected from TABLESPACE_SIZE - ALLOCATED_SPACE field of DBA_TEMP_FREE_SPACE table.

• NON-TEMP tablespace

Space.free.bytes :- Collected as SUM(bytes) of DBA_FREE_SPACE table.
Space.used.bytes :- Collected as SUM(bytes) of DBA_DATA_FILES table.

In integration Space.total.bytes is total size of TEMP and NON-TEMP tablespaces.

Space.total.bytes :- Total of ( SUM(bytes) of DBA_DATA_FILES + SUM(bytes) of DBA_TEMP_FILES )

@agithomas can you please provide your thoughts here, based on above possible values what should be set as a value of Space.total.bytes for NON-TEMP tables in metricbeat module?

[agithomas]

@niraj-elastic , Unless there exists any reason for not using the logic used in the Integrations, the same approach can be used in Oracle Metricbeat module.

[niraj-elastic]

@agithomas, Currently if tablespace is TEMP than space.total.bytes is collected as total bytes of TEMP tablespace. If we choose to implement integration logic, we will have to make change in logic of TEMP tablespace as well, because in integration space.total.bytes field shows sum of all the bytes of TEMP and NON-TEMP tablespaces (total bytes of all the tablespaces). So if we choose to go with integration logic there will be major logical changes for current users of this metricset.

[agithomas]

Ok, what are you proposing?

[niraj-elastic]

@agithomas By doing some more analysis it seems that space.total.bytes is incorrectly set for TEMP tablespace as well in metricbeat module. based on this description, space.total.bytes should be sum of all the tablespaces just like integration. So changing logic to integration logic ( SUM(bytes) of DBA_DATA_FILES + SUM(bytes) of DBA_TEMP_FILES ) of space.total.bytes field for TEMP and adding same logic for NON-TEMP tablespace seems right approach, let me know your thoughts here.