Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-28967

Very slow query in sitestats aggregator job for Oracle

    Details

    • Type: (Deprecated) Contributed Patch
    • Status: Resolved
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 2.9.3, 10.3, 10.7, 11.0
    • Fix Version/s: 10.8 [Tentative], 11.0
    • Component/s: Sitestats
    • Labels:
    • 10 status:
      Resolved

      Description

      The sitestats aggregator job runs the following query for Oracle, which takes minutes to complete:

      SELECT * FROM
      (SELECT ROW_NUMBER() OVER (ORDER BY EVENT_ID ASC) AS rn,
      EVENT_ID,EVENT_DATE,EVENT,REF,SESSION_USER,e.SESSION_ID SESSION_ID,CONTEXT
      from SAKAI_EVENT e join SAKAI_SESSION s on e.SESSION_ID=s.SESSION_ID
      where EVENT_ID >= 264857674)
      WHERE rn BETWEEN 0 AND 1000

      This query is executed multiple times per job run (by default, 50 times), so it has a significant impact.

      The equivalent query used for MySQL works for Oracle as well and takes less than one second. The attached patch changes the code to run the faster query for both MySQL and Oracle.

      This has been running in production at Western for over a year now.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                bjones86 Brian Jones
                Reporter:
                bjones86 Brian Jones
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Git Source Code