R12 Performance Issue When Switching "Projects" Responsibilities (Doc ID 1988254.1)

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.

Symptoms

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.

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);

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms