problems zabbix 6.4.1 with oracle database backend

[Geo Quit]

Hello!

we have zabbix server 6.4.1 with oracle database backend with several zabbix-proxies connected
we've getting an error "cannot collect configuration data for proxy "proxy_name": failed to get data from table "item_preproc""
the reason for this error is:
[Z3005] query failed: [-1] ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
oracle terminates session due to error.
the reason for this behaviour is that zabbix generates very long unbinded queries - 200Kb and even longer. Oracle just cannot parse it.

i've found exact the same bug - https://support.zabbix.com/browse/ZBX-17121
```
I've solved the problem.

Changed the file src/libs/zbxdbhigh/proxy.c from:
#define ZBX_MAX_IDS_PER_SELECT 10000

To:
#define ZBX_MAX_IDS_PER_SELECT 100

Rebuild the server and voilà.

```

but in version 6.4 - there is no parameter ZBX_MAX_IDS_PER_SELECT

after a little digging - i've found parameter
#define ZBX_MAX_SQL_SIZE 262144

i've decreased it to 16384, rebuild zabbix_server binary
but - no luck.
zabbix_server still generates very long unbinded queries which oracle cannot parse. the size of generated queries can be even 250Kb. It seems, that value of parameter ZBX_MAX_SQL_SIZE - is ignored somewhere.

please help.

[cyber]

Not that it helps you here...
You should think of backend change... from v7 Oracle will be deprecated https://www.zabbix.com/documentation...-db-deprecated

Any possibility to increase PGA on Oracle side?

[Geo Quit]

we've tried to increase PGA - big enough queries still getting killed. and it takes a lot of time for oracle to parse it - it leads to timeouts.
we've already have rather big limit - pga_aggregate_limit 12G

after looking at sources - found out that configuration for proxy is collected at zabbix_server/proxyconfigread/proxyconfig_read.c
ZBX_MAX_SQL_SIZE - isn't metioned there.

also i've found constant MAX_EXPRESSIONS 950 at libs/zbxdbhigh/db.c in functions zbx_db_add_condition_alloc, zbx_db_add_str_condition_alloc

may be decreasing it will help?

also, i've checked that code in recent version of zabbix_server and in 7.0.0-beta1 - that behaviour persist in all of them.
i think it's a bug, because if someone have even not big installation with proxy (and couple of hundreds of hosts behind) with oracle db backend
zabbix_server fails to pass configuration to proxy (active/passive mode - doesn't matter).

[cyber]

You probably need some comments from devs... you can submit a bug request to https://support.zabbix.com/browse/ZBX

[Geo Quit]

ok thank you
i've registered a bugreport

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