[Building Sakai] Heavy Samigo query

[Kusnetz, Jeremy]

And we just crashed again this morning from these queries.

-----Original Message-----
From: sakai-dev-bounces at collab.sakaiproject.org
[mailto:sakai-dev-bounces at collab.sakaiproject.org] On Behalf Of Martin
B. Smith
Sent: Monday, October 17, 2011 9:42 AM
To: sakai-dev at collab.sakaiproject.org
Subject: Re: [Building Sakai] Heavy Samigo query

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

This message is private and confidential. If you have received it in error, please notify the sender and remove it from your system.