Hello everyone,
We would like to share a performance issue we have identified with the Oracle tablespace monitoring functionality in Zabbix Agent 2, specifically the oracle.ts.stats query.
We are experiencing this issue in Oracle Multitenant RAC environments, where the tablespace monitoring query generates excessive CPU consumption, significant I/O activity, and a large number of Parallel Query (PX) processes. 1. Related forum discussion
We found another user reporting a very similar problem involving the same SQL statement:
Zabbix Oracle template 7.4-1 – Tablespace query RC contention issue on Multitenant DB
Although that user mainly reported Result Cache contention, our investigation identified excessive CPU and Parallel Query workload associated with the same SQL implementation. 2. Root cause identified
The current oracle.ts.stats query includes the following aggregation:
SELECT TRUNC(SUM(bytes)) AS free,
file_id
FROM cdb_free_space
GROUP BY file_id
The problem is that CDB_FREE_SPACE is aggregated without restricting the data to the specific PDB and tablespace being monitored.
In Oracle Multitenant environments, accessing these CDB views can involve CONTAINERS FULL operations.
When several monitoring requests overlap, Oracle may execute multiple Query Coordinators and PX servers across RAC instances, significantly increasing the CPU and I/O workload.
In our investigation, we identified:
The sessions were associated with the godror module and zabbix_agent2. 3. Optimized SQL tested
We developed and tested an optimized version of the query.
The main changes are:
Our controlled performance tests produced the following results:
This represents approximately a 98% reduction in execution time and PX CPU consumption, and a 99% reduction in buffer gets in the tested scenario.
Both versions returned identical JSON results. 4. Additional evidence
We also performed a controlled comparison with Zabbix tablespace monitoring enabled and disabled.
The results showed an 82.8% reduction in ASH ON CPU samples across the monitored databases when monitoring was disabled.
This provides additional evidence that the current tablespace monitoring implementation can contribute significantly to database CPU consumption. 5. Official Zabbix issue
We have opened the following issue with the technical investigation and proposed SQL optimization:
ZBX-28141 — Oracle Agent 2 oracle.ts.stats – CDB_FREE_SPACE query causes excessive PX workload on Multitenant RAC
The issue contains the investigation details, execution plans, performance measurements, and the proposed optimized SQL statement. 6. Request to the Zabbix development team
We would appreciate it if the Zabbix development team could review ZBX-28141 and consider incorporating the optimized SQL into the Oracle Agent 2 tablespace monitoring implementation.
Filtering CDB_FREE_SPACE before aggregation could substantially reduce unnecessary CPU and I/O consumption in large Oracle Multitenant environments.
We believe this change would benefit other organizations monitoring Oracle RAC and Multitenant databases with Zabbix.
If anyone else is experiencing similar CPU consumption, Parallel Query activity, or Result Cache contention associated with oracle.ts.stats, we would be interested in comparing findings.
Thank you.
We would like to share a performance issue we have identified with the Oracle tablespace monitoring functionality in Zabbix Agent 2, specifically the oracle.ts.stats query.
We are experiencing this issue in Oracle Multitenant RAC environments, where the tablespace monitoring query generates excessive CPU consumption, significant I/O activity, and a large number of Parallel Query (PX) processes. 1. Related forum discussion
We found another user reporting a very similar problem involving the same SQL statement:
Zabbix Oracle template 7.4-1 – Tablespace query RC contention issue on Multitenant DB
Although that user mainly reported Result Cache contention, our investigation identified excessive CPU and Parallel Query workload associated with the same SQL implementation. 2. Root cause identified
The current oracle.ts.stats query includes the following aggregation:
SELECT TRUNC(SUM(bytes)) AS free,
file_id
FROM cdb_free_space
GROUP BY file_id
The problem is that CDB_FREE_SPACE is aggregated without restricting the data to the specific PDB and tablespace being monitored.
In Oracle Multitenant environments, accessing these CDB views can involve CONTAINERS FULL operations.
When several monitoring requests overlap, Oracle may execute multiple Query Coordinators and PX servers across RAC instances, significantly increasing the CPU and I/O workload.
In our investigation, we identified:
- SQL_ID: a198c2at3jq30
- Plan hash value: 1475281604
- Multiple concurrent Zabbix monitoring sessions.
- Parallel Query execution across both RAC nodes.
- Significant accumulated CPU time, buffer gets, and disk reads.
- Repeated execution of the same SQL statement.
The sessions were associated with the godror module and zabbix_agent2. 3. Optimized SQL tested
We developed and tested an optimized version of the query.
The main changes are:
- Restrict CDB_FREE_SPACE to the requested PDB using CON_ID.
- Apply the tablespace filter before aggregating free-space information.
- Join the results using both CON_ID and FILE_ID.
- Preserve the original JSON output and monitoring functionality.
Our controlled performance tests produced the following results:
| Execution time | 4.25 seconds | 0.09 seconds |
| PX CPU time | 2.4241 seconds | 0.0496 seconds |
| Buffer gets | 258,515 | 2,099 |
| Disk reads | 38,665 | 0 |
Both versions returned identical JSON results. 4. Additional evidence
We also performed a controlled comparison with Zabbix tablespace monitoring enabled and disabled.
The results showed an 82.8% reduction in ASH ON CPU samples across the monitored databases when monitoring was disabled.
This provides additional evidence that the current tablespace monitoring implementation can contribute significantly to database CPU consumption. 5. Official Zabbix issue
We have opened the following issue with the technical investigation and proposed SQL optimization:
ZBX-28141 — Oracle Agent 2 oracle.ts.stats – CDB_FREE_SPACE query causes excessive PX workload on Multitenant RAC
The issue contains the investigation details, execution plans, performance measurements, and the proposed optimized SQL statement. 6. Request to the Zabbix development team
We would appreciate it if the Zabbix development team could review ZBX-28141 and consider incorporating the optimized SQL into the Oracle Agent 2 tablespace monitoring implementation.
Filtering CDB_FREE_SPACE before aggregation could substantially reduce unnecessary CPU and I/O consumption in large Oracle Multitenant environments.
We believe this change would benefit other organizations monitoring Oracle RAC and Multitenant databases with Zabbix.
If anyone else is experiencing similar CPU consumption, Parallel Query activity, or Result Cache contention associated with oracle.ts.stats, we would be interested in comparing findings.
Thank you.