Uploaded image for project: 'University of Michigan'
  1. University of Michigan
  2. UMICH-814

Test Center performance problem in Spanish Test site with many legacy test center submissions

    Details

      Description

      on 5/31/13, tosca server became unresponsive because of heavy Test Center usage (please refer to ptlblog for more details) The symptoms include high app cpu, high db cpu, etc.

      We have traced down the following problem query within awr report:

      CPU CPU per Elapsed
      Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id
      ---------- ------------ ---------- ------ ---------- ------ ------ -------------
      109.6 16 6.85 22.9 109.1 100.5 .0 fpcp7ndz82hs7
      Module: JDBC Thin Client
      SELECT A.GUEST, A.EVAL_ATRIB_DATE, A.EVAL_ATRIB_USER, A.EVAL_ATTACHMENTS, A.EVAL
      _COMMENT, A.EVAL_EVALUATED, A.EVAL_SCORE, A.ID, A.PART_ID, A.QUESTION_ID, A.QUES
      TION_TYPE, A.REASON, A.REVIEW, A.SUBMISSION_ID, A.SUBMITTED_DATE, A.AUTO_SCORE,
      A.CONTEST FROM MNEME_ANSWER A JOIN MNEME_SUBMISSION S ON A.SUBMISSION_ID=S.ID WH...

      Notice that the query was not executed many times, but one query was costly to execute.

      The Spanish Placement Test site was created in 2011, and has been reused since every year for all incoming students' Spanish placement tests. No data purge was done for the past records.So the current row count for the above query is 502424, which no wonder takes long time to execute.

      =======================

      The suggested fix here is to at least limit the return result to those user still active within the site. Please see the attached patch.

        Gliffy Diagrams

          Zeplin

            Attachments

              Activity

                People

                Assignee:
                zqian Zhen Qian
                Reporter:
                zqian Zhen Qian
                Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved: