Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-21754

Oracle DB slow query when rownum is using

    XMLWordPrintable

Details

    • Team A
    • Sprint 95 (Dec 2022)
    • 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

      Attachments

        1. fast query.html
          38 kB
        2. firstlast query.JPG
          firstlast query.JPG
          18 kB
        3. image.jpeg
          image.jpeg
          18 kB
        4. image-1.jpeg
          image-1.jpeg
          18 kB
        5. image-2022-10-11-20-56-42-258.png
          image-2022-10-11-20-56-42-258.png
          159 kB
        6. slow query.html
          48 kB
        7. ZBX-21754-5.0.diff
          0.9 kB

        Issue Links

          Activity

            People

              vso Vladislavs Sokurenko
              avolodin Aleksey Volodin
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated: