Gantt Chart Performance Issue in Resource View - Viewing Many Resources At One Time
(Doc ID 2069625.1)
Last updated on FEBRUARY 26, 2019
Applies to:
Oracle Advanced Supply Chain Planning - Version 12.1.3 and laterInformation 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)
Changes
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 |
Changes |
Cause |
Solution |
References |