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!