[Building Sakai] Oracle Row Lock Contention During Samigo and Message Center Queries

[Earle Nietzel]

Hi Michael,

Sorry to hear of your troubles.

First thing to check is to make sure those tables listed in the queries have
the appropriate indexes.

Then try running an explain on those queries that well show the query plan.
This should show if the chosen query plan is efficient.

If it isn't efficient then its a matter of figuring out how to make it
efficient, sometimes this can be a matter of adding another index. It really
depends on what the query plan shows.
On Apr 15, 2011 4:30 PM, "Stoufer,R M" <kc4uf at ufl.edu> wrote:
> Hello Everyone,
>
> During the past couple of days we have experienced spikes in our normal
database activity which have resulted in 'row lock contention' messages on
our Oracle database.
>
> We are using: Sakai 2.6.x, Samigo 2.7.x, Msg Ctr 2.7.x, Oracle 11g R2
>
> The culprit SQLs are the following:
>
>
> update SAM_ITEMGRADING_T set ASSESSMENTGRADINGID=:1 , PUBLISHEDITEMID=:2
>
> , PUBLISHEDITEMTEXTID=:3 , AGENTID=:4 , SUBMITTEDDATE=:5 ,
>
> PUBLISHEDANSWERID=:6 , RATIONALE=:7 , ANSWERTEXT=:8 , AUTOSCORE=:9 ,
>
> OVERRIDESCORE=:10 , COMMENTS=:11 , GRADEDBY=:12 , GRADEDDATE=:13 ,
>
> REVIEW=:14 , ATTEMPTSREMAINING=:15 , LASTDURATION=:16 where
>
> ITEMGRADINGID=:17
>
> and
>
>
> select messageimp0_.ID as ID17_, messageimp0_.VERSION as VERSION17_,
>
> messageimp0_.UUID as UUID17_, messageimp0_.CREATED as CREATED17_,
>
> messageimp0_.CREATED_BY as CREATED6_17_, messageimp0_.MODIFIED as
>
> MODIFIED17_, messageimp0_.MODIFIED_BY as MODIFIED8_17_,
>
> messageimp0_.TITLE as TITLE17_, messageimp0_.BODY as BODY17_,
>
> messageimp0_.AUTHOR as AUTHOR17_, messageimp0_.HAS_ATTACHMENTS as
>
> HAS12_17_, mess...
>
>
> The Samigo update statement caused our average
>
> database load to be about 50x normal. Oracle describes it as,
>
> 'Significant row contention was detected in the TABLE
>
> "SAKAI.SAM_ITEMGRADING_T".'
>
> Does anyone have any insight as to what may be going on? We are
approaching final exams in a week and would like to get this resolved ASAP.
>
> Thanks!
>
> R. Michael Stoufer
> Academic Technology - ICS
> University of Florida
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20110415/d57a2599/attachment.html