Hi,

We are using RTC 4.0.2 with Oracle 11g. At times, the system hangs (it works very slow). I've checked the Oracle logs and I see that, at that time, the following query consumes most of the database activity (more than 30%):

     with ctx as
(select distinct TEAMCONCERT.REPOSITORY_CONTEXT_SUBCONTEXTS.CONTEXT_UUID as ctx_uuid
from TEAMCONCERT.REPOSITORY_CONTEXT_SUBCONTEXTS join TEAMCONCERT.REPOSITORY_CONTEXT_MEMBERS on TEAMCONCERT.REPOSITORY_CONTEXT_MEMBERS.CONTEXT_UUID = TEAMCONCERT.REPOSITORY_CONTEXT_SUBCONTEXTS.SUBCONTEXT_UUID
where TEAMCONCERT.REPOSITORY_CONTEXT_MEMBERS.MEMBER_UUID in('_8lNyYNwSEd2pIJ5QVwgQGg', '_qS7MML1hEeKkN6rIxdjhUg') union
select distinct CONTEXT_UUID
from TEAMCONCERT.REPOSITORY_CONTEXT_MEMBERS
where MEMBER_UUID in(' _8lNyYNwSEd2pIJ5QVwgQGg', '_qS7MML1hEeKkN6rIxdjhUg') union
(select '_qS7MML1hEeKkN6rIxdjhUg'
from dual ))
select distinct t1.ITEM_ID, t1.STATE_ID
from
(select *
from TEAMCONCERT.MODEL_CATEGORY r1
where r1.CONTEXT_ID
in(select ctx_uuid
from ctx)) t1 left outer join TEAMCONCERT.MODEL_CATEGORY_TEAM_AREAS j1 on (t1.ITEM_ID = j1.JZ_PARENT_ID) left outer join
(select *
from TEAMCONCERT.PROCESS_PROCESS_AREA r1
where r1.CONTEXT_ID
in(select ctx_uuid
from ctx)) t3 on (j1.ITEM_ID = t3.ITEM_ID)
where ((t1.DEFAULT_TEAM_AREA_ITEM_ID = '_p5xyALzVEeKRIvWTKcbrgA' ) or (t3.ITEM_ID = '_p5xyALzVEeKRIvWTKcbrgA' ))


The Oracle Enterprise Manager shows that the query was executed 1311 times in one hour, and each execution took around 26 sec.

Is it normal that this select statement is executed several times? Do you know what is the process that launches this query?

I´ll really appreciate your help.


Thanks