The Capacity Overview and Role Capacity Portlet is very slow
Article ID: 18227
Updated On:
Products
Issue/Introduction
Description:
The Capacity Overview portlet takes more than 5 minutes to return filtered results (which contains as few as 6 rows). When filtering using an OBS unit that has a lot of resources associated it can time out on the user. The same problem can be reproduced by going to Resource Planning -> Capacity -> Role Capacity Portlet and filtering for OBS.
This issue has been observed by customers with a large amount of investment and resource data.
Steps to Reproduce:
- Log in as a user with sufficient rights (such as an admin), hover over Home > Resource Management > Capacity Overview
- In the filter area, on 'Resource OBS' field use the binocular to select an OBS unit, click Filter
Expected Result: Filtered result is shown within a few seconds.
Actual Result: Filtered result (as few as 6 rows) is shown after 5 minutes; the portlet times out if the number of rows returned is large.
Upon reviewing the AWR report the queries below are found to be taking a long time (hundreds of seconds to return less than 10 rows) to run:
SELECT OBS.ID ID , periods.start_date PERIOD , SUM(slices.slice) TOTAL FROM PRJ_BLB_SLICES_M_ALC slices ,(SELECT MONTH_KEY , min(day) start_date , max(day) end_date FROM nbi_dim_calendar_time WHERE day >= '2014-03-01 00:00:00.0' AND day < '2014-09-01 00:00:00.0' GROUP BY MONTH_KEY ) periods , INV_INVESTMENTS , PRTEAM , SRM_RESOURCES RS , PRJ_RESOURCES PRES , PRJ_OBS_UNITS OBS , PRJ_OBS_UNITS_FLAT FLAT WHERE slices.SLICE_DATE >= periods.start_date AND slices.SLICE_DATE <= periods.end_date AND slices.PRJ_OBJECT_ID = PRTEAM.PRID AND RS.ID = PRTEAM.PRRESOURCEID AND PRES.PRID = PRTEAM.PRRESOURCEID AND INV_INVESTMENTS.ID = PRTEAM.PRPROJECTID AND NVL((SELECT IS_TEMPLATE FROM INV_PROJECTS WHERE INV_PROJECTS.PRID = INV_INVESTMENTS.ID), 0) = 0 AND FLAT.BRANCH_UNIT_ID = OBS.ID and ((PRTEAM.RSF_OBSUNIT_ID IN (SELECT UNIT_ID FROM PRJ_OBS_UNITS_FLAT UF WHERE BRANCH_UNIT_ID = 5056035) AND PRTEAM.RSF_OBSUNIT_ID = FLAT.UNIT_ID) or (PRTEAM.RSF_OBSUNIT_ID IS NULL and slices.RESOURCE_ID IN (select DISTINCT OA.RECORD_ID from PRJ_OBS_ASSOCIATIONS OA, PRJ_OBS_UNITS_FLAT UF where OA.TABLE_NAME = 'SRM_RESOURCES' AND OA.UNIT_ID = UF.UNIT_ID AND UF.BRANCH_UNIT_ID = 5056035 AND OA.UNIT_ID = FLAT.UNIT_ID ))) AND INV_INVESTMENTS.IS_ACTIVE = 1 AND RS.IS_ACTIVE = 1 AND OBS.ID IN (5056035) GROUP BY OBS.ID, periods.start_date ORDER BY OBS.ID, periods.start_date
SELECT OBS.ID ID , periods.start_date PERIOD , SUM(slices.slice) TOTAL FROM PRJ_BLB_SLICES_M_ALC slices ,(SELECT MONTH_KEY , min(day) start_date , max(day) end_date FROM nbi_dim_calendar_time WHERE day >= '2014-03-01 00:00:00.0' AND day < '2014-09-01 00:00:00.0' GROUP BY MONTH_KEY ) periods , INV_INVESTMENTS , PRTEAM , SRM_RESOURCES RS , PRJ_RESOURCES PRES , PRJ_OBS_UNITS OBS , PRJ_OBS_UNITS_FLAT FLAT WHERE slices.SLICE_DATE >= periods.start_date AND slices.SLICE_DATE <= periods.end_date AND slices.PRJ_OBJECT_ID = PRTEAM.PRID AND RS.ID = PRTEAM.PRRESOURCEID AND PRES.PRID = PRTEAM.PRRESOURCEID AND INV_INVESTMENTS.ID = PRTEAM.PRPROJECTID AND NVL((SELECT IS_TEMPLATE FROM INV_PROJECTS WHERE INV_PROJECTS.PRID = INV_INVESTMENTS.ID), 0) = 0 AND FLAT.BRANCH_UNIT_ID = OBS.ID AND PRTEAM.PRBOOKING IN (5,10) and ((PRTEAM.RSF_OBSUNIT_ID IN (SELECT UNIT_ID FROM PRJ_OBS_UNITS_FLAT UF WHERE BRANCH_UNIT_ID = 5056035) AND PRTEAM.RSF_OBSUNIT_ID = FLAT.UNIT_ID) or (PRTEAM.RSF_OBSUNIT_ID IS NULL and slices.RESOURCE_ID IN (select DISTINCT OA.RECORD_ID from PRJ_OBS_ASSOCIATIONS OA, PRJ_OBS_UNITS_FLAT UF where OA.TABLE_NAME = 'SRM_RESOURCES' AND OA.UNIT_ID = UF.UNIT_ID AND UF.BRANCH_UNIT_ID = 5056035 AND OA.UNIT_ID = FLAT.UNIT_ID ))) AND PRES.PRISROLE = 0 AND INV_INVESTMENTS.IS_ACTIVE = 1 AND RS.IS_ACTIVE = 1 AND OBS.ID IN (5056035) GROUP BY OBS.ID, periods.start_date ORDER BY OBS.ID, periods.start_date
Solution:
This issue has been fixed in 14.2 as CLRT-74191.
Environment
Component:
Feedback
