Removing Redundant Indexes in PeopleSoft

This is the second of a two-part article that discusses how to identify and remove redundant indexes from a PeopleSoft system. 
This article describes a script on my website (psredundantant.sql) that uses a similar query to that described in the previous article to identify redundant indexes from the metadata in the PeopleTools tables. It uses an anonymous block of PL/SQL so nothing is installed in the database. For each redundant index identified it:

• Adds the record and redundant index into an Application Designer project called REDUNDANT INDEXES. The record must also be added, otherwise Application will not generate a DDL script to remove the index.
• Unsets the platform flag on the redundant index if the superset index is active on the same platform. Thus Application Designer will generate a script to drop redundant indexes when the project is built. The redundant index definition remains in the record definition in case there is a need to revert the change.
• If the superset index is active on Oracle
• Creates and collects extended optimizer statistics on the combination of columns in the redundant index. If error ORA-00054 occurs, the exception will be ignored and the script will continue.
• Makes the index invisible. Note that indexes are not dropped. That can be done later by building an alter script for the project. It would be normal to test the consequences of making the indexes invisible for a while, and drop them later. 

Sample Output

Project REDUNDANT INDEXES already exists
…
Record PSPROJECTITEM, Redundant Index C already in project
Creating Extended Statistics on PSPROJECTITEM for OBJECTVALUE4,OBJECTTYPE
Making index PSCPSPROJECTITEM invisible
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
…
Record PSTREENODE, Redundant Index C already in project
…
Record TL_RPTD_TIME, Redundant Index C added to project
Making index PSCTL_RPTD_TIME invisible
Record TL_TA_BATCH, Redundant Index _ added to project
Making index PS_TL_TA_BATCH invisible
Making index PS_TL_TA_BATCH1 invisible
…
Making index PS_TL_TA_BATCH48 invisible
…
Record WRK_XREF_CALC, Redundant Index A already in project
Creating Extended Statistics on PS_WRK_XREF_CALC for PROCESS_INSTANCE,SEQ_NBR
Creating Extended Statistics on PS_WRK_XREF_CALC1 for PROCESS_INSTANCE,SEQ_NBR
…
Creating Extended Statistics on PS_WRK_XREF_CALC6 for PROCESS_INSTANCE,SEQ_NBR
Making index PSAWRK_XREF_CALC invisible
Making index PSAWRK_XREF_CALC1 invisible
…
Making index PSAWRK_XREF_CALC6 invisible

Note:

• The script commits any changes it makes. There is also a rollback command at the top to prevent it accidentally committing something else.
• Having run it once, should you immediately run the script again, nothing will be found because any redundant indexes have been marked as disabled on Oracle
• Should you want to revert changes, mark all indexes in the REDUNDANT INDEXES project as active on Oracle.

UPDATE psindexdefn
SET    platform_ora = 1
,      activeflag = 1
WHERE  (recname, indexid) IN (
  SELECT objectvalue1, objectvalue2
  FROM   psprojectitem
  WHERE  objecttype = 1
  AND    projectname = 'REDUNDANT INDEXES')
AND    platform_ora = 0;
COMMIT;

We can see in Application Designer that index PSAWRK_XREF_CALC is a subset of PS_WRK_XREF_CALC because both start with PROCESS_INSTANCE and SEQ_NBR.


Index A was marked inactive on all platforms because the superset index _ is active on all platforms.  


PeopleSoft delivers some indexes for some platforms only. For example, PSETREENODE is only active on Informix. Therefore the script only deactivates the Informix platform flag on PSCTREENODE, and the platform flag changes to 'Some'. The index is still added to the project, but no attempt is made to create extended statistics or to make it invisible, and the index continues to be built on Oracle.

The comment applied to index C indicates that only the Informix flag was changed.



The final step, when you are satisfied that making the indexes invisible has no unacceptable consequences, is to drop the redundant indexes using the script generated by Application Designer

…
DROP INDEX PSCPSPROJECTITEM
/
DROP INDEX PSEPSPROJECTITEM
/
…
DROP INDEX PSAWRK_XREF_CALC
/
DROP INDEX PSAWRK_XREF_CALC1
/
…
DROP INDEX PSAWRK_XREF_CALC6
/

Identifying Redundant Indexes in PeopleSoft

This is the first of a two-part series that discusses how to identify and remove redundant indexes from a PeopleSoft system.
I recently wrote a series of articles on my Oracle blog about identifying redundant indexes on an Oracle database. The same approach can be taken with PeopleSoft, but using the metadata in the PeopleTools tables rather than the database catalogue.
(This means you could take a similar approach for PeopleSoft on databases other than Oracle, but I can't say anything about the behaviour of other optimizers) 
The following query returns a report of superset and redundant subset indexes in a PeopleSoft system. It can be downloaded as a script from my website (psredundant.sql).
The generic Oracle version of this script will identify subset indexes that are unique as redundant because the primary key and unique constraints can be altered to use the superset index.  However, this query does not make subset unique indexes as redundant because PeopleSoft does not explicitly create unique constraints, only unique indexes.  If I disabled a unique index, I would not be able to maintain the unique constraint via Application Designer.
Subquery IC returns a list of columns for each index. Subqueries UNI, AS2 and USI retrieve columns for unique/duplicate key, alternate search and user defined indexes respectively. The absolute value of key position is not important for this exercise, it is merely used to sort the column list in the listagg() function in subquery I.

REM psredundant.sql
set lines 160 pages 50 trimspool on
column recname format a15
column superset_index format a60
column redundant_index format a60
break on recname skip 1 on indexid skip 1
spool psredundant

WITH uni AS (/*unique indexes*/
SELECT /*+MATERIALIZE*/ f.recname, i.indexid
,      MIN(i.uniqueflag) OVER (PARTITION BY f.recname) uniqueflag
,      CASE WHEN MAX(CASE WHEN f.recname != f.recname_parent THEN 1 ELSE 0 END) OVER (PARTITION BY f.recname)=1 
        THEN f.fieldnum ELSE k.keyposn END keyposn
,      k.fieldname
FROM   psrecfielddb f
,      psindexdefn i
,      pskeydefn k
WHERE  i.recname IN(f.recname,f.recname_parent)
AND    i.recname = k.recname
AND    k.fieldname = f.fieldname
AND    i.indexid = '_' /*unique index*/
AND    k.indexid = i.indexid
AND    bitand(f.useedit,3) > 0 /*unique or dup key*/
), as0 AS (/*leading column on alternate search indexes*/
SELECT f0.recname, k0.indexid, i0.uniqueflag, 0 keyposn, f0.fieldname
FROM   psrecfielddb f0
,      psindexdefn i0
,      pskeydefn k0
WHERE  bitand(f0.useedit,16) = 16 /*alternate search key*/
AND    k0.recname = f0.recname_parent
AND    k0.fieldname = f0.fieldname
AND    i0.recname = k0.recname
AND    i0.indexid = k0.indexid
AND    i0.indexid BETWEEN '0' AND '9' /*alternate search index*/
), as1 AS ( /*now add unique columns*/
SELECT as0.recname, as0.indexid, as0.uniqueflag, as0.keyposn, as0.fieldname
FROM   as0
UNION ALL /*append unique key index columns*/
SELECT as0.recname, as0.indexid, as0.uniqueflag, uni.keyposn, uni.fieldname
FROM   as0, uni
WHERE  as0.recname = uni.recname
), as2 AS (
SELECT as1.recname, as1.indexid, as1.uniqueflag, NVL(k.keyposn,as1.keyposn), as1.fieldname
FROM   as1
       LEFT OUTER JOIN pskeydefn k /*to handle custom key order*/
       ON  k.recname = as1.recname
       AND k.indexid = as1.indexid
       AND k.fieldname = as1.fieldname
), usi AS (/*user indexes*/
SELECT i.recname, i.indexid, i.uniqueflag, k.keyposn, k.fieldname
FROM   psindexdefn i
,      pskeydefn k
WHERE  k.recname = i.recname
AND    k.indexid = i.indexid
AND    k.indexid BETWEEN 'A' AND 'Z'
AND    i.platform_ora = 1
), m AS (/*merge three kinds of index here*/
SELECT uni.recname, uni.indexid, uni.uniqueflag, uni.keyposn, uni.fieldname
FROM   uni
UNION ALL
SELECT as1.recname, as1.indexid, as1.uniqueflag, as1.keyposn, as1.fieldname
FROM   as1
UNION ALL
SELECT usi.recname, usi.indexid, usi.uniqueflag, usi.keyposn, usi.fieldname
FROM   usi
), ic AS ( /*list of columns, restrict to tables*/
SELECT r.recname, m.indexid, m.uniqueflag, m.keyposn, m.fieldname
FROM   m
,      psrecdefn r
WHERE  r.rectype IN(0,7)
And    r.recname = m.recname
), i AS ( --construct column list
SELECT /*+ MATERIALIZE*/
       ic.recname, ic.indexid, ic.uniqueflag
,      count(*) num_columns
,      listagg(ic.fieldname,',') within group (order by ic.keyposn) AS fieldlist
FROM   ic
GROUP BY ic.recname, ic.indexid, ic.uniqueflag
)
SELECT r.recname
,      i.indexid||' ('||i.fieldlist||')' superset_index
,      r.indexid||' ('||r.fieldlist||')' redundant_index
FROM   i
,      i r
WHERE  i.recname = r.recname
AND    i.indexid != r.indexid
AND    r.uniqueflag = 0 /*non-unique redundant*/
AND    i.fieldlist LIKE r.fieldlist||',%'
AND    i.num_columns > r.num_columns
order by r.recname, r.indexid
/

spool off

This is part of the output for a HR 9.2 demo database. In all it identified 58 indexes on 41 records. However, some of those records are temporary records with several physical instances, so the number of database objects that will be removed is higher.

RECNAME         SUPERSET_INDEX                                               REDUNDANT_INDEX
--------------- ------------------------------------------------------------ -----------------------------------------------------------
…
HRS_APP_PROFILE C (HRS_AL_LOG_ID,HRS_PERSON_ID)                              B (HRS_AL_LOG_ID)

HRS_SJT_JO      B (HRS_JOB_OPENING_ID,SCRTY_TYPE_CD)                         A (HRS_JOB_OPENING_ID)

HR_PROMOTE_XREF C (EMPLID,EMPL_RCD)                                          A (EMPLID)
                B (EMPLID,EMPL_RCD,ACTION_DT_SS,EFFSEQ)                      A (EMPLID)
                B (EMPLID,EMPL_RCD,ACTION_DT_SS,EFFSEQ)                      C (EMPLID,EMPL_RCD)

HR_SSTEXT_EFFDT _ (OBJECTOWNERID,HR_SSTEXT_SUB_ID,TEXT_ID,EFFDT)             A (OBJECTOWNERID,HR_SSTEXT_SUB_ID,TEXT_ID)

HR_TRANSFR_XREF C (EMPLID,EMPL_RCD)                                          A (EMPLID)
                B (EMPLID,EMPL_RCD,ACTION_DT_SS,EFFSEQ)                      A (EMPLID)
                B (EMPLID,EMPL_RCD,ACTION_DT_SS,EFFSEQ)                      C (EMPLID,EMPL_RCD)

PA_ALIAS_USE    0 (ALIAS_TYPE,ALIAS_NAME)                                    A (ALIAS_TYPE)

…
WRK_XREF_CALC   _ (PROCESS_INSTANCE,SEQ_NBR,XREF_NUM)                        A (PROCESS_INSTANCE,SEQ_NBR)

The articles on my Oracle blog discuss how to identify redundant indexes on an Oracle database, and then how to go about removing them.  Of course, in PeopleSoft, you should only remove indexes by making the change within Application Designer, and that is what the next posting discusses.

Setting Environment Variables in Application Server/Process Scheduler Tuxedo Domains

The topic of how to manage environment variables was mentioned recently on the PeopleSoft Administrator Podcast

• Episode 34: psadmin-plus
• Managing Environment Variables When Using Decoupled Homes

Recently I have built a pair of PeopleSoft environments for a proof-of-concept and have faced into exactly this problem.  I have two PS_APP_HOMEs (application homes) share the same PS_HOME (PeopleTools home).  The environment variables need to be correct before I open psadmin to administer the application server and process scheduler domains.
On Unix, I would usually recommend running domains for different PS_APP_HOMEs in different Unix user accounts.  Thus environmental variables can be set up in the .profile or .bashrc scripts.  The processes then run as different Unix users which makes them easier to monitor.  The Unix users should be in the same Unix group and have group permissions to the PS_HOME directory.  This approach is not possible on Windows.
The alternative is to have a scripts or a menu that sets up those variables before you enter psadmin.
I noticed that there is a new menu in the domain configuration menu in psadmin which permits you to set environmental variables that are then built into the Tuxedo domain configuration.  In fact this has always been possible by editing the psappsrv.ubx and psprcs.ubx files directly to cause variables to be created in the psappsrv.env and psprcs.env files, but now you just have to enter the variables through the menu in psadmin.
When we start psadmin we can see the relevant environmental parameters.   PS_APP_HOME points to my HR installation.

PSADMIN -- PeopleTools Release: 8.54.16
Copyright (c) 1996, 2014, Oracle. All rights reserved.

--------------------------------
PeopleSoft Server Administration
--------------------------------

  PS_CFG_HOME            /home/psadm1/psft/pt/8.54
  PS_HOME                /opt/oracle/psft/pt/tools
  PS_APP_HOME            /opt/oracle/psft/pt/hr91
  PS_CUST_HOME           /opt/oracle/psft/pt/hr91/pscust

  1) Application Server
…

But what if I have a Financials installation under the same PS_APP_HOME?  Option 16 of the configuration menu lets me define environmental settings.  Something very similar happens in the Process Scheduler configuration.

----------------------------------------------
Quick-configure menu -- domain: RASC3K
----------------------------------------------
      Features                      Settings
     ==========                    ==========
  1) Pub/Sub Servers   : Yes   17) DBNAME     :[XXXXXX]
…
      Actions
     =========
 14) Load config as shown
 15) Custom configuration
 16) Edit environment settings
  h) Help for this menu
  q) Return to previous menu

HINT: Enter 17 to edit DBNAME, then 14 to load

So, I have added PS_APP_HOME, PS_CUST_HOME and INTERFACE_HOME and they have become a part of the configuration of the Tuxedo domain.

--------------------------------------
PeopleSoft Domain Environment Settings
--------------------------------------
     Domain Name: RASC3K

     TEMP                     :[{LOGDIR}{FS}tmp]
     TMP                      :[{LOGDIR}{FS}tmp]
     TM_BOOTTIMEOUT           :[120]
     TM_RESTARTSRVTIMEOUT     :[120]
     TM_BOOTPRESUMEDFAIL      :[Y]
     FLDTBLDIR32              :[{$TUXDIR}{FS}udataobj]
     FIELDTBLS32              :[jrep.f32,tpadm]
     ALOGPFX                  :[{LOGDIR}{FS}TUXACCESSLOG]
     INFORMIXSERVER           :[{$Startup\ServerName}]
     COBPATH                  :[{$PS_APP_HOME}/cblbin:{$PS_HOME}/cblbin]
     PATH                     :[{$PATH}:{$Domain Settings\Add to PATH}]
     PS_APP_HOME              :[/opt/oracle/psft/pt/fin91]
     PS_CUST_HOME             :[/opt/oracle/psft/pt/fin91/pscust]
     INTERFACE_HOME           :[/opt/oracle/psft/pt/fin91/pscust/interfaces]

  1) Edit environment variable
  2) Add environment variable
  3) Remove environment variable
  4) Comment / uncomment environment variable
  5) Show resolved environment variables
  6) Save
  h) Help for this menu
  q) Return to previous menu

Command to execute (1-6, h or q) :

What is going on here?  These variables have been added to the PS_ENVFILE section of psappsrv.ubx.

# ----------------------------------------------------------------------
*PS_ENVFILE
TEMP={LOGDIR}{FS}tmp
TMP={LOGDIR}{FS}tmp
TM_BOOTTIMEOUT=120
TM_RESTARTSRVTIMEOUT=120
TM_BOOTPRESUMEDFAIL=Y
FLDTBLDIR32={$TUXDIR}{FS}udataobj
FIELDTBLS32=jrep.f32,tpadm
ALOGPFX={LOGDIR}{FS}TUXACCESSLOG
{WINDOWS}
COBPATH={$PS_HOME}\CBLBIN%PS_COBOLTYPE%
INFORMIXSERVER={$Startup\ServerName}
# Set IPC_EXIT_PROCESS=1 to use ExitProcess to terminate server process.
# Set IPC_TERMINATE_PROCESS=1 to use TerminateProcess to terminate server process.
# If both are set, TerminateProcess will be used to terminate server process.
#IPC_EXIT_PROCESS=1
IPC_TERMINATE_PROCESS=1
PATH={$PS_HOME}\verity\{VERITY_OS}\{VERITY_PLATFORM}\bin;{$PATH};{$Domain Settings\Add to PATH}
{WINDOWS}
{UNIX}
INFORMIXSERVER={$Startup\ServerName}
COBPATH={$PS_APP_HOME}/cblbin:{$PS_HOME}/cblbin
PATH={$PATH}:{$Domain Settings\Add to PATH}
{UNIX}
PS_APP_HOME=/opt/oracle/psft/pt/fin91
PS_CUST_HOME=/opt/oracle/psft/pt/fin91/pscust
INTERFACE_HOME=/opt/oracle/psft/pt/fin91/pscust/interfaces

They then appear in the psappsrv.env file that is generated when the domain is configured.  This file contains fully resolved values of environmental variables that are set by every tuxedo application server process when it starts.

TEMP=/home/psadm1/psft/pt/8.54/appserv/XXXXXX/LOGS/tmp
TMP=/home/psadm1/psft/pt/8.54/appserv/XXXXXX/LOGS/tmp
TM_BOOTTIMEOUT=120
TM_RESTARTSRVTIMEOUT=120
TM_BOOTPRESUMEDFAIL=Y
FLDTBLDIR32=/opt/oracle/psft/pt/bea/tuxedo/udataobj
FIELDTBLS32=jrep.f32,tpadm
ALOGPFX=/home/psadm1/psft/pt/8.54/appserv/XXXXXX/LOGS/TUXACCESSLOG
# Set IPC_EXIT_PROCESS=1 to use ExitProcess to terminate server process.
# Set IPC_TERMINATE_PROCESS=1 to use TerminateProcess to terminate server process.
# If both are set, TerminateProcess will be used to terminate server process.
#IPC_EXIT_PROCESS=1
IPC_TERMINATE_PROCESS=1
IPC_TERMINATE_PROCESS=1
INFORMIXSERVER=
COBPATH=/opt/oracle/psft/pt/fin91/cblbin:/opt/oracle/psft/pt/tools/cblbin
PATH=/opt/oracle/psft/pt/fin91/bin:/opt/oracle/psft/pt/fin91/bin/interfacedrivers:/opt/oracle/psft/pt/tools/jre/bin:/opt/oracle/psft/pt/tools/appserv:/opt/oracle/psft/pt/tools/setup:/opt/oracle/psft/pt/bea/tuxedo/bin:.:/opt/oracle/psft/pt/oracle-client/12.1.0.1/bin:/opt/oracle/psft/pt/oracle-client/12.1.0.1/OPatch:/opt/oracle/psft/pt/oracle-client/12.1.0.1/perl/bin:/opt/mf/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/opt/oracle/psft/pt/tools/bin:/opt/oracle/psft/pt/tools/bin/sqr/ORA/bin:/opt/oracle/psft/pt/tools/verity/linux/_ilnx21/bin:/home/psadm1/bin::.
PS_APP_HOME=/opt/oracle/psft/pt/fin91
PS_CUST_HOME=/opt/oracle/psft/pt/fin91/pscust
INTERFACE_HOME=/opt/oracle/psft/pt/fin91/pscust/interfaces

Warning

I found that I had to specify fully resolved paths for the variables I defined.  I do try setting variables in terms of other variables,

…
     PS_APP_HOME              :[/opt/oracle/psft/pt/fin91]
     PS_CUST_HOME             :[{$PS_APP_HOME}{FS}pscust]
     INTERFACE_HOME           :[{$PS_CUST_HOME}{FS}interfaces]
…

but I started to get errors.

==============ERROR!================
Value for PS_CUST_HOME: {$PS_APP_HOME}{FS}pscust, is invalid.  Your environment
may not work as expected.
==============ERROR!================

And some variables were not fully resolved in the psappsrv.env file.

PS_APP_HOME=/opt/oracle/psft/pt/fin91
PS_CUST_HOME=/opt/oracle/psft/pt/fin91/pscust
INTERFACE_HOME={$PS_APP_HOME}{FS}pscust/interfaces

Configuration Settings in the .ubx –v- .cfg

My only reservation is that there is now environment specific configuration in the psappsrv.ubx file, rather than the psappsrv.cfg file.   When I have done this in the past I would create additional variables in psappsrv.cfg that were referenced from the psappsrv.ubx file.  Thus the psappsrv.ubx was consistent across environments, and all the configuration is in the main configuration file psappsrv.cfg.
Although, you can add additional variables in psappsrv.cfg, thus

[Domain Settings]
;=========================================================================
; General settings for this Application Server.
;=========================================================================
…
Application Home=/opt/oracle/psft/pt/fin91
Custom Directory=pscust
Interface Directory=pscust/interfaces
…

and then reference them in the variables, and they will resolve correctly in the psappsrv.env.

PS_APP_HOME={$Domain Settings\Application Home}
PS_CUST_HOME={$Domain Settings\PS_APP_HOME}{FS}{$Domain Settings\Custom Directory}
INTERFACE_HOME={$Domain Settings\PS_APP_HOME}{FS}{$Domain Settings\Interface Directory}

You may experience errors in psadmin

==============ERROR!================
Value for PS_APP_HOME: {$Domain Settings\Application Home}, is invalid.  Your
environment may not work as expected.
==============ERROR!================

==============ERROR!================
Value for PS_CUST_HOME: {$Domain Settings\Application Home}{FS}{$Domain
Settings\Custom Directory}, is invalid.  Your environment may not work as
expected.
==============ERROR!================

Conclusion

Using this technique, it does not matter how environment variables are set when you go into psadmin to start the application server, the correct setting is defined in the Tuxedo domain and overrides that.
You have always been able to do this in Tuxedo, but you would have had to edit the psappsrv.ubx file yourself, now the menu allows you to administer this.
There is no way to view the psappsrv.ubx and psappsrv.env files from within psadmin, only psappsrv.cfg can be opened.  If you want to check your settings have reached the psappsrv.env file, you will need to leave psadmin and look in the files for yourself.

PeopleSoft on Oracle 12c

I was asked by Dan Iverson from psadmin.io about my experiences of PeopleSoft on Oracle 12c.  I have seen a number of PeopleSoft Financials systems on 12c recently.  Generally, the experience is very positive, but one common feature is that they had all disabled Adaptive Query Optimization.

What is Adaptive Query Optimization?

Adaptive Query Optimization is a term for a set of new features in Oracle 12c to allow the optimizer to improve the accuracy of execution plans.  It is described in the Optimizer with Oracle 12c white paper.  Put simply, Oracle collects additional statistics at parse which it can use to generate a better execution plan than if just using the ordinarily collected statistics, and further statistics at execution time which can be used to decide to change the next execution of the same SQL statement.

Why does it cause a Problem in PeopleSoft?

Adaptive Optimization is clearly a very useful feature in an application where SQL is shareable and reused.  But there's the rub.  This is another Oracle feature that works best with an application that is written the way that Oracle advise applications be written (Automatic Memory Management and the plan stability/management technologies also come to mind), and PeopleSoft is not such an application.  PeopleSoft applications are SQL parse intensive.

Parsing SQL is an expensive business in terms of time and CPU.  The library cache preserves the results of previous parses to save the overhead of repeatedly parsing the same SQL for subsequent executions.  If the SQL is different, even just by a literal value, it will have a different SQL_ID and will not be matched.  If SQL statements in the library cache are not being reused, then you incur the cost of parse for each execution.  Adaptive Optimization adds to that overhead, in PeopleSoft sometimes to the point where it can have a significant effect on performance.

PeopleSoft has several behaviours which lead to different SQL statements being generated from the same source:

• Application Engine %BIND() variables are resolved to literals in the generated SQL if the ReUseStatement attribute is not set, which by default it is not.  Thus, each iteration of a loop may cause the same Application Engine step to produce a different SQL statement with a different SQL ID (see Performance Benefits of ReUse Statement Flag in Application Engine).  PeopleSoft development has got better at using this attribute in delivered code.  However, there are still many places where it could be set but is not.
• There are many places in PeopleSoft where SQL is generated dynamically to cater for dynamic application configuration options.  Bind variables may be embedded in the SQL as literals, or more significant changes may be introduced such as completely different criteria or joining different tables.  In Application Engine, ReUseStatement usually cannot be used in conjunction with such steps.
• Different concurrently executing instances of the same Application Engine programs will be allocated a different non-shared instance of a temporary record, so they reference different tables.  

Some parts of PeopleSoft do produce shareable SQL statements.  Much of the SQL generated by the component processor and PeopleCode uses bind variables, the exception being where dynamic SQL is generated in PeopleCode.  PeopleSoft COBOL programs also widely use bind variables, but again some places dynamically generate SQL statements.

What does Oracle say about it?

There are a number of documents on the Oracle support website that touch on this (you will need to log in to follow these links):

Advice for the PeopleSoft Oracle DBA: The source of this document is not clear.  It is not attached to a MOS note, and is not in the usual format for such notes.  It is the only document that I can find that makes specific recommendations for Adaptive Optimization in conjunction with PeopleSoft, and it recommends totally disabling the entire Adaptive Optimization feature:

• "optimizer_adaptive_features = FALSE - After upgrading to Oracle Database 12c, many PeopleSoft customers have noticed overall performance degradation which is related to Oracle 12c Optimizer Adaptive Feature (OAF). It is recommended that this value be disabled in all Oracle Databases running PeopleSoft Applications."
• It has always been tempting to enable cursor_sharing with PeopleSoft so that Oracle converts literals in the SQL back to bind variables and thus matching SQLs in the library cache with different literal values.  However, it has no benefit for some dynamic SQL and different temporary table instances.  It can also introduce other optimizer problems.  On the few occasions that I have tried it, I have never had a good experience.  This document also recommends against it.

How to Approach Issues that Appear Related to Adaptive Features Such as Adaptive Query Optimization (Doc ID 2068807.1): This note acknowledges that "while some issues with adaptive features have been found, in a number of cases, issues manifesting as performance degradation associated with Adaptive Query Optimization simply highlight deficiencies in the system, primarily inaccurate or stale object or system statistics,  Due to the nature of the activities that Adaptive Query Optimization perform, the impact of degraded performance can be widespread and can include (but not limited to):

• Poor SQL execution performance (where a poor plan is selected)
• Poor Parse performance (where the optimizer takes more time to determine the optimal access path)
• The scope of either of the issues may not be limited to individual statements but can impact the whole system in unforeseen ways, for example, both poor SQL execution and parse performance may cause locks to be held for a greater duration than normal causing lock contention while poor parse performance may hold latches or mutexes to similar results.
• Blanket disabling of features: While disabling Adaptive Features with blanket parameters (see: Document 2031605.1) may provide a short-term workaround, the loss of their benefits to other queries may be significant. If the underlying cause is some other factor then a better solution is to identify and address that"

Adaptive Query Optimization (Doc ID 2031605.1) discusses how to disable either the entire Adaptive Query Optimization feature

OPTIMIZER_ADAPATIVE_FEATURES=FALSE /*disables adaptive optimisation as a whole*/

or to disable individual sub-features

_optimizer_adaptive_plans=false /*disables adaptive plans*/
_optimizer_use_feedback=false /*disables the use of statistics feedback for subsequent executions.  Default is true*/
_px_adaptive_dist_method=off /*disables the adaptive parallel distribution methods*/
_optimizer_dsdir_usage_control=0 /* disables the optimizer usage of dynamic statistics (sampling) directives. Default value is 126 */
_optimizer_gather_feedback=false /*disables the gathering of execution feedback in the optimizer*/
_optimizer_nlj_hj_adaptive_join=false /*disables only the adaptive join from nested loops to hash join*/

Summary

I have worked with a number of PeopleSoft Financials systems on Oracle 12c.  All but one of them had already disabled Adaptive Query Optimization, and I had to disable it on that one.  Once disabled performance improved such that it was at least as good as it had been on 11g.
There is no question that it is important to maintain accurate statistics and histograms on tables in PeopleSoft.  I have written elsewhere about how to achieve that, especially on temporary tables.  However, I am left with a concern that disabling the entire feature may be excessive and that there may be areas in PeopleSoft where it can bring some benefits.  I would like to find the opportunity to test whether it is possible to achieve better performance by only disabling certain parts of the feature.

Update 8.7.2017: The story moves on.  PeopleSoft and Adaptive Query Optimization in Oracle 12c.

PS360: A Utility to Extract and Present PeopleSoft Configuration and Performance Data

Introduction

Most of a PeopleSoft application is itself stored in the database in PeopleTools tables.  Therefore there is lot of information about the configuration and operation of a PeopleSoft system.  There are also performance metrics, particularly about batch processes.
PS360 is a new tool on which I am working.  It just uses SQL scripts to extract that data to html files, and package them up in a zip file so that they can be sent for further analysis.  The style and method is closely modelled on Enkitec's EDB360 by Carlos Sierra.  This is another free tool used for health check and performance analysis of any Oracle database system.  PS360 aims to gather PeopleSoft specific information that is not presented by EDB360.  It also runs in Oracle's SQL*Plus tool, and so is only available for use with an Oracle database.
Every section of PS360 is just the output of a SQL query, sometimes pre-processing is done in an anonymous PL/SQL block.  It does not install anything into the database, and does not update any table (other than the PLAN_TABLE which is used for temporary working storage).  Each report is in tabular and/or graphical format.  All the charts are produced with the Google chart API.

The output falls into three sections.  

• Configuration: Simple reports of certain configuration tables.
• Performance Metrics: Process Scheduler and Application Engine timings
• Consistency Checks: both within PeopleTools tables and between the PeopleSoft and Oracle catalogues.

Sample PS360 Index Page generated on PeopleSoft Demo Database

Instructions

The tool can be run by anyone with access to the PeopleSoft Owner database user (usually SYSADM).  That user will already have the privilege to read the Oracle catalogue.
Download the tool and unzip it into a directory.

• Navigate to the ps360 (master) directory
• Open SQL*Plus and connect as the owner of the PeopleSoft database (usually SYSADM).
• Execute the script ps360.sql.  
• The output will be written to a zip file in the same directory.  
• Unpack that zip file on your own PC.
• Open the file ps360_[database name]_0_index.html with a browser.

Feedback Enhancements

I am looking for feedback about the tool, and suggestions for further enhancements.
Please either leave comments here or e-mail me at info@go-faster.co.uk.