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

Missing indexes for ASN_SUBMISSION

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: OPEN
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: 12.4
    • Fix Version/s: None
    • Component/s: Assignments
    • Labels:
    • Environment:
      Oracle
    • Test Plan:
      Hide

      Please add a Test Plan here.

      Show
      Please add a Test Plan here.

      Description

      Hi there,

      While looking at the Assignments tool in 12.4 (with NYU's post-conversion data set), I noticed that loading the tool was very slow.  It's taking several minutes to load the list of assignments for a site.

      The stack trace shows my request thread sitting in AssignmentRepositoryImpl.countAssignmentSubmissions, which should be a fairly simple query.  Running it directly against the DB like this:

      select count(1) from asn_submission where assignment_id = 'whatever' AND submitted = 1;
      

      Shows the same sort of slowness. The EXPLAIN shows it's doing a full table scan; are there some indexes missing here?:

      > explain plan for select count(1) from asn_submission where assignment_id = 'whatever' AND submitted = 1;
      affected 0 rows (568 msec)
      
      > select * from table(dbms_xplan.display);
      ---------------------------------------------------------------------------------------+
                                         PLAN_TABLE_OUTPUT                                   |
      ---------------------------------------------------------------------------------------+
       Plan hash value: 1151887571                                                           |
                                                                                             |
       ------------------------------------------------------------------------------------- |
       | Id  | Operation          | Name           | Rows  | Bytes | Cost (%CPU)| Time     | |
       ------------------------------------------------------------------------------------- |
       |   0 | SELECT STATEMENT   |                |     1 |    33 |   111K  (1)| 00:22:21 | |
       |   1 |  SORT AGGREGATE    |                |     1 |    33 |            |          | |
       |*  2 |   TABLE ACCESS FULL| ASN_SUBMISSION |   815 | 26895 |   111K  (1)| 00:22:21 | |
       ------------------------------------------------------------------------------------- |
                                                                                             |
       Predicate Information (identified by operation id):                                   |
       ---------------------------------------------------                                   |
                                                                                             |
          2 - filter("ASSIGNMENT_ID"='whatever' AND "SUBMITTED"=1)                           |
                                                                                             |
       Note                                                                                  |
       -----                                                                                 |
          - dynamic sampling used for this statement (level=2)                               |
      ---------------------------------------------------------------------------------------+
      18 rows in result (first row: 2.238 sec; total: 2.240 sec)
      

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                ern Earle R Nietzel
                Reporter:
                marktriggs Mark Triggs
              • Votes:
                3 Vote for this issue
                Watchers:
                11 Start watching this issue

                Dates

                • Created:
                  Updated:

                  Git Source Code