David Kurtz

Or then again they might not! The non-PeopleSoft Oracle stuff is at blog.go-faster.co.uk.David Kurtzhttp://www.blogger.com/profile/00468908370233805717noreply@blogger.comBlogger163125
Reporting View Hierarchies
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
For example, this is the report for PS_POSN_HISTORY3 from a demo HCM database. It is only three levels deep. "POSN_HISTORY3 is the third of three nested views which retrieve position incumbent history. It selects job records with effective dates before position exits to obtain exit salaries." 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
We can see from the report that view PS_POSN_HISTORY3 calls view PS_POSN_HISTORY2 that in turn calls view PS_POSN_HISTORY. Oracle Active Data Guard in PeopleSoft with Oracle 19c DML Redirection
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.
Database Initialisation ParametersSQL>select * from ps.psdbowner;
DBNAME OWNERID
-------- --------
FINPRD SYSADM
FINADG SYSADM
Application Server Configuration[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
Process Scheduler Configuration- Oracle Support Note: E-AE: Application Engine Process Might Stay in Initiated Status if PSAESRV Disabled on PeopleSoft ADG Enabled Environment (Doc ID 1641764.1)
- This was raised as Bug 18482301: PSAE may stay in initiated status on ADG configured environment. It was closed as 'not a bug'.
- Using Two Temporary Tablespace in PeopleSoft.
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
To continue to use stand-alone PSAE processes, as recommended in the PeopleTools Performance Guidelines Red Paper (Doc ID: 747389.1), it is necessary to configure separate process schedulers that connect only to the Active Data Guard standby database, and processes will need to be redirected appropriately by process categories to these schedulers.[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=FINADG
DBType=ORACLE
UserId=PSAPPS
UserPswd={V2}YoAQq7Ut4WBHJL89N9gv9E0AWwLaecGZ4qep
ConnectId=PEOPLE
ConnectPswd={V2}OlSYHuFMZa2c8uonfYkKk+3+APYvTU9N
StandbyDBName=
StandbyDBType=
StandbyUserId=
StandbyUserPswd=
These schedulers will ONLY run:- Processes in the new ADGOnly process category.
- A single LOADCACHE category process will be permitted.
- The max concurrence of all other categories will be 0.
- Other process types such as SQR might be needed if they are to be run on the ADG standby
[Startup]
;=========================================================================
; Database Signon settings
;=========================================================================
DBName=FINPRD
DBType=ORACLE
UserId=PSAPPS
UserPswd={V2}EcxeV3mit3GMT5kDfz/z+s0L9B1aUb6ix04f
ConnectId=PEOPLE
ConnectPswd={V2}NtXafW7hlcGY016bhazl2kqqvlSNYMK1
StandbyDBName=
StandbyDBType=
StandbyUserId=
StandbyUserPswd=
Process Definition- ADGOnly concurrency will be 0, or it will be the maximum API aware concurrency on ADG process schedulers.
- PSQUERY, any read-only processes, and any processes in ADGOnly category, will all be marked as being both read-only and in the ADGOnly category.
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
/
Sample OutputPSOFT-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
…
PSQUERY Application Engine AmendmentPeopleTools 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
So we need to close the transactions by making the PSQUERY application engine commit. Application Engine steps explicitly commit after each step. I have added an extra step added that does nothing, but Application Engine issues a commitPeopleTools 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
- Navigate to: PeopleTools, Utilities, Administration, Query Administration,
- Go to the last tab (Settings),
- Uncheck Run Query Statistics.
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'
/
Running nVision in a Mixed OpenXML/Excel mode
- Support Note: E-NV: nVision Performance using Microsoft Office 2010 and above (Doc ID 1537491.1)
- Oracle's PeopleSoft Technology Blog: OpenXML Mode in Peoplesoft nVision Looks Hard, but it Isn't!
- PeopleSoft DBA Blog: nVisionPerformance Tuning: 11. Excel -v- OpenXML
- 0 = Open XML Mode (see Oracle Support Note 1317246.1)
- 1 = Excel Mode
- 2 = Cross-Platform OpenXML (see Oracle Support Note 1304907.1
…
[nVision]
;=========================================================================
; General settings for nVision
;=========================================================================
…
UseExcelAutomation=2
…
- Maximum concurrency for the Excel server has been reduced to 1.
- The maximum number of API aware processes for PSNT_E1 has also be reduced to 1.
- The 'Default' category is disabled on both PSNT_E1 and PSNT_X1.
- These servers only run nVision and PSJob process types,
- They load balance within the same operating system (although this is not appropriate for PSNT_X1 if you also run OpenXML on other non-Windows process schedulers).
- nvisionprcsdefnclone.sql creates the cloned process and server definitions, it can also be configured to create multiple pairs of server definitions etc.
- nvision_prcscategory.sql updates the concurrency on existing process definitions.
- PSPRCSQUE is polled by the process schedulers when looking for work.
- PSPRCSRQST reports the status of processes and is exposed in the Process Monitor component in PeopleSoft.
- PRCSNAME determines the name of the process to be run.
- PRCSCATEGORY specifies the category of the process in the request.
- Change the process name from RPTBOOK to RPTBOOKE, or from NVSRUN to NVSRUNE.
- Change the process category from nVisionOpenXML to nVisionExcel. It is changing the process category that makes the difference to which process scheduler picks up the request, but the name is also changed to maintain consistency.
- If the process request specifies a particular process scheduler (specified in SERVERNAMERQST) that cannot run the updated process category, then the requested server name is blanked out.
- The triggers will only fire when requests for nVision-Report/NVSRUN or nVision-ReportBook/RPTBOOK are submitted.
- There is an almost identical trigger on each table (apart from the conditionally compiled debug code). I will only exhibit the trigger on PSPRCSRQST in this post.
- Normally, one would not code WHEN OTHERS THEN NULL exceptions handlers in PL/SQL. However, I do this on triggers on process scheduler tables to prevent any error being raised causing the scheduler to crash.
- Conditionally compiled debug code in the trigger is not compiled if the mydebug flag is set to FALSE.
ALTER SESSION SET PLSQL_CCFLAGS = 'mydebug:FALSE';
CREATE OR REPLACE TRIGGER sysadm.gfc_nvision_excel_redirect_rqst
BEFORE INSERT ON s.psprcsrqst
FOR EACH ROW
WHEN (new.prcstype IN('nVision-Report','nVision-ReportBook')
AND new.prcsname IN('RPTBOOK','NVSRUN')
)
DECLARE
l_excel INTEGER := 0;
BEGIN
$IF $$mydebug $THEN dbms_output.put_line('Entering Trigger psoft.gfc_nvision_excel_redirect_rqst'); $END
IF :new.prcstype = 'nVision-ReportBook' THEN
--check for reportbook running report that uses layout on Excel list
SELECT 1
INTO l_excel
FROM psnvsbookrequst b
, ps_nvs_report n
, ps_nvs_redir_excel e
WHERE b.oprid = :new.oprid
AND b.run_cntl_id = :new.runcntlid
AND b.eff_status = 'A'
AND n.business_unit = b.business_unit
AND n.report_id = b.report_id
AND n.layout_id = e.layout_id
AND e.eff_status = 'A'
AND rownum=1;
ELSE
--look in command line for report running layout on Excel list
SELECT 1
INTO l_excel
FROM psprcsparms p
, ps_nvs_report n
, ps_nvs_redir_excel e
WHERE p.prcsinstance = :new.prcsinstance
AND n.report_id = substr(regexp_substr(p.parmlist,'-NRN[^ ]+'),5)
AND n.layout_id = e.layout_id
AND e.eff_status = 'A'
AND rownum=1;
END IF;
--update name of process if to be run on Excel
$IF $$mydebug $THEN dbms_output.put_line('found Excel nVision layout for oprid='||:new.oprid||', runcntlid='||:new.runcntlid); $END
IF :new.prcsname IN('RPTBOOK') THEN
:new.prcsname := 'RPTBOOKE';
ELSE
:new.prcsname := :new.prcsname||'E';
END IF;
--get category of new process definition
SELECT d.prcscategory
INTO :new.prcscategory
FROM ps_prcsdefn d
WHERE d.prcstype = :new.prcstype
AND d.prcsname = :new.prcsname;
--get max concurrency of new category on new server
SELECT maxconcurrent
INTO l_maxconcurrent
FROM ps_servercategory
WHERE prcscategory = :new.prcscategory
AND servername = :new.servernamerqst;
--if request assigned to server where it cannot run blank out server assignment and allow load balancing to determine it
IF l_maxconcurrent = 0 THEN
:new.servernamerqst := ' ';
END IF;
$IF $$mydebug $THEN dbms_output.put_line('set process name to '||:new.prcsname); $END
EXCEPTION
WHEN no_data_found THEN
$IF $$mydebug $THEN dbms_output.put_line('No excel redirect found'); $ELSE NULL; $END
WHEN others THEN
$IF $$mydebug $THEN dbms_output.put_line('Other Error'); $ELSE NULL; $END
END;
/
show errors
REM gfc_nvsion_excel_redirect_metadata.sql
REM (c)Go-Faster Consultancy 2021
REM load metadata of layouts that have to run on Excel rather than OpenXML
spool gfc_nvsion_excel_redirect_metadata
INSERT INTO ps_nvs_redir_excel VALUES ('EXCELNVS','A');
commit;
spool off
- excel_only_reportbooks.sql determines which nVision ReportBooks contain only some layouts that require to be run on nVision. These are candidates to be split up.
ReportBooks with both Excel and OpenXML nVision layouts
Number of
All Excel
OPRID RUN_CNTL_ID Layouts Layouts Excel Layouts OpenXML Layouts
---------- ------------------------------ ------- ---------- ------------------------------ ----------------------------------------------------------------------
BATCH ADHOC_NVISION 8 1 GLXXXO21 GLXYZD03, GLXXXO03, GLXXXO05, GLXXXO22, GLXXXO23, GLXXXO31, GLXXXO32
BATCH ADHOC_09062016 3 1 ZYXVIS14 ZYXVBS14, ZYXVIS12
BATCH GLXXX_GLXXXO02_ABDC_YYY 2 1 GLXXXO02 GLXXXO28
BATCH GLXXX_GLXXXO21_ABDC_YYY 3 2 GLXXXO21, GLXXXO98 GLXXXO71
BATCH GLXXX_ZYXB4080_M000_ZZZ 10 2 ZYXVBS64, ZYXVIS14 ZYXVBS04, ZYXVBS14, ZYXVBS14_LED, ZYXVBS16, ZYXVBS16_LED, ZYXVBS54, ZB
UVIS04, ZYXVIS16
Process Server Server Server
PRCSINSTANCE RUNDTTM PRCSNAME Category Request Run Assign RUNCNTLID STA EXCEL_LAYOUT_IDS
------------ ---------------------------- ------------ --------------- -------- -------- -------- ------------------------------ --- ------------------------------
12345680 31-MAR-21 07.42.51.000000 AM RPTBOOKE nVisionExcel PSNT_E1 PSNT_E1 GLXXX_GLXXXO10_ABDC_YYY OK GLXXXO10
12345681 31-MAR-21 07.43.25.000000 AM RPTBOOKE nVisionExcel PSNT_E1 PSNT_E1 GLXXX_GLXXXO21_ABDC_YYY OK GLXXXO21, GLXXXO98
12345683 31-MAR-21 08.06.42.000000 AM RPTBOOKE nVisionExcel PSNT_E1 PSNT_E1 GLXXX_GLXXXO98_ADHOC OK GLXXXO98
12345684 31-MAR-21 08.32.12.000000 AM RPTBOOKE nVisionExcel PSNT_E1 PSNT_E1 GLXXX_GLXXXO21_ABDC_YYY OK GLXXXO21, GLXXXO98
12345685 31-MAR-21 09.18.23.000000 AM FBRPTBK nVisionOpenXML PSNT_X1 PSNT_X1 GLXXX_GLXYAD06_ABDC_YYY OK
12345686 31-MAR-21 09.20.01.000000 AM RPTBOOK nVisionOpenXML PSNT_X1 PSNT_X1 GLXXX_GLXYBP00_ABDC_YYY OK
12345687 31-MAR-21 09.22.21.000000 AM RPTBOOK nVisionOpenXML PSNT_X1 PSNT_X1 GLXXX_GLXYAD03_ABDC_YYY OK
12345688 31-MAR-21 09.23.11.000000 AM RPTBOOK nVisionOpenXML PSNT_X1 PSNT_X1 GLXXX_GLVLAD03_ABDC_XXX OK
12345689 31-MAR-21 09.24.18.000000 AM RPTBOOK nVisionOpenXML PSNT_X1 PSNT_X1 GLXXX_ZYXAB001_M000_ZZZ OK
Tuning Dynamically Generated SQL from PeopleSoft COBOL and Application Engine
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.
ExampleINSERT 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
- Different SQL profiles will be needed for each accounting period because there will be a different list of accounting periods in the IN() condition. Subquery factors n and n1 produce a list of accounting periods.
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
- However, if I was working on a temporary record used in an Application Engine program, I would need to look up the number of instances of that record.
- The number of non-shared temporary record tables is the sum of all the instances defined on each application engine program to which the record is allocated, plus the number of global instances, up to a maximum of 99. Instance 0 is the shared instance. The number can be altered in development and the additional tables built by Application Designer. This can require additional SQL Profiles be built.
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
- In this particular example, I know that every permutation of all three tables could occur in all accounting period, so I simply Cartesian join all the subquery factors.
- In other cases, only some permutations may occur. This must be handled in the code that is written. Literal values do not need to be considered because the profile will be created with force matching.
- In Application Engine, although you often see the same instance of different temporary records used in the same process, there is nothing to prevent different instances of different records being used, and so all permutations must be considered.
- I will also concatenate the ID for each table, and also the accounting period to produce an ID string that I can use in the name of the SQL profile.
'TS'||ts.seq||'_RT'||rt.seq||'_TBL'||wk.seq||'_'||n1.period
- Nigel Bayliss: Adding and Disabling Hints Using SQL Patch.
- Kerry Osborne: Oracle Support Sanctions Manually Created SQL Profiles!
- The SQL statement in the FOR clause returns all the permutations of the variations in the SQL statement in an implicit cursor.
- Table names are concatenated into the SQL text from the columns in the implicit cursor.
- Single quotation marks are doubled so that the string contains the single quotation mark.
- It is important not to add or remove any spaces when introducing these changes.
- Profiles are dropped and created inside the loop. Force Matching is enabled.
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.
- Go-Faster Oracle Blog: Hints, Patches, Force Matching and SQL Profiles. This post also demonstrates dynamically generating SQL Profiles, on this occasion for nVision queries.
- Jonathan Lewis: Hacking Profiles
PeopleSoft Financials Ledger Partitioning Recommendations
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.
Summary Ledgers or Materialized ViewsYou 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.
Alternatives for Partitioning PS_LEDGERIf 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.
1. Multi-column composite partitioningI 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.
3. Composite Range-Range Partitioning on ACCOUNTING_PERIOD and FISCAL_YEARI 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.
ConclusionThere 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.
Practical Application Performance Tuning: An nVision Case Study
Learning about and understanding the principles and mechanics of the Oracle database is fundamentally important for both DBAs and developers. It is one of the reasons we still physical conferences.
This presentation tells the story of a performance tuning project for the GL reporting on a Financials system on an engineered system. It required various techniques and features to be brought to bear. Having a theoretical understanding of how the database and various features work allowed us to make reasonable predictions about whether they would be effective in our environment. Some ideas were discounted, some were taken forward.
We will look at instrumentation, ASH, statistics collection, partitioning, hybrid columnar compression, Bloom filtering, SQL profiles. All of them played a part in the solution, some added further complications that had to be worked around, some had to be carefully integrated with the application, and some required some reconfiguration of the application into order to work properly.
Ultimately, performance improvement is an experimental science, and it requires a similar rigorous thought process.
nVision Bug in PeopleTools 8.55/8.56 Impacts Performance
Problem nVision SQLThis is an example of the problematic SQL generated by nVision. The problem is that all of the SQL looks like this. There is never any group by clause, nor any grouping columns in the select clause in from of the SUM().
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=' '
Each query only returns a single row, that only populates a single cell in the report, and therefore a different SQL statement is generated and executed for every cell in the report. Therefore, more statements are parsed and executed, and more scans of the ledger indexes and look-ups of the ledger table and performed. This consumes more CPU, more logical I/O.Normal nVision SQLThis is how I would expect normal nVision SQL to look. This example, although obfuscated, came from a real customer system. Note how the query is grouped by TREE_NODE_NUM from two of the tree selector tables, so this one query now populates a block of cells.
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
The BugThis Oracle note details an nVision bug:"UPTO SET2A-C Fixes - Details-only nPlosion not happening for Single Chart-field nPlosion Criteria.
And also encountered a performance issue when enabled details-only nPlosion for most of the row criteria in the same layout
Issue was introduced on build 8.55.19.
Condition: When most of the row filter criteria enabled Details-only nPlosion. This is solved in 8.55.22 & 8.56.07.
UPTO SET3 Fixes - Performance issue due to the SET2A-C fixes has solved but encountered new one. Performance issue when first chart-field is same for most of the row criteria in the same layout.
Issue was introduced on builds 8.55.22 & 8.56.07.
Condition: When most of the filter criteria’s first chart-field is same. The issue is solved in 8.55.25 & 8.56.10."
- Bug introduced in PeopleTools 8.55.19, fully resolved in 8.55.25.
- Bug introduced in PeopleTools 8.56.07, fully resolved in 8.56.10.
Which Version Number Corresponds Which PeopleTools Object?
There is some documentation on the Oracle Support website, but it is not exhaustive and may not be completely up to date.
- 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.
In the previous blog post, I showed how to increment version numbers before updating PeopleTools objects. I knew RDM (the Record Definition Manager) is the OBJECTTYPENAME for PSRECDEFN because I worked that out by tracing Application Designer while it saved a record change. That remains the only completely reliable way to determine the relationship.
However, version number matching is also a useful technique, though when it does not provide a completely definitive answer, it dramatically cuts down the amount of work then necessary.
I have written a little PL/SQL code, that is delivered with PTRef, that extracts the maximum version number for each PeopleTools table in PeopleSoft (other than the PS%DEL) tables and stores it on a working storage table (I used the PLAN_TABLE because it is always there on an Oracle database). Then you can compare the version number on PSLOCK/PSVERSION with the maximum version on the PeopleTools object.
If the version number is 1, you can’t do the match because the version number has never been incremented, at least since it was last reset by the VERSION application engine.
If the version is only updated occasionally you may have some trouble with duplicate matches. In my example, 3 tables have a maximum version number of 80, while only one version number is 80.
RECNAME MAX CNT
------------------ ---------- ----------
PSAEAPPLDEFN 80 3504
PSMSGNODEDEFN 80 78
PSOPRVERDFN 80 1468
I would guess that OPRVM matches PSOPRVERDFN, and the above support notes confirm this, but otherwise, you would have to check that manually with trace.OBJECTTY VERSION
-------- ----------
OPRVM 80
Higher version numbers are easier to match because they are less likely to have duplicate matches.So to return to the original question, what is CRM? In my sample system, version numbers CRM and SCATM are both 3.
OBJECTTY VERSION
-------- ----------
CRM 3
SCATM 3
However, only PSCONTDEFN has a maximum version of 3.RECNAME MAX CNT
------------------ ---------- ----------
PSCONTDEFN 3 7567
Again not a perfect match, but again Oracle Support Note 664848.1 confirms that CRM corresponds to PSCONTDEFN. CRM stands for Content Registry Manager.So the question now becomes what is updating the content definitions, and hence increasing the version number? It turned out to be an entity registry synchronisation process that was run daily.
It is perfectly legitimate for many updated rows on the PeopleTools table can be given the same version number. The version number does not need to be incremented again for each row being updated, but then the row-level locks on PSVERSION and PSLOCK created by their updates must be held until the update on the PeopleTools table is committed. That can increase contention on the version number update. The alternative is to commit after each update and then increment the version numbers again. Many PeopleSoft processes do exactly that, and it can, in turn, lead to massive increase in some version numbers.
Understanding PeopleTools Object Version Numbers
CachingThe PeopleSoft data model and application are mostly stored in the database in PeopleTools tables. These tables are queried as the application executes. For example, when you open a component, the component and pages, including all the PeopleCode, the definition of any records used, and so on have to be loaded into the component buffer. Ultimately this information comes from the PeopleTools tables. To save the overhead of repeatedly querying these tables, PeopleSoft caches this data locally in physical files the application server and process scheduler domains. The application servers also cache some of this information in memory to save visiting the local physical cache. Application Designer also maintains a physical cache.
Over time, as the application executes, the cache files build up. Occasionally, when it is necessary to delete the cache files and then it becomes clear just how significant is the overhead of the PeopleTools queries as a period of poor performance is seen as the application builds up fresh cache files.
Physical cache files are created in directories in the application server and process scheduler Tuxedo domains. By default, each process maintains its own private cache. Separate directories of cache files are created for each type of PeopleSoft server process in each domain. Pairs of cache files are created in each directory for each object type as needed. There is also a CACHE.LOK file in each directory that is used to ensure that only one process is accessing that cache directory concurrently.
It is possible to run with a shared physical cache, but then it is read-only and must be pre-generated. It is very rare to see this implemented, because everyone expects to continuously deliver changes over time, and if you had a shared cache you would have to deliver an updated set of shared cache file to every domain every time you delivered a new PeopleTools object.
The cache files come in pairs. The name of the cache files is the Object Type Name. This corresponds to the OBJECTTYPENAME on the PSLOCK and PSVERSION tables. The .DAT file contains the data to be cached. The .KEY file is an index for the .DAT file, and it also holds the version number of the cached object.
…
-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
…
Version NumbersSQL> desc psversion
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECTTYPENAME NOT NULL VARCHAR2(8 CHAR)
VERSION NOT NULL NUMBER(38)
There are now over 100 different version numbers, each with a specific object type name that each track a specific PeopleTools object. There is a global version number, with the object type name of SYS, that is incremented whenever any other version number is incremented.I have no idea why two identical tables of version numbers were created. I can see no reason for this, but it has been like this since the version numbers were changed (if I remember correctly) in PeopleTools 7. In early versions of PeopleTools, not all version numbers were on both tables, but in at least PeopleTools 8.55 only one object type appears on PSVERSION and not PSLOCK.
When an object is changed, the object and global version numbers are incremented, and the incremented object version number is recorded on the object in the PeopleTools table. The version number on the object is also stored in the physical cache files when the object is cached. If the version on the database is higher than that in the cache file, then the PeopleSoft process knows it must query the latest version from the PeopleTools table and update the cache file.
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 am updating a PeopleTools object (PSINDEXDEFN) that doesn't have a version number, but its parent is PSRECDEFN that does have a version number. I happen to know that object type RDM (the Record Definition Manager) generates the version number for PSRECDEFN. I found that out by tracing Application Designer while it saved a record change. That is the only completely reliable method to determine which sequence is used for which record. However, I will discuss another less onerous matching method in a subsequent blog post.PeopleTools Table Reference Generator
I am not the only person to have started to document the PeopleTools tables on their website or blog, most people have picked a few tables that are of particular interest. I wanted to produce a complete reference. However, with over 4000 PeopleTools tables and views, it is simply not viable to do even a significant number of them manually. So, I wrote some SQL and PL/SQL to dynamically generate a page for each PeopleTools table and views and put the generated pages on my website. If you use Google to search for a PeopleTools name you will probably find the reference page.
I have now revisited that code and made a number of changes and improvements.
- 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.
The idea of the original PeopleTools tables reference was the people could contribute additional descriptions and information that were not in the PeopleTools tables. That can still happen, and indeed should be easier, by making changes to the scripts that load the additional data and uploading new versions to GitHub.
PS360 enhancement: Added report of DDL models
- 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.
PS360 can be download from https://github.com/davidkurtz/ps360.
PeopleSoft Adminstrator Podcast: #184 – nVision Performance
(10 May 2019) #184 – nVision Performance You can listen to the podcast on psadmin.io, or subscribe with your favourite podcast player, or in iTunes.
PeopleSoft Administrator Podcast: #183 – Effective Performance Monitoring
(3 May 2019) #183 – Effective Performance Monitoring You can listen to the podcast on psadmin.io, or subscribe with your favourite podcast player, or in iTunes.
Effective PeopleSoft Performance Monitoring
Contents
- 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.
"Application developers can use the DBMS_APPLICATION_INFO package to record the name of the executing module or transaction in the database for use later when tracking the performance of various modules… System administrators can also use this information to track resource usage by module. When an application registers with the database, its name and actions are recorded in the V$SESSION and V$SQLAREA views. Your applications should set the name of the module and name of the action automatically each time a user enters that module. The module name could be the name of a … code segment in an … application. The action name should usually be the name or description of the current transaction within a module."• Oracle 7 Tuning, release 7.3.3 ©Oracle 1997, Chapter 23 Registering Applications• See also One of my Favourite Database Things: DBMS_APPLICATION_INFO
If module and action are consistently set to meaningful values as the application executes it is then possible to determine from where SQL statements originated and how different parts of the application are performing.
ASH is separately licenced as part the Diagnostics Pack, that is only available on Enterprise Edition of the database. However, most PeopleSoft customers running on Oracle do so on Enterprise Edition and are licenced for the diagnostics pack.
PeopleSoft has set client_info since PeopleTools 7.53 so the PeopleSoft operator ID can be associated with the database session, mainly to allow auditing to be done with database triggers. However, this attribute is not persisted to the ASH data. However, client_id is also set to the operator ID, and this is collected by ASH.
PeopleTools has set module and action since PeopleTools 8.50, though the exact values used have changed sometimes with the PeopleTools version.
A new application server/process scheduler domain parameter EnableAEMonitoring was introduced in PeopleTools 8.54 to control this behaviour and it is not enabled by default. If monitoring is not enabled module defaults to the program name and action remains blank.
Recommendation: 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.
See also:
- 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
In the Integration Broker module and action are set to service name and queue name.
Application Engine Step InformationIn Application Engine, module is set to a string that includes the name of the Application Engine main program the was called and the sessionid_num for the current process instance number recorded on the process scheduler request table PSPRCSRQST. For example: PSAE.PSPMCSOSUM.1448
Later on, it may be necessary to use regular expressions in SQL to process this string before profiling the ASH data.
Action is set to string concatenated from the Application Engine program, section, step name and step type. For example: PSPMCSOSUM.GETCNT.CNT.P
The program name may be different from that shown in module if one AE program calls another.
Note: Application Engine still doesn't reset ACTION on DO SELECT steps
Cobol/nVision InstrumentationCobol, nVision and SQR do not set module and action. Instead, they can be set at the start of every program initiated by Process Scheduler using a trigger on PSPRCSRQST. The first thing a process does when it is initiated is to set the run status on its scheduler request record to 7, indicating that it is processing. This is visible in the Process Monitor component. A trigger on this transition can set module and action for the session to the program and process instance number on that row of data.
This technique was used prior to PeopleSoft adding instrumentation to PeopleTools. It can still be applied to all processes, including Application Engine, because any PeopleSoft instrumentation will simply overwrite the value set by the trigger.
Recommendation: Implement the PSFTAPI package and trigger described above in order to set module and action at the start of all processes.
The same technique was also used prior to the introduction of ASH to enable Oracle SQL Trace if a particular run control was specified, and is still occasionally useful.
nVision Layout InstrumentationOne of the challenges of tuning and monitoring nVision is to be able to identify each report being run. nVision reports always run as the same process name, either NVSRUN for a single report, RPTBOOK for a report book of many reports, or DRILLDWN for a nVision drill-down query. Knowing the process instance is useful because then we can look up the operator and run control ID
However, it would also be useful to know the report ID being run. When each individual nVision report starts it queries the runtime parameters from the PS_NVS_REPORT PeopleTools table. There is no update, so it is not possible to capture this with a DML trigger. Instead, it is possible to define a fine-grained audit policy on the query and set module within a PL/SQL handler package that is invoked by the fine-grained audit.
Recommendation: Implement Fine-Grained Audit policy and handler to instrument nVision processes if required
2nd Database Connection InformationPeopleSoft programs use a second database connection to increment sequence numbers to minimise the row level locking on such tables. It is like an AUTONOMOUS_TRANSACTION in PL/SQL. There is no PeopleSoft instrumentation on this session. It is possible to use an AFTER LOGON trigger to set client_info, module and action.
Oracle Automatic Workload Repository (AWR) SnapshotsPeopleSoft generates a lot of non-shareable SQL.
- 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
Recommendation: Reduce AWR snapshot frequency from the default of 1 hour to every 15 minutes. This change results in only a modest increase overhead in processing and space on AWR, but it is worth the additional information that is captured.
This advice also applies to Statspack that may be used if you are not licenced for the Diagnostics Pack.
PeopleTools Performance MetricsBatch TimingsPeopleSoft Application Engine and Cobol programs can emit batch timings reports on successful completion.
Application EngineApplication Engine batch timings are controlled by the AETrace flag in the Process Scheduler domain configuration file and for on-line AE programs in the Application Server domain configuration files.
- 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
Recommendation: 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.
The trace setting in the process scheduler configuration can be overridden by setting process specific command line parameter overrides in the process definition. This is often done to set other trace settings, it is also common to see these unintentionally left in place longer than necessary. If trace is set in this way it should always also set the batch timings flags.
See PeopleBooks -> Development Tools -> Application Engine -> Enabling Application Engine Tracing
CobolPeopleSoft Cobol programs can only write batching timings reports to file and not to the database. This is controlled by a different parameter.
- TraceSQL = 128: Enable Cobol statement timings report
This trace setting can also be overridden by setting process specific command line parameter overrides in the process definition. If trace is set in this way it should always also set the batch timings flags.
PeopleSoft Performance MonitorThis provides information about the performance of the PIA including response times for the online transactions. Metrics are stored in a separate monitoring PeopleSoft system to minimize the effect of measurement intrusion. It optionally samples the state of each web server, application server and process scheduler collecting operating system and Tuxedo metrics. It also has a PIA session trace capability.
The sampled data includes the number of busy application server processes and length of inbound Tuxedo service queues. This data can be used to validate the sizing of the application servers.
Recommendation: 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.
How Not to Collect Optimizer Statistics in an Application Engine Program
ORA-06533: Subscript beyond count
ORA-06512: at "SYS.DBMS_STATS", line 36873
…
It is possibly a manifestation of a database bug. The workaround was not to use AUTO_SAMPLE_SIZE, so instead, this customer initially coded an explicit call to DBMS_STATS.GATHER_TABLE_STATS with a specific sample size.This blog is not about the bug, but how to manage the workaround.
DO NOT TRY THIS AT HOME!
I think that there are a number of problems with this approach
- 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.
- 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.
Committing inside a do while loop, including any other Application Engine program called from inside the loop is suppressed at Application Engine section/step level and therefore the execution of %UpdateStats macro is also suppressed. Otherwise, you could get rogue ORA-01555 Snapshot Too Old errors. Suppression of %UpdateStats is reported in the Application Engine step trace.
-- 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)
Even a previously uncommitted SQL step can lead to %UpdateStats being suppressed.-- 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
If you code DBMS_STATS.GATHER_TABLE_STATS explicitly, Application Engine will not recognise the step as having committed. In the following example. you can see the %UpdateStats on the last step has been suppressed because it Application Engine does not recognise that the update in the first step has been committed by the call to DBMS_STATS.-- 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
/
Perhaps, the safest form of this workaround would be to have the step with DBMS_STATS and the immediately preceding step explicitly commit as in the following example. I have also made the program restartable. Now restart data is checkpointed, and the %UpdateStats macro executes at step US1.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
/
However, you have to consider the state the application data after an error, whether you wish to restart or cancel the Application Engine because you can no longer rollback.Doing the Right Thing
- You should only ever collect stats in Application Engine with the %UpdateStats macro that in turn executes the command in the DDL model.
- From Oracle 11g both PeopleSoft statistics gathering DDL models should be the same and should ultimately call DBMS_STATS without any parameters other than the table name. The default value of ESTIMATE_PERCENT is AUTO_SAMPLE_SIZE.
Automatic sample size was introduced in Oracle 9i. In Oracle 9i and 10g, it was usual to use automatic sample size in the high statistics gathering model and a small fixed sample size in the low model for use on very large tables. The LOW parameter was specified on %Updatestats in Application Engine programs as necessary.
This approach became redundant from Oracle 11g with the introduction of table preferences. If you need to collect statistics with a specific rather than the automatic sample size or specify any other parameters, then a table preference should be defined. It applies whenever statistics are gathered on that table, during the statistics maintenance window, and any places where statistics are explicitly collected, rather than just a single call.
From Oracle 12c, as the new NDV algorithm only works with AUTO_SAMPLE_SIZE, you should always use the default unless you have an overarching reason to the contrary, and then you should use a table preference. This approach does not require any application code change because the preference is an attribute of a table in the database.
I recommend using GFCPSSTATS package, calling it from the DDL model (see previous blog Managing Cost-Based Optimizer Statistics for PeopleSoft). The package also includes a mechanism to specify table preferences in metadata, so that they are automatically instantiated when a table is created or altered by Application Designer, and are applied to every instance of a temporary record.
gfcpsstats11.ps_stats(p_ownname=>[DBNAME], p_tabname=>[TBNAME]);
Alternatively, just call DBMS_STATS with no additional parameters (other than FORCE, in case you lock statistics on temporary tables) but then you must manage table preferences manually.DBMS_STATS.GATHER_TABLE_STATS(ownname=>[DBNAME], tabname=>[TBNAME], force=>TRUE);
Further Reading- Oracle Optimizer Blog: How does AUTO_SAMPLE_SIZE work in Oracle Database 12c?
- Oracle Optimizer Blog: How does AUTO_SAMPLE_SIZE work in Oracle Database 11g
- Oracle Optimizer Blog: Understanding DBMS_STATS.SET_*_PREFS procedures
- PeopleSoft DBA Blog: Oracle/PeopleSoft have mixed up DDL Models used by %UpdateStats from PeopleTools 8.48
- PeopleBooks PT8.56: Development Tools -> Application Engine -> Using Application Engine MetaSQL -> %UpdateStats
Managing Cost-Based Optimizer Statistics for PeopleSoft

PeopleSoft presents some special challenges when it comes to collecting and maintaining the object statistics used by the cost-based optimizer.
I have previously written and blogged on this subject. This presentation focuses exclusively on the Oracle database and draws together the various concepts into a single consistent picture. It makes clear recommendations for Oracle 12c that will help you work with the cost-based optimizer, rather than continually fight against it.
It looks at collecting statistics for permanent and temporary working storage tables and considers some other factors that can affect optimizer statistics.
This presentation also discusses PSCBO_STATS, that is going to be shipped with PeopleTools, and compares and contrasts it with GFCPSSTATS11.
Application Engine in Process Scheduler: PSAESRV Server Process -v- Standalone PSAE executable
In PeopleTools 8.4, the Process Scheduler became a fully fledged Tuxedo domain. The PSAESRV process was also introduced at this time. It is a persistent process that handles both Application Engine and Optimization Engine requests. Each server process creates persistent database connections. A number of these server processes are started with the domain. The PSAESRV process does not spawn like other Tuxedo server processes. Instead, you must configure the number of server processes to match the maximum number of concurrent Application Engine process requests and concurrent Optimization Engine requests that the process scheduler can run. The server was introduced to handle very short-lived Application Engine programs thus avoiding the overhead of instantiating a new process and new database sessions for each process request. CRM typically uses Application Engine in this fashion, but generally, you do not see this in other PeopleSoft products.
Oracle has not always been clear what they mean by a short-lived process. It has been suggested that if Application Engine processes are typically taking less than 10-30 seconds, or if you run more than 1000 Application Engine processes requests per hour (note 651970.1) you should use PSAESRVs.
PeopleBooks advises “you should use PSAESRV because it delivers improved system performance”. However, PeopleTools Performance Guidelines Red Paper (Doc ID 747389.1) contradicts this somewhat. Ultimately, if you have any doubts, you should it test each way and determine whether one way gives a clear advantage over the other.
Oracle Support Note "What will be the impact of disabling PSAESRV on the Process Scheduler (Doc ID 651970.1)" explains that if PSAESRV is disabled in the Tuxedo domain configuration, the Process Scheduler goes back to the legacy behaviour and spawns a stand-alone PSAE process for each Application Engine request. “The Application Engine will take a bit longer to start, [the] time delay may be range from millisecond to seconds” depending on hardware and configuration.
The stand-alone process has several advantages.
- 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.
Resetting High Water Marks on On-line Temporary Table Instances
From PeopleTools 8, many copies of each temporary working storage table are created. Application Engines that run on the Process Scheduler are allocated exclusive use of a particular copy of the table. This avoids the inter-process contention. They start by truncating each allocated table, which resets the high-water mark.
Some delivered processing uses batch programs that are run apparently synchronously from the PIA. On-line edit and post in Financials General Ledger is a common example. Up to PeopleTools 7, the application server would synchronously spawn a batch process and wait for it to complete. From PeopleTools 8 the process is submitted to the process scheduler, and the PIA polls the Scheduler tables waiting for the process to complete. However, Application Engine can be run within the component processor. In Financials General Ledger, this can be chosen by a setting an installation configuration option. The truly on-line method can perform better because you are no longer waiting for the process scheduler to pick up the process request. A separate process Application Engine is not spawned, but the Application Engine program is executed by the PSAPPSRV application server process. One of the limitations is that the Application Engine program cannot commit. Committing after steps or sections is suppressed, and the %TruncateTable macro generates a delete statement instead. Therefore, on-line temporary table instances are never truncated by any process and their high-water marks can be raised by processes that handle larger volumes of data. This can have impacts for subsequent processes with smaller data volumes but that still have to full-scan working storage tables up to their high water marks.
Set serveroutput on
EXECUTE xx_onlineinsthwmreset.main;
The package can be run in test mode when it will list the commands without executing them. Thus you can see what it will do without actually doing it.EXECUTE xx_onlineinsthwmreset.main(p_testmode=>TRUE);
The package can optionally deallocate any physical storage. Storage will be reallocated next time the table is used.EXECUTE xx_onlineinsthwmreset.main(p_drop_storage=>TRUE, p_min_extents=>0);
The package can be run for certain tables that match a particular pattern.EXECUTE xx_onlineinsthwmreset.main(p_recname_like=>'JP%');
I recommend that the package is run daily. However, it can be run safely while the users are doing on-line edit/post processing, but it would be sensible to choose a quiet time.Fewer Platform Flags on Indexes from PeopleTools 8.55
Over the years, the number of supported PeopleSoft platforms has declined. In PeopleTools 8.45, it went down from 9 to 6 and in PeopleTools 8.55 it has gone down to just 4, but there are still 9 columns on PSINDEXDEFN that correspond to the original 9 supported platforms.
I explained in a previous blog that you can have all or none of the platform flags set to the same value, but with the platform radio button on the index properties dialogue box is still set to 'some' because one or more of the platform flag columns for some of the unsupported platforms is set differently. Of course, this is a purely cosmetic problem, but one that can cause confusion in Application Designer.
column RECNAME format a15
column INDEXID format a3 heading 'Idx|ID'
column DDLCOUNT format 999 heading 'DDL|Cnt'
column CUSTKEYORDER format 9999 heading 'Cust|Key|Order'
column KEYCOUNT format 999 heading 'Key|Cnt'
column PLATFORM_SBS format 999 heading 'SBS'
column PLATFORM_DB2 format 999 heading 'DB2'
column PLATFORM_ORA format 999 heading 'ORA'
column PLATFORM_INF format 999 heading 'INF'
column PLATFORM_DBX format 999 heading 'DBx'
column PLATFORM_ALB format 999 heading 'ALB'
column PLATFORM_SYB format 999 heading 'SYB'
column PLATFORM_MSS format 999 heading 'MSS'
column PLATFORM_DB4 format 999 heading 'DB4'
column ACTIVEFLAG Format 999 heading 'Active'
column CLUSTERFLAG format 999 heading 'Clst|Flg'
column UNIQUEFLAG format 999 heading 'Uniq|Flg'
column INDEXTYPE format 999 heading 'Idx|Type'
column IDXCOMMENTS format a60
spool platformfix855
SELECT *
FROM PSINDEXDEFN
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_MSS
AND (PLATFORM_ORA!=PLATFORM_SBS
OR PLATFORM_ORA!=PLATFORM_ALB
OR PLATFORM_ORA!=PLATFORM_SYB
OR PLATFORM_ORA!=PLATFORM_INF
OR PLATFORM_ORA!=PLATFORM_DB4)
;
Cust
Idx Idx Uniq Clst Key Key DDL
RECNAME ID Type Flg Flg Active Order Cnt Cnt SBS DB2 ORA INF DBx ALB SYB MSS DB4
--------------- --- ---- ---- ---- ------ ----- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
IDXCOMMENTS
------------------------------------------------------------
PSPMTRANSHIST _ 1 1 1 1 0 4 1 1 1 1 1 1 1 1 1 0
…
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 IN (
SELECT RECNAME
FROM PSINDEXDEFN
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND ( PLATFORM_ORA!=PLATFORM_SBS
OR PLATFORM_ORA!=PLATFORM_ALB
OR PLATFORM_ORA!=PLATFORM_DB4)
);
UPDATE psindexdefn
SET PLATFORM_DB4=PLATFORM_ORA
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND PLATFORM_SYB=PLATFORM_MSS
AND PLATFORM_ORA!=PLATFORM_DB4;
UPDATE psindexdefn
SET PLATFORM_ALB=PLATFORM_ORA
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND PLATFORM_SYB=PLATFORM_MSS
AND PLATFORM_ORA!=PLATFORM_ALB;
UPDATE psindexdefn
SET PLATFORM_SBS=PLATFORM_ORA
WHERE PLATFORM_DB2=PLATFORM_DBX
AND PLATFORM_DBX=PLATFORM_INF
AND PLATFORM_INF=PLATFORM_ORA
AND PLATFORM_ORA=PLATFORM_SYB
AND PLATFORM_SYB=PLATFORM_MSS
AND PLATFORM_ORA!=PLATFORM_SBS;
The new platformfix855.sql script is available on collection of miscellaneous scripts on Github.
Setting Oracle Session Parameters for Specific Process Scheduler Processes
A table is used to hold metadata that described what setting is applied to which processes. A trigger on the process scheduler request table PSPRCSRQST reads that data and alters the session setting. This approach is easier to adjust and understand that static PL/SQL code in a trigger.
NB: The PSAESRV server process is used to run application engine programs in the process scheduler by default. It should only be used for very short-lived programs and should usually be deconfigured. It maintains a persistent connection to the database. Therefore, session parameters set for one application engine program will carry forward into subsequent programs run on the same server process with the potential for unintended and unpredictable results. Other processes all establish their own database session that ends when the process terminates. This trigger mechanism can be still used to set parameters for some processes that run on PSAESRV, but the default parameter value should then be set for all other application engine processes.
Metadata The table that holds the metadata should be defined in Application Designer.
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
/
I will demonstrate this mechanism for nVision. The metadata is simply inserted into the table by script.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');
Here we have 5 session parameters that will apply to all nVision reportbooks, but which I don't want to apply to the rest of the system.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
The objective is to use limited parallelism only in the nVision reporting, and without decorating underlying ledger tables- 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');
The specific setting for one particular operation ID and run control takes precedence of the generic setting for all reportbooks. In this case, I will scale the degree of parallelism further for a particular reportbook.PRCSTYPE PRCSNAME OPRID RUNCNTLID PARAM_NAME PARMVALUE
-------------------- ------------ ---------- --------------- ------------------------------ --------------------
…
nVision-ReportBook RPTBOOK parallel_degree_level 150
nVision-ReportBook RPTBOOK NVOPR NVSRBK_2 parallel_degree_level 200
Trigger When a process starts the first thing it does is update its own status to 7 to indicate that it is processing. This is another example of a trigger created on that transition that injects behaviour at the start of a PeopleSoft process. This trigger reads the metadata and applies the settings with an ALTER SESSION command. The process type, name, operation and run control attributes must exactly match the process request, but a blank space is treated as a wildcard. Underscore parameters must be delimited in double quotes.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;
/
The trigger script set_prcs_sess_parm.sql can be download from my miscellaneous PeopleSoft scripts repository on Github.Testing The trigger can be tested by updating the process scheduler request table in SQL*Plus, but be careful to roll back the update afterwards rather than committing. The trigger writes debug information to the server output that can be seen in SQL*Plus showing the rule being used and the ALTER SESSION command generated. However, this output is not captured in any PeopleSoft log when the trigger is fired by a PeopleSoft process.
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
In the above example, the specific rule for NVSRBK_2 was applied setting PARALLEL_DEGREE_LEVEL to 200, whereas in the next example the generic setting of 150 is applied to NVSRBK_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