[Building Sakai] SQL causing db server load

[Benito J. Gonzalez]

Sorry, forgot to add we are running rSmart CLE version 2.6.1 on Oracle 
Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit on Solaris 10.

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


On 12/14/10 16:56, Benito J. Gonzalez wrote:
> 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!
>