[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: File ZBX-21754-5.0.diff     HTML File fast query.html     JPEG File firstlast query.JPG     JPEG File image-1.jpeg     PNG File image-2022-10-11-20-56-42-258.png     JPEG File image.jpeg     HTML File slow query.html    
Issue Links:
Causes
Team: Team A
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.
http://www.dba-oracle.com/t_sql_tuning_rownum_equals_one.htm



 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

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  2 01:44:19 PM EEST 2022

Sun Oct  9 01:44:19 PM EEST 2022

Expected:
It should instead read few days before reading whole week

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 ZBXNEXT-7920

Comment by Vladislavs Sokurenko [ 2023 Jan 16 ]

Fixed in

Generated at Sun May 18 08:09:51 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.