The Capacity Overview and Role Capacity Portlet is very slow

Document ID : KB000018227
Last Modified Date : 14/02/2018
Show Technical Document Details

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:

  1. Log in as a user with sufficient rights (such as an admin), hover over Home > Resource Management > Capacity Overview
  2. 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.