R12 Performance Issue When Switching "Projects" Responsibilities
Last updated on APRIL 30, 2015
Applies to:Oracle Project Foundation - Version 12.1.3 and later
Information in this document applies to any platform.
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;
- Already applied patch 20088018
This is being caused by the below two lines in the PJI_PMV_ENGINE package.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms