[ZBX-21754] Oracle DB slow query when rownum is using Created: 2022 Oct 11 Updated: 2024 Apr 10 Resolved: 2023 Jan 23 |
|
Status: | Closed |
Project: | ZABBIX BUGS AND ISSUES |
Component/s: | Server (S) |
Affects Version/s: | None |
Fix Version/s: | 5.0.31rc1, 6.0.13rc1, 6.2.7rc1, 6.4.0beta6, 6.4 (plan) |
Type: | Problem report | Priority: | Trivial |
Reporter: | Aleksey Volodin | Assignee: | Vladislavs Sokurenko |
Resolution: | Fixed | Votes: | 0 |
Labels: | None | ||
Remaining Estimate: | Not Specified | ||
Time Spent: | Not Specified | ||
Original Estimate: | Not Specified |
Attachments: |
![]() ![]() ![]() ![]() ![]() ![]() ![]() |
||||
Issue Links: |
|
||||
Team: | |||||
Sprint: | Sprint 95 (Dec 2022), Sprint 96 (Jan 2023) | ||||
Story Points: | 0.25 |
Description |
Oracle 19.1.0 with partitioning with internal jobs and procedures. DB host performance and health is fine. There is no load for disks or CPU/memory. Default optimizer_adaptive is using. Zabbix Server 5.0.24 with Proxy. For now all Proxy are disabled. Main issue - history_uint related queries extremely slow. We cannot even get get any last data or graphs. Important point here - trends work fine. We can get graphs for more then 5 day period, when trends is using. Slow queries is mostly this one: 1529 19:43:02.781010 write(8, " 1529:20221010:194302.780 slow query: 587.294631 sec, \"select * from (select clock,ns,value from history_uint where itemid=3290659 and clock<=1665312259 and clock>1664707459 order by clock desc) where rownum<=2\"\n", 213) = 213 <0.000013> Line 353: 1529 19:43:02.781010 write(8, " 1529:20221010:194302.780 slow query: 587.294631 sec, \"select * from (select clock,ns,value from history_uint where itemid=3290659 and clock<=1665312259 and clock>1664707459 order by clock desc) where rownum<=2\"\n", 213) = 213 <0.000013> Line 373: 1519 19:43:02.781607 write(8, " 1519:20221010:194302.781 slow query: 587.259824 sec, \"select * from (select clock,ns,value from history_uint where itemid=3607148 and clock<=1665312248 and clock>1664707448 order by clock desc) where rownum<=1\"\n", 213) = 213 <0.000027> Line 373: 1519 19:43:02.781607 write(8, " 1519:20221010:194302.781 slow query: 587.259824 sec, \"select * from (select clock,ns,value from history_uint where itemid=3607148 and clock<=1665312248 and clock>1664707448 order by clock desc) where rownum<=1\"\n", 213) = 213 <0.000027> Line 395: 1504 19:43:02.782209 write(8, " 1504:20221010:194302.781 slow query: 587.079826 sec, \"select * from (select clock,ns,value from history_uint where itemid=4863552 and clock<=1665312253 and clock>1664707453 order by clock desc) where rownum<=2\"\n", 213) = 213 <0.000016> Line 395: 1504 19:43:02.782209 write(8, " 1504:20221010:194302.781 slow query: 587.079826 sec, \"select * from (select clock,ns,value from history_uint where itemid=4863552 and clock<=1665312253 and clock>1664707453 order by clock desc) where rownum<=2\"\n", 213) = 213 <0.000016> Line 740: 1505 19:44:51.229668 write(8, " 1505:20221010:194451.229 slow query: 671.026438 sec, \"select * from (select clock,ns,value from history_uint where itemid=1128817 and clock<=1665312257 and clock>1664707457 order by clock desc) where rownum<=3\"\n", 213) = 213 <0.000016> Line 740: 1505 19:44:51.229668 write(8, " 1505:20221010:194451.229 slow query: 671.026438 sec, \"select * from (select clock,ns,value from history_uint where itemid=1128817 and clock<=1665312257 and clock>1664707457 order by clock desc) where rownum<=3\"\n", 213) = 213 <0.000016> Line 754: 1531 19:44:51.235144 write(8, " 1531:20221010:194451.235 slow query: 567.998976 sec, \"select * from (select clock,ns,value from history_uint where itemid=2915063 and clock<=1665312263 and clock>1664707463 order by clock desc) where rownum<=2\"\n", 213) = 213 <0.000015> Line 754: 1531 19:44:51.235144 write(8, " 1531:20221010:194451.235 slow query: 567.998976 sec, \"select * from (select clock,ns,value from history_uint where itemid=2915063 and clock<=1665312263 and clock>1664707463 order by clock desc) where rownum<=2\"\n", 213) = 213 <0.000015> Where select * from (select . . . ) where rownum<=2 is using. Query without rownum take seconds: But with: select * from (select clock,ns,value from history_uint where itemid=2915063 and clock<=1663707463 and clock>1663501938 order by clock desc) where rownum<=2; From slow query plan we can see that STOPKEY is using for such queries. This issue is very similar to slow top-n. |
Comments |
Comment by Vladislavs Sokurenko [ 2022 Oct 12 ] |
It seems that it is tried to select 2 last values but there is none so it attempts to find deep in history, could try limiting queries to one day. |
Comment by Vladislavs Sokurenko [ 2022 Oct 12 ] |
Suggest to change algorithm so that we attempt to read 1 day and then 2 days and then 3 days and then 4 days instead of 1 hour, than 1 day and then 1 week if not enough data in one day. For example currently is 9th of October and you have values: Sat Oct 8 07:37:49 PM EEST 2022 Fri Oct 7 07:37:49 PM EEST 2022 But zabbix server attempts to read range: Sun Oct 9 01:44:19 PM EEST 2022 Expected: |
Comment by Vladislavs Sokurenko [ 2022 Oct 12 ] |
Please try ZBX-21754-5.0.diff |
Comment by Vladislavs Sokurenko [ 2022 Oct 12 ] |
Also looks similar to |
Comment by Vladislavs Sokurenko [ 2023 Jan 16 ] |
Fixed in
|