R12: Performance Issue When Switching "Projects" Responsibilities And Opening Forms
(Doc ID 2272455.1)
Last updated on JULY 08, 2020
Applies to:
Oracle Project Foundation - Version 12.1.3 and laterInformation in this document applies to any platform.
Symptoms
Performance issue when switching between Project responsibilities only.
Users encounter a performance issue when switching Project Responsibilities and when opening forms.
The most expensive sql query is:
INSERT INTO PA_REP_SEC_PORGZ_TMP ( ORGANIZATION_ID ) SELECT
INSTANCE_PK1_VALUE FROM FND_GRANTS FG, FND_OBJECTS FOB, (SELECT
NVL(PA_SECURITY_PVT.GET_MENU_ID('PA_PRM_PROJ_AUTH'),-1) MENU_ID ,
NVL(PA_SECURITY_PVT.GET_GRANTEE_KEY, -1) GRANTEE_KEY FROM DUAL)
PRJ_AUTH_MENU WHERE FG.INSTANCE_TYPE = 'INSTANCE' AND FG.GRANTEE_TYPE =
'USER' AND FG.OBJECT_ID = FOB.OBJECT_ID AND FOB.OBJ_NAME = 'ORGANIZATION'
AND FG.MENU_ID = PRJ_AUTH_MENU.MENU_ID AND FG.GRANTEE_KEY =
PRJ_AUTH_MENU.GRANTEE_KEY AND TRUNC(SYSDATE) BETWEEN TRUNC(FG.START_DATE)
AND TRUNC(NVL(FG.END_DATE, SYSDATE+1))
Users also observed when multiple sessions are running the below PLSQL blocks, there are lots of library cache locks in the database.
This is causing a severe performance degradation.
begin PJI_PMV_ENGINE.Convert_Organization; end;
This is being caused by the below two lines in the PJI_PMV_ENGINE package.
PJI_PMV_ENGINE.set_table_stats('PJI','PJI_PMV_ORG_DIM_TMP',10,10,10);
PJI_PMV_ENGINE.set_table_stats('PJI','PJI_PMV_ORGZ_DIM_TMP',10,10,10);
- Already applied <Patch 20088018> and <Patch 20571411>:R12.PJF.B
- Not happening with any other responsibility
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 |