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

Calendar_event query inefficient, gradually becomes top query in wait time

    Details

    • Type: (Deprecated) Contributed Patch
    • Status: RESOLVED
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.7.1
    • Fix Version/s: 10.0
    • Component/s: Calendar
    • Labels:
    • Environment:
      Oracle 11g, Sakai 2.7.1, Kernel 1.1.11
    • 2.9 Status:
      Merge

      Description

      The problem:
      At first startup, the calendar_event query has a relatively reasonable explain plan. Oracle chooses this plan and it results in an execution time of around 114 ms. However, over time, this degrades to an execution time of several seconds. We observed this particular query gradually bubbling to the very top of our list in terms of execution time and resources.

      The idea:
      Because these ranges are variable, over time, Oracle drifts away from the stable explain plan, and is unable to adapt, for some reason, by choosing better plans.

      The fix:
      One of our developers decided to use bind variables instead, and this resulted in Oracle handling the query more efficiently. It forces Oracle to use a certain plan, regardless of the dynamic where clause.

      I've created and attached a patch from our fix that will apply cleanly to sakai trunk (as of rev 116388 ) today.

      Please also note, IU has been using this in production since November, and this patch completely fixed the issue described above.

      Additionally, looking at the code changes, I don't see any reason why this would not also be better performance regardless what RDBMS an institution uses, and I certainly don't see this as being an Oracle-specific issue.

        Gliffy Diagrams

          Zeplin

            Attachments

              Issue Links

                Activity

                  People

                  Assignee:
                  gjthomas@iu.edu Greg Thomas
                  Reporter:
                  davpoind David R Poindexter (Inactive)
                  Votes:
                  0 Vote for this issue
                  Watchers:
                  6 Start watching this issue

                    Dates

                    Created:
                    Updated:
                    Resolved:

                      Git Integration