Hello Andrzej
How often do you execute these queries? Could be that previous one has not been completed yet.
Could you please attach screenshots with LLD rules configuration?
Which version of Zabbix are you running?
Does Oracle only serve Zabbix or there are other applications using it?
Server Zabbix in version 4.0.3 we are soon upgrading to 4.2.5.
This error ORA-04030 occured when we change version Zabbix Server 3.2 to 4.0.
Then it helped to increase the PGA in the database.
Oracle work only for Zabbix Server.
These queries are made by Zabbix Server, not me at different times.
I found this queries in Zabbix Server log.
First querie select from four table graphs, graphs_items, items, item_discovery.
But why does the Zabbix Server do these queries if we don't use charts in zabbix (only a few but without LLD)?
I will add that the database is from version 2.0 and only upgrade.
I suspect that the error is somewhere in the LLD regarding graphs, because the problem described below occurs for different LLDs.
LLD for first queries: 1.select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=459689 I do: A. select * from item_discovery where parent_itemid=459689; get 3 Items. (459690, 459691, 459692). B. select * from items where itemid=459690 this is item whit zabbix key_ = net.if.in[{#IFNAME}] C. select * from hosts where hostid=16833; Found host AND there are no Graph and Graph prototypes. D. select * from graphs_items where itemid=459690 Rows 0 for this tree Items (459690, 459691, 459692). E. I did a manual query in the database from point 1. zero rows, execution time 0.032 seconds. No memory error.
Have you tried tuning kernel limits on DB server?
Yes.
We changed the setting of "ulimit" in Solaris.
unlimit -a
We're thinking about restarting the database.
These error ORA-04030 slow down the writing of new data to the database and slow down the frontend.
Hi.
I update zabbix server 4.0.3 to 4.2.6.
In my team we determine to pic up memory and swap for user Oracle (projects in Solaris) and restart database.
It helped, the error ORA-04030 disappeared.
But after upgrade I have new problem 
:
19052:20191011:093557.946 [Z3005] query failed: [-1] ORA-06550: line 2, column 1:
----- Error Stack Dump ----- ORA-06550: line 2, column 1: PLS-00103: Encountered the symbol "END" when expecting one of the following: ( begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge ----- Current SQL Statement for this session (sql_id=c70mm65m2wszv) ----- begin end;
Output oracle.trc before and after the error occurred ORA-06550.
| SAMPLE_TIME | SQL_ID | SQL_CHILD_NUMBER | SQL_OPNAME | TOP_LEVEL_SQL_ID | SQL_EXEC_ID | SQL_EXEC_START | SQL_TEXT |
| 2019/09/13 16:16:52.568 | 5fhtby4bvp5qa | 3 | SELECT | 5fhtby4bvp5qa | select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=62339 | ||
| 2019/09/13 16:16:55.569 | -1 | ||||||
| 2019/09/13 16:17:13.627 | 1gw5qapr9qbwg | 0 | SELECT | 1gw5qapr9qbwg | 16777941 | 13-wrzesień-2019 16:17:13 | select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=390821 |
| 2019/09/13 16:17:33.740 | 4vhfv5g8h56q8 | 0 | SELECT | 4vhfv5g8h56q8 | 16777950 | 13-wrzesień-2019 16:17:33 | select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=233219 |
| 2019/09/13 16:17:37.741 | 0 | SELECT | 4kgyfqwbjmcxf | ||||
| 2019/09/13 16:17:43.744 | 0 | UPDATE | 2gt698v762r4u | ||||
| 2019/09/13 16:18:45.029 | badza2dzs0b3u | 0 | SELECT | badza2dzs0b3u | select i.itemid,i.name,i.key_,i.type,i.value_type,i.delay,i.history,i.trends,i.status,i.trapper_hosts,i.units,i.formula,i.logtimefmt,i.valuemapid,i.params,i.ipmi_sensor,i.snmp_community,i.snmp_oid,i.port,i.snmpv3_securityname,i.snmpv3_securitylevel,i.snmpv3_authprotocol,i.snmpv3_authpassphrase,i.snmpv3_privprotocol,i.snmpv3_privpassphrase,i.authtype,i.username,i.password,i.publickey,i.privatekey,i.description,i.interfaceid,i.snmpv3_contextname,i.jmx_endpoint,i.master_itemid,i.timeout,i.url,i.query_fields,i.posts,i.status_codes,i.follow_redirects,i.post_type,i.http_proxy,i.headers,i.retrieve_mode,i.request_method,i.output_format,i.ssl_cert_file,i.ssl_key_file,i.ssl_key_password,i.verify_peer,i.verify_host,i.allow_traps from items i,item_discovery id where i.itemid=id.itemid and id.parent_itemid=454999 | ||
| 2019/09/13 16:20:42.415 | 0 | SELECT | 2n5s7nwbzdv8c | ||||
| 2019/09/13 16:20:47.476 | -1 | ||||||
| 2019/09/13 16:21:03.527 | -1 | ||||||
| 2019/09/13 16:21:22.580 | -1 | ||||||
| 2019/09/13 16:21:24.582 | -1 | ||||||
| 2019/09/13 16:21:25.582 | 2 | SELECT | 8qaq2ctnrad09 | ||||
| 2019/09/13 16:21:33.624 | -1 | ||||||
| 2019/09/13 16:21:34.624 | -1 | ||||||
| 2019/09/13 16:21:38.626 | amxx7xh9877as | 0 | SELECT | amxx7xh9877as | 16777941 | 13-wrzesień-2019 16:21:38 | select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=456953 |
| 2019/09/13 16:22:15.772 | dwamy0szg1w11 | 0 | SELECT | dwamy0szg1w11 | 16777821 | 13-wrzesień-2019 16:22:14 | select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=356311 |
| 2019/09/13 16:22:43.831 | 033kmav5mu6q1 | 0 | SELECT | 033kmav5mu6q1 | select id.itemid,id.key_,id.lastcheck,id.ts_delete,i.name,i.key_,i.type,i.value_type,i.delay,i.history,i.trends,i.trapper_hosts,i.units,i.formula,i.logtimefmt,i.valuemapid,i.params,i.ipmi_sensor,i.snmp_community,i.snmp_oid,i.port,i.snmpv3_securityname,i.snmpv3_securitylevel,i.snmpv3_authprotocol,i.snmpv3_authpassphrase,i.snmpv3_privprotocol,i.snmpv3_privpassphrase,i.authtype,i.username,i.password,i.publickey,i.privatekey,i.description,i.interfaceid,i.snmpv3_contextname,i.jmx_endpoint,i.master_itemid,i.timeout,i.url,i.query_fields,i.posts,i.status_codes,i.follow_redirects,i.post_type,i.http_proxy,i.headers,i.retrieve_mode,i.request_method,i.output_format,i.ssl_cert_file,i.ssl_key_file,i.ssl_key_password,i.verify_peer,i.verify_host,id.parent_itemid,i.allow_traps from item_discovery id join items i on id.itemid=i.itemid where id.parent_itemid in (399920,399921,399922,399923) | ||
| 2019/09/13 16:22:52.879 | -1 | 340v19byxartu | |||||
| 2019/09/13 16:24:04.126 | 4aj4w3cz7r3gj | 0 | SELECT | 4aj4w3cz7r3gj | select h.proxy_hostid,h.ipmi_authtype,h.ipmi_privilege,h.ipmi_username,h.ipmi_password,h.tls_connect,h.tls_accept,h.tls_issuer,h.tls_subject,h.tls_psk_identity,h.tls_psk from hosts h,items i where h.hostid=i.hostid and i.itemid=229065 | ||
| 2019/09/13 16:25:14.340 | ay7ucjd25jw7w | 0 | SELECT | ay7ucjd25jw7w | 16777977 | 13-wrzesień-2019 16:25:13 | select td.parent_triggerid,t.triggerid,t.description,t.expression,t.type,t.priority,t.comments,t.url,t.recovery_expression,t.recovery_mode,t.correlation_mode,t.correlation_tag,t.manual_close from triggers t,trigger_discovery td where t.triggerid=td.triggerid and td.parent_triggerid in (176299,176300) |
| 2019/09/13 16:25:25.344 | gbuh1dndrz35x | 0 | SELECT | gbuh1dndrz35x | select id.itemid,id.key_,id.lastcheck,id.ts_delete,i.name,i.key_,i.type,i.value_type,i.delay,i.history,i.trends,i.trapper_hosts,i.units,i.formula,i.logtimefmt,i.valuemapid,i.params,i.ipmi_sensor,i.snmp_community,i.snmp_oid,i.port,i.snmpv3_securityname,i.snmpv3_securitylevel,i.snmpv3_authprotocol,i.snmpv3_authpassphrase,i.snmpv3_privprotocol,i.snmpv3_privpassphrase,i.authtype,i.username,i.password,i.publickey,i.privatekey,i.description,i.interfaceid,i.snmpv3_contextname,i.jmx_endpoint,i.master_itemid,i.timeout,i.url,i.query_fields,i.posts,i.status_codes,i.follow_redirects,i.post_type,i.http_proxy,i.headers,i.retrieve_mode,i.request_method,i.output_format,i.ssl_cert_file,i.ssl_key_file,i.ssl_key_password,i.verify_peer,i.verify_host,id.parent_itemid,i.allow_traps from item_discovery id join items i on id.itemid=i.itemid where id.parent_itemid in (137999,138000,138001,138002) | ||
| 2019/09/13 16:26:20.549 | f8kxc2yw2wf9z | 0 | SELECT | f8kxc2yw2wf9z | select distinct i.itemid,i.flags from items i,functions f where i.itemid=f.itemid and f.triggerid in (84333,84334) | ||
| 2019/09/13 16:27:04.711 | 4srm2xqv35xsj | 23 | SELECT | 4srm2xqv35xsj | select functionid,triggerid,itemid,name,parameter from functions where (triggerid between 156052 and 156063 or triggerid in (155840,155841)) | ||
| 2019/09/13 16:27:05.712 | 9bc37aj5mbkxs | 0 | SELECT | 9bc37aj5mbkxs | 16777942 | 13-wrzesień-2019 16:27:04 | select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=279327 |
| 2019/09/13 16:27:14.773 | dy27a0v2v4hn2 | 0 | SELECT | dy27a0v2v4hn2 | 16777960 | 13-wrzesień-2019 16:27:14 | select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=387400 |
| 2019/09/13 16:27:32.840 | 0 | UPDATE | at51kxbfkugyf | ||||
| 2019/09/13 16:27:34.841 | 0 | UPDATE | cysff2p00n2n8 | ||||
| 2019/09/13 16:27:42.844 | 0 | UPDATE | 72hktnc64ghag | ||||
| 2019/09/13 16:27:44.884 | -1 | c70mm65m2wszv | |||||
| 2019/09/13 16:27:45.885 | -1 | c70mm65m2wszv | |||||
| 2019/09/13 16:27:52.887 | -1 | ||||||
| 2019/09/13 16:28:08.945 | -1 | ||||||
| 2019/09/13 16:30:23.353 | 9nvjkgk0h9f92 | 0 | SELECT | 9nvjkgk0h9f92 | select id.itemid,id.key_,id.lastcheck,id.ts_delete,i.name,i.key_,i.type,i.value_type,i.delay,i.history,i.trends,i.trapper_hosts,i.units,i.formula,i.logtimefmt,i.valuemapid,i.params,i.ipmi_sensor,i.snmp_community,i.snmp_oid,i.port,i.snmpv3_securityname,i.snmpv3_securitylevel,i.snmpv3_authprotocol,i.snmpv3_authpassphrase,i.snmpv3_privprotocol,i.snmpv3_privpassphrase,i.authtype,i.username,i.password,i.publickey,i.privatekey,i.description,i.interfaceid,i.snmpv3_contextname,i.jmx_endpoint,i.master_itemid,i.timeout,i.url,i.query_fields,i.posts,i.status_codes,i.follow_redirects,i.post_type,i.http_proxy,i.headers,i.retrieve_mode,i.request_method,i.output_format,i.ssl_cert_file,i.ssl_key_file,i.ssl_key_password,i.verify_peer,i.verify_host,id.parent_itemid,i.allow_traps from item_discovery id join items i on id.itemid=i.itemid where id.parent_itemid in (276222,276223,276224) | ||
| 2019/09/13 16:31:08.527 | 9xn03f62grskp | 0 | SELECT | 9xn03f62grskp | select id.itemid,id.key_,id.lastcheck,id.ts_delete,i.name,i.key_,i.type,i.value_type,i.delay,i.history,i.trends,i.trapper_hosts,i.units,i.formula,i.logtimefmt,i.valuemapid,i.params,i.ipmi_sensor,i.snmp_community,i.snmp_oid,i.port,i.snmpv3_securityname,i.snmpv3_securitylevel,i.snmpv3_authprotocol,i.snmpv3_authpassphrase,i.snmpv3_privprotocol,i.snmpv3_privpassphrase,i.authtype,i.username,i.password,i.publickey,i.privatekey,i.description,i.interfaceid,i.snmpv3_contextname,i.jmx_endpoint,i.master_itemid,i.timeout,i.url,i.query_fields,i.posts,i.status_codes,i.follow_redirects,i.post_type,i.http_proxy,i.headers,i.retrieve_mode,i.request_method,i.output_format,i.ssl_cert_file,i.ssl_key_file,i.ssl_key_password,i.verify_peer,i.verify_host,id.parent_itemid,i.allow_traps from item_discovery id join items i on id.itemid=i.itemid where id.parent_itemid in (194182,194183,194184,194185) | ||
| 2019/09/13 16:31:49.682 | 8wgv332wj95rv | 0 | SELECT | 8wgv332wj95rv | 16777982 | 13-wrzesień-2019 16:31:48 | select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=288840 |