E-AD: Application Designer performance is Very Slow in 2tier when opening records
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.
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.
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
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.
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