David Kurtz

However, the Oracle database describes "dependencies between procedures, packages, functions, package bodies, and triggers accessible to the current user, including dependencies on views created without any database links" in the view ALL_DEPENDENCIES.

This hierarchical query (depend_heir.sql) on this view will report the structure of views within views. 

REM depend_hier.sql
undefine view_name
set pages 999 lines 176 long 50000
break on name skip 1 on owner 
ttitle 'Dependency Hierarchy'
column my_level format a5 heading 'Level'
column owner format a12
column name format a18
column type format a7
column referenced_type format a7 heading 'Refd|Type'
column referenced_owner format a6 heading 'Refd|Owner'
column referenced_name format a18 heading 'Refd|Name'
column referenced_link_name format a10 heading 'Refd|Link'
column dependency_type heading 'Dep|Type'
column text heading 'View Text' format a80 wrap on
spool depend_hier.&&view_name..lst
with d as (
  select * from all_dependencies
  union all
  select null, null, null, owner, view_name, 'VIEW', null, null
  from all_views 
  where owner = 'SYSADM' and view_name = UPPER('&&view_name')
)
select LPAD(TO_CHAR(level),level,'.') my_level
, d.type, d.owner, d.name
, d.referenced_type, d.referenced_owner, d.referenced_name, d.referenced_link_name
, d.dependency_type
, v.text
from d
  left outer join all_views v 
    on  v.owner = d.referenced_owner
    and v.view_name = d.referenced_name
  connect by nocycle
        d.name = prior d.referenced_name
  and   d.owner = prior d.referenced_owner
start with d.owner IS NULL and d.name IS NULL
/
spool off
ttitle off

Wed Aug 11                                                                                                                                                             page    1
                                                                              Dependency Hierarchy

                                              Refd    Refd   Refd               Refd       Dep
Level TYPE    OWNER        NAME               Type    Owner  Name               Link       Type View Text
----- ------- ------------ ------------------ ------- ------ ------------------ ---------- ---- --------------------------------------------------------------------------------
1                                             VIEW    SYSADM PS_POSN_HISTORY3                   SELECT A.Position_Nbr ,A.Position_Entry_Dt ,A.Emplid ,A.EMPL_RCD ,B.EFFDT ,B.EFF
                                                                                                SEQ ,B.Sal_Admin_Plan ,B.Grade ,B.Step ,B.Comprate ,B.Comp_Frequency ,B.Currency
                                                                                                _Cd ,' ' ,' ' ,' ' ,' ' FROM PS_POSN_HISTORY2 A ,PS_JOB B WHERE B.EmplID = A.Emp
                                                                                                lID AND B.EMPL_RCD = A.EMPL_RCD AND B.Position_Nbr = A.Position_Nbr AND B.EffDt
                                                                                                = ( SELECT MAX(C.EffDt) FROM PS_JOB C WHERE C.EmplID = B.EmplID AND C.EMPL_RCD =
                                                                                                 B.EMPL_RCD AND (C.EffDt < A.Position_End_Dt OR (C.EffDt = A.Position_End_Dt AND
                                                                                                 C.EffSeq = A.EffSeq - 1))) AND B.Effseq = ( SELECT MAX(C.Effseq) FROM PS_JOB C
                                                                                                WHERE C.EmplID = B.EmplID AND C.EMPL_RCD = B.EMPL_RCD AND (C.EffDt < A.Position_
                                                                                                End_Dt OR (C.EffDt = A.Position_End_Dt AND C.EffSeq = A.EffSeq - 1)))


.2    VIEW    SYSADM       PS_POSN_HISTORY3   TABLE   SYSADM PS_JOB                        HARD
.2    VIEW                                    VIEW    SYSADM PS_POSN_HISTORY2              HARD SELECT A.Position_Nbr , A.Position_Entry_Dt , A.Emplid , A.EMPL_RCD , B.EffDt ,
                                                                                                B.EffSeq , B.Action FROM PS_POSN_HISTORY A , PS_JOB B WHERE A.EmplID = B.EmplID
                                                                                                AND A.Empl_Rcd = B.Empl_Rcd AND B.EffDt = ( SELECT MIN(C.EffDt) FROM PS_JOB C WH
                                                                                                ERE C.EmplID = B.EmplID AND C.Empl_Rcd = B.Empl_Rcd AND (C.EffDt > A.Position_En
                                                                                                try_Dt OR (C.EffDt = A.Position_Entry_Dt AND C.EffSeq > A.EffSeq)) AND ((C.Posit
                                                                                                ion_Nbr <> A.Position_Nbr) OR (C.HR_STATUS <> 'A'))) AND B.EffDt<=TO_DATE(TO_CHA
                                                                                                R(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') AND B.EffSeq = ( SELECT MIN(D.EffSeq) FROM
                                                                                                 PS_JOB D WHERE D.EmplID = B.EmplID AND D.Empl_Rcd = B.Empl_Rcd AND D.EffDt = B.
                                                                                                EffDt AND ((D.Position_Nbr <> A.Position_Nbr) OR (D.HR_STATUS <> 'A')))


..3   VIEW    SYSADM       PS_POSN_HISTORY2   TABLE   SYSADM PS_JOB                        HARD
..3   VIEW                                    VIEW    SYSADM PS_POSN_HISTORY               HARD SELECT A.Position_Nbr ,A.Position_Entry_Dt ,A.Emplid ,A.EMPL_RCD ,A.EFFSEQ ,A.EF
                                                                                                FDT ,A.Sal_Admin_Plan ,A.Grade ,A.Step ,A.CompRate ,A.Comp_Frequency ,A.Currency
                                                                                                _Cd ,' ' ,' ' ,' ' ,' ' FROM PS_Job A WHERE A.Position_Entry_Dt = A.Effdt AND A.
                                                                                                Effseq = ( SELECT MIN(B.Effseq) FROM PS_Job B WHERE B.Emplid = A.Emplid AND B.EM
                                                                                                PL_RCD = A.EMPL_RCD AND B.Effdt = A.Effdt AND B.Position_Nbr = A.Position_Nbr)


...4  VIEW    SYSADM       PS_POSN_HISTORY    TABLE   SYSADM PS_JOB                        HARD

Active Data Guard is a configuration option that became available in Oracle 11g where a standby database is maintained as a synchronised physical replica of the primary database and is also open for read-only SQL queries.

PeopleSoft added configuration to direct certain read-only components and processes to an ADG standby using secondary connections in the application servers and process schedulers.  However, in PeopleSoft, all scheduled processes update at least the process scheduler request tables, even if they make no updates to application tables.  This cannot be done on a read-only standby database and must be directed back to the primary database.

PeopleBooks sets out a method for Implementing Active Data Guard (this link is to the PeopleTools 8.58 documentation).  It uses a second schema to which PeopleSoft application servers and process schedulers connect using a second access profile.  The second schema contains synonyms for each table and view that either point to the corresponding object in the original schema on the ADG standby, or if the object is going to be updated by the application then via database links to the corresponding object in the primary database.  This approach requires knowledge of which tables are updated during otherwise read-only processing, a lot of scripting to generate all the synonyms and grants, and ongoing maintenances as new objects are added to the database.

However, that approach is rendered obsolete by Active Data Guard DML redirection, a new feature in 19c.  This post explains how to configure PeopleSoft to make use of Active Data Guard on Oracle 19c with DML redirect.

With DML redirection enabled, updates on the secondary database are automatically redirected back to the primary database via a SQL*Net connection between the databases (not unlike a database link), and then they will be replicated back to the standby database like any other change.  PeopleSoft no longer needs to be configured specially to handle updated tables differently.  Consequently, the PeopleSoft ADG configuration is massively simplified.

Processes no longer have to be strictly read-only to run on the ADG database.  If there are only a small quantity of updates the redirect can handle it.

SQL>select * from ps.psdbowner;

DBNAME   OWNERID
-------- --------
FINPRD   SYSADM
FINADG   SYSADM

[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=FINPRD
DBType=ORACLE
UserId=PSAPPS
UserPswd={V2}FEhk7rIFt2f0GRYaH6B9la8DXXMNtsz1kPZ+
ConnectId=PEOPLE
ConnectPswd={V2}Mw3RFr0MHFBpJHbqXh7Dx9qCsO7TFT4G
StandbyDBName=FINADG
StandbyDBType=ORACLE
StandbyUserId=PSAPPS
StandbyUserPswd={V2}Ski/r2xYCvbTbBhXOGfH8HO7zCRxoDFK5rmb

Error in sign on
 Database Type: 7 (ORACLE)
 Database Name: HCM91
 Server Name: 
 OperID: 
 ConnectID: people
 Process Instance: 0
 Reason: Invalid user ID or password for database signon. (id=)
 Note: Attempt to authenticate using GUID 6a1ced41-2fe0-11e2-9183-be3e31d6e740
Invalid command line argument list.
 process command line: -CT ORACLE -CD HCM91 -GUID 6a1ced41-2fe0-11e2-9183-be3e31d6e740 -SS NO -SN NO 
 GUID command line : -CT ORACLE -CD HCM91 -CO "PS" -CP Unavailable -R 1 -I 852 -AI AEMINITEST -OT 6 -FP 
"C:\app\pt\appserv\prcs\HCM91PSNT\log_output\AE_AEMINITEST_852\" -OF 1

New Process Schedulers for ADG

New process schedulers should be created for running ADG only jobs.  They will only be connected to the standby database.  The standby connection should be left blank.  All of the SQL updates made by the Process Scheduler processes will be handled by DML redirection.  You will need to have at least 2 so they do not become a single point of failure.

[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=FINADG
DBType=ORACLE
UserId=PSAPPS
UserPswd={V2}YoAQq7Ut4WBHJL89N9gv9E0AWwLaecGZ4qep
ConnectId=PEOPLE
ConnectPswd={V2}OlSYHuFMZa2c8uonfYkKk+3+APYvTU9N
StandbyDBName=
StandbyDBType=
StandbyUserId=
StandbyUserPswd=

Existing Process Schedulers

All process categories apply to all process schedulers.  The concurrency of the ADG category should be set to 0 on all other process schedulers to prevent it from running there.  These schedulers will remain connected to the primary database only.  They will not be connected to the secondary database.

[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=FINPRD
DBType=ORACLE
UserId=PSAPPS
UserPswd={V2}EcxeV3mit3GMT5kDfz/z+s0L9B1aUb6ix04f
ConnectId=PEOPLE
ConnectPswd={V2}NtXafW7hlcGY016bhazl2kqqvlSNYMK1
StandbyDBName=
StandbyDBType=
StandbyUserId=
StandbyUserPswd=

Configuration by SQL

It may be easier to update the process scheduler configuration by SQL

update pslock 
set version = version + 1
where objecttypename IN('SYS','PPC')
/
update psversion
set version = version + 1
where objecttypename IN('SYS','PPC')
/
update  ps_servercategory c 
set     maxconcurrent = CASE WHEN servername like 'PSUNX_A%' 
                             THEN (SELECT s.maxapiaware FROM ps_serverdefn s 
                                   WHERE  s.servername = c.servername)
                             ELSE 0 END
where   prcscategory = 'ADGOnly'
/
update ps_serverdefn
set    version = (SELECT version from psversion where objecttypename = 'PPC')
,      lastupddttm = systimestamp
/
update ps_prcsdefn 
set    version = (SELECT version from psversion where objecttypename = 'PPC')
,      prcsreadonly = 1
,      prcscategory = 'ADGOnly'
,      lastupddttm = systimestamp
where  prcsreadonly = 1
or     prcscategory = 'ADGOnly'
or     prcsname = 'PSQUERY'
/
select prcstype, prcsname, prcscategory, prcsreadonly
from   ps_prcsdefn
where  prcsreadonly = 1
or     prcscategory = 'ADGOnly'
or     prcsname = 'PSQUERY'
/
select * from ps_servercategory
where prcscategory IN('ADGOnly')
order by 2,1
/
commit
/

PSOFT-FINADG>Select prcstype, prcsname, prcscategory, prcsreadonly
  2  From   ps_prcsdefn
  3  where  prcsreadonly = 1
  4  or     prcscategory = 'ADGOnly'
  5  or     prcsname = 'PSQUERY'
  6  /

PRCSTYPE                       PRCSNAME     PRCSCATEGORY                   P
------------------------------ ------------ ------------------------------ -
Application Engine             AEMINITEST   ADGOnly                        1
COBOL SQL                      PTPDBTST     ADGOnly                        1
SQR Report                     PTSQRTST     ADGOnly                        1
Application Engine             FB_GEN_EXTR  ADGOnly                        1
SQR Report                     XRFWIN       ADGOnly                        1
SQR Report                     SWPAUDIT     ADGOnly                        1
SQR Report                     SYSAUDIT     ADGOnly                        1
SQR Report                     XRFAPFL      ADGOnly                        1
SQR Report                     XRFAEPL      ADGOnly                        1
SQR Report                     XRFPGDZ      ADGOnly                        1
SQR Report                     DDDAUDIT     ADGOnly                        1
SQR Report                     XRFEVPC      ADGOnly                        1
SQR Report                     XRFFLPC      ADGOnly                        1
SQR Report                     XRFFLPN      ADGOnly                        1
SQR Report                     XRFFLRC      ADGOnly                        1
SQR Report                     XRFIELDS     ADGOnly                        1
SQR Report                     XRFMENU      ADGOnly                        1
SQR Report                     XRFPANEL     ADGOnly                        1
SQR Report                     XRFPCFL      ADGOnly                        1
SQR Report                     XRFPNPC      ADGOnly                        1
SQR Report                     XRFRCFL      ADGOnly                        1
SQR Report                     XRFRCPN      ADGOnly                        1
Application Engine             PSQUERY      ADGOnly                        1
Application Engine             PSCONQRS     ADGOnly                        1

SYSADM-FINADG>select * from ps_servercategory
  2  where prcscategory IN('ADGOnly')
  3  order by 2,1
  4  /

SERVERNA PRCSCATEGORY                   P MAXCONCURRENT
-------- ------------------------------ - -------------
…
PSNT     ADGOnly                        5             0
PSUNX    ADGOnly                        5             0
PSUNX_A1 ADGOnly                        5             5
…

PeopleTools 8.58.07 - Application Engine
Copyright (c) 1988-2021 Oracle and/or its affiliates.
All Rights Reserved

PeopleTools SQL Trace value: 159 (0x9f): /cs/psoft/cfg/FINPRD/appserv/prcs/FINPRDA1/log_output/AE_PSQUERY_xxxxxxxx/AE_PSQUERY_xxxxxxxx.trc
PeopleTools PeopleCode Trace value: 64 (0x40): /cs/psoft/cfg/FINPRD/appserv/prcs/FINPRDA1/log_output/AE_PSQUERY_xxxxxxxx/AE_PSQUERY_xxxxxxxx.trc

File: /vob/peopletools/src/pssys/qpm.cppSQL error. Stmt #: 8495  Error Position: 3055  Return: 16000 - ORA-16000: database or pluggable database open for read-only access 
Failed SQL stmt: SELECT …
<a query that references a remote database via a database link>
…
Error in running query because of SQL Error, Code=16000, Message=ORA-16000: database or pluggable database open for read-only access  (50,380)

PeopleCode Exit(1) Abort invoked by Application at PSQUERY.MAIN.ExecQry. (108,543)

Process xxxxxxxx ABENDED at Step PSQUERY.MAIN.ExecQry (PeopleCode) -- RC = 16 (108,524)

Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s

Disable Query Statistics

I have found that query statistics can cause a problem when the query is run on ADG.

PeopleTools 8.58.07 - Application Engine
Copyright (c) 1988-2021 Oracle and/or its affiliates.
All Rights Reserved

PeopleTools SQL Trace value: 159 (0x9f): /cs/psoft/cfg/FINPRD/appserv/prcs/FINPRDA2/log_output/AE_PSQUERY_19356674/AE_PSQUERY_19356674.trc

File: /vob/peopletools/src/pssys/qdmutil.cppSQL error. Stmt #: 4608  Error Position: 91  Return: 16397 - ORA-16397: statement redirection from Oracle Active Data Guard standby database to primary database failed 
Failed SQL stmt: SELECT EXECCOUNT, AVGEXECTIME, AVGFETCHTIME, LASTEXECDTTM, AVGNUMROWS, OPRID, QRYNAME FROM PSQRYSTATS WHERE OPRID = :1 AND QRYNAME = :2 FOR UPDATE OF EXECCOUNT
Application Engine program PSQUERY ended normally

They can be disabled in the PIA

• Navigate to: PeopleTools, Utilities, Administration, Query Administration, 
• Go to the last tab (Settings), 
• Uncheck Run Query Statistics.

Enable Query Execution Logging

Query stats (logged in the table PSQRYSTATS) are not particularly useful as they only compute average metrics over a period that is not defined.  Instead, query execution logging (to the table PSQRYEXECLOG) is far more useful for performance analysis because you know who ran the query when it ran, and how many rows it retrieved.  It is enabled for individual queries.  It can be enabled across the board with the following SQL.

update pslock
set version = version + 1
where objecttypename IN('SYS','QDM')
/
update psversion
set version = version + 1
where objecttypename IN('SYS','QDM')
/
update psqrydefn
set    version = (SELECT version from psversion where objecttypename = 'QDM')
,      execlogging = 'Y'
,      lastupddttm = systimestamp
where  execlogging != 'Y'
/

When working on a performance issue, you may reach the point where you want to introduce one or more hints into a SQL statement.  It is often tempting to simply add the hints to the source code.  Doing so may appear to be simpler.  That is often the case with Application Engine programs, however, it is much more difficult in PeopleSoft COBOL programs.  

A strong argument against such code change is that having made it, you have also to functionally test the change and push it through the entire release management process to get it into production.  Then, should you ever want to change or remove the hints, you have to go through the entire process again.

Oracle provides several mechanisms to define a database object containing a hint or set of hints and to apply them to matching SQL statements.  These mechanisms work best with SQL that uses bind variables rather than literals.  If an otherwise identical SQL statement has different literal values then it has a different SQL_ID and is treated by the database as a new statement.  SQL Plan Baselines, SQL Patches and SQL Profiles match to the specific text of a SQL statement.  Different literal values will prevent matching and defeat these mechanisms.  These techniques must still be tested and migrated in a controlled manner, but they have no functional impact and so only testing of performance is needed.

SQL Profiles can also perform forced matching, where statements that are similar except for literal values are matched.  However, note that they also require licencing of Tuning Pack.

Some parts of PeopleTools and the PeopleSoft applications are better than others at producing sharable SQL with bind variables.  Most of the SQL generated by the component processor uses bind variables.  In Application Engine, if the ReUse Statement property is not set, which it is not by default, the %BIND fields are substituted with their literal values in the SQL statement.  However, if the property is set then %BIND fields become bind variables.  Over the years much more PeopleSoft code has been delivered with this attribute enabled.  Doing so has significant performance benefits (see Performance Benefits of ReUse Statement Flag in Application Engine).  

Where, under normal circumstances, I might use a baseline or patch to inject a hint or profile of hints into a particular SQL statement (i.e. where I am dealing with a single SQL_ID), if the statement has literal values that change, then each statement has a different SQL_ID.  I have experimented with setting CURSOR_SHARING to FORCE at session-level for a specific scheduled process, but I have always had very poor experiences with that approach.  It invariably causes more problems than it solves.  Instead, I use force matched SQL Profiles.

The PeopleTools documentation sets out situations where ReUse Statement cannot be set.  This includes dynamically generated code where %BIND(…,NOQUOUTES) is used to embed a piece of SQL held in a variable.  This is a very common technique in PeopleSoft; often dynamically generated code is driven by the application configuration.  

We also see a similar design in PeopleSoft's COBOL programs.  Static statements are loaded from the stored SQL statements table (PS_SQLSTMT_TBL) and do use bind variables, but dynamic statements are assembled at runtime from static fragments in the COBOL code and any variable values are embedded as literals rather than using bind variables.

Forced matching will allow a SQL profile to match a statement that is the same except for different literal values.   However, dynamic SQL statements can change in ways that are beyond that, including:

• Different instances of working storage tables can be used by different instances of the same process.
• Different columns can be used in select and group by clauses.
• Different criteria can be introduced. 
• A different number of terms in an IN() condition.

Occasionally, and I really mean very occasionally when I have exhausted other alternatives, I have dynamically created groups of SQL Profiles (still with forced matching) to cover every permutation of the variations of the dynamic SQL statement.

INSERT INTO PS_CURR_WRK_TBL001 (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFI
LIATE_INTRA1,AFFILIATE_INTRA2,ALTACCT,BOOK_CODE,BUDGET_REF,CHARTFIELD1,C
HARTFIELD2,CHARTFIELD3,CLASS_FLD,CURRENCY_CD,DATE_CODE,DEPTID,FISCAL_YEA
R,FUND_CODE,GL_ADJUST_TYPE,LEDGER,OPERATING_UNIT,POSTED_BASE_AMT,POSTED_
TOTAL_AMT,POSTED_TOTAL_CR,POSTED_TOTAL_DR,POSTED_TRAN_AMT,POSTED_TRAN_CR
,POSTED_TRAN_DR,PROCESS_INSTANCE,PRODUCT,PROGRAM_CODE,PROJECT_ID,STATIST
ICS_CODE,RATE_DIV,RATE_MULT,GAINLOSS_ACCT,RESTATED_AMT,REVAL_ADJUSTMENT,
TARGET_CURRENCY) SELECT A.ACCOUNT,003,AFFILIATE,' ',' ','
',A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,'
',A.CURRENCY_CD,DATE_CODE,DEPTID,2020,FUND_CODE,GL_ADJUST_TYPE,'X_UKCORE
',OPERATING_UNIT,SUM(A.POSTED_BASE_AMT),SUM(POSTED_TOTAL_AMT),0,0,0,0,0,
0002858795,PRODUCT,PROGRAM_CODE,PROJECT_ID,' ',R.RATE_DIV,R.RATE_MULT,'
',ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02
),ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02 )
- SUM(A.POSTED_BASE_AMT),'USD' FROM PS_LEDGER A , PS_TREE_SEL10_R001 B
, PS_CURR_WRK_RT001 R WHERE A.LEDGER='X_UKCORE' AND A.FISCAL_YEAR =
2020 AND A.ACCOUNTING_PERIOD IN ( 0, 1, 2, 3) AND B
.PROCESS_INSTANCE=0002858795 AND B .CHARTFIELD='ACCOUNT' AND
A.ACCOUNT>=B .RANGE_FROM_10 AND A.ACCOUNT<=B .RANGE_TO_10 AND
A.BUSINESS_UNIT='96013' AND A.CURRENCY_CD <> 'GBP' AND FROM_CUR =
A.CURRENCY_CD AND TO_CUR = 'GBP' AND R.PROCESS_INSTANCE = 0002858795
GROUP BY A.ACCOUNT,AFFILIATE,A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIE
LD2,CHARTFIELD3,A.CURRENCY_CD,DATE_CODE,DEPTID,FUND_CODE,GL_ADJUST_TYPE,
OPERATING_UNIT,PRODUCT,PROGRAM_CODE,PROJECT_ID,RATE_DIV,RATE_MULT

INSERT INTO PS_CURR_WRK_TBL001 (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFI
LIATE_INTRA1,AFFILIATE_INTRA2,ALTACCT,BOOK_CODE,BUDGET_REF,CHARTFIELD1,C
HARTFIELD2,CHARTFIELD3,CLASS_FLD,CURRENCY_CD,DATE_CODE,DEPTID,FISCAL_YEA
R,FUND_CODE,GL_ADJUST_TYPE,LEDGER,OPERATING_UNIT,POSTED_BASE_AMT,POSTED_
TOTAL_AMT,POSTED_TOTAL_CR,POSTED_TOTAL_DR,POSTED_TRAN_AMT,POSTED_TRAN_CR
,POSTED_TRAN_DR,PROCESS_INSTANCE,PRODUCT,PROGRAM_CODE,PROJECT_ID,STATIST
ICS_CODE,RATE_DIV,RATE_MULT,GAINLOSS_ACCT,RESTATED_AMT,REVAL_ADJUSTMENT,
TARGET_CURRENCY) SELECT A.ACCOUNT,005,AFFILIATE,' ',' ','
',A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,'
',A.CURRENCY_CD,DATE_CODE,DEPTID,2020,FUND_CODE,GL_ADJUST_TYPE,'XXXX',OP
ERATING_UNIT,SUM(A.POSTED_BASE_AMT),SUM(POSTED_TOTAL_AMT),0,0,0,0,0,0002
991789,PRODUCT,PROGRAM_CODE,PROJECT_ID,' ',R.RATE_DIV,R.RATE_MULT,'
',ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02
),ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02 )
- SUM(A.POSTED_BASE_AMT),'AUD' FROM PS_LEDGER A , PS_TREE_SEL10_R B ,
PS_CURR_WRK_RT002 R WHERE A.LEDGER='XXXX' AND A.FISCAL_YEAR = 2020 AND
A.ACCOUNTING_PERIOD IN ( 0, 1, 2, 3, 4, 5) AND B
.PROCESS_INSTANCE=0002991789 AND B .CHARTFIELD='ACCOUNT' AND
A.ACCOUNT>=B .RANGE_FROM_10 AND A.ACCOUNT<=B .RANGE_TO_10 AND
A.BUSINESS_UNIT='13051' AND A.CURRENCY_CD <> 'AUD' AND FROM_CUR =
A.CURRENCY_CD AND TO_CUR = 'AUD' AND R.PROCESS_INSTANCE = 0002991789
GROUP BY A.ACCOUNT,AFFILIATE,A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIE
LD2,CHARTFIELD3,A.CURRENCY_CD,DATE_CODE,DEPTID,FUND_CODE,GL_ADJUST_TYPE,
OPERATING_UNIT,PRODUCT,PROGRAM_CODE,PROJECT_ID,RATE_DIV,RATE_MULT

WITH n AS (SELECT rownum n FROM dual CONNECT BY level<=12
), n1 AS (
SELECT n1.n period, LISTAGG(n2.n,', ') WITHIN GROUP (ORDER BY n2.n) periods
FROM   n n1, n n2
WHERE  n2.n <= n1.n 
AND    n1.n <= 12
GROUP BY n1.n
)
SELECT * FROM n1
/

    PERIOD PERIODS
---------- ----------------------------------------
         1 1
         2 1, 2
         3 1, 2, 3
         4 1, 2, 3, 4
         5 1, 2, 3, 4, 5
         6 1, 2, 3, 4, 5, 6
         7 1, 2, 3, 4, 5, 6, 7
         8 1, 2, 3, 4, 5, 6, 7, 8
         9 1, 2, 3, 4, 5, 6, 7, 8, 9
        10 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
        11 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
        12 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12

• Lists of the various tables used for working storage can be queried from the PeopleSoft data dictionary, PSRECDEFN.  I can see that there are 5 versions of the current work table that the process can choose from.  Note that these are ordinary tables, so there are 5 different records in PSRECDEFN. 

SELECT TO_NUMBER(substr(recname,13)) seq
,      decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
FROM   psrecdefn
WHERE  recname like 'CURR_WRK_TBL%'
/

SEQ        TABLE_NAME
---------- ------------------
           PS_CURR_WKK_TBL
         1 PS_CURR_WKK_TBL001
         2 PS_CURR_WKK_TBL002
         3 PS_CURR_WKK_TBL003
         4 PS_CURR_WKK_TBL004

WITH n AS (
SELECT rownum-1 n FROM dual CONNECT BY LEVEL<=100
), c AS (
SELECT c.recname
,      n.n instance
,      DECODE(r.sqltablename, ' ', 'PS_'||r.recname,r.sqltablename)||DECODE(n.n,0,'',n.n) table_name
FROM   n
,      pstemptblcntvw c
,      psrecdefn r
,      psoptions o
WHERE  r.recname = c.recname
AND    n.n <= c.temptblinstances+o.temptblinstances
)
SELECT instance, table_name
FROM   c
WHERE  recname = 'WRK_XREF_CAL'
/

  INSTANCE TABLE_NAME
---------- ----------------------------------------------------------
         0 PS_WRK_XREF_CAL
         1 PS_WRK_XREF_CAL1
         2 PS_WRK_XREF_CAL2
         3 PS_WRK_XREF_CAL3
         4 PS_WRK_XREF_CAL4
         5 PS_WRK_XREF_CAL5
         6 PS_WRK_XREF_CAL6

'TS'||ts.seq||'_RT'||rt.seq||'_TBL'||wk.seq||'_'||n1.period

REM coe_xfr_sql_profile_FSPCCURR_tree_rate_curr_wrk.sql
SPO coe_xfr_sql_profile_FSPCCURR_tree_rate_curr_wrk.log;

WHENEVER SQLERROR CONTINUE
REM WHENEVER SQLERROR EXIT SQL.SQLCODE;
VAR signature NUMBER;

DECLARE
  sql_txt CLOB;
  h       SYS.SQLPROF_ATTR;
  e_no_sql_profile EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_no_sql_profile, -13833);
BEGIN
  FOR i IN(
    WITH n AS (SELECT rownum n FROM dual CONNECT BY level<=12
    ), n1 AS (
    SELECT n1.n period, LISTAGG(n2.n,', ') WITHIN GROUP (ORDER BY n2.n) periods
    FROM   n n1, n n2
    WHERE  n2.n <= n1.n
    GROUP BY n1.n
    ), ts AS (
    SELECT TO_NUMBER(substr(recname,13)) seq
    ,      decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
    FROM   psrecdefn
    WHERE  recname like 'TREE_SEL10_R%'
    ), rt AS (
    SELECT TO_NUMBER(substr(recname,12)) seq
    ,      decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
    FROM   psrecdefn
    WHERE  recname like 'CURR_WRK_RT%'
    ), wk AS (
    SELECT TO_NUMBER(substr(recname,13)) seq
    ,      decode(sqltablename,' ','PS_'||recname,sqltablename) table_name
    FROM   psrecdefn
    WHERE  recname like 'CURR_WRK_TBL%'
    )
    SELECT 'TS'||ts.seq||'_RT'||rt.seq||'_TBL'||wk.seq||'_'||n1.period id
    ,      ts.table_name ts_table_name
    ,      rt.table_name rt_table_name
    ,      wk.table_name wk_table_name
    ,      n1.period, n1.periods
    FROM   n1, ts, rt, wk
  ) LOOP
    sql_txt := 'INSERT INTO '||i.wk_table_name||' (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFILIATE_INTRA1,AFFILIATE_INTRA2,ALTACCT,
BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,CLASS_FLD,CURRENCY_CD,DATE_CODE,DEPTID,FISCAL_YEAR,
FUND_CODE,GL_ADJUST_TYPE,LEDGER,OPERATING_UNIT,POSTED_BASE_AMT,POSTED_TOTAL_AMT,POSTED_TOTAL_CR,POSTED_TOTAL_DR,
POSTED_TRAN_AMT,POSTED_TRAN_CR,POSTED_TRAN_DR,PROCESS_INSTANCE,PRODUCT,PROGRAM_CODE,PROJECT_ID,STATISTICS_CODE,
RATE_DIV,RATE_MULT,GAINLOSS_ACCT,RESTATED_AMT,REVAL_ADJUSTMENT,TARGET_CURRENCY)
SELECT A.ACCOUNT,011,AFFILIATE,
'' '','' '','' '',A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,'' '',A.CURRENCY_CD,DATE_CODE,DEPTID,2016,
FUND_CODE,GL_ADJUST_TYPE,''X_UKCORE'',OPERATING_UNIT,SUM(A.POSTED_BASE_AMT),SUM(POSTED_TOTAL_AMT),0,0,0,0,0,
0001234567,PRODUCT,PROGRAM_CODE,PROJECT_ID,'' '',R.RATE_DIV,R.RATE_MULT,'' '',
ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02
),ROUND(SUM(((((A.POSTED_TOTAL_AMT) / (RATE_DIV))) * (RATE_MULT))),02 )
- SUM(A.POSTED_BASE_AMT),''GBP'' FROM PS_LEDGER A , '||i.ts_table_name||' B
, '||i.rt_table_name||' R WHERE A.LEDGER=''X_UKCORE'' AND A.FISCAL_YEAR =
2016 AND A.ACCOUNTING_PERIOD IN ( 0, '||i.periods||')
AND B .PROCESS_INSTANCE=0001234567 AND B .CHARTFIELD=''ACCOUNT'' AND
A.ACCOUNT>=B .RANGE_FROM_10 AND A.ACCOUNT<=B .RANGE_TO_10 AND
A.BUSINESS_UNIT=''12345'' AND A.CURRENCY_CD <> ''GBP''
AND FROM_CUR = A.CURRENCY_CD
AND TO_CUR = ''GBP''
AND R.PROCESS_INSTANCE = 0001234567
GROUP BY A.ACCOUNT,AFFILIATE,A.BOOK_CODE,BUDGET_REF,CHARTFIELD1,CHARTFIELD2,CHARTFIELD3,A.CURRENCY_CD,DATE_CODE,
DEPTID,FUND_CODE,GL_ADJUST_TYPE,OPERATING_UNIT,PRODUCT,PROGRAM_CODE,PROJECT_ID,RATE_DIV,RATE_MULT';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[ALL_ROWS]',
q'[LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1" "R"@"SEL$1")]',
q'[USE_NL(@"SEL$1" "A"@"SEL$1")]',
q'[SWAP_JOIN_INPUTS(@"SEL$1" "R"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
    :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
    dbms_output.put_line(i.id||':'||:signature);

    BEGIN
      DBMS_SQLTUNE.drop_SQL_PROFILE (name => 'FSPCCURR_'||i.id);
    EXCEPTION 
      WHEN e_no_sql_profile THEN NULL;
    END;

    IF 1=1 THEN
      DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
        sql_text    => sql_txt,
        profile     => h,
        name        => 'FSPCCURR_'||i.id,
        description => 'coe FSPCCURR '||i.id||' @ '||:signature||'',
        category    => 'DEFAULT',
        validate    => TRUE,
        replace     => TRUE,
        force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). 
                                    FALSE:EXACT (similar to CURSOR_SHARING) */ );
    END IF;

  END LOOP;
END;
/

column name format a30
select name, status, sql_text
from dba_sql_profiles
where name like '%FSPCCURR%'
order by 1
/
SPO OFF;

…
TS1_RT4_TBL4_12:1278395827722096644
TS2_RT4_TBL4_12:4126874017730826984

PL/SQL procedure successfully completed.

NAME                           STATUS   SQL_TEXT                                                                        
------------------------------ -------- --------------------------------------------------------------------------------
…
FSPCCURR_TS1_RT4_TBL4_12       ENABLED  INSERT INTO PS_CURR_WRK_TBL004 (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFILIATE_IN
…
FSPCCURR_TS2_RT4_TBL4_12       ENABLED  INSERT INTO PS_CURR_WRK_TBL004 (ACCOUNT,ACCOUNTING_PERIOD,AFFILIATE,AFFILIATE_IN
…

900 rows selected.

I have written previously about partitioning the PS_LEDGER table in Financials for the benefit of General Ledger (GL) reporting.  I have always recommended top-level range partitioning on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD with separate partitions for each accounting period.  It also leaves sub-partitioning available to use another column, usually LEDGER.

• PeopleSoft DBA blog: nVision Performance Tuning: 4. Partitioning of Ledger, Ledger Budget, and Summary Ledger Tables
• Go-Faster blog: Retrofitting Partitioning into Existing Applications: Example 1. General Ledger

However, recent research into partition change tracking (PCT) and materialized views has made me question that advice.  The decision is not as clear cut.

• Go-Faster blog: Partition Change Tracking During Materialized View Refresh and Query Rewrite

You can create summary ledger tables in PeopleSoft Financials that are incrementally maintained by batch processes, and then nVision reports can reference the summary ledgers instead.  If the summary ledgers are not up to date, then the reports will produce out of date results.

Similarly, materialized views can be used in an Oracle database to create pre-generated reporting tables.  An application can reference a materialized view directly, or the database can dynamically rewrite submitted SQL queries to use the materialized view if they are sufficiently similar to the query that created the materialized view and if the optimizer judges that it is cheaper to query the materialized view.  By default, the database will check that the materialized view is up to date, that is no change has been made to the underlying tables since the last refresh commenced, before it can rewrite the SQL query.  So the query will always return the same data, but if the materialized view is out of date you don't get the performance improvement.

You can optionally choose to configure the database to write SQL queries to use stale materialized views by setting QUERY_REWRITE_INTEGRITY=stale_tolerated at either database or session-level.  

Materialized views can be created for the nVision queries that you wish to optimise, and no further code change is required because the database will rewrite the SQL.  You can see a typical example of this in my blog about PCT.

Partition Change Tracking is a mechanism the Oracle database uses to 'track freshness to a finer grain than the entire materialized view'.  It can identify which partitions and subpartitions are fresh and can be used for query rewrite, and to refresh just the partitions that are stale or that contain stale sub-partitions.  

If you wish to create materialized views on the main ledger table, and rely upon query rewrite, and keep the materialized views up to date with respect to the ledger table, and only use them when they are up to date, then you probably want PCT to help with both rewrite and refresh.

I usually like to range partition PS_LEDGER on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD to produce a separate range partition for each accounting period.   Partition pruning works very effectively with the SQL generated by nVision.  It also allows sub-partitioning on another column, usually LEDGER.  This is desirable when a system has multiple actuals ledgers, and especially since 9.2 where the budget data is also stored in PS_LEDGER rather than PS_LEDGER_BUDG.  

However, amongst the documented restrictions of PCT is that partitioned tables must use either range, or list partitioning, or composite partitioning with range or list as the top-level partitioning strategy.  Also, the top-level partition key must consist of only a single column (see Database Data Warehousing Guide -> Advanced Materialized Views -> About Partition Change Tracking)

If I want to use query rewrite to materialized views for queries on LEDGER table then I have a few choices.

• If I stick with multi-column range partitioning, then I cannot use PCT.  I must either keep the materialized views fresh, or the queries remain on the ledger table.  Any update to any partition in the ledger table will render the entire materialized view stale and prevent query rewrite.  Many customers run a suite of nVision reportbooks overnight.  I could set QUERY_REWRITE_INTEGRITY=stale_tolerated at session-level for the report books processes using a trigger on the process scheduler request table (PSPRCSRQST) - see Setting Oracle Session Parameters for Specific Process Scheduler Processes.  In this case, I would have to take responsibility for refreshing the materialized views prior to running, say, a suite of report processes.  This is effectively the same situation as using summary ledgers, but without code change to the reports.
• I have created materialized views on summary ledger tables in order to provide compressed copies of the summary ledger.  Again, in this case, the materialized views had to be refreshed after the summary ledger maintenance process.
• Or, I have to produce a simpler partitioning strategy for the ledger table that is still compatible with PCT.

I could composite partition both the LEDGER table and the materialized views by FISCAL_YEAR and ACCOUNTING_PERIOD, but then I cannot further subpartition by other columns.  This would degrade queries on smaller ledgers that could not be rewritten to dedicated materialized views.

CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (FISCAL_YEAR) 
SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES LESS THAN (1) 
,SUBPARTITION ap_01 VALUES LESS THAN (2) 
,SUBPARTITION ap_02 VALUES LESS THAN (3) 
,SUBPARTITION ap_03 VALUES LESS THAN (4) 
,SUBPARTITION ap_04 VALUES LESS THAN (5) 
,SUBPARTITION ap_05 VALUES LESS THAN (6) 
,SUBPARTITION ap_06 VALUES LESS THAN (7) 
,SUBPARTITION ap_07 VALUES LESS THAN (8) 
,SUBPARTITION ap_08 VALUES LESS THAN (9) 
,SUBPARTITION ap_09 VALUES LESS THAN (10) 
,SUBPARTITION ap_10 VALUES LESS THAN (11) 
,SUBPARTITION ap_11 VALUES LESS THAN (12) 
,SUBPARTITION ap_12 VALUES LESS THAN (13) 
,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
 (PARTITION ledger_2018 VALUES LESS THAN (2019) PCTFREE 0 COMPRESS
,PARTITION ledger_2019 VALUES LESS THAN (2020) PCTFREE 0 COMPRESS
,PARTITION ledger_2020 VALUES LESS THAN (2021) 
,PARTITION ledger_2021 VALUES LESS THAN (2022) 
)
ENABLE ROW MOVEMENT NOLOGGING
/

CREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (FISCAL_YEAR)
SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES LESS THAN (1) 
,SUBPARTITION ap_01 VALUES LESS THAN (2) 
,SUBPARTITION ap_02 VALUES LESS THAN (3) 
,SUBPARTITION ap_03 VALUES LESS THAN (4) 
,SUBPARTITION ap_04 VALUES LESS THAN (5) 
,SUBPARTITION ap_05 VALUES LESS THAN (6) 
,SUBPARTITION ap_06 VALUES LESS THAN (7) 
,SUBPARTITION ap_07 VALUES LESS THAN (8) 
,SUBPARTITION ap_08 VALUES LESS THAN (9) 
,SUBPARTITION ap_09 VALUES LESS THAN (10) 
,SUBPARTITION ap_10 VALUES LESS THAN (11) 
,SUBPARTITION ap_11 VALUES LESS THAN (12) 
,SUBPARTITION ap_12 VALUES LESS THAN (13) 
,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
(PARTITION ledger_2019 VALUES LESS THAN (2020)
,PARTITION ledger_2020 VALUES LESS THAN (2021)
) PCTFREE 0 COMPRESS PARALLEL
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year >= 2019
AND   ledger = 'ACTUALS'
AND   currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/

Note that I have equality criteria on LEDGER and CURRENCY_CD in the materialized view, but I have not included those columns in the select clause, and so they are not in the view.  Oracle can still rewrite queries to use this materialized view and that specify the same criteria on PS_LEDGER

PCT will determine whether any unpruned partitions or subpartitions are stale and if so prevent query rewrite.  It is documented behaviour that the materialized view refresh will truncate and rebuild the whole top-level partitions, in this case each fiscal year, where the partition or any constituent sub-partitions are stale.  So even if just one subpartition, for one accounting period is stale, the whole fiscal year is refreshed.

I investigated making ACCOUNTING_PERIOD the top-level partitioning key, and sub-partitioning on FISCAL_YEAR.  

• Partitioning pruning works correctly, so the query performance will be unaffected, 
• PCT refresh processed all years for the single accounting period, rather than all accounting periods for the single year.  That is less work if you have fewer fiscal years than accounting periods.  Generally, this is the case.  I usually see systems that contain 3 to 6 fiscal years of data.

However, it has a number of problems.

• I can't specify storage options or compression attributes on sub-partitions in the create table DDL command, so I have to come along afterwards with PL/SQL to alter the sub-partitions.
• It is not possible to interval range sub-partition an object, so I can't automatically add partitions for future fiscal years on demand.  Instead, I am going to have to add new fiscal year subpartitions to each of the 14 range partitions.

On balance, I don't think I would choose to implement this.

There is no single clear recommendation for partitioning and PCT.  It will depend on the circumstances.

• If I don't need to introduce materialized views on PS_LEDGER then I would usually stick with the multi-column composite partitioning with the top-level range partition on FISCAL_YEAR and ACCOUNTING_PERIOD.
• PeopleSoft provides summary ledgers to improve the performance of the ledger queries and compressed materialized views can be built on these.
• If you only have a single actuals ledger then composite range-range partition on FISCAL_YEAR and ACCOUNTING_PERIOD is attractive.  
• I do not recommend interval partitioning on FISCAL_YEAR because this affects the scope of the refresh process.  It processes a second top-level range partition.
• If the budget data is stored in the PS_LEDGER table rather than LEDGER_BUDG then consider building separate materialized views for each value of LEDGER.  
• If you can manage to build materialized views to support rewrite of most the queries on the smaller ledgers, then the lack of sub-partitioning by LEDGER is not going to be a problem unless the query doesn't get rewritten because the materialized views are stale.  Keeping the materialized views up to date is going to be a bigger challenge.

SELECT SUM(A.POSTED_BASE_AMT) 
FROM PS_LEDGER A, PSTREESELECT10 L2, PSTREESELECT10 L1 
WHERE A.LEDGER='ACTUAL' AND A.FISCAL_YEAR=2018 AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 8
AND L2.SELECTOR_NUM=159077 AND A.ACCOUNT=L2.RANGE_FROM_10 
AND (A.BUSINESS_UNIT='10000') 
AND L1.SELECTOR_NUM=159075 AND A.DEPTID=L1.RANGE_FROM_10 
AND A.CURRENCY_CD='GBP' AND A.STATISTICS_CODE=' '

SELECT L2.TREE_NODE_NUM,L3.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT) 
FROM PS_LEDGER A, PSTREESELECT05 L2, PSTREESELECT10 L3 
WHERE A.LEDGER='S_UKMGT' 
AND A.FISCAL_YEAR=2018 
AND A.ACCOUNTING_PERIOD BETWEEN 0 AND 12 
AND (A.DEPTID BETWEEN 'A0000' AND 'A8999' OR A.DEPTID BETWEEN 'B0000' AND 'B9149' 
OR A.DEPTID='B9156' OR A.DEPTID='B9158' OR A.DEPTID BETWEEN 'B9165' AND 'B9999' 
OR A.DEPTID BETWEEN 'C0000' AND 'C9999' OR A.DEPTID BETWEEN 'D0000' AND 'D9999' 
OR A.DEPTID BETWEEN 'G0000' AND 'G9999' OR A.DEPTID BETWEEN 'H0000' AND 'H9999' 
OR A.DEPTID='B9150' OR A.DEPTID=' ') 
AND L2.SELECTOR_NUM=10228 
AND A.BUSINESS_UNIT=L2.RANGE_FROM_05 
AND L3.SELECTOR_NUM=10231 
AND A.ACCOUNT=L3.RANGE_FROM_10 
AND A.CHARTFIELD1='0012345' 
AND A.CURRENCY_CD='GBP' 
GROUP BY L2.TREE_NODE_NUM,L3.TREE_NODE_NUM

• E-NV: nVision Performance issues on both Client and WEB showing up starting 8.55.19 producing Extra SQL statements in trace (Doc ID 2417622.1)
• It is also referenced in E-NV: nVision Performance which performs better Excel Automation or OPENXML via Process Scheduler (Doc ID 2481835.1)

• E-AD: What is OBJECTTYPENAME in PSVERSION and PSLOCK for? (Doc ID 664848.1)  contains a document that lists the object type names and the PeopleTools table they relate to.  This document was originally published in 2013 and relates to PeopleTools 8.49.
• E-AD: What Tables Are Associated With The New Objecttypenames In PSVERSION In PT 8.50? (Doc ID 1278867.1) lists the new object types in PeopleTools 8.50.

RECNAME                   MAX        CNT
------------------ ---------- ----------
PSAEAPPLDEFN               80       3504
PSMSGNODEDEFN              80         78
PSOPRVERDFN                80       1468

OBJECTTY    VERSION
-------- ----------
OPRVM            80

OBJECTTY    VERSION
-------- ----------
CRM               3
SCATM             3

RECNAME                   MAX        CNT
------------------ ---------- ----------
PSCONTDEFN                  3       7567

…
-rw------- 1 psadm2 oracle  5228492 Jun 12 06:37 RDM.DAT
-rw------- 1 psadm2 oracle    69120 Jun 12 06:37 RDM.KEY
-rw------- 1 psadm2 oracle        0 Oct 26  2015 ROLM.DAT
-rw------- 1 psadm2 oracle    24192 Oct 26  2015 ROLM.KEY
-rw------- 1 psadm2 oracle        0 Oct 26  2015 RSM.DAT
-rw------- 1 psadm2 oracle    24192 Oct 26  2015 RSM.KEY
…

SQL> desc psversion
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECTTYPENAME                            NOT NULL VARCHAR2(8 CHAR)
 VERSION                                   NOT NULL NUMBER(38)

UPDATE psversion SET version = version + 1
WHERE objecttypename IN('SYS','RDM');

UPDATE pslock SET version = version + 1
WHERE objecttypename IN('SYS','RDM');

UPDATE psrecdefn
SET version = (
SELECT version FROM psversion WHERE objecttypename = 'RDM')
WHERE recname = '';

UPDATE psindexdefn a
SET a.uniqueflag = 1
WHERE a.uniqueflag = 0
AND a.indextype = 3
AND EXISTS(
  SELECT 'x'
  FROM psindexdefn k
  WHERE k.recname = a.recname
  AND k.indexid = '_'
  AND k.indextype = 1
  AND k.uniqueflag = 1)
AND a.recname = '';

• I have used a later version of PeopleTools to generate the reference on my website.  The list of PeopleTools tables is no longer defined in PeopleTools by object security, so I have used an independent and somewhat broader definition: Table or View records that are either owned by PPT or whose SQLTABLENAME is the same as the record name.
• There is much less reliance on static pages.  There are now only 3 such pages everything else is generated.  Instead, additional data is loaded from static scripts into the PLAN_TABLE that should always be present in an Oracle database and so it doesn't have to be created.  It should be a global temporary table so there is no problem with debris being left behind or interference with other processes.  That data is then combined with data in the PeopleTools tables during the generation process.
• The utility has been rewritten as a PL/SQL package that should be created in the PeopleSoft Owner ID schema (usually SYSADM).
• The generated HTML is simply tidier, and more consistent.

• Managing Cost-Based Optimizer Statistics for PeopleSoft recommends
• If you are going to continue to use DBMS_STATS in the DDL model then
• Do not specify ESTIMATE_PERCENT because it disables the hash-based number-of-distinct-values calculation, forcing it to go back to the COUNT(DISTINCT ...) method that requires a sort, and may not produce accurate values because it only samples data.
• Do not specify METHOD_OPT='FOR ALL INDEXED COLUMNS SIZE 1' because this will not collect histograms on indexed columns, and will not update column statistics on unindexed columns.
• Do specify FORCE=>TRUE so that you can lock and delete statistics on temporary records.
• However, the alternative is to use GFCSTATS11 package to collects these statistics.  This package is controlled by a metadata table so you can defined statistics collection behaviour for specific records.
• How Not to Collect Optimizer Statistics in an Application Engine Program
• This blog explains why you should not explicitly code DBMS_STATS calls into Application Engine programs.

• Oracle RDBMS Instrumentation
• On-Line Component/Page Information
• Application Engine Step Information
• Cobol/nVision Instrumentation
• nVision Layout Instrumentation
• 2nd Database Connection Instrumentation
• PeopleTools Performance Metrics
• Cobol and Application Engine Batch Timings
• PeopleSoft Performance Monitor

• Set EnableAEMonitoring=1 in all Application Server and process scheduler domains in order to enable PeopleSoft instrumentation on-line, in the integration broker, and in Application Engine programs.
• Implement the PSFTAPI package and trigger described above to set module and action at the start of all processes.
• Implement Fine-Grained Audit policy and handler to instrument nVision processes if required.
• Reduce AWR snapshot frequency from the default of 1 hour to every 15 minutes.
• Enable Application Engine batch timings to at least database and preferably also file by setting AETrace=1152 in all Application Server and Process Scheduler domain configuration files.
• Enable Cobol statement timings report should be written to log file by setting TraceSQL=128 in all Process Scheduler domain configuration files.
• Performance Monitor is complex to set up and the delivered analytics are limited.  Nonetheless, the information that can be obtained from the data collected can be worth the effort of configuration to address on-line configuration and performances issues.

• PeopleTools 8.50 uses DBMS_APPLICATION_INFO to Identify Database Sessions
• PeopleTools 8.52 Application Engine sets MODULE and ACTION 
• Undocumented (until PeopleTools 8.55) Application Engine Parameter: EnableAEMonitoring  

• See PeopleTools 8.52 Application Engine sets MODULE and ACTION).

• See PSFTAPI package and trigger

• See Enabling Oracle Database Trace on PeopleSoft processes with a Trigger (improved)

• See nVision Performance Tuning: 5. Additional Instrumentation of nVision

• See also Sequence Number Allocation in PeopleSoft

• Dynamically generated SQL, often in PeopleCode, concatenates strings of SQL with bind variables, thus the bind variables become literals in the final SQL statement.  Statements with different literal values are considered to be different statements with different SQL_IDs.
• Similarly, dynamic Cobol statements result in literal values in the SQL statement.
• %BIND() variables in Application Engine will also become literal values in the SQL Statement unless the ReUseStatement attribute is set on the AE step, however, this cannot be set on statements with dynamic fragments code are introduced with %BIND(…,NOQUOTES).
• Application Engine programs that use temporary records can use different non-shared instances of the record in different executions, and this also results in different statements with different SQL_IDs.
• See also Performance Benefits of ReUse Statement Flag in Application Engine

• AETrace=128: batch timings report is written to the AE Trace file to
• AETrace=1024: batch timings are written to PS_BAT_TIMINGS% tables in the database

• TraceSQL = 128: Enable Cobol statement timings report 

ORA-06533: Subscript beyond count 
ORA-06512: at "SYS.DBMS_STATS", line 36873 
…

1. Using a fixed sample size rather than AUTO_SAMPLE_SIZE should only be considered as a temporary workaround. The new hash-based number-of-distinct-values (NDV) algorithm in Oracle 12c only works with AUTO_SAMPLE_SIZE, and it produces more accurate statistics and runs faster because saves a large sort operation. Coding a fixed sample size into an Application Engine requires a managed change to be made, tested and released into production, and then when the underlying problem is resolved the customisation needs to be removed by the same managed process. 
2. DBMS_STATS.GATHER_TABLE_STATS implies a commit. That can lead to problems that PeopleSoft avoids by only calling statistics via the %UpdateStats macro and controlling when that macro does and does not execute. 

-- 19:07:37.199 .(AEMINITEST.MAIN.LOOP) (Do Select)
%Select(recname) SELECT recname FROM psrecdefn WHERE recname = 'JOB'
/
-- Buffers:
--   1) JOB
-- 19:07:37.204 Iteration 1 of .(AEMINITEST.MAIN.LOOP) (Do Select) loop

-- 19:07:37.208 .(AEMINITEST.MAIN.LOOP) (SQL)
RECSTATS PS_JOB LOW
/
-- 19:07:37.211 UpdateStats ignored - COMMIT required

-- 19:07:37.212 .(AEMINITEST.MAIN.LOOP) (Do Fetch)

-- 19:07:35.205 .(AEMINITEST.MAIN.Step01) (SQL)
UPDATE PS_AETEMPTBLMGR SET AE_DEDICATED = 1 WHERE 0 = 1
/
-- Row(s) affected: 0

-- 19:07:35.213 .(AEMINITEST.MAIN.Step02) (PeopleCode)

-- 19:07:35.220 .(AEMINITEST.MAIN.US1) (SQL)
RECSTATS PS_JOB LOW
/
-- 19:07:35.227 UpdateStats ignored - COMMIT required

-- 19:12:06.690 .(AEMINITEST.MAIN.Step01) (SQL)
UPDATE PS_AETEMPTBLMGR SET AE_DEDICATED = 1 WHERE 0 = 1
/
-- Row(s) affected: 0
-- 19:12:06.696 .(AEMINITEST.MAIN.Step02) (PeopleCode)
-- 19:12:06.700 .(AEMINITEST.MAIN.GTS) (SQL)
%Execute(/) BEGIN 
dbms_stats.gather_table_stats('SYSADM','PS_JOB',estimate_percent=>1); END; /
/
-- Row(s) affected: 1

-- 19:12:09.871 .(AEMINITEST.MAIN.US1) (SQL)
RECSTATS PS_JOB LOW
/
-- 19:12:09.877 UpdateStats ignored - COMMIT required
/

Restart Data CheckPointed
/
COMMIT
/

-- 19:20:24.792 .(AEMINITEST.MAIN.GTS) (SQL)
%Execute(/) BEGIN 
dbms_stats.gather_table_stats('SYSADM','PS_JOB',estimate_percent=>1); END; /
/
-- Row(s) affected: 1
/ 
/
Restart Data CheckPointed
/
COMMIT
/

-- 19:20:29.153 .(AEMINITEST.MAIN.US1) (SQL)
RECSTATS PS_JOB LOW
/
-- Row(s) affected: 1
/

gfcpsstats11.ps_stats(p_ownname=>[DBNAME], p_tabname=>[TBNAME]);

DBMS_STATS.GATHER_TABLE_STATS(ownname=>[DBNAME], tabname=>[TBNAME], force=>TRUE);

• At the end of the Application Engine program, it disconnects from the database and terminates.  Thus releasing resources from the process and the database session.  Whereas the persistent Application Engine process has been reported to accumulate allocated memory over time.
• If you are using Oracle database Global Temporary Tables in an application engine, then you should not use PSAESRV because the tables are always created PRESERVE ON COMMIT and so are only released when the database session terminates.
• If you set any session parameters within an Application Engine program run via PSAESRV, or enable database trace, then these settings will carry forward from one Application Program to the next unless you reset the parameter at the end of the program, or the start of the next.  This is not a concern with standalone PSAE processes.

• If you are using Oracle Active Data Guard and wish to mark some Application Engine programs as read-only then they must be run via the PSAESRV process

• PeopleTools Performance Guidelines Red Paper (Doc ID 747389.1 sums it up very nicely: “PSAE is as good as PSAESRV for most practical purposes.  If you have an application engine job that runs longer than 10 seconds, PSAE is equivalent to PSAESRV.  PSAE has the added advantage of being recycled at the end of each application engine job, cleaning up any outstanding SQL cursors to the database that may have been left behind.  Because PSAE recycles after each use, PSAE does not have any possible memory leakage problem that may occupy the precious system memory.  In short, PSAE is a cleaner workhorse.”
• I think it is reasonable to use PSAESRV in CRM.  For all other products, I recommend that PSAESRV should be disabled from all Process Schedulers.
• If you do have some Application Processes that are both short-lived (i.e. less than 10 seconds) and run frequently, then consider creating other process schedulers with PSAESRV processes that are dedicated to running only these process.  You can then move these processes to a new Process Scheduler category that only runs on these new Process Scheduler.
• PSAESRV is configured by default, so if you don’t want to use it, and mostly you won’t, then you have to remember to disable it.

Set serveroutput on 
EXECUTE xx_onlineinsthwmreset.main;

EXECUTE xx_onlineinsthwmreset.main(p_testmode=>TRUE);

EXECUTE xx_onlineinsthwmreset.main(p_drop_storage=>TRUE, p_min_extents=>0);

EXECUTE xx_onlineinsthwmreset.main(p_recname_like=>'JP%');

CREATE TABLE PS_PRCS_SESS_PARM (PRCSTYPE VARCHAR2(30) NOT NULL,
   PRCSNAME VARCHAR2(12) NOT NULL,
   OPRID VARCHAR2(30) NOT NULL,
   RUNCNTLID VARCHAR2(30) NOT NULL,
   PARAM_NAME VARCHAR2(50) NOT NULL,
   PARMVALUE VARCHAR2(128) NOT NULL) TABLESPACE PTTBL STORAGE (INITIAL
 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10
 PCTUSED 80
/
CREATE UNIQUE  iNDEX PS_PRCS_SESS_PARM ON PS_PRCS_SESS_PARM (PRCSTYPE,
   PRCSNAME,
   OPRID,
   RUNCNTLID,
   PARAM_NAME) TABLESPACE PSINDEX STORAGE (INITIAL 40000 NEXT 100000
 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PARALLEL NOLOGGING
/
ALTER INDEX PS_PRCS_SESS_PARM NOPARALLEL LOGGING
/

INSERT INTO sysadm.PS_PRCS_SESS_PARM (prcstype, prcsname, oprid, runcntlid, param_name, parmvalue)
VALUES ('nVision-ReportBook','RPTBOOK',' ',' ', 'parallel_degree_policy','auto');
INSERT INTO sysadm.PS_PRCS_SESS_PARM (prcstype, prcsname, oprid, runcntlid, param_name, parmvalue)
VALUES ('nVision-ReportBook','RPTBOOK',' ',' ', 'parallel_degree_limit','4');
INSERT INTO sysadm.PS_PRCS_SESS_PARM (prcstype, prcsname, oprid, runcntlid, param_name, parmvalue)
VALUES ('nVision-ReportBook','RPTBOOK',' ',' ', 'parallel_degree_level','150');
INSERT INTO sysadm.PS_PRCS_SESS_PARM (prcstype, prcsname, oprid, runcntlid, param_name, parmvalue)
VALUES ('nVision-ReportBook','RPTBOOK',' ',' ', 'parallel_min_time_threshold','1');
INSERT INTO sysadm.PS_PRCS_SESS_PARM (prcstype, prcsname, oprid, runcntlid, param_name, parmvalue)
VALUES ('nVision-ReportBook','RPTBOOK',' ',' ', '_optimizer_skip_scan_enabled','FALSE');

PRCSTYPE             PRCSNAME     OPRID      RUNCNTLID       PARAM_NAME                     PARMVALUE
-------------------- ------------ ---------- --------------- ------------------------------ --------------------
nVision-ReportBook   RPTBOOK                                 parallel_degree_policy         auto
nVision-ReportBook   RPTBOOK                                 parallel_degree_limit          4
nVision-ReportBook   RPTBOOK                                 parallel_degree_level          150
nVision-ReportBook   RPTBOOK                                 parallel_min_time_threshold    1
nVision-ReportBook   RPTBOOK                                 _optimizer_skip_scan_enabled   FALSE

• parallel_degree_policy=auto enables the new 12c automatic degree of parallel calculation, statement queuing. 
• parallel_min_time_threshold is set to 1 second. The default is 10. Statements whose runtime is estimated to be greater than or equal to this value will be considered for automatic degree of parallelism. 
• parallel_degree_limit=4 restricts the automatic degree of parallelism to 4 to prevent any one statement using excessive parallelism.
• parallel_degree_level=150 scales up the automatic degree of parallelism calculation but within the parallel_degree_limit. See Kerry Osborne's blog 12c – parallel_degree_level (control for auto DOP). 
• _optimiser_skip_scan_enabled=FALSE disables index skip scan to promote the use of smart full scan and Bloom filtering. It is recommended for engineered systems in Advice for the PeopleSoft DBA. Skip scan can prevent the optimizer choosing a smart full scan, so it makes sense to limits the setting to just nVision. I can also specify a parameter that will only be set when the reportbook is run by a particular operator with a particular run control.

INSERT INTO sysadm.PS_PRCS_SESS_PARM (prcstype, prcsname, oprid, runcntlid, param_name, parmvalue) VALUES ('nVision-ReportBook','RPTBOOK','NVOPR','NVSRBK_2', 'parallel_degree_level','200');

PRCSTYPE             PRCSNAME     OPRID      RUNCNTLID       PARAM_NAME                     PARMVALUE
-------------------- ------------ ---------- --------------- ------------------------------ --------------------
…
nVision-ReportBook   RPTBOOK                                 parallel_degree_level          150
nVision-ReportBook   RPTBOOK      NVOPR      NVSRBK_2        parallel_degree_level          200

CREATE OR REPLACE TRIGGER sysadm.set_prcs_sess_parm
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
WHEN (new.runstatus = 7 
  AND old.runstatus != 7 
  AND new.prcstype != 'PSJob')
DECLARE
  l_cmd VARCHAR2(100 CHAR);
  l_delim VARCHAR2(1 CHAR);
BEGIN
  FOR i IN (
    WITH x as (
      SELECT p.*
      ,      row_number() over (partition by param_name 
                               order by NULLIF(prcstype, ' ') nulls last,
                                        NULLIF(prcsname, ' ') nulls last,
                                        NULLIF(oprid, ' ') nulls last,
                                        NULLIF(runcntlid,' ') nulls last
                              ) priority
      FROM   PS_PRCS_SESS_PARM p
      WHERE  (p.prcstype  = :new.prcstype  OR p.prcstype  = ' ')
      AND    (p.prcsname  = :new.prcsname  OR p.prcsname  = ' ')
      AND    (p.oprid     = :new.oprid     OR p.oprid     = ' ')
      AND    (p.runcntlid = :new.runcntlid OR p.runcntlid = ' ')
    ) 
    SELECT * FROM x
    WHERE priority = 1 
  ) LOOP
    IF SUBSTR(i.param_name,1,1) = '_' THEN 
      l_delim := '"';
    ELSE
      l_delim := '';
    END IF;

    IF NULLIF(i.parmvalue,' ') IS NOT NULL THEN
      dbms_output.put_line('Rule:'||NVL(NULLIF(i.prcstype,' '),'*')
                             ||'.'||NVL(NULLIF(i.prcsname,' '),'*')
                             ||':'||NVL(NULLIF(i.oprid,' '),'*')
                             ||'.'||NVL(NULLIF(i.runcntlid,' '),'*')
                             ||':'||i.param_name||'='||i.parmvalue);

      l_cmd := 'ALTER SESSION SET '||l_delim||i.param_name||l_delim||'='||i.parmvalue;
      dbms_output.put_line('PI='||:new.prcsinstance||':'||:new.prcstype||'.'||:new.prcsname||':'
                                ||:new.oprid||'.'||:new.runcntlid||':'||l_cmd);
      EXECUTE IMMEDIATE l_cmd;
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(sqlerrm);
END;
/

set serveroutput on 
update psprcsrqst set runstatus = 7 where runstatus != 7
and prcsname = 'RPTBOOK' and runcntlid = 'NVSRBK_2' and oprid = 'NVOPR' and rownum = 1;
rollback;

Rule:nVision-ReportBook.RPTBOOK:*.*:_optimizer_skip_scan_enabled=FALSE
PI=3543986:nVision-ReportBook.RPTBOOK:NVOPR.NVSRBK_2:ALTER SESSION SET "_optimizer_skip_scan_enabled"=FALSE
Rule:nVision-ReportBook.RPTBOOK:NVOPR.NVSRBK_2:parallel_degree_level=200
PI=3543986:nVision-ReportBook.RPTBOOK:NVOPR.NVSRBK_2:ALTER SESSION SET parallel_degree_level=200
Rule:nVision-ReportBook.RPTBOOK:*.*:parallel_degree_limit=4
PI=3543986:nVision-ReportBook.RPTBOOK:NVOPR.NVSRBK_2:ALTER SESSION SET parallel_degree_limit=4
Rule:nVision-ReportBook.RPTBOOK:*.*:parallel_degree_policy=auto
PI=3543986:nVision-ReportBook.RPTBOOK:NVOPR.NVSRBK_2:ALTER SESSION SET parallel_degree_policy=auto
Rule:nVision-ReportBook.RPTBOOK:*.*:parallel_min_time_threshold=1
PI=3543986:nVision-ReportBook.RPTBOOK:NVOPR.NVSRBK_2:ALTER SESSION SET parallel_min_time_threshold=1

update psprcsrqst set runstatus = 7 where runstatus != 7
and prcsname = 'RPTBOOK' and runcntlid = 'NVSRBK_1' and oprid = 'NVOPR' and rownum = 1;
rollback;

Rule:nVision-ReportBook.RPTBOOK:*.*:_optimizer_skip_scan_enabled=FALSE
PI=3543964:nVision-ReportBook.RPTBOOK:NVOPR.NVSRBK_1:ALTER SESSION SET "_optimizer_skip_scan_enabled"=FALSE
Rule:nVision-ReportBook.RPTBOOK:*.*:parallel_degree_level=150
PI=3543964:nVision-ReportBook.RPTBOOK:NVOPR.NVSRBK_1:ALTER SESSION SET parallel_degree_level=150
Rule:nVision-ReportBook.RPTBOOK:*.*:parallel_degree_limit=4
PI=3543964:nVision-ReportBook.RPTBOOK:NVOPR.NVSRBK_1:ALTER SESSION SET parallel_degree_limit=4
Rule:nVision-ReportBook.RPTBOOK:*.*:parallel_degree_policy=auto
PI=3543964:nVision-ReportBook.RPTBOOK:NVOPR.NVSRBK_1:ALTER SESSION SET parallel_degree_policy=auto
Rule:nVision-ReportBook.RPTBOOK:*.*:parallel_min_time_threshold=1
PI=3543964:nVision-ReportBook.RPTBOOK:NVOPR.NVSRBK_1:ALTER SESSION SET parallel_min_time_threshold=1