[Building Sakai] Heavy Samigo query

[Martin B. Smith]

On 10/17/2011 09:12 AM, Kusnetz, Jeremy wrote:
> That would be absolutely fantastic.  We didn’t survive last night, too
> may of these queries piling up by the time we hit peak loads.
>
> Here are two more queries that were really stinging:
>
> select publisheds0_.ASSESSMENTID as col_0_0_ from SAM_PUBLISHEDSECTION_T
> publisheds0_, SAM_PUBLISHEDSECTIONMETADATA_T publisheds1_ where
> publisheds0_.SECTIONID=publisheds1_.SECTIONID and
> publisheds1_.LABEL='AUTHOR_TYPE' and publisheds1_.ENTRY='2' group by
> publisheds0_.ASSESSMENTID
>
> select assessment0_.PUBLISHEDASSESSMENTID as col_0_0_,
> count(assessment0_.ASSESSMENTGRADINGID) as col_1_0_ from
> SAM_ASSESSMENTGRADING_T assessment0_ group by
> assessment0_.PUBLISHEDASSESSMENTID
>
> The 2^nd one doesn’t even have a where statement.
>
> We absolutely need to limit these queries to query by SITE_ID so we are
> only pulling exams for just the one site the user is in.
>
> There are probably other queries that aren’t limited by SITE_ID, ideally
> all queries would be.
>
> I will open a JIRA ASAP.  If at all possible we would be able to get
> these changes in by next Saturday so we can hopefully survive next Sunday.
>

(long version of previous message)

Hi all,

We're on Oracle 11.2.0.2, using data guard. Last night, we saw the 
following queries really destroy our performance:

"select publisheds0_.ASSESSMENTID as col_0_0_
from SAM_PUBLISHEDSECTION_T publisheds0_, SAM_PUBLISHEDSECTIONMETADATA_T 
publisheds1_
where publisheds0_.SECTIONID=publisheds1_.SECTIONID and 
publisheds1_.LABEL=:1 and publisheds1_.ENTRY=:2 group by 
publisheds0_.ASSESSMENTID"

"select publishede0_.ASSESSMENTID as col_0_0_, publishede0_.SCORINGTYPE 
as col_1_0_, publisheda1_.SUBMISSIONSALLOWED as col_2_0_
from SAM_PUBLISHEDEVALUATION_T publishede0_, 
SAM_PUBLISHEDACCESSCONTROL_T publisheda1_
where publishede0_.ASSESSMENTID=publisheda1_.ASSESSMENTID"

When we're really busy with testing, with ten application servers and 
8k+ sessions online (like last night), these queries tend to pile up on 
the database, causing >8 CPUs spending a lot of time in CPU and network 
wait events.

Unfortunately, we have indexes on the appropriate columns here. So we're 
not sure what else to do except consider rewriting them.
-- 
Martin B. Smith
smithmb at ufl.edu - (352) 273-1374
CNS/Open Systems Group
University of Florida

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 3737 bytes
Desc: S/MIME Cryptographic Signature
Url : http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20111017/428e8806/attachment.bin