Accenture Enkitec Group E4 Webinar

Friday, June 30, 2017

PeopleSoft and Adaptive Query Optimization in Oracle 12c

Adaptive Query Optimization is a significant feature in Oracle 12c. Oracle has made lots of information available on the subject.(See
Adaptive Query Optimization is a set of capabilities that enable the optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics…There are two distinct aspects in Adaptive Query Optimization: adaptive plans, which focuses on improving the execution of a query and adaptive statistics, which uses additional information to improve query execution plans.
from Optimizer with Oracle Database 12c Release 2, White Paper June 2017 (see
Adaptive Statistics includes:
  • SQL plan directives: created and used to adapt SQL execution plans.
  • Statistics feedback: cardinality from table joins is used to improve SQL execution plans.
  • Performance feedback: improves the degree of parallelism chosen when PARALLEL_DEGREE_POLICY is set to ADAPTIVE
  • Adaptive dynamic sampling for parallel execution: dynamic statistics are gathered for certain parallel queries to improve cardinality estimates. The sample size is determined automatically.
(Parallelism is not used by default within PeopleSoft because all objects are marked NOPARALLEL by Application Designer).
This additional information should help the optimizer make better decisions, but it comes at the price of making the database do more work during SQL parse. Unfortunately, PeopleSoft makes extensive use of dynamically generated SQL, often with literal values leading to large amounts of parse. Even a small additional overhead during SQL parse can result in a significant overhead for the entire system.
Adaptive Plans includes:
  • Nested loop join/Hash join selection: the optimizer chooses between nested loops or hash joins at query runtime.
  • Adaptive parallel distribution method: the parallel distribution method is determined at runtime.
  • Star transformation bitmap pruning: certain bitmap indexes may be removed from the SQL execution plan at runtime if selectivity is significantly poorer than the estimate.
When joining tables, making the best choice between nested loops and hash joins is often critical for good performance. This feature could be of significant benefit to many applications, not just PeopleSoft. Let's take, as an example, this SQL generated by nVision on a 12c release 1 database. Due to the literal values it is very unlikely that the statement will be executed a second time.
BETWEEN 1 AND 12 OR A.ACCOUNTING_PERIOD BETWEEN 998 AND 999) AND A.ACCOUNT IN ('40000001','40000002','40000003','40000004') AND 
This recursive SQL was generated by dynamic statistics processing during SQL parse. Such SQL is easily identifiable by the /* DS_SVC */ comment.
SELECT /* DS_SVC */ /*+ dynamic_sampling(0) no_sql_tune no_monitoring
  optimizer_features_enable(default) no_parallel result_cache(snapshot=3600) */
SELECT /*+ qb_name("innerQuery") NO_INDEX_FFS( "A")  */ 1 AS C1 
  ("A"."ACCOUNT"='40000001' OR "A"."ACCOUNT"='40000002' OR
  "A"."ACCOUNT"='40000003' OR "A"."ACCOUNT"='40000004') AND
  ("A"."DEPTID"='001A' OR "A"."DEPTID"='002A' OR "A"."DEPTID"='003A' OR
  "A"."DEPTID"='004A' OR "A"."DEPTID"='005B' OR "A"."DEPTID"='006A' OR
  ("A"."STATISTICS_CODE"=' ')) innerQuery
It is easy to see that you wouldn't need too many additional queries like this to have a significant on system performance. In the early days of Oracle 12c many PeopleSoft customers reported just such experience. It is no surprise therefore that Oracle recommend disabling adaptive optimization in 12c. 
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. 
from E-ORA Advice for the PeopleSoft Oracle DBA (Doc ID 1445965.1) (see
In Oracle 12c release 1, the single parameter OPTIMIZER_ADAPTIVE_FEATUES controls all the adaptive optimization features. However, in Oracle 12c release 2 this is replaced by 2 new parameters:
Significantly this enhancement is available in 12c release 1. See
There are two patches available
  • patch for bug# 22652097 introduces the two parameters OPTIMIZER_ADAPTIVE_PLANS and OPTIMIZER_ADAPTIVE_STATISTICS and removes the parameter OPTIMIZER_ADAPTIVE_FEATURES.
  • patch for bug# 21171382 disables the automatic creation of extended statistics unless the optimizer preference AUTO_STATS_EXTENSIONS is set to ON.
Here is an example of another query from nVision, this time on a 12.1 system where the two patches have been applied. The optimizer has switched from a nested loop to a hash join in an adaptive plan. In this particular case, this was a good decision and the hash join outperformed the nested loop.

Plan hash value: 4041461164

| Id  | Operation                                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
|   0 | SELECT STATEMENT                                 |                   |       |       |    86 (100)|          |       |       |
|   1 |  HASH GROUP BY                                   |                   |     1 |    67 |    86   (3)| 00:00:01 |       |       |
|   2 |   HASH JOIN                                      |                   |     9 |   603 |    85   (2)| 00:00:01 |       |       |
|-  3 |    NESTED LOOPS                                  |                   |     9 |   603 |    85   (2)| 00:00:01 |       |       |
|-  4 |     STATISTICS COLLECTOR                         |                   |       |       |            |          |       |       |
|   5 |      PARTITION RANGE ITERATOR                    |                   |     9 |   459 |    84   (2)| 00:00:01 |    16 |    28 |
|   6 |       INLIST ITERATOR                            |                   |       |       |            |          |       |       |
|   7 |        PARTITION LIST ITERATOR                   |                   |     9 |   459 |    84   (2)| 00:00:01 |KEY(I) |KEY(I) |
|   8 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PS_LEDGER         |     9 |   459 |    84   (2)| 00:00:01 |   KEY |   KEY |
|   9 |          INDEX RANGE SCAN                        | PSCLEDGER         |   117 |       |    52   (2)| 00:00:01 |   KEY |   KEY |
|- 10 |     PARTITION RANGE SINGLE                       |                   |     1 |    16 |     1   (0)| 00:00:01 | 63382 | 63382 |
|- 11 |      INDEX RANGE SCAN                            | PSAPSTREESELECT10 |     1 |    16 |     1   (0)| 00:00:01 | 63382 | 63382 |
|  12 |    PARTITION RANGE SINGLE                        |                   |    23 |   368 |     1   (0)| 00:00:01 | 63382 | 63382 |
|  13 |     INDEX RANGE SCAN                             | PSAPSTREESELECT10 |    23 |   368 |     1   (0)| 00:00:01 | 63382 | 63382 |
   - this is an adaptive plan (rows marked '-' are inactive)


Adaptive Optimization is a great feature that does deliver real benefits, but like many Oracle features, it works best on an application that has been developed how Oracle would recommend applications be developed. Since the default value for OPTIMIZER_ADAPTIVE_STATISTICS is FALSE, PeopleSoft is clearly not alone in not being such an application.
Given PeopleSoft's propensity to generate non-sharable SQL, adaptive statistics are not a viable option, and should not be enabled, and in 12c release 1 that has meant sacrificing the benefits of adaptive plans. However, patch 22652097 means that we don't have to wait for release 2, we can now choose which feature to use.
Every system is different, even every PeopleSoft system is different. However, my experience of this so far with PeopleSoft is that I have not seen Oracle switch join method cause a problem. It certainly isn't a panacea either. When the join method has changed, it hasn't always made a big difference, but it has been net beneficial. I would certainly recommend applying these patches.

Friday, May 26, 2017

Interview with PeopleSoft Administrator Podcast: Cost-Based Optimizer Statistics in PeopleSoft

I recently recorded another interview with Dan Iverson and Kyle Benson for the PeopleSoft Administrator Podcast, this time about management of Cost-Based Optimizer Statistics in PeopleSoft systems.

(19 May 2017) #81 - Database Statistics

You can listen to the podcast on, or subscribe with your favourite podcast player, or in iTunes.

Sunday, February 26, 2017

Running Unix Shell Scripts from the PeopleSoft Process Scheduler

It is nearly 10 years since I first wrote about how to call Unix shell scripts from the Process Scheduler.  Although very little has changed, I have had a number of questions recently, so I thought it was time I checked the script and updated the posting.  I have used PeopleTools 8.54 in the preparation of this note.
The Process Scheduler is essentially just a mechanism for initiating processes on another server.  Mostly those are other PeopleSoft delivered executables.  The exception is the Application Engine Tuxedo Server process (PSAESRV) where the Process Scheduler submits a service request message that is picked up by one of the server processes that are already running.
NB: although the PSAESRV server is configured by default in the Process Scheduler domain, Oracle recommend that you should only use this when you have lots of very short-lived (runtime less than 30 seconds) application engine processes.  Typically, this only occurs in CRM.

Process Type Definition

First you need to create a new process type. I chose to call it ‘Shell Script’.  It runs a named shell wrapper script,  The wrapper script calls the script that is to be executed. Note that the command line in the process type definition includes the fully qualified path.

Wrapper Script

This is the wrapper script,, that will be called by the process scheduler.
# (c) David Kurtz 2007
# Script:
# where
# DBNAME is the name of the PeopleSoft datbase with a corresponding TNS entry
# ACCESSID is the schema containing the PeopleSoft database
# ACCESSPSWD is the password to ACCESSID
# PRCSINSTANCE is the process instance number supplied by PeopleSoft
# Purpose: To start Standard UNIX Shell Script from Process Scheduler, and interface with the PeopleSoft Process Scheduler
# 07.09.2007 Initial Version
# 23.02.2017 Remove unnecessary logfiles section
#set -x 

if [ $# -lt 4 ]; then
  echo "Usage $0: <DBNAME> <ACCESSID> <ACCESSPSWD> <PRCSINSTANCE> <command>"
  exit 1

shift 4

# Function to set status of API aware process instance
function prcsapi2
if [ $# -lt 2 ]; then
  echo "Parameter Error in function $0"
  exit 1


if [ ${PRCSINSTANCE} -gt 0 ];then
  echo "Setting process request ${PRCSINSTANCE} to status ${STATUS}"
sqlplus -S /nolog <<!
set termout off echo off feedback off verify off
connect ${CONNECT}
UPDATE psprcsque
SET    runstatus = ${STATUS}
,      sessionidnum = $$3
,      lastupddttm = SYSTIMESTAMP
WHERE  prcsinstance = ${PRCSINSTANCE}
UPDATE psprcsrqst 
SET    runstatus = ${STATUS}
,      prcsrtncd = ${PRCSRTNCD}
,      continuejob = DECODE(${STATUS},2,1,7,1,9,1,0)4
,      lastupddttm = SYSTIMESTAMP

  if [ ! $RET ];then
    echo "SQL*Plus Error Return Code: $?"

# Main Execution Starts Here

echo $0:$*
uname -a
echo "Current Directory: `pwd`"
echo "Process log files in: ${PSPRCSLOGDIR}"
prcsapi begindttm 75 
#Run the command

if [ ${PRCSRTNCD} -ne 0 ]; then
  prcsapi enddttm 3 # failure
  prcsapi enddttm 9 # success

  1. The Oracle user ID, password and TNS name for the PeopleSoft database are supplied in the first three parameters to the wrapper script. The PeopleSoft Process Instance number is the 4th command line parameter. These parameters are then removed with the shift command leaving any other parameters that have been specified.
  2. Function prcsapi sets the status on the process request row and updates the appropriate timestamp columns in the Process Scheduler tables.
  3. PSPRCSQUE.SESSIONIDNUM holds the operating system process ID of the shell executing the wrapper script.
  4. When the process completes and an end of process status is set (either 9 for success, 3 for failure or 2 for delete) CONTINUEJOB is set to 1, otherwise it is set to 0.
  5. When the wrapper scripts start it sets the process status on the process request record to 7 indicate that it is processing.  This can be seen in the Process Monitor.
  6. The return code of the executed script is captured. Later it will be recorded on
    PSPRCSRQST.PRCSRTNCD. A non-zero return code indicates an error and the process status will be set to error.

Process Definition

Now I can create a Process Definition that will use the process type twrapper script to execute another command or script.
The first four parameters passed to the wrapper script are the name of the database, the access ID and password, and the process instance. A string of further parameters will be appended in the individual Process Definition that is the specific command and parameters to be executed.
It is important that this new process type is defined as being API aware.  That means the process interacts with the Process Scheduler by updating the process status.  You can see how the interaction should be done by looking at procedure Update-Process-Status in the delivered SQR library prcsapi.sqc. Otherwise, the Process Scheduler cannot determine their status.  Consequently, all API-unaware processes have a run status of Success to indicate that they were started successfully.

I have written a silly test script called i that I want to be executed by the Process Scheduler.  It just prints out the command line parameters as banner text to both standard output and a file called mybanner.log.  This script will be called by
The Process Scheduler creates a working directory for each process request.  It sets the variable $PSPRCSLOGDIR to the fully qualified location of this directory. Note that changes the current directory to the location of this variable so that it writes mybanner.log there, and thus it is picked up by the distribution agent and made available via the report repository.  You may wish to do this in your scripts.
Current working directory can be specified at Process Type or Process definition.  However, during my testing, I found that these settings had no effect.  The working directory of the script did not change, and the value was not found in any environmental variable.
#A silly script to test
#(c) David Kurtz 2017
#banner function from

if [ "$PSPRCSLOGDIR" ] ; then

while [ $# -gt 0 ]
  /opt/oracle/psft/ptdb/custhome/banner $1
) | tee mybanner.log
exit $?
I can now create a Process Definition that uses the Shell Script process type that will execute  Note that this command line is appended to the existing command line specified in the Process Type definition

You can't quite see it in the screen shot, but the parameter list includes the process instance number:
/opt/oracle/psft/ptdb/custhome/ "Hello World" %%INSTANCE%%

Process Scheduler System Settings

During my testing, I found that it was necessary to specify output type settings for process type Other in the Process Scheduler System Settings; otherwise the output files were not posted to the report repository.

The newly defined Process can be run just as any other process is usually run. Any output from the script on the standard output channel is captured by the Process Scheduler and written to a log file that can then be viewed from the View Log/Trace facility within Process Monitor.
In this case the standard output was written to OTH_DMKTEST_.log, and I also get the mybanner.log that was written to $PSPRCSLOGDIR  in the list of available files.

contains just the three words passed as parameters
H     H         ll      ll              
H     H          l       l              
H     H  eeee    l       l       oooo   
HHHHHHH e    e   l       l      o    o  
H     H eeeeee   l       l      o    o  
H     H e        l       l      o    o  
H     H  eeee   lll     lll      oooo   
W     W                 ll           d  
W     W                  l           d  
W     W  oooo   rr rr    l           d  
W     W o    o   rr  r   l       ddddd  
W  W  W o    o   r       l      d    d  
W W W W o    o   r       l      d    d  
 W   W   oooo   rr      lll      dddd d 
 33333   99999   99999  5555555  00000  
3     3 9     9 9     9 5       0    00 
      3 9     9 9     9 5       0   0 0 
  3333   999999  999999  55555  0  0  0 
      3       9       9       5 0 0   0 
3     3       9       9 5     5 00    0 
 33333   99999   99999   55555   00000
OTH_DMKTEST_39950.log contains the standard output of the entire command - including the additional messages emitted by (in bold).
Note that the current directory is reported as being the location of the Process Scheduler Tuxedo domain. 
/opt/oracle/psft/ptdb/custhome/ Hello World 39950
Tue Sep  1 21:59:46 UTC 2015
Linux 2.6.39-400.215.10.el5uek #1 SMP Tue Sep 9 22:51:46 PDT 2014 x86_64 x86_64 x86_64 GNU/Linux
Current Directory: /home/psadm2/psft/pt/8.54/appserv/prcs/PRCSDOM
Process log files in: /home/psadm2/psft/pt/8.54/appserv/prcs/PRCSDOM/log_output/OTH_DMKTEST_39950
Setting process request 39950 to status 7
H     H         ll      ll              
H     H          l       l              
H     H  eeee    l       l       oooo   
HHHHHHH e    e   l       l      o    o  
H     H eeeeee   l       l      o    o  
H     H e        l       l      o    o  
H     H  eeee   lll     lll      oooo   
W     W                 ll           d  
W     W                  l           d  
W     W  oooo   rr rr    l           d  
W     W o    o   rr  r   l       ddddd  
W  W  W o    o   r       l      d    d  
W W W W o    o   r       l      d    d  
 W   W   oooo   rr      lll      dddd d 
 33333   99999   99999  5555555  00000  
3     3 9     9 9     9 5       0    00 
      3 9     9 9     9 5       0   0 0 
  3333   999999  999999  55555  0  0  0 
      3       9       9       5 0 0   0 
3     3       9       9 5     5 00    0 
 33333   99999   99999   55555   00000  
Setting process request 39950 to status 9
Tue Sep  1 21:59:46 UTC 2015 
A more detailed version of this document can be found at

Tuesday, January 17, 2017

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 (psredundantfix.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
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
  • 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;
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