E-AD: Application Designer performance is Very Slow in 2tier when opening records (Doc ID 2006885.1)

Last updated on OCTOBER 11, 2016

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.52 and later
Information in this document applies to any platform.

Symptoms

Opening a record definition in Application Designer when connected in 2tier mode (directly to the database) causes a very big performance issue. For example, when PS_INSTALLATION record is opened, it could take longer than 5 minutes.

The issue does not happen when using Application Designer in 3tier or when trying to run the same SELECT statement directly from SQLPlus.


Steps:
-------
1. Open Application Designer in 2 tier
2. Open record PS_INSTALLATION
3. Notice how this is taking a very long time to open (> 5 min)

 

Each time you open a record definition from Application Designer, a SELECT statement such as below is issued at the database level:

SELECT DISTINCT A.RECNAME,A.RECTYPE,C.RECDESCR
FROM
 PSRECDEFN A,PSRECDEFNLANG C WHERE A.RECNAME LIKE 'INSTALLATION%' AND
  A.RECNAME = C.RECNAME AND C.LANGUAGE_CD = 'ENG' UNION SELECT DISTINCT
  A.RECNAME,A.RECTYPE,A.RECDESCR FROM PSRECDEFN A WHERE A.RECNAME LIKE
  'INSTALLATION%' AND NOT EXISTS ( SELECT 'X' FROM PSRECDEFNLANG S WHERE
  S.RECNAME = A.RECNAME AND S.LANGUAGE_CD = 'ENG') ORDER BY 1,2

 

By enabling a detailed trace at the database level, you can easily see that when opening a record definition from App Designer in 2tier mode, a FULL TABLE SCAN is issued for some records contained in the SELECT statement generated in the background (in this example, a TABLE ACCESS FULL will get triggered for record PSRECDEFNLANG). Executing the same above SELECT statement from SQLPlus will reveal a different execution plan, without the FULL TABLE SCAN taking place, thus resulting in no performance issue.

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