R12: Performance Issue When Switching "Projects" Responsibilities And Opening Forms (Doc ID 2272455.1)

Last updated on JULY 08, 2020

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.