My Oracle Support Banner

Gantt Chart Performance Issue in Resource View - Viewing Many Resources At Ome Time (Doc ID 2069625.1)

Last updated on JUNE 28, 2017

Applies to:

Oracle Advanced Supply Chain Planning - Version 12.1.3 and later
Information in this document applies to any platform.

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

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.