Zabbix Oracle template 7.4-1 – Tablespace query RC contention issue on Multitenant DB

[razimhuseynov]

Hello,
We are using the Zabbix Oracle template 7.4-1 for monitoring Oracle CDB databases.
The query used for the tablespace macro {$ORACLE.TBS.USED.PCT.FROM.MAX.HIGH} is executed simultaneously on every PDB.
The problematic part of the query is:
(SELECT TRUNC(SUM(BYTES)) AS FREE,
FILE_ID
FROM CDB_FREE_SPACE
GROUP BY FILE_ID) f

Observed issue:

• When the query runs in parallel across all PDBs, sessions block each other with Wait Event: enq: RC - Result Cache: Contention.
• Because CDB_FREE_SPACE is constantly changing, the result cache entries quickly become stale, which increases contention even further.
• The query sometimes takes 30+ seconds, times out in Zabbix, and generates a heavy load on the database.

Full query from the template:
SELECT JSON_ARRAYAGG(
JSON_OBJECT(
TABLESPACE_NAME VALUE JSON_OBJECT(
'contents' VALUE CONTENTS,
'file_bytes' VALUE FILE_BYTES,
'max_bytes' VALUE MAX_BYTES,
'free_bytes' VALUE FREE_BYTES,
'used_bytes' VALUE USED_BYTES,
'used_pct_max' VALUE USED_PCT_MAX,
'used_file_pct' VALUE USED_FILE_PCT,
'used_from_max_pct' VALUE USED_FROM_MAX_PCT,
'status' VALUE STATUS
)
) RETURNING CLOB
)
FROM (SELECT df.TABLESPACE_NAME AS TABLESPACE_NAME,
df.CONTENTS AS CONTENTS,
NVL(SUM(df.BYTES), 0) AS FILE_BYTES,
NVL(SUM(df.MAX_BYTES), 0) AS MAX_BYTES,
NVL(SUM(f.FREE), 0) AS FREE_BYTES,
NVL(SUM(df.BYTES) - SUM(f.FREE), 0) AS USED_BYTES,
ROUND(CASE SUM(df.MAX_BYTES)
WHEN 0 THEN 0
ELSE (SUM(df.BYTES) / SUM(df.MAX_BYTES) * 100)
END, 2) AS USED_PCT_MAX,
ROUND(CASE SUM(df.BYTES)
WHEN 0 THEN 0
ELSE (NVL(SUM(df.BYTES) - SUM(f.FREE), 0)) / SUM(df.BYTES) * 100
END, 2) AS USED_FILE_PCT,
ROUND(CASE SUM(df.MAX_BYTES)
WHEN 0 THEN 0
ELSE NVL(SUM(df.BYTES) - SUM(f.FREE), 0) / SUM(df.MAX_BYTES) * 100
END, 2) AS USED_FROM_MAX_PCT,
CASE df.STATUS
WHEN 'ONLINE' THEN 1
WHEN 'OFFLINE' THEN 2
WHEN 'READ ONLY' THEN 3
ELSE 0
END AS STATUS
FROM (SELECT ct.CON$NAME AS CON_NAME,
cdf.FILE_ID,
ct.CONTENTS,
ct.STATUS,
cdf.FILE_NAME,
cdf.TABLESPACE_NAME,
TRUNC(cdf.BYTES) AS BYTES,
TRUNC(GREATEST(cdf.BYTES, cdf.MAXBYTES)) AS MAX_BYTES
FROM CDB_DATA_FILES cdf,
CDB_TABLESPACES ct
WHERE cdf.TABLESPACE_NAME = ct.TABLESPACE_NAME
AND cdf.CON_ID = ct.CON_ID
AND (ct.CON$NAME = :1 or (ct.CON$NAME is null and ct.CON_ID = 0))) df,
(SELECT TRUNC(SUM(BYTES)) AS FREE,
FILE_ID
FROM CDB_FREE_SPACE
GROUP BY FILE_ID) f
WHERE df.FILE_ID = f.FILE_ID (+)
AND df.TABLESPACE_NAME = :2
GROUP BY df.CON_NAME,
df.TABLESPACE_NAME,
df.CONTENTS,
df.STATUS);

My question:

• Has anyone else experienced this issue?
• Since CDB_FREE_SPACE is constantly changing, are others seeing the same Result Cache contention when this query runs across multiple PDBs?
• If you’ve encountered this, how did you handle it?

Thanks!

[Moises Moreno]

Hello,

We are experiencing a very similar issue with the Oracle tablespace monitoring query used by Zabbix Agent 2 (oracle.ts.stats).

In our case, the problem occurs in an Oracle 26ai Multitenant RAC environment, where the current Zabbix query generates a significant amount of CPU activity due to Parallel Query (PX) execution.

After investigating the issue in detail, we identified the same problematic SQL fragment mentioned in your post:
SELECT TRUNC(SUM(bytes)) AS free,
file_id
FROM cdb_free_space
GROUP BY file_id


The main problem is that CDB_FREE_SPACE is aggregated without first restricting the data to the PDB and tablespace being monitored.

In our environment, Oracle internally transforms the CDB views into CONTAINERS FULL operations. When several Zabbix monitoring queries execute concurrently, multiple Query Coordinators and PX servers can generate substantial CPU and I/O overhead across the RAC nodes.

We developed and tested an optimized version of the query.

The main improvement is to filter CDB_FREE_SPACE by PDB (CON_ID) and tablespace before performing the aggregation, and then join the results using both CON_ID and FILE_ID.

Our controlled tests produced the following results:

Execution time	4.25 s	0.09 s
PX CPU time	2.4241 s	0.0496 s
Buffer gets	258,515	2,099
Disk reads	38,665	0

This represents approximately:

• 97.9% reduction in execution time
• 98.0% reduction in PX CPU time
• 99.2% reduction in buffer gets

Both queries returned exactly the same JSON result for the tested PDB and tablespace.

We also performed a Zabbix ON/OFF comparison, which showed an 82.8% reduction in ASH ON CPU samples across the monitored databases when Zabbix monitoring was disabled.

These results strongly suggest that the current SQL implementation can generate unnecessary database workload, particularly in large Oracle Multitenant RAC environments.

We have opened an official Zabbix issue with the complete technical investigation and our proposed SQL optimization:

ZBX-28141 — Oracle Agent 2 oracle.ts.stats – CDB_FREE_SPACE query causes excessive PX workload on Multitenant RAC

https://support.zabbix.com/browse/ZBX-28141

The issue includes execution plans, SQL statistics, performance comparisons, evidence of RAC/PX activity, and the proposed query rewrite.

We would appreciate it if the Zabbix development team could review this issue and consider updating the SQL used by oracle.ts.stats to filter CDB_FREE_SPACE before aggregation.

We believe this optimization could significantly reduce the CPU and I/O overhead generated by Oracle tablespace monitoring, while preserving the existing monitoring functionality and JSON output.

Hopefully, this investigation will also help other users experiencing similar problems.

Thank you.
Regards,
Moises Moreno