[Building Sakai] SQL causing db server load
Benito J. Gonzalez
bgonzalez2 at ucmerced.edu
Thu Dec 16 13:46:38 PST 2010
Hi Drew,
Thanks so much for the detailed response. Looks like one of our
customizations is impacting the query.
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 19:07, Drew Zhu wrote:
> Hi Benito,
>
> I don't see any big performance issue in our production database. The
> following are what we have in our database, including the current
> indexes in the two tables, the number of rows in the tables, and the
> execution plan and stats of a sample query with 5000 rows returned.
> Hope that can be helpful for your comparison.
>
> Thanks,
> Drew Zhu
> ITS Oracle DBA
> University of Michigan
>
> 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 =45
> AND gradableob1_.REMOVED =0
> AND assignment0_.OBJECT_TYPE_ID =1
> ORDER BY assignment0_.POINTS_EARNED
>
>
>
> GB_GRADE_RECORD_T
> INDEX_NAME COLUMN_NAME
> COLUMN_POSITION
> ------------------------------ ------------------------------
> ---------------
> SYS_C0013033
> ID 1
> GB_GRADE_RECORD_O_T_IDX
> OBJECT_TYPE_ID 1
> GB_GRADE_RECORD_G_O_IDX
> GRADABLE_OBJECT_ID 1
> GB_GRADE_RECORD_STUDENT_ID_IDX
> STUDENT_ID 1
> SYS_C0096256
> GRADABLE_OBJECT_ID 1
> SYS_C0096256
> STUDENT_ID 2
>
>
> GB_GRADABLE_OBJECT_T
> INDEX_NAME COLUMN_NAME
> COLUMN_POSITION
> ------------------------------ ------------------------------
> ---------------
> SYS_C0013039
> ID 1
> GRADEBOOK_ID
> GRADEBOOK_ID 1
> GB_GRADABLE_OBJ_ASN_IDX
> OBJECT_TYPE_ID 1
> GB_GRADABLE_OBJ_ASN_IDX
> GRADEBOOK_ID 2
> GB_GRADABLE_OBJ_ASN_IDX
> NAME 3
> GB_GRADABLE_OBJ_ASN_IDX
> REMOVED 4
> GB_GRADABLE_OBJ_CT_IDX
> CATEGORY_ID 1
>
>
> GB_GRADABLE_OBJECT_T;
>
> COUNT(*)
> ----------
> 130460
>
> GB_GRADE_RECORD_T;
>
> COUNT(*)
> ----------
> 4404910
>
> Execution Plan
> ----------------------------------------------------------
> Plan hash value: 3523405970
>
> ----------------------------------------------------------------------------------------------------------
> | Id | Operation | Name | Rows
> | Bytes | Cost (%CPU)| Time |
> ----------------------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | 92
> | 8188 | 33 (4)| 00:00:01 |
> | 1 | SORT ORDER BY | | 92
> | 8188 | 33 (4)| 00:00:01 |
> |* 2 | TABLE ACCESS BY INDEX ROWID | GB_GRADE_RECORD_T | 22
> | 1738 | 7 (0)| 00:00:01 |
> | 3 | NESTED LOOPS | | 92
> | 8188 | 32 (0)| 00:00:01 |
> |* 4 | TABLE ACCESS BY INDEX ROWID| GB_GRADABLE_OBJECT_T | 4
> | 40 | 8 (0)| 00:00:01 |
> |* 5 | INDEX RANGE SCAN | GRADEBOOK_ID | 9
> | | 1 (0)| 00:00:01 |
> |* 6 | INDEX RANGE SCAN | GB_GRADE_RECORD_G_O_IDX | 43
> | | 2 (0)| 00:00:01 |
> ----------------------------------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
> 2 - filter("ASSIGNMENT0_"."OBJECT_TYPE_ID"=1)
> 4 - filter("GRADABLEOB1_"."REMOVED"=0)
> 5 - access("GRADABLEOB1_"."GRADEBOOK_ID"=45)
> 6 - access("ASSIGNMENT0_"."GRADABLE_OBJECT_ID"="GRADABLEOB1_"."ID")
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 0 db block gets
> 144 consistent gets
> 0 physical reads
> 0 redo size
> 182844 bytes sent via SQL*Net to client
> 4155 bytes received via SQL*Net from client
> 335 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 5003 rows processed
>
>
> On 12/14/2010 8:45 PM, Benito J. Gonzalez wrote:
>> 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!
>>>
>> _______________________________________________
>> sakai-dev mailing list
>> sakai-dev at collab.sakaiproject.org
>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>
>> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
>>
>>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
More information about the sakai-dev
mailing list