[Building Sakai] SQL causing db server load
Drew Zhu
qszhu at umich.edu
Tue Dec 14 19:07:24 PST 2010
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"
>
>
More information about the sakai-dev
mailing list