Summary:
Oracle Agent 2 oracle.ts.stats – CDB_FREE_SPACE query causes excessive PX workload on Multitenant RAC

Description:

We are experiencing significant Oracle database load caused by the tablespace statistics query used by Zabbix Agent 2 (oracle.ts.stats) on an Oracle Multitenant RAC environment.

The issue is reproducible and appears to be caused mainly by the way CDB_FREE_SPACE is aggregated in the current query.

Environment:

• Oracle AI Database 26ai
• Oracle Multitenant
• Oracle RAC
• 4 vCPU per RAC node
• Zabbix Agent 2 Oracle monitoring
• Oracle connection module: godror v0.48.0 + ODPI-C 5.5.1

The production Zabbix SQL observed in Oracle has SQL_ID:

a198c2at3jq30

The expensive part of the current query is:

SELECT TRUNC(SUM(bytes)) AS free,
       file_id
FROM cdb_free_space
GROUP BY file_id

This aggregates CDB_FREE_SPACE without first restricting the operation to the PDB/tablespace requested by the Zabbix item.

On our Oracle Multitenant environment, the CDB views are internally transformed into CONTAINERS FULL operations and Parallel Query is used.

Under concurrent Zabbix polling, multiple Query Coordinators (QCs) can coexist and each execution can use PX servers. On RAC, this produces a significant amount of additional CPU activity.

We performed controlled tests to isolate the SQL cost.

1. ORIGINAL QUERY

Test target:

PDB: PSPRE
Tablespace: DATA_VIGIADM_1

Controlled-test SQL_ID:
3d9jak4mg9ugk

Plan hash:
1475281604

SQL*Plus elapsed time:
4.25 seconds

PX child statistics:
CPU time:        2.4241 s
Elapsed time:    4.1615 s
Buffer gets:     258,515
Disk reads:      38,665

The execution plan shows:

PX COORDINATOR
CONTAINERS FULL DBA_TABLESPACES
CONTAINERS FULL DBA_DATA_FILES
CONTAINERS FULL DBA_FREE_SPACE

The DBA_FREE_SPACE branch processed approximately:

A-Rows:       ~165,000
Buffers:      ~257,000
Disk reads:    38,665
A-Time:       ~4.12 s

2. PROPOSED QUERY REWRITE

We changed the free-space aggregation so that CDB_FREE_SPACE is restricted to the requested tablespace/PDB before aggregation.

The relevant part of the proposed query is:

SELECT fs.con_id,
       fs.file_id,
       TRUNC(SUM(fs.bytes)) AS free_bytes
FROM cdb_free_space fs
JOIN (
    SELECT DISTINCT con_id
    FROM cdb_tablespaces
    WHERE con$name = :pdb_name
) c
  ON c.con_id = fs.con_id
WHERE fs.tablespace_name = :tbs_name
GROUP BY fs.con_id,
         fs.file_id

The result is then joined using both CON_ID and FILE_ID:

ON fs.con_id = df.con_id
AND fs.file_id = df.file_id

3. CONTROLLED TEST OF THE OPTIMIZED QUERY

Controlled-test SQL_ID:
1rbv1dmm5bsrk

Plan hash:
3566185902

SQL*Plus elapsed time:
0.09 seconds

PX child statistics:
CPU time:        0.0496 s
Elapsed time:    0.0627 s
Buffer gets:     2,099
Disk reads:      0

DBA_FREE_SPACE branch:

A-Rows:          4,819
Buffers:           892
A-Time:          ~0.02 s

The optimized query returned exactly the same JSON result as the original query for the tested PDB/tablespace:

[\{"DATA_VIGIADM_1":{"contents":"PERMANENT","file_bytes":1521703960576,"max_bytes":1614859452416,"free_bytes":350385405952,"used_bytes":1171318554624,"used_pct_max":94.23,"used_file_pct":76.97,"used_from_max_pct":72.53,"status":1}}]

4. PERFORMANCE COMPARISON

                                Current query    Proposed query
SQL*Plus elapsed                  4.25 s            0.09 s
PX child CPU                      2.4241 s          0.0496 s
PX child buffer gets              258,515           2,099
PX child disk reads                38,665               0
DBA_FREE_SPACE A-Rows             ~165K             4,819
DBA_FREE_SPACE buffers            ~257K               892
JSON result                       Same              Same

In this controlled execution, this represents approximately:

• 97.9% lower SQL*Plus elapsed time
• 98.0% lower PX child CPU time
• 99.2% fewer buffer gets

The physical-read reduction should be interpreted with caution because the second execution may benefit from cached blocks. However, the reduction in logical I/O/buffer gets demonstrates that substantially less database work is being performed.

5. NO_PARALLEL TEST

We also tested the proposed query both with and without NO_PARALLEL hints.

With NO_PARALLEL:
SQL_ID: cs5hxpgu51nnd
Plan hash: 3566185902
Buffer gets: 2,099
Disk reads: 0
SQL*Plus elapsed: 0.09 s

Without NO_PARALLEL:
SQL_ID: 1rbv1dmm5bsrk
Plan hash: 3566185902
Buffer gets: 2,099
Disk reads: 0
SQL*Plus elapsed: 0.09 s

Both variants produced the same plan hash and essentially identical execution statistics.

Furthermore, PX COORDINATOR and CONTAINERS FULL operations are still present in the execution plan.

Therefore, the performance improvement does not appear to come from NO_PARALLEL.

The important change is reducing the amount of data processed by CDB_FREE_SPACE before aggregation.

6. IMPACT OBSERVED WITH ZABBIX ENABLED/DISABLED

We also performed an operational A/B comparison with Zabbix enabled and disabled during equivalent 09:00-10:30 time windows.

ASH ON CPU samples:

PSPRE:
Zabbix ON:  1,527
Zabbix OFF:   340
Reduction:  77.7%

NEOPRE:
Zabbix ON:  1,753
Zabbix OFF:   224
Reduction:  87.2%

Total:
Zabbix ON:  3,280
Zabbix OFF:   564
Reduction:  82.8%

These values are ASH sample counts and should not be interpreted as literal CPU seconds.

When Zabbix was enabled, multiple godror sessions and PX processes associated with the tablespace query were observed. This activity disappeared when the agent was stopped.

7. RELATED ZABBIX ISSUE

We found ZBX-24269:

"Oracle tablespace query sometimes failed by timeout"

That issue describes the Oracle tablespace query as non-optimized.

According to the issue history, the implemented resolution increased the item timeout in Zabbix 7.0.22 / 7.4.6 and later.

Increasing the timeout may prevent collection failures, but it does not reduce the Oracle workload generated by the query itself.

Our tests suggest that filtering CDB_FREE_SPACE before aggregation can substantially reduce the database workload while preserving the expected JSON result.

REQUEST

Could the Zabbix team please review the SQL implementation used by oracle.ts.stats and consider changing the CDB_FREE_SPACE aggregation so that the requested PDB/tablespace is filtered before the aggregation?

We can provide:

• Complete current/original SQL
• Complete proposed SQL
• DBMS_XPLAN output with ALLSTATS LAST +PARALLEL +PREDICATE
• GV$SQL statistics
• RAC/PX session evidence
• Additional tests if required

Thank you.