Last updated on JUNE 28, 2017

Applies to:

Symptoms

VCP 12.1.3.8 Patch 14247039

We have GOP Plan which is enabled for ECC . When we try view resource loading information in Gantt chart , it takes more times and most of the time hangs .

STEPS
1. Navigator (resource view)--->resource-->gantt chart--->Resource hours view Yes ,we were trying to load 10 resources

 I created folder with option " Open as Default" , I am not able to view the folder options without opening Gantt chart .
  Below is the folder condition used
  Resource Among ( RES 1, RES 2,. RES 3...........N) Total 10 resource
  Organization Among ('OGA','OGB')

2. Tried changing Java Parameter. from -Xmx512M to -Xmx1024M
NAV: Control Panel / Java
- go to Java Tab - click View and show both User tab and System tab
- changed setting / cleared browser cache and signed back into application -
This did not help the issue.

Performance DBA narrowed down issue to a problem with the MSC_GANTT_UTILS.ISRESCONSTRAINT function as the root cause for the poorly performing gh0zxtunnmdzt stmt
MSC_GANTT_UTILS MSCGNTUB.pls 120.61.12010000.8

A> The stack sequence is:
Function MSC_GANTT_PKG.resourceView
Procedure MSC_GANTT_UTILS.populateResReqGanttNew
SQLID gh0zxtunnmdzt (cursor res_req_cur in above proc)
Function MSC_GANTT_UTILS.isResConstraint
SQLID cw4vfhxkc3vdu (cursor resource_constraint_cur in above function)

B> The ISRESCONSTRAINT function is called multiple times per row by cursors in procedures populateResActGantt and populateResReqGanttNew, and at some point the function was made inefficient by a code change (shown below).
As a result, it became cpu intensive with degraded response whenever a significant no. of rows is processed.

The function has the following cursor definition (please note the commented out row):

cursor resource_constraint_cur is
select 'EXISTS'
from msc_exception_details
where number1 = p_transaction_id
and sr_instance_id = p_instance_id
and plan_id = p_plan_id
and exception_type =36
and organization_id = p_organization_id
--and inventory_item_id = p_inventory_item_id
and department_id = p_department_id
and resource_id = p_resource_id;

Which results in the following query:

SQL_ID cw4vfhxkc3vdu
---------------------
SELECT 'EXISTS' FROM MSC_EXCEPTION_DETAILS WHERE NUMBER1 = :B6 AND
SR_INSTANCE_ID = :B5 AND PLAN_ID = :B4 AND EXCEPTION_TYPE =36 AND
ORGANIZATION_ID = :B3 AND DEPARTMENT_ID = :B2 AND RESOURCE_ID = :B1

Plan hash value: 324018775

-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 14 (100)| | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 30 | 14 (0)| 00:00:01 | KEY | KEY |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| MSC_EXCEPTION_DETAILS | 1 | 30 | 14 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX RANGE SCAN | MSC_EXCEPTION_DETAILS_N1 | 3 | | 11 (0)| 00:00:01 | KEY | KEY |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

 2 - filter("NUMBER1"=:B6)
 3 - access("PLAN_ID"=:B4 AND "EXCEPTION_TYPE"=36 AND "ORGANIZATION_ID"=:B3 AND "SR_INSTANCE_ID"=:B5 AND
  "DEPARTMENT_ID"=:B2 AND "RESOURCE_ID"=:B1)
  filter(("RESOURCE_ID"=:B1 AND "DEPARTMENT_ID"=:B2))

Problem is, none of the 7 columns in index MSC_EXCEPTION_DETAILS_N1 (PLAN_ID,EXCEPTION_TYPE,ORGANIZATION_ID,SR_INSTANCE_ID,INVENTORY_ITEM_ID,DEPARTMENT_ID,RESOURCE_ID) is really selective except for INVENTORY_ITEM_ID, which has been commented out in the cursor definition -- thus making the index probe significantly less efficient.

On the other hand, column "NUMBER1" is highly selective but is not indexed so it can only be applied as a table filter.

The workaround is to make "NUMBER1" part of an index as in:

create index MSC.XX_MSC_EXCEPTION_DETAILS_X1
on MSC.MSC_EXCEPTION_DETAILS (PLAN_ID,EXCEPTION_TYPE,ORGANIZATION_ID,SR_INSTANCE_ID,DEPARTMENT_ID,RESOURCE_ID,NUMBER1)
online local tablespace APPS_TS_TX_IDX;

... resulting in much lower cost (3 buffer gets/exec vs. 422 previously) with the following plan:

Plan hash value: 1361007191

----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 58 | 3 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 2 | 58 | 3 (0)| 00:00:01 | KEY | KEY |
|* 2 | INDEX RANGE SCAN | XX_MSC_EXCEPTION_DETAILS_X1 | 2 | 58 | 3 (0)| 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("PLAN_ID"=:B4 AND "EXCEPTION_TYPE"=36 AND "ORGANIZATION_ID"=:B3 AND "SR_INSTANCE_ID"=:B5 AND
  "DEPARTMENT_ID"=:B2 AND "RESOURCE_ID"=:B1 AND "NUMBER1"=:B6)

Cause