My Oracle Support Banner

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

Last updated on APRIL 02, 2018

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

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
Cause
Solution
References


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