One of our customers wrote:

Hi guys,

No rush on this request, it’s just something we noticed and not sure about.

Currently i am performing consultancy services at a company specialised in DBA services, and they are working with Oracle DBs.
So we use Zabbix agent2, the oracle plugin and get the monitoring perfect. At the same time, in the official template (https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates/db/oracle_agent2) there is a tablespace discovery.

This customer is running databases with massive amounts of tablespaces and after the discovery we see performance issues combined with ‘hanging’ sessions on the database. After checking the source code of the agent2 plagin( https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/src/go/plugins/oracle/handler_tablespaces.go) it seems the agent is opening 1 sessies, but executing per discovered table space various queries. As a result, eventually timeouts occur from the agent perspective and the sessions remain open, causing overhead/load on the DB. This is of course combined with the update interval of just 1 minute of the master item.

Are you aware of these issues, was there some extensive testing done on bigger oracle DBs and any guidance for me?
Again, no rush, it’s an observation where I am not 100% sure about which party (zabbix or the db) is doing some strange stuffs.

Thanks in advance

---------------------------- SOME EXTRA INFO FROM THE CUSTOMER ------------------------------

Today (28/10/21) I had another session at this customer, and we went over the SQL queries that are present in the source of the plugin. It seems are more functions are having problems.

For example: https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/src/go/plugins/oracle/handler_sessions.go

In this function there are quite a few SELECT <whatever> FROM V$SESSION followed by an UNION statement. This can (and should) be optimaized as much as possible, as this is causing issues in databases with as low as 75 table spaces. Let alone this is done on 200-300 table space databases...

Same thing with:
https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/src/go/plugins/oracle/handler_fra.go
https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/src/go/plugins/oracle/handler_sga.go
etc etc