[Building Sakai] SQL causing db server load

[Benito J. Gonzalez]

Hi all,

Our DBA found a select statement that is causing havoc on our database 
server:

    SELECT assignment0_.ID                AS ID537_,
       assignment0_.VERSION                AS VERSION537_,
       assignment0_.GRADABLE_OBJECT_ID     AS GRADABLE4_537_,
       assignment0_.STUDENT_ID             AS STUDENT5_537_,
       assignment0_.GRADER_ID              AS GRADER6_537_,
       assignment0_.DATE_RECORDED          AS DATE7_537_,
       assignment0_.POINTS_EARNED          AS POINTS8_537_,
       assignment0_.IS_EXCLUDED_FROM_GRADE AS IS9_537_
    FROM GB_GRADE_RECORD_T assignment0_,
       GB_GRADABLE_OBJECT_T gradableob1_
    WHERE assignment0_.GRADABLE_OBJECT_ID=gradableob1_.ID
    AND gradableob1_.GRADEBOOK_ID      =:1
    AND gradableob1_.REMOVED           =0
    AND assignment0_.OBJECT_TYPE_ID  =1
    ORDER BY assignment0_.POINTS_EARNED
    /


I managed to improve the situation with the following two indices:

     create index GB_GRADEABLE_OBJ_GB_RM_ID_IDX on 
GB_GRADABLE_OBJECT_T(GRADEBOOK_ID,REMOVED,ID);
     create index GB_GRADE_RECORD_GO_T_IDX on 
GB_GRADE_RECORD_T(GRADABLE_OBJECT_ID,OBJECT_TYPE_ID);

Still seems a significant amount of sorting going on for this query.  
Anyone else found a solution for this SQL's performance?

Thanks!

-- 
Benito J. Gonzalez
Manager, Enterprise Web Application Development
Information Technology Department
University of California, Merced
Desk: 209.228.2974
Cell: 209.201.5052
Email: bgonzalez2 at ucmerced.edu