It's all about the answers!

Ask a question

Query consuming 100 % of CPU in DB server


Lorena Almela (1811317) | asked Mar 19 '15, 2:16 p.m.
Hi,
we are using RTC 4.0.6. Since last week Server administrators have pointed us that query is consuming 100 % of CPU, locking other queries and hanging the DB server. What process could be running this query?
Query is:
(@P0 nvarchar(4000),@P1 nvarchar(4000),@P2 nvarchar(4000),@P3 nvarchar(4000),@P4 nvarchar(4000),@P5 nvarchar(4000))with ctx(ctx_uuid) as (select distinct REPOSITORY.CONTEXT_SUBCONTEXTS.CONTEXT_UUID from REPOSITORY.CONTEXT_SUBCONTEXTS join REPOSITORY.CONTEXT_MEMBERS on REPOSITORY.CONTEXT_MEMBERS.CONTEXT_UUID = REPOSITORY.CONTEXT_SUBCONTEXTS.SUBCONTEXT_UUID where REPOSITORY.CONTEXT_MEMBERS.MEMBER_UUID in(@P0, @P1)  union  select distinct CONTEXT_UUID from REPOSITORY.CONTEXT_MEMBERS where MEMBER_UUID in(@P2, @P3)  union (select @P4) union (select @P5)) select t1.ITEM_ID, t1.ID, t1.SUMMARY, t1.CREATION_DATE, t1.MODIFIED, t2.NAME, t1.PROJECT_AREA_ITEM_ID, t2.ARCHIVED, t4.NAME, t3.DEFAULT_TEAM_AREA_ITEM_ID, t4.ARCHIVED, t5.USER_ID, t5.NAME, t1.OWNER_ITEM_ID, t5.ARCHIVED, t6.USER_ID, t6.NAME, t1.CREATOR_ITEM_ID, t6.ARCHIVED, t3.NAME, t1.CATEGORY_ITEM_ID, t3.ARCHIVED, t7.NAME, t7.ID, t1.TARGET_ITEM_ID, t7.ARCHIVED, t8.NAME, t7.INTERNAL_DEVELOPMENT_LINE_TM_D, t8.ARCHIVED, t1.WORK_ITEM_TYPE, t1.INTERNAL_STATE, t1.INTERNAL_PRIORITY, t1.INTERNAL_SEVERITY, t1.INTERNAL_TAGS, SUM(t1.DURATION), SUM(t1.TIME_SPENT), SUM(t1.CORRECTED_ESTIMATE), t9.KEY_COL, t9.VALUE, t10.KEY_COL, t10.VALUE, t11.KEY_COL, t11.VALUE, t12.KEY_COL, t12.VALUE, t13.KEY_COL, t13.VALUE, t14.KEY_COL, t14.VALUE, t15.KEY_COL, t15.VALUE, t16.KEY_COL, t1.INTERNAL_RESOLUTION, t1.RESOLUTION_DATE, t17.NAME, t17.ARCHIVED, t1.START_DATE, t1.DUE_DATE, t19.NAME, t18.CREATOR_ITEM_ID, t19.ARCHIVED, COUNT(t18.INTERNAL_ID), t20.NAME, t20.ITEM_ID, t20.ARCHIVED, COUNT(t20.ITEM_ID), t21.NAME, t1.RESOLVER_ITEM_ID, t21.ARCHIVED, t22.STATE_IDENTIFIER, COUNT(t22.INTERNAL_ID), t23.NAME, t22.APPROVER_ITEM_ID, t23.ARCHIVED, t1.ID, COUNT(t1.ITEM_ID) from (select * from MODEL.WORK_ITEM r1 where r1.CONTEXT_ID in(select ctx_uuid from ctx)) t1 left outer join (select * from PROCESS.PROCESS_AREA r1 where r1.CONTEXT_ID in(select ctx_uuid from ctx)) t2 on (t1.PROJECT_AREA_ITEM_ID = t2.ITEM_ID) left outer join (select * from MODEL.CATEGORY r1 where r1.CONTEXT_ID in(select ctx_uuid from ctx)) t3 on (t1.CATEGORY_ITEM_ID = t3.ITEM_ID) left outer join (select * from PROCESS.PROCESS_AREA r1 where r1.CONTEXT_ID in(select ctx_uuid from ctx)) t4 on (t3.DEFAULT_TEAM_AREA_ITEM_ID = t4.ITEM_ID) left outer join (select * from REPOSITORY.CONTRIBUTOR r1 where r1.CONTEXT_ID in(select ctx_uuid from ctx)) t5 on (t1.OWNER_ITEM_ID = t5.ITEM_ID) left outer join (select * from REPOSITORY.CONTRIBUTOR r1 where r1.CONTEXT_ID in(select ctx_uuid from ctx)) t6 on (t1.CREATOR_ITEM_ID = t6.ITEM_ID) left outer join (select * from PROCESS.ITERATION r1 where r1.CONTEXT_ID in(select ctx_uuid from ctx)) t7 on (t1.TARGET_ITEM_ID = t7.ITEM_ID) left outer join (select * from PROCESS.DEVELOPMENT_LINE r1 where r1.CONTEXT_ID in(select ctx_uuid from ctx)) t8 on (t7.INTERNAL_DEVELOPMENT_LINE_TM_D = t8.ITEM_ID) left outer join MODEL.WORK_ITEM_BOOLEAN_EXTENSIONS t9 on (t1.ITEM_ID = t9.JZ_PARENT_ID) left outer join MODEL.WORK_ITEM_INT_EXTENSIONS t10 on (t1.ITEM_ID = t10.JZ_PARENT_ID) left outer join MODEL.WORK_ITEM_LONG_EXTENSIONS t11 on (t1.ITEM_ID = t11.JZ_PARENT_ID) left outer join MODEL.WORK_ITEM_STRING_EXTENSIONS t12 on (t1.ITEM_ID = t12.JZ_PARENT_ID) left outer join MODEL.WORK_ITEM_TIMESTAMP_EXTENSIONS t13 on (t1.ITEM_ID = t13.JZ_PARENT_ID) left outer join MODEL.WORK_ITEM_MEDIUM_STRING_EXTENSIONS t14 on (t1.ITEM_ID = t14.JZ_PARENT_ID) left outer join MODEL.WORK_ITEM_BIG_DECIMAL_EXTENSIONS t15 on (t1.ITEM_ID = t15.JZ_PARENT_ID) left outer join MODEL.WORK_ITEM_LARGE_STRING_EXTENSIONS t16 on (t1.ITEM_ID = t16.JZ_PARENT_ID) left outer join (select * from MODEL.DELIVERABLE r1 where r1.CONTEXT_ID in(select ctx_uuid from ctx)) t17 on (t1.FOUND_IN_ITEM_ID = t17.ITEM_ID) left outer join MODEL.WORK_ITEM_INTERNAL_COMMENTS t18 on (t1.ITEM_ID = t18.JZ_PARENT_ID) left outer join (select * from REPOSITORY.CONTRIBUTOR r1 where r1.CONTEXT_ID in(select ctx_uuid from ctx)) t19 on (t18.CREATOR_ITEM_ID = t19.ITEM_ID) left outer join MODEL.WORK_ITEM_INTERNAL_SUBSCRIPTIONS j1 on (t1.ITEM_ID = j1.JZ_PARENT_ID) left outer join (select * from REPOSITORY.CONTRIBUTOR r1 where r1.CONTEXT_ID in(select ctx_uuid from ctx)) t20 on (j1.ITEM_ID = t20.ITEM_ID) left outer join (select * from REPOSITORY.CONTRIBUTOR r1 where r1.CONTEXT_ID in(select ctx_uuid from ctx)) t21 on (t1.RESOLVER_ITEM_ID = t21.ITEM_ID) left outer join MODEL.WORK_ITEM_INTERNAL_APPROVALS t22 on (t1.ITEM_ID = t22.JZ_PARENT_ID) left outer join (select * from REPOSITORY.CONTRIBUTOR r1 where r1.CONTEXT_ID in(select ctx_uuid from ctx)) t23 on (t22.APPROVER_ITEM_ID = t23.ITEM_ID) group by t1.ITEM_ID, t1.ID, t1.SUMMARY, t1.CREATION_DATE, t1.MODIFIED, t2.NAME, t1.PROJECT_AREA_ITEM_ID, t2.ARCHIVED, t4.NAME, t3.DEFAULT_TEAM_AREA_ITEM_ID, t4.ARCHIVED, t5.USER_ID, t5.NAME, t1.OWNER_ITEM_ID, t5.ARCHIVED, t6.USER_ID, t6.NAME, t1.CREATOR_ITEM_ID, t6.ARCHIVED, t3.NAME, t1.CATEGORY_ITEM_ID, t3.ARCHIVED, t7.NAME, t7.ID, t1.TARGET_ITEM_ID, t7.ARCHIVED, t8.NAME, t7.INTERNAL_DEVELOPMENT_LINE_TM_D, t8.ARCHIVED, t1.WORK_ITEM_TYPE, t1.INTERNAL_STATE, t1.INTERNAL_PRIORITY, t1.INTERNAL_SEVERITY, t1.INTERNAL_TAGS, t9.KEY_COL, t9.VALUE, t10.KEY_COL, t10.VALUE, t11.KEY_COL, t11.VALUE, t12.KEY_COL, t12.VALUE, t13.KEY_COL, t13.VALUE, t14.KEY_COL, t14.VALUE, t15.KEY_COL, t15.VALUE, t16.KEY_COL, t1.INTERNAL_RESOLUTION, t1.RESOLUTION_DATE, t17.NAME, t17.ARCHIVED, t1.START_DATE, t1.DUE_DATE, t19.NAME, t18.CREATOR_ITEM_ID, t19.ARCHIVED, t20.NAME, t20.ITEM_ID, t20.ARCHIVED, t21.NAME, t1.RESOLVER_ITEM_ID, t21.ARCHIVED, t22.STATE_IDENTIFIER, t23.NAME, t22.APPROVER_ITEM_ID, t23.ARCHIVED, t1.ID
Regards,

One answer



permanent link
Lawrence Smith (3764) | answered Mar 19 '15, 3:24 p.m.
JAZZ DEVELOPER
This looks like a work items query. The conditions appear to be constructing many joins and some sums. I did a search in the support system but did not see this precise issue.   If the query comes through the WorkItemRestService it can come from a web page, dashboard, plan or report. Queries can also be submitted by build systems, extensions and other related services.  Are the spikes always or do they occur at a particular time or day... e.g., when some automated report runs.   Reports can be created that cause large joins which cause performance issues.

Here is an article that discusses issues with BIRT reports wrt performance of reports that generate large table joins: http://www-01.ibm.com/support/docview.wss?uid=swg21671005

Here is an article that discusses changing the query cache size: http://www-01.ibm.com/support/docview.wss?uid=swg21390693
I don't know if this would help.

The some of fields queried are here... maybe this will be recognizable. It looks like this may be a report showing accumulated time and values.

select t1.ITEM_ID, t1.ID, t1.SUMMARY, t1.CREATION_DATE, t1.MODIFIED, t2.NAME, t1.PROJECT_AREA_ITEM_ID, t2.ARCHIVED, t4.NAME, t3.DEFAULT_TEAM_AREA_ITEM_ID, t4.ARCHIVED, t5.USER_ID, t5.NAME, t1.OWNER_ITEM_ID, t5.ARCHIVED, t6.USER_ID, t6.NAME, t1.CREATOR_ITEM_ID, t6.ARCHIVED, t3.NAME, t1.CATEGORY_ITEM_ID, t3.ARCHIVED, t7.NAME, t7.ID, t1.TARGET_ITEM_ID, t7.ARCHIVED, t8.NAME, t7.INTERNAL_DEVELOPMENT_LINE_TM_D, t8.ARCHIVED, t1.WORK_ITEM_TYPE, t1.INTERNAL_STATE, t1.INTERNAL_PRIORITY, t1.INTERNAL_SEVERITY, t1.INTERNAL_TAGS, SUM(t1.DURATION), SUM(t1.TIME_SPENT), SUM(t1.CORRECTED_ESTIMATE), t9.KEY_COL, t9.VALUE, t10.KEY_COL, t10.VALUE, t11.KEY_COL, t11.VALUE, t12.KEY_COL, t12.VALUE, t13.KEY_COL, t13.VALUE, t14.KEY_COL, t14.VALUE, t15.KEY_COL, t15.VALUE, t16.KEY_COL, t1.INTERNAL_RESOLUTION, t1.RESOLUTION_DATE, t17.NAME, t17.ARCHIVED, t1.START_DATE, t1.DUE_DATE, t19.NAME, t18.CREATOR_ITEM_ID, t19.ARCHIVED, COUNT(t18.INTERNAL_ID), t20.NAME, t20.ITEM_ID, t20.ARCHIVED, COUNT(t20.ITEM_ID), t21.NAME, t1.RESOLVER_ITEM_ID, t21.ARCHIVED, t22.STATE_IDENTIFIER, COUNT(t22.INTERNAL_ID), t23.NAME, t22.APPROVER_ITEM_ID, t23.ARCHIVED, t1.ID, COUNT(t1.ITEM_ID

Lawrence Smith [IBM]

Your answer


Register or to post your answer.