It's all about the answers!

Ask a question

RTC 4.0.2 with Oracle hangs when an internal select query is executed


Leonardo Marzo (24964852) | asked Jul 24 '13, 3:46 p.m.
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

Comments
Lorena Almela commented Mar 18 '15, 4:52 p.m. | edited Mar 18 '15, 9:03 p.m.

Hi Leonardo,

we are facing similar issue with a query.

Were you able to troubleshoot the issue?

What process was running that query?

How did you fix it?

Regards,

One answer



permanent link
Krzysztof Kaźmierczyk (7.4k35399) | answered Jul 25 '13, 3:36 a.m.
Hi Leonardo,
I do not expect, that this is normal. I would suggest you opening new PMR for further investigation by the support.

Your answer


Register or to post your answer.