Zabbix 3.2 + Oracle 12c + DBsyncer = issue

[MikleZZzz]

Hi guys, i'm new here, pls excuse me for my english.

I ran into issue with oracle as backend for zabbix (i have huge positive experience with mysql backend also).
First of all, here is my stats:

Zabbix server is running	Yes	localhost:10051
Number of hosts (enabled/disabled/templates)	599	497 / 3 / 99
Number of items (enabled/disabled/not supported)	41933	40785 / 504 / 644
Number of triggers (enabled/disabled [problem/ok])	3906	3805 / 101 [34 / 3771]
Number of users (online)	13	4
Required server performance, new values per second	201.3

Very humble instance, as you can see.
Nevertheless, every time i restart Zabbix daemon Dbsyncer process goes up to 100% and hangs for about 5-10 minutes, it looks like:

zabbix   17667  0.0  1.5 497700 59176 ?        S    08:03   0:02 zabbix_server: history syncer #1 [synced 999 items in 658.679820 sec, syncing history]
zabbix   17668  0.0  1.4 496092 57088 ?        S    08:03   0:02 zabbix_server: history syncer #2 [synced 998 items in 637.566454 sec, syncing history]
zabbix   17669  0.0  1.4 496728 57956 ?        S    08:03   0:02 zabbix_server: history syncer #3 [synced 994 items in 649.465721 sec, syncing history]
zabbix   17670  0.0  1.4 496064 57128 ?        S    08:03   0:02 zabbix_server: history syncer #4 [synced 994 items in 642.839733 sec, syncing history]

After all, Dbsyncer starts to work as usual (syncing history) and all goes well.

[MikleZZzz]

Ok, i'm back.

The funniest thing begins at the start of next hour - Dbsyncer starts to do "SOMETHING" (i noticed a lot of updates in trends_uint with WHERE clause like
itemid=xxxx and clock="last hour in epoch format").
This process continues for about 30-60 minutes and during this time zabbix doesn't collect any items (dbsyncer is busy so all data goes to cache until it fall to zero).
Ultimately the "war" with trends_uint ends and dbsyncer starts to perform as usual with out any problems for weeks/months....until next restart. And the life cycle starts again.

[kloczek]

zabbix   17667  0.0  1.5 497700 59176 ?        S    08:03   0:02 zabbix_server: history syncer #1 [synced 999 items in 658.679820 sec, syncing history]
zabbix   17668  0.0  1.4 496092 57088 ?        S    08:03   0:02 zabbix_server: history syncer #2 [synced 998 items in 637.566454 sec, syncing history]
zabbix   17669  0.0  1.4 496728 57956 ?        S    08:03   0:02 zabbix_server: history syncer #3 [synced 994 items in 649.465721 sec, syncing history]
zabbix   17670  0.0  1.4 496064 57128 ?        S    08:03   0:02 zabbix_server: history syncer #4 [synced 994 items in 642.839733 sec, syncing history]

After all, Dbsyncer starts to work as usual (syncing history) and all goes well.

You issue has nothing to do with zabbix per se.
Zabbix stack with about 200 nvps is it very small setup and if DB backend is chocking you should turn you eye on why this DB backend is not able to handle so small DB inserts queries traffic.
Zabbix db history syncers are responsible for injecting into DB history* tables new data coming from agents and proxies. What exactly is wrong with Oracle engine settings is not possible to say over only data collected by zabbix.
I'm only guessing that you have probably quite hing number of items with 5 or 10 min sampling rate and despite quite low "Required server performance" in peaks you may have temporary some congestion. However this is only one of many possible causes. What really happens is not possible to say without looking on DB stats.

[MikleZZzz]

you should turn you eye on why this DB backend is not able to handle so small DB inserts queries traffic.

yep, you're right. It is why i'am here. I don't have enough exp in Ora so any advice and suggestions will be greatly appreciated.

A couple of words about backend.
It's 12c with no tuning. Runs on VM with 8 CPU, 14Gb Ram.
History and trends tables are partitioned (Housekeeper is disabled for hist/trends). Indexes Blevels are 2 or below.

[MikleZZzz]

Hi guys,i'm back again The issue was fixed with patch from https://support.zabbix.com/browse/ZBX-5051.

The culprit is a bunch of hard parse during DBsync acitivity because Oracle can't apply cursor_sharing to PL/SQL inside BEGIN/END.

Now, my instance is flying (Tested on zabbix3.4,oracle12c)