Steps to reproduce:

1. Zabbix server 6.x with Oracle db backend
2. add proxy, and register 200+(300+) hosts with that proxy
3. 13504:20240206:122746.024 cannot collect configuration data for proxy "zabbix_proxy": failed to get data from table "item_preproc"
4. 13517:20240206:110559.456 [Z3005] query failed: [-1] ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT [select item_preprocid,item
   id,step,type,params,error_handler,error_handler_params from item_preproc where (

Result:
See log files

Expected:
expected that zabbix_server was able to collect and pass configuration to its proxies

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.

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

i've found exact the same bug - 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 voila.

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.

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?