David Kurtz

In the cloud, either you are spending too much money on too much CPU, or your system is constrained by CPU at peak times.  You can have as much performance as you are willing to pay for. 

This presentation (from the UKOUG 2024 conference) is the story of how one PeopleSoft customer improved performance and reduced cloud subscription costs, by clearly stating their performance goals, and creating a matching resource manager plan.

Effective use of machine resources has always been a challenge for PeopleSoft systems.  As systems move to the cloud that is in ever sharper focus.  In the cloud, you mostly pay for CPU.  You can generally have as much performance as you are willing to pay for, but every architectural decision you make has an immediate cost consequence. That drives out different behaviours. 

In the cloud, you rent hardware as an operational expense, rather than purchasing it as a capital expense.  If you are not short of CPU, you are probably spending too much. If you are short of CPU, then you need to the Oracle database's Resource Manager to manage what happens.

This presentation looks at how that played out at one PeopleSoft customer, who moved their GL reporting batch on Financials onto Exadata Cloud-at-Customer. The single most important thing they did was to clearly state their goals. That set the ground rules for sizing and configuring both their database and their application, implementing various database features, including defining a resource manager plan, as well as using partitioning, materialized views, compression, and in-memory. 

They have continued to improve performance and save money on their cloud costs.  They were recently able to switch off another CPU. 

The session also describes a generic resource plan that can be used as a starting point for any PeopleSoft system to which individual requirements can be added.

• PeopleSoft DBA Blog: PSFT_PLAN: A Sample Oracle Database Resource Manager Plan for PeopleSoft
• GitHub: psft_resource_plan_simple.sql

Finally, there are some ideas for prioritising Tuxedo server processes on Linux.

Many PeopleSoft users like its ad hoc query tool because they can write their own queries directly on the system, without having to learn to write structured query language (SQL), or getting a developer to write it for them.   

It is easy for users to create poor queries, that either don't work as intended or can run for long periods, even indefinitely, consuming resources without ever producing results.  This can consume significant amounts of CPU, and in the cloud, that is mostly what you pay for!  The effect can be mitigated with the database's resource manager, but it is better not to do it in the first place.

One cause of long-running queries that I come across is missing join criteria leading the database to perform Cartesian Merge Joins.  I should stress that not all Cartesian joins are evil.  For example, in some data warehouse queries (e.g. GL nVision reporting), it can be a very effective strategy to Cartesian join dimension tables before visiting the fact table, especially if you can use Bloom filter a full scan on the fact table.  It works well with parallel query, and on engineered systems this can also be pushed down to the storage cells.

The following query profiles database time by execution plan from ASH for SQL statements from PS/Queries run via the PSQUERY application engine program on a process scheduler.  It returns the longest-running statement for each execution plan.

The data is generated and processed through several common table expressions.

• R returns the PSQUERY processes that ran in the time window of interest
• P returns the execution plans captured by AWR that generate Cartesian products for which the SQL text is also captured.  
• X returns the ASH data for Cartesian join executions. When P is joined with the ASH data, then we just get the queries that performed Cartesian joins.
• Y sums and groups the ASH data by statement and process
• Z sums the data by execution plan and identifies the longest-running SQL statement for that plan.

REM qry_cartesianplans.sql
WITH r as ( /*processes of interest*/
SELECT /*+MATERIALIZE*/ r.oprid, r.prcsinstance, r.prcsname, r.begindttm, r.enddttm
,      DECODE(c.private_query_flag,'Y','Private','N','Public') private_query_flag, c.qryname
FROM   psprcsrqst r
       LEFT OUTER JOIN ps_query_run_cntrl c ON c.oprid = r.oprid AND c.run_cntl_id = r.runcntlid
WHERE prcsname = 'PSQUERY'
AND r.begindttm >= trunc(SYSDATE)-0+8/24
AND r.begindttm <= trunc(SYSDATE)-0+19/24
), p as ( /*known Cartesian plans with SQL text*/
SELECT /*+MATERIALIZE*/ p.plan_hash_value, MAX(p.options) options
FROM   dbA_hist_sql_plan p
,      dba_hist_sqltext t
WHERE  t.sql_id = p.sql_id
AND    (p.id = 0 OR p.options = 'CARTESIAN')
GROUP BY p.plan_hash_Value
), x AS ( /*ASH for processes*/
SELECT /*+materialize leading(r x)*/  r.prcsinstance, r.oprid, r.private_query_flag, r.qryname
,      h.event, x.dbid, h.sample_id, h.sample_time, h.instance_number
,      CASE WHEN h.module IS NULL       THEN REGEXP_SUBSTR(h.program, '[^@]+',1,1)
            WHEN h.module LIKE 'PSAE.%' THEN REGEXP_SUBSTR(h.module, '[^.]+',1,2) 
            ELSE                             REGEXP_SUBSTR(h.module, '[^.@]+',1,1) 
       END AS module
,      h.action
,      NULLIF(h.top_level_sql_id, h.sql_id) top_level_sql_id
,      h.sql_id, h.sql_plan_hash_value, h.force_matching_signature, h.sql_exec_id
,      h.session_id, h.session_serial#, h.qc_instance_id, h.qc_Session_id, h.qc_Session_serial#
,      f.name, p.options
,      NVL(usecs_per_row,1e7) usecs_per_row
,      CASE WHEN p.plan_hash_value IS NOT NULL THEN NVL(usecs_per_row,1e7) ELSE 0 END usecs_per_row2
FROM   dba_hist_snapshot x
,      dba_hist_active_sess_history h
       LEFT OUTER JOIN p ON p.plan_hash_value = h.sql_plan_hash_value
       LEFT OUTER JOIN dba_sql_profiles f ON h.force_matching_signature = f.signature
,      r
,      sysadm.psprcsque q
WHERE  h.SNAP_id = X.SNAP_id
AND    h.dbid = x.dbid
AND    h.instance_number = x.instance_number
AND    x.end_interval_time >= r.begindttm
AND    x.begin_interval_time <= NVL(r.enddttm,SYSDATE)
AND    h.sample_time BETWEEN r.begindttm AND NVL(r.enddttm,SYSDATE)
AND    q.prcsinstance = r.prcsinstance
AND    (  (h.module = r.prcsname AND h.action like 'PI='||r.prcsinstance||':Processing')
       OR  h.module like 'PSAE.'||r.prcsname||'.'||q.sessionidnum)
), y as( /*profile time by statement/process*/
SELECT prcsinstance, oprid, private_query_flag, qryname, sql_plan_hash_value, sql_id, force_matching_signature, name
,      dbid, module, action, top_level_sql_id
,      count(distinct qc_session_id||qc_session_serial#||sql_id||sql_exec_id) execs
,      sum(usecs_per_row)/1e6 ash_Secs
,      sum(usecs_per_Row2)/1e6 awr_secs
,      avg(usecs_per_row)/1e6*count(distinct sample_time) elapsed_secs
,      count(distinct instance_number||session_id||session_serial#) num_procs
,      max(options) options
FROM   x 
GROUP BY prcsinstance, oprid, private_query_flag, qryname, sql_plan_hash_value, sql_id, force_matching_signature, name
,      dbid, module, action, top_level_sql_id, qc_instance_id, qc_session_id, qc_session_serial#
), z as ( /*find top statement per plan and sum across all executions*/
SELECT row_number() over (partition by force_matching_signature, sql_plan_hash_value order by awr_secs desc) plan_seq
,      prcsinstance, oprid, name, private_query_flag, NVL(qryname,action) qryname, options
,      sql_id, sql_plan_hash_Value, force_matching_signature
,      count(distinct sql_id) over (partition by force_matching_signature, sql_plan_hash_value) sql_ids
,      sum(execs) over (partition by force_matching_signature, sql_plan_hash_value) plan_execs
,      sum(ash_Secs) over (partition by force_matching_signature, sql_plan_hash_value) plan_ash_secs
,      sum(awr_Secs) over (partition by force_matching_signature, sql_plan_hash_value) plan_awr_secs
,      sum(elapsed_Secs) over (partition by force_matching_signature, sql_plan_hash_value) elap_secs
,      sum(num_procs) over (partition by force_matching_signature, sql_plan_hash_value) max_procs
FROM   y
)
Select z.*, z.plan_ash_secs/z.elap_secs eff_para
from   z
where  plan_seq = 1
and    sql_id is not null
and    plan_ash_secs >= 300
ORDER BY plan_ash_secs DESC
FETCH FIRST 50 ROWS ONLY
/

Plan    Plan
     Process                                          Private                                                           SQL Plan        Force Matching  SQL   Plan     ASH     AWR Elapsed  Max  Eff.
 #  Instance OPRID     NAME                           Query   QRYNAME                        OPTIONS   SQL_ID         Hash Value             Signature  IDs  Execs    Secs    Secs Seconds  Prc  Para
-- --------- --------- ------------------------------ ------- ------------------------------ --------- ------------- ----------- --------------------- ---- ------ ------- ------- ------- ---- -----
 1  12344342 NXXXXXX                                  Public  XXX_TRIAL_BALANCE_BY_BU_XXX_V2           c4zfcub2bnju8  2128864041   4468535744829993986    4      4  103473  103473  103473    4   1.0
 1  12344471 FXXXXXX                                  Public  XXXAM_FIN_GL_AP                          d8jnxzmgx20mq  4189069557  16033793374717384734    1      1   32599   32599   32599    1   1.0
 1  12344448 VXXXXXX                                  Private XXX1_LEDGERBAL1_UPRDAC_XXXX1             ftn7nz1xafh5z           0  15193759933860031914    2      2   20615   20615   20615    2   1.0
 1  12345574 BXXXXXX                                  Private XXX_GL_BJU                     CARTESIAN ab2v91h9zj3hv   603930234   4189289347608449750    1      1   16862   16862   16862    1   1.0
 1  12345681 BXXXXXX                                  Private XXX_GL_BJU                     CARTESIAN 05tphb379fu8j   603930234   6203431496815450503    1      1   15452   15452   15452    1   1.0
 1  12345852 WXXXXXX                                  Public  XXXINSOLVENTS_JRNL_DETAIL                51aw4ahxba0gq  3918624993  11145663850623390044    1      1   13435   13435   13435    1   1.0
 1  12345863 CXXXXXX                                  Public  XXX_COMMUTATIONS_JRNL_DTL                7q9kt75bh35dg           0  11985643849566057390    1      1   13283   13283   13283    1   1.0
 1  12344773 WXXXXXX                                  Private XXX_COMMUTATION_JRNL_DETAIL_2            361gck3w3mak7           0  18367721225324700858    1      2   12883   12883   12883    2   1.0
 1  12344682 DXXXXXX                                  Private COMBINED_JE_DETAIL_DV                    2gchgaf465ku5           0   5375582220398622005    1      1    9279    9279    9279    1   1.0
 1  12345618 DXXXXXX                                  Private COMBINED_JE_DETAIL_DV_NO_AFF             2q2faj9c6003u           0  15355473744647942117    1      1    5079    5079    5079    1   1.0
…

SELECT * FROM table(dbms_xplan.display_workload_repository('ab2v91h9zj3hv',603930234,'ADVANCED +ADAPTIVE'));

In this example, there are two similar SQL statements, with different force matching signatures, that produce the same execution plan.  The difference is that one has an IN list of 3 accounts, and the other has an equi-join to just one account.  This is enough to produce a different force matching signature.  This is why I often group ASH data by execution plan hash value.  Even if the SQL statement is different, if the execution plan is the same, then the issues and solutions tend to be the same.

The statements have been reformated to make them easier to read.  Both are just joins between two objects.  There are criteria on PS_JRNL_DRILL_VW (a view on PS_JRNL_LN), but there are no join criteria between it and its parent table JRNL_HEADER, thus a meaningless Cartesian product that joins every journal line to every journal header was created and sorted.

SQL_ID ab2v91h9zj3hv 
--------------------
SELECT A.BUSINESS_UNIT, A.JOURNAL_ID,
TO_CHAR(A.JOURNAL_DATE,'YYYY-MM-DD'), B.DESCR254, A.ACCOUNT,
A.LINE_DESCR, SUM( A.MONETARY_AMOUNT), A.LEDGER, B.ACCOUNTING_PERIOD,
B.SOURCE, B.OPRID, A.PRODUCT, A.CLASS_FLD, A.PROGRAM_CODE,
A.CHARTFIELD1, A.CHARTFIELD3, A.CURRENCY_CD, A.FOREIGN_CURRENCY 
FROM PS_JRNL_DRILL_VW A, PS_JRNL_HEADER B 
WHERE ( A.BUSINESS_UNIT IN('12341','12347') 
AND A.LEDGER IN ('CORE','LOCAL_ADJ','LOCAL_ADJ2') 
AND A.ACCOUNT IN ('1234510040','1234510000','1234510060') 
AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 12 AND A.FISCAL_YEAR = 2023) 
GROUP BY A.BUSINESS_UNIT, A.JOURNAL_ID, A.JOURNAL_DATE, B.DESCR254, A.ACCOUNT,
A.LINE_DESCR, A.LEDGER, B.ACCOUNTING_PERIOD, B.SOURCE, B.OPRID,
A.PRODUCT, A.CLASS_FLD, A.PROGRAM_CODE, A.CHARTFIELD1, A.CHARTFIELD3,
A.CURRENCY_CD, A.FOREIGN_CURRENCY ORDER BY 11

SQL_ID 05tphb379fu8j
--------------------
SELECT A.BUSINESS_UNIT, A.JOURNAL_ID,
TO_CHAR(A.JOURNAL_DATE,'YYYY-MM-DD'), B.DESCR254, A.ACCOUNT,
A.LINE_DESCR, SUM( A.MONETARY_AMOUNT), A.LEDGER, B.ACCOUNTING_PERIOD,
B.SOURCE, B.OPRID, A.PRODUCT, A.CLASS_FLD, A.PROGRAM_CODE,
A.CHARTFIELD1, A.CHARTFIELD3, A.CURRENCY_CD, A.FOREIGN_CURRENCY 
FROM PS_JRNL_DRILL_VW A, PS_JRNL_HEADER B 
WHERE ( A.BUSINESS_UNIT IN('12341','12347') 
AND A.LEDGER IN ('CORE','LOCAL_ADJ','LOCAL_ADJ2') 
AND A.ACCOUNT = '1234510000' 
AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 12 
AND A.FISCAL_YEAR = 2023) 
GROUP BY A.BUSINESS_UNIT, A.JOURNAL_ID, A.JOURNAL_DATE, B.DESCR254, A.ACCOUNT
, A.LINE_DESCR, A.LEDGER, B.ACCOUNTING_PERIOD, B.SOURCE, B.OPRID
, A.PRODUCT, A.CLASS_FLD,A.PROGRAM_CODE, A.CHARTFIELD1, A.CHARTFIELD3
, A.CURRENCY_CD, A.FOREIGN_CURRENCY ORDER BY 11

Plan hash value: 603930234

---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | Inst   |IN-OUT|
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |                |       |       | 84648 (100)|          |       |       |        |      |
|   1 |  SORT GROUP BY                                   |                |    57 |  9063 | 84648   (1)| 00:00:04 |       |       |        |      |
|   2 |   MERGE JOIN CARTESIAN                           |                |    57 |  9063 | 84647   (1)| 00:00:04 |       |       |        |      |
|   3 |    NESTED LOOPS                                  |                |     1 |   145 |  1636   (0)| 00:00:01 |       |       |        |      |
|   4 |     VIEW                                         | PS_JRNL_HEADER |   112 |  4032 |  1188   (0)| 00:00:01 |       |       |        |      |
|   5 |      UNION-ALL                                   |                |       |       |            |          |       |       |        |      |
|   6 |       REMOTE                                     | PS_JRNL_HEADER |    76 |  5624 |    18   (0)| 00:00:01 |       |       | FSARC~ | R->S |
|   7 |       INLIST ITERATOR                            |                |       |       |            |          |       |       |        |      |
|   8 |        TABLE ACCESS BY INDEX ROWID BATCHED       | PS_JRNL_HEADER | 16679 |   586K| 11634   (1)| 00:00:01 |       |       |        |      |
|*  9 |         INDEX RANGE SCAN                         | PSEJRNL_HEADER | 16679 |       |   347   (0)| 00:00:01 |       |       |        |      |
|  10 |     VIEW                                         | PS_JRNL_LN     |     1 |   109 |     4   (0)| 00:00:01 |       |       |        |      |
|  11 |      UNION-ALL PARTITION                         |                |       |       |            |          |       |       |        |      |
|* 12 |       FILTER                                     |                |       |       |            |          |       |       |        |      |
|  13 |        REMOTE                                    | PS_JRNL_LN     |     1 |   217 |     5   (0)| 00:00:01 |       |       | FSARC~ | R->S |
|* 14 |       FILTER                                     |                |       |       |            |          |       |       |        |      |
|  15 |        PARTITION RANGE SINGLE                    |                |     1 |   109 |     5   (0)| 00:00:01 |   KEY |   KEY |        |      |
|* 16 |         TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PS_JRNL_LN     |     1 |   109 |     5   (0)| 00:00:01 |   KEY |   KEY |        |      |
|* 17 |          INDEX RANGE SCAN                        | PS_JRNL_LN     |     1 |       |     4   (0)| 00:00:01 |   KEY |   KEY |        |      |
|  18 |    BUFFER SORT                                   |                |  7749K|   103M| 84644   (1)| 00:00:04 |       |       |        |      |
|  19 |     VIEW                                         | PS_JRNL_HEADER |  7749K|   103M| 83011   (1)| 00:00:04 |       |       |        |      |
|  20 |      UNION-ALL                                   |                |       |       |            |          |       |       |        |      |
|  21 |       REMOTE                                     | PS_JRNL_HEADER |  5698K|  1880M| 50467   (1)| 00:00:02 |       |       | FSARC~ | R->S |
|  22 |       TABLE ACCESS STORAGE FULL                  | PS_JRNL_HEADER |  2050K|    86M| 32544   (1)| 00:00:02 |       |       |        |      |
---------------------------------------------------------------------------------------------------------------------------------------------------

                                                                                                                    Stmt    Stmt
   SQL Plan SQL Plan                                                                  H   E I     ASH                ASH    Elap
 Hash Value  Line ID EVENT                                                            P P x M    Secs  ELAP_SECS    Secs    Secs
----------- -------- ---------------------------------------------------------------- - - - - ------- ---------- ------- -------
  603930234        1 CPU+CPU Wait                                                     N N Y N  217091 23405.3608  299088   32314
                  18 direct path read temp                                            N N Y N   64395 7034.44748  299088   32314
                  18 CPU+CPU Wait                                                     N N Y N   16998 1812.39445  299088   32314
                   1 ASM IO for non-blocking poll                                     N N Y N     195 20.4802032  299088   32314
                  21 CPU+CPU Wait                                                     N N Y N     195   20.47995  299088   32314
                  16 CPU+CPU Wait                                                     N N Y N     113   10.24021  299088   32314
                     CPU+CPU Wait                                                     N N Y N     103   10.25244  299088   32314

The answer in this particular case is to fix the code.  We have to go back to the user, explain why it is necessary to join parent and child tables and get them to correct their PS/Query.

In PeopleSoft, the parent of a child record is recorded on PSRECDEFN in the column PARENTRECNAME.  However, this does not translate into a foreign key relationship in any database supported by PeopleSoft.  This is part of PeopleSoft's original platform-agnosticism.  Not all databases previously supported by PeopleSoft supported database enforced referential integrity.  Therefore it never became part of the implementation, and there is no guarantee that the applications were written in such a way to honour foreign-key constraints (i.e. insert parents before children, delete children before parents etc.).

The below query looks at pairs of parent-child records in each select block of each PS/Query and counts the number of key columns for which there are criteria on the child record that are joined to the parent record.  It is restricted to just the journal header/line tables and views.

It returns rows where no joined key columns are found.  These queries are therefore suspected of being faulty.  However, there may be false positives where child records are joined to grandparents rather than immediate parents.  Such an approach in SQL is perfectly valid, and can even result in better performance.  

WITH x as (
SELECT r1.oprid, r1.qryname, r1.selnum
, r1.rcdnum rcdnum1, r1.recname recname1, r1.corrname corrname1
, r2.rcdnum rcdnum2, r2.recname recname2, r2.corrname corrname2
, (SELECT count(*) 
   FROM psqryfield qf1 --INNER JOIN psrecfielddb f1 ON f1.recname = r1.recname AND f1.fieldname = qf1.fieldname
   ,    psqryfield qf2 INNER JOIN psrecfielddb f2 ON f2.recname = r2.recname AND f2.fieldname = qf2.fieldname 
                                                 AND MOD(f2.useedit,2)=1 /*key fields only*/
   , psqrycriteria c
   WHERE qf1.oprid = r1.oprid AND qf1.qryname = r1.qryname AND qf1.selnum = r1.selnum AND qf1.recname = r1.recname AND qf1.fldrcdnum = r1.rcdnum
   AND   qf2.oprid = r2.oprid AND qf2.qryname = r2.qryname AND qf2.selnum = r2.selnum AND qf2.recname = r2.recname AND qf2.fldrcdnum = r2.rcdnum
   AND    c.oprid = r1.oprid AND c.qryname = r1.qryname AND  c.selnum = r1.selnum 
   AND   (  (c.lcrtselnum = r1.selnum AND c.lcrtfldnum = qf1.fldnum AND c.r1crtselnum = r2.selnum AND c.r1crtfldnum = qf2.fldnum)
         OR (c.lcrtselnum = r2.selnum AND c.lcrtfldnum = qf2.fldnum AND c.r1crtselnum = r1.selnum AND c.r1crtfldnum = qf1.fldnum))
-- AND rownum = 1
  ) num_key_fields
FROM psrecdefn r
, psqryrecord r1
  INNER JOIN psqryrecord r2 ON r1.oprid = r2.oprid AND r1.qryname = r2.qryname AND r1.selnum = r2.selnum AND r1.rcdnum != r2.rcdnum --AND r1.corrname < r2.corrname
WHERE r.recname = r2.recname AND r.parentrecname = r1.recname
)
SELECT x.* FROM x
WHERE num_key_fields = 0
AND recname1 IN('JRNL_HEADER')
AND recname2 IN('JRNL_LN','JRNL_DRILL_VW')
ORDER BY 1,2,3
/

However, these queries may not have been run recently.  Users tend to write queries, save a modification as a new version, and then abandon the old version.

                                          Sel Rec1                    Cor Rec2                    Cor #Key
OPRID     QRYNAME                           #    # Record 1           #1     # Record             #2  Flds
--------- ------------------------------ ---- ---- ------------------ --- ---- ------------------ --- ----
          2_XX_CHI_JOURNAL_MES2_RE          1    1 JRNL_HEADER        A      2 JRNL_LN            B      0
          12300_GL_ACCOUNT_DETAIL           1    1 JRNL_HEADER        A      2 JRNL_LN            B      0
          123_DK                            1    1 JRNL_HEADER        A      2 JRNL_LN            B      0
          123_NEW                           1    1 JRNL_HEADER        A      2 JRNL_LN            B      0
          12345_ACCRUAL_JE_DETAILS          1    1 JRNL_HEADER        A      2 JRNL_LN            C      0
          12345_ACCRUAL_JE_DETAILS_V2       1    1 JRNL_HEADER        A      2 JRNL_LN            C      0
          12345_ACCRUAL_JE_DETAILS_V3       1    1 JRNL_HEADER        A      2 JRNL_LN            C      0
          12345_HARDSOFT_JE_DETAILS_V3      1    1 JRNL_HEADER        A      2 JRNL_LN            C      0
          12345_BM_CURR_ACTIVITY2           1    1 JRNL_HEADER        A      2 JRNL_LN            B      0
          AAIC_CBP_POOLS                    1    1 JRNL_HEADER        A      2 JRNL_LN            B      0
…

I demonstrated how to identify long-running PS/Queries on the process schedulers in an earlier blog post. The following query merges in that query, so that it only considers queries that have run on a process scheduler within the purge period, for which join criteria may be missing.  They are sorted by descending execution time.

REM qry_missingjoins.sql
WITH q1 as (
SELECT r.prcsinstance
, r.oprid runoprid, r.runcntlid
, DECODE(c.private_query_flag,'Y','Private','N','Public') private_query_flag
, DECODE(c.private_query_flag,'Y',r.oprid,' ') oprid
, c.qryname
, CAST(begindttm AS DATE) begindttm
, CAST(enddttm AS DATE) enddttm
, runstatus
, (CAST(NVL(enddttm,SYSDATE) AS DATE)-CAST(begindttm AS DATE))*86400 exec_Secs
FROM psprcsrqst r
  LEFT OUTER JOIN ps_query_run_cntrl c ON c.oprid = r.oprid AND c.run_cntl_id = r.runcntlid
WHERE prcsname = 'PSQUERY'
AND dbname IN(select DISTINCT dbname from ps.psdbowner)
--AND r.begindttm >= trunc(SYSDATE)-2+8/24
--AND r.begindttm <= trunc(SYSDATE)-2+19/24
), q as (
Select /*+MATERIALIZE*/ oprid, qryname
, SUM(exec_secs) exec_secs
, COUNT(*) num_execs
, COUNT(DECODE(runstatus,'9',1,NULL)) complete_execs
, COUNT(DISTINCT runoprid) runoprids
FROM q1
GROUP BY oprid, qryname
), x as (
SELECT r1.oprid, r1.qryname, r1.selnum
, r1.rcdnum rcdnum1, r1.recname recname1, r1.corrname corrname1
, r2.rcdnum rcdnum2, r2.recname recname2, r2.corrname corrname2
, (SELECT count(*) 
   FROM psqryfield qf1 --INNER JOIN psrecfielddb f1 ON f1.recname = r1.recname AND f1.fieldname = qf1.fieldname
   ,    psqryfield qf2 INNER JOIN psrecfielddb f2 ON f2.recname = r2.recname AND f2.fieldname = qf2.fieldname AND MOD(f2.useedit,2)=1
   , psqrycriteria c
   WHERE qf1.oprid = r1.oprid AND qf1.qryname = r1.qryname AND qf1.selnum = r1.selnum AND qf1.recname = r1.recname AND qf1.fldrcdnum = r1.rcdnum
   AND   qf2.oprid = r2.oprid AND qf2.qryname = r2.qryname AND qf2.selnum = r2.selnum AND qf2.recname = r2.recname AND qf2.fldrcdnum = r2.rcdnum
   AND    c.oprid = r1.oprid AND  c.qryname = r1.qryname AND  c.selnum = r1.selnum 
   AND   (  (c.lcrtselnum = r1.selnum AND c.lcrtfldnum = qf1.fldnum AND c.r1crtselnum = r2.selnum AND c.r1crtfldnum = qf2.fldnum)
         OR (c.lcrtselnum = r2.selnum AND c.lcrtfldnum = qf2.fldnum AND c.r1crtselnum = r1.selnum AND c.r1crtfldnum = qf1.fldnum))
   AND rownum = 1
  ) num_key_fields
FROM psrecdefn r
, psqryrecord r1
  INNER JOIN psqryrecord r2 ON r1.oprid = r2.oprid AND r1.qryname = r2.qryname AND r1.selnum = r2.selnum AND r1.rcdnum != r2.rcdnum --AND r1.corrname < r2.corrname
WHERE r.recname = r2.recname AND r.parentrecname = r1.recname
)
SELECT /*+LEADING(Q)*/ q.*, x.selnum
, x.rcdnum1, x.recname1, x.corrname1
, x.rcdnum2, x.recname2, x.corrname2, x.num_key_fields
FROM x
  INNER JOIN q ON q.oprid = x.oprid AND q.qryname = x.qryname
WHERE num_key_fields = 0
AND exec_secs >= 600
ORDER BY exec_secs desc

/

Now I have a list of candidate queries that have been used recently and may be missing joins that I investigate further.

                                                                                          Sel Rec1                    Cor Rec2                    Cor #Key
OPRID     QRYNAME                         EXEC_SECS  NUM_EXECS COMPLETE_EXECS  RUNOPRIDS    #    # Record 1           #1     # Record 2           #2  Flds
--------- ------------------------------ ---------- ---------- -------------- ---------- ---- ---- ------------------ --- ---- ------------------ --- ----
UKXXXXXXX AR_VENDOR_LOCATION_DETAILB         264317        361            360          1    1    1 VENDOR             A      8 VNDR_LOC_SCROL     H      0
          XX_COL_MOV_ALT_ACCT2_PERIO         193692       2096           2051         14    1    1 JRNL_HEADER        A      3 OPEN_ITEM_GL       C      0
          APC_123_LEDGER_ACTIVITY_BY_BU      151438       2959           2938         73    2    1 JRNL_HEADER        B      2 JRNL_LN            C      0
MXXXXXX   MT_AUSTRALIA_TAX_PMTS              137471         36             28          1    1    1 JRNL_HEADER        A      2 JRNL_LN            B      0
          XX_PAN_ASIA_JOURNALS_REF           135825         48             47          4    1    1 JRNL_HEADER        A      5 JRNL_OPENITM_VW    E      0
          XXX_STKCOMP_LIFE                   120537        526            523          1    1    1 JRNL_HEADER        A      2 JRNL_LN            B      0
          XXX_123_TB_LEDGER_BAL_BU           100848       2093           2044         17    3    1 JRNL_HEADER        B      2 JRNL_LN            C      0
KXXXXXX   XXX_JRNL_LIST_AUDIT_KL              99843        489            482          1    1    2 JRNL_HEADER        B      1 JRNL_DRILL_VW      A      0
          XXX_JE_ID_QUERY                     86106        156            151          1    1    1 JRNL_HEADER        A      2 JRNL_LN            C      0
          XXX_ACTIVITY_DETAILS_2              85356        336            302          5    1    1 JRNL_HEADER        A      2 JRNL_LN            B      0
…

Anyone can inspect any public queries, but you must be logged in as the owner of a private query to be able to see it.

The scripts in this article can be downloaded from GitHub davidkurtz/psscripts.

I can query who has run which queries, and how long they ran for.  Simply outer join the run control record for the PSQUERY application engine (PS_QUERY_RUN_CNTL) to the process scheduler request table (PSPRCSRQST).

REM qry_missingjoins.sql
WITH x as (
SELECT r.prcsinstance, r.oprid, r.runcntlid
,      DECODE(c.private_query_flag,'Y','Private','N','Public') private_query_flag, c.qryname
,      CAST(begindttm AS DATE) begindttm
,      CAST(enddttm AS DATE) enddttm
,      runstatus
,      (CAST(NVL(enddttm,SYSDATE) AS DATE)-CAST(begindttm AS DATE))*86400 exec_Secs
FROM   psprcsrqst r
  LEFT OUTER JOIN ps_query_run_cntrl c ON c.oprid = r.oprid AND c.run_cntl_id = r.runcntlid
WHERE  prcsname = 'PSQUERY'
AND    r.begindttm >= TRUNC(SYSDATE)-0+8/24 /*from 8am*/
AND    r.begindttm <= TRUNC(SYSDATE)-0+19/24 /*to 7pm*/
)
SELECT x.* FROM x
WHERE  exec_Secs >= 300 /*Over 5 minutes*/
ORDER BY exec_secs desc /*descending order of elapsed time*/
FETCH FIRST 50 ROWS ONLY /*top 50 ROWS ONLY*/
/

  Process                                           Private                                                                    Run     Exec
 Instance OPRID      RUNCNTLID                      Query   QRYNAME                        BEGINDTTM         ENDDTTM           Stat    Secs
--------- ---------- ------------------------------ ------- ------------------------------ ----------------- ----------------- ---- -------
 12344471 F******    ***AM_FIN_GL_AP                Public  ***AM_FIN_GL_AP                10:06:21 19.**.** 19:08:52 19.**.** 8      32551
 12344342 N******    ownxxxxxxxxxxxx                Public  ***_TRIAL_BALANCE_BY_BU_***_V2 09:41:58 19.**.** 18:20:09 19.**.** 10     31091
 12344336 N******    ojnxxxxxxxxxx                  Public  ***_TRIAL_BALANCE_BY_BU_***    09:40:27 19.**.** 16:51:11 19.**.** 10     25844
 12345209 N******    eowxxxxxxxxxxxxx               Public  ***_TRIAL_BALANCE_BY_BU_***    12:41:17 19.**.** 19:08:30 19.**.** 8      23233
 12345213 N******    iwoxxxxxxxxxxxxx               Public  ***_TRIAL_BALANCE_BY_BU_***_V2 12:41:53 19.**.** 19:08:56 19.**.** 8      23223
 12345574 B******    gl                             Private ***_GL_BJU                     14:27:32 19.**.** 19:08:59 19.**.** 8      16887
 12345681 B******    gl                             Private ***_GL_BJU                     14:51:06 19.**.** 19:09:02 19.**.** 8      15476
 12345852 W******    insolvents                     Public  ***INSOLVENTS_JRNL_DETAIL      15:24:41 19.**.** 19:09:04 19.**.** 8      13463
…
                                                                                                                                    -------
sum                                                                                                                                  268112

In this article, I look at a method to identify candidate processes for cursor sharing.  Then it is necessary to test whether cursor sharing actually is beneficial.

My example is based on nVision reports in a PeopleSoft Financials system, but the technique can be applied to other processes and is not even limited to PeopleSoft.  nVision reports example because they vary from report to report, depending upon how they are written, and the nature of the reporting trees they use.  Some nVision reports benefit from cursor sharing, others it makes little difference, and for some it is detrimental.

As always Active Session History (ASH) is your friend.  First, you need to know which ASH data relates to which process, so you need to enable PeopleSoft instrumentation (see Effective PeopleSoft Performance Monitoring), and install my psftapi package and trigger to enable instrumentation of Cobol, nVision and SQR.

                                            Cursor           Avg StdDev    Avg StdDev    Avg StdDev    Avg StdDev    Avg StdDev   Avg
                                    Cursor  Sharing   Num   Elap   Elap    ASH    ASH ResMgr ResMgr  Parse  Parse    CPU    CPU   SQL  Avg
OPRID      RUNCNTLID                Sharing Setting Procs   Secs   Secs   Secs   Secs   Secs   Secs   Secs   Secs   Secs   Secs   IDs  FMS
---------- ------------------------ ------- ------- ----- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ----- ----
…
NVISION    NVS_RPTBOOK_1            EXACT   FORCE      33   3691   1062   2687   1071    741    702   2232    932   1791    479   238   16
                                    FORCE   FORCE      13   1623    377    664    394    357    373     43     19    353     85    11   12
…
           NVS_RPTBOOK_2            EXACT   EXACT      39   3696   1435   3316   1431   1038    927   1026    661   2042    611   137   27
                                    FORCE   EXACT       7   4028   2508   3676   2490   1333   1563     17     12   2275    939    19   19

It is always worth looking at individual process executions.  

• The second script (high_parse_nvision.sql) runs a similar query, but it reports each process individually.

We can see that cursor sharing was introduced on 31st July.  Even though there is a lot of variance in runtimes due to variances in data volumes and other system activities, it is clear that cursor sharing is beneficial for this process.

                                                                                                                                       Cursor
                              Process R                                              Elap    ASH ResMgr  Parse    CPU   SQL        ASH Sharing Cursor  Parse   S:F
OPRID      RUNCNTLID         Instance S  MIN_SAMPLE_TIME      MAX_SAMPLE_TIME        Secs   Secs   Secs   Secs   Secs   IDs  FMS  Samp Setting Sharing     % Ratio
---------- ---------------- --------- -- -------------------- -------------------- ------ ------ ------ ------ ------ ----- ---- ----- ------- ------- ----- -----
NVISION    NVS_RPTBOOK_1     12447036 9  21.07.2024 21.03.25  21.07.2024 21.47.02    2645   1543    174   1297   1277   145   17   150 FORCE   EXACT      84   8.5
                             12452568 9  22.07.2024 21.02.04  22.07.2024 21.41.03    2373   1413    123   1188   1250   133   13   138 FORCE   EXACT      84  10.2
                             12458455 9  23.07.2024 21.07.15  23.07.2024 21.52.25    2759   1587     51   1372   1423   152   14   155 FORCE   EXACT      86  10.9
                             12465042 9  24.07.2024 20.58.08  24.07.2024 21.50.19    3154   2100    369   1782   1557   201   18   205 FORCE   EXACT      85  11.2
                             12471732 9  25.07.2024 21.25.34  25.07.2024 22.46.32    4885   3861   1946   3318   1843   333   14   377 FORCE   EXACT      86  23.8
                             12477118 9  26.07.2024 22.41.07  26.07.2024 23.26.07    2730   1791    113   1526   1586   173   14   174 FORCE   EXACT      85  12.4
                             12479163 9  27.07.2024 23.13.40  28.07.2024 00.01.23    2917   1688    161   1513   1260   156   14   164 FORCE   EXACT      90  11.1
                             12480710 9  28.07.2024 21.47.44  28.07.2024 22.29.08    2529   1586    205   1320   1238   149   12   154 FORCE   EXACT      83  12.4
                             12487744 9  29.07.2024 21.47.44  29.07.2024 22.51.05    3834   2815    797   2292   1843   248   16   273 FORCE   EXACT      81  15.5
                             12495417 9  30.07.2024 22.57.13  30.07.2024 23.46.48    3015   2084    307   1869   1592   200   15   203 FORCE   EXACT      90  13.3
…
                             12501446 9  31.07.2024 21.27.51  31.07.2024 21.51.18    1478    461     72     31    389    10   11    45 FORCE   FORCE       7   0.9
                             12507769 9  01.08.2024 21.44.01  01.08.2024 22.05.56    1387    357    100     21    246     7    8    34 FORCE   FORCE       6   0.9
                             12513527 9  02.08.2024 21.02.27  02.08.2024 21.27.47    1538    635    236     31    400    11   12    62 FORCE   FORCE       5   0.9
                             12515368 9  03.08.2024 22.12.50  03.08.2024 22.40.03    1682    686    143     51    532     9   10    67 FORCE   FORCE       7   0.9
                             12516959 9  04.08.2024 21.38.01  04.08.2024 21.57.00    1263    266            51    266     8    9    26 FORCE   FORCE      19   0.9
                             12522863 9  05.08.2024 21.14.36  05.08.2024 21.48.40    2082   1167    727     51    430    14   13   114 FORCE   EXACT       4   1.1
                             12529263 9  06.08.2024 21.02.59  06.08.2024 21.39.47    2223   1300    900     51    389    12   13   126 FORCE   FORCE       4   0.9
                             12535782 9  07.08.2024 21.08.23  07.08.2024 21.37.48    1774    974    585     52    379    12   13    94 FORCE   FORCE       5   0.9
                             12541727 9  08.08.2024 21.07.43  08.08.2024 21.40.54    2014   1085    809     51    276    16   17   106 FORCE   FORCE       5   0.9
                             12547232 9  09.08.2024 21.27.28  09.08.2024 21.47.08    1213    236            31    236     8    9    23 FORCE   FORCE      13   0.9
…

1. Introduction
2. What happens during SQL Parse?  What is a 'hard' parse?  What is a 'soft' parse?  The additional overhead of a hard parse.
3. How to set CURSOR_SHARING for specific scheduled processes
4. How to identify candidate processes for cursor sharing.

If you cannot remove the literal values in the application SQL code, then another option is to enable cursor sharing and have Oracle do it.  Literals are converted to bind variables before the SQL is parsed; thus, statements that only differ in the literal values can be treated as the same statement.  If the statement is still in the shared pool, it is not fully reparsed and uses the same execution plan.

Oracle cautions against using cursor sharing as a long-term fix: "The best practice is to write sharable SQL and use the default of EXACT for CURSOR_SHARING… FORCE is not meant to be a permanent development solution."

I realise that I am now about to suggest doing exactly that, but only for specific processes, and never for the whole database.  Over the years, I have tested enabling cursor sharing at database level a few times and have never had a good experience.  

However, enabling cursor sharing in a few carefully selected processes can be beneficial.  It can save some of the time spent in the database on hard parse, but will have no effect on the time that PeopleSoft processes spend generating the SQL.

CREATE OR REPLACE TRIGGER sysadm.set_prcs_sess_parm
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
FOLLOWS sysadm.psftapi_store_prcsinstance 
WHEN (new.runstatus = 7 AND old.runstatus != 7 AND new.prcstype != 'PSJob')
DECLARE
  l_cmd VARCHAR2(100 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   sysadm.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 NULLIF(i.parmvalue,' ') IS NOT NULL THEN
      l_cmd := 'ALTER SESSION '||i.keyword||' '||l_delim||i.param_name||l_delim||l_op||i.parmvalue;
      EXECUTE IMMEDIATE l_cmd;
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN …
END;
/

INSERT INTO sysadm.ps_prcs_sess_parm (prcstype, prcsname, oprid, runcntlid, keyword, param_name, parmvalue)
with x as (
          select 'inmemory_query' param_name, 'SET' keyword, 'DISABLE' parmvalue from dual --Disable inmemory 
union all select 'cursor_sharing'           , 'SET' keyword, 'FORCE'             from dual --to mitigate excessive parse
), y as (
  select prcstype, prcsname, ' ' oprid, ' ' runcntlid
  from	 ps_prcsdefn
  where  prcsname IN('GLPOCONS')
)
select  y.prcstype, y.prcsname, y.oprid, y.runcntlid, x.keyword, x.param_name, x.parmvalue
from    x,y
/

CREATE OR REPLACE TRIGGER gfc_jrnl_ln_gl_jedit2
FOR UPDATE OF process_instance ON ps_jrnl_ln
WHEN (new.process_instance != 0 and old.process_instance = 0)
COMPOUND TRIGGER
  l_process_instance INTEGER;
  l_runcntlid VARCHAR2(30);
  l_module VARCHAR2(64);
  l_action VARCHAR2(64);
  l_prcsname VARCHAR2(12);
  l_cursor_sharing CONSTANT VARCHAR2(64) := 'ALTER SESSION SET cursor_sharing=FORCE';

  AFTER EACH ROW IS 
  BEGIN
    l_process_instance := :new.process_instance;
  END AFTER EACH ROW;
  
  AFTER STATEMENT IS 
  BEGIN
    IF l_process_instance != 0 THEN
      dbms_application_info.read_module(l_module,l_action);
      IF l_module like 'PSAE.GL_JEDIT2.%' THEN --check this session is instrumented as being GL_JEDIT2
        --check process instance being set is a running FSPCCURR process
        SELECT prcsname, runcntlid
        INTO l_prcsname, l_runcntlid
        FROM psprcsrqst
        WHERE prcsinstance = l_process_instance
        AND prcsname IN('FSPCCURR','GLPOCONS')
        AND runstatus = '7';
        
        l_module := regexp_substr(l_module,'PSAE\.GL_JEDIT2\.[0-9]+',1,1)
                    ||':'||l_prcsname||':PI='||l_process_instance||':'||l_runcntlid;
        dbms_application_info.set_module(l_module,l_action);
        EXECUTE IMMEDIATE l_cursor_sharing;
      END IF;
    END IF;
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
      NULL; --cannot find running fspccurr/glpocons with this process instance number
    WHEN OTHERS THEN
      NULL;
  END AFTER STATEMENT;

END gfc_jrnl_ln_gl_jedit2;
/

This is the second in a series of 4 posts about the selective use of cursor sharing in scheduled processes in PeopleSoft.

1. Introduction
2. What happens during SQL Parse?  What is a 'hard' parse?  What is a 'soft' parse?  The additional overhead of a hard parse.
3. How to set CURSOR_SHARING for specific scheduled processes.
4. How to identify candidate processes for cursor sharing.

To understand why cursor sharing can be beneficial it is necessary to understand

• What happens when Oracle parses and executes a SQL statement?.
• How some PeopleSoft processes dynamically construct SQL statements

SQL Processing: 'Hard' Parse -v- 'Soft' Parse

SQL parse processing is set out in various places in the Oracle database documentation

• SQL Tuning Guide -> Optimizer Fundamentals -> SQL Processing.
• Tuning Guide: Improving Real-World Performance Through Cursor Sharing.
• Reference: Initialisation Parameter Cursor Sharing.
• This diagram appears in the Oracle documentation.

When a statement is submitted to the database it goes through a number of stages of parsing before it is executed. 

• Syntax Check: Is the statement syntactically valid?
• Semantic Check:  Is the statement meaningful?  Do the referenced objects exist and is the user allowed to access them?
• SGA Check: Does the statement already exist in the shared SQL area?  

The database looks for an exact matching statement in the shared SQL area. If it is not found, the database must perform additional tasks called 'hard' parsing.  The stages performed up to this point are referred to as 'soft parsing'.

• Generation of the optimal execution plan
• Row Source Generation - The execution plan is used to generate an iterative execution plan that is usable by the rest of the database.

Thus the Oracle Database must perform a 'hard' parse at least once for every unique DML statement.

What is Cursor Sharing?

A cursor is a name or handle for a private SQL area that contains session-specific information about a statement, including bind variables, state information and result sets.  A cursor in the private area points to the shared SQL area in the library cache that contains the parse tree and execution plan for a statement.  Multiple private areas can reference a single shared SQL area.  This is known as cursor sharing.

• See also SQL Tuning Guide -> Improving Real-World Performance Through Cursor Sharing -> CURSOR_SHARING and Bind Variable Substitution

The database allows only textually identical statements to share a cursor. By default, the CURSOR_SHARING parameter is set to EXACT, and thus is disabled.  "The optimizer generates a plan for each statement based on the literal value."
When CURSOR_SHARING is set to FORCE, the database replaces literal values with system-generated variables.  The database still only exactly matches statements, but after the literal values have been substituted, thus giving the appearance of matching statements that differ only by their literal values.  "For statements that are identical after bind variables replace the literals, the optimizer uses the same plan. Using this technique, the database can sometimes reduce the number of parent cursors in the shared SQL area." The database only performs a soft parse.  
In systems, such as PeopleSoft, that generate many distinct statements, cursor sharing can significantly reduce hard parse, and therefore CPU and time spent on it.

Sources of Hard Parse in PeopleSoft

PeopleSoft has a deserved reputation for suffering from high volumes of SQL hard parse.  Generally, the cause of this is dynamically generated code.  Often each statement has different literal values. 

• In Application Engine, %BIND() resolves to a literal value rather than bind variable in the resulting SQL statement unless the ReUseStatement attribute is enabled.  The problem is that it is disabled by default, and there are limitations to when it can be set.
• Dynamic statements in COBOL processes.  This is effectively the same behaviour as Application Engine, but here the dynamic generation of SQL is hard-coded in the COBOL from a combination of static fragments and configuration data. PeopleSoft COBOL programs generally just embed literal values in such statements because it is easier than creating dynamic SQL statements with possibly varying numbers of bind variables.
• In nVision where 'dynamic selectors' and 'use literal values' tree performance options are selected.  These settings are often preferable because the resulting SQL statements can make effective use of Bloom filters and Hybrid Column Compression (on Exadata).  The penalty is that it can lead to more hard parse operations.

ReUseStatement -v- Cursor  Sharing

Of course, it would be better if PeopleSoft SQL used bind variables more often, rather than literal values. 

In Application Engine, if the ReUseStatement attribute is set on a step, then bind variables in Application Engine remain bind variables in the resulting SQL and are not converted back to literals.  

This can reduce both the amount of time Application Engine spends dynamically generating SQL statements before submitting them to the database as well as the time the database spends parsing them (see Minimising Parse Time in Application Engine with ReUseStatement). 

However, this attribute is not set by default on newly created Application Engine steps in Application Designer.  This was to maintain backward compatibility with programs created in earlier versions of PeopleTools.

However, there are restrictions to where it cannot be used.  Most commonly because %BIND(…NOQUOTES) has been used to dynamically generate part of the statement based on configuration data.

Over the years, PeopleSoft development has got much better at setting this attribute where possible in the delivered application code.  Nonetheless, there are still places where it could be added.  

See also:

• Hinting Dynamically Generated SQL in Application Engine
• PeopleTools -> Development Tools -> Application Engine -> Reusing Statements

However, there are some considerations before we add it ourselves.

• ReUseStatement cannot be introduced across the board, but only on steps that meet certain criteria set out in the documentation.  It doesn't work when dynamic code is generated with %BIND(…,NOQUOTES), or if a %BIND() is used in a SELECT clause.  Worse, setting this attribute incorrectly can cause the application to function incorrectly.  So each change has to be tested carefully.
• When a customer sets the ReUseStatement attribute in the delivered code, it is a customisation to an Application Engine step that has to be migrated using Application Designer.  It then has to be maintained to ensure that subsequent PeopleSoft releases and patches do not revert it.
• There is no equivalent option for PeopleSoft COBOL, SQR, or nVision.  The way that SQL is generated in each is effectively hard-coded.

Recommendation: It is not a case of either ReUseStatement or cursor sharing.  It may be both.  If you are writing your own Application Engine code, or customising delivered code anyway, then it is usually advantageous to set ReUseStatement where you can.  You will save non-database execution time as well as database time because you are then using bind variables, and Application Engine does not have to spend time generating the text of a new SQL statement with new literal values for every execution.  You may still benefit from cursor sharing for statements where you cannot set ReUseStatement.  

However, as you will see in the last article in this series, cursor sharing is not always effective, you have to test.

This is the first in a series of 4 posts about the selective use of cursor sharing in scheduled processes in PeopleSoft.

1. Introduction
2. What happens during SQL Parse?  What is a 'hard' parse?  What is a 'soft' parse?  The additional overhead of a hard parse.
3. How to set CURSOR_SHARING for specific scheduled processes.
4. How to identify candidate processes for cursor sharing.

One of the challenges that PeopleSoft gives to an Oracle database is that many processes dynamically generate many SQL statements.  They usually have different literal values each time, some may also reference different non-shared instances of temporary records.  Each statement must be fully parsed by the Oracle statements.  That consumes CPU and takes time.  Oracle has already recommended using bind variables instead of literal values for that reason.  

It would generally be better if the SQL used bind variables rather than literal values.  In Application Engine, one option is to set the ReUseStatement attribute on the steps in question.  Then bind variables in Application Engine remain bind variables in the SQL and are not converted to literals.  This can reduce parse time (see Minimising Parse Time in Application Engine with ReUseStatement). However, this attribute is not set by default.  This is partly for legacy PeopleTools reasons, and partly due to the pitfalls discussed below.  Over the years, Oracle has got much better at setting this attribute where possible in delivered PeopleSoft application code.  There are still many places where it could still be added.  However, there are some considerations before we add it ourselves.

• When a customer sets the ReUseStatement attribute in the delivered code, it is a customisation that has to be migrated using Application Designer.  It has to be maintained to ensure that subsequent releases and patches do not revert it.
• ReUseStatement cannot be introduced across the board, but only on steps that meet certain criteria.  It doesn't work when dynamic code is generated with %BIND(…,NOQUOTES), or if a %BIND() is used in a SELECT clause.  Worse, setting this attribute when it should not be can cause the application to function incorrectly.  So each change has to be tested carefully.

If you can't remove the literal values in the SQL code, then another option is to introduce cursor sharing in Oracle.  Essentially, all literals are converted to bind variables before the SQL is parsed, and thus statements that only differ in the literal values can be treated as the same statement.  If the statement is still in the shared pool, then it is not fully reparsed and uses the same execution plan.

Oracle cautions against using cursor sharing as a long-term fix: "The best practice is to write sharable SQL and use the default of EXACT for CURSOR_SHARING… FORCE is not meant to be a permanent development solution."

I realise that I am now about to suggest doing exactly that, but only for specific processes, and never for the whole database.  I have tested enabling cursor sharing at database level a few times and have never had a good experience.

It is easy to set a session setting for a specific process run on the PeopleSoft process scheduler.   The first thing a process does is to set the status of its own request record to 7, indicating that it is processing.  

A trigger can be created on this transition that will then be executed in the session of the process.  I initially developed this technique to set other session settings for nVision reports.  I introduced a database table to hold a list of the settings, and the trigger matches this metadata to the processes being run by up for 4 attributes: process type, process name, operation and run control.

• see Setting Oracle Session Parameters for Specific Process Scheduler Processes 
• The scripts are available on GitHub
• Trigger: set_prcs_sess_parm_trg.sql.  The trigger expects that psftapi.sql has also been installed.
• Example metadata set_prcs_sess_parm.sql
  

CREATE OR REPLACE TRIGGER sysadm.set_prcs_sess_parm
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
FOLLOWS sysadm.psftapi_store_prcsinstance 
WHEN (new.runstatus = 7 AND old.runstatus != 7 AND new.prcstype != 'PSJob')
DECLARE
  l_cmd VARCHAR2(100 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   sysadm.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 NULLIF(i.parmvalue,' ') IS NOT NULL THEN
      l_cmd := 'ALTER SESSION '||i.keyword||' '||l_delim||i.param_name||l_delim||l_op||i.parmvalue;
      EXECUTE IMMEDIATE l_cmd;
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN …
END;
/

INSERT INTO sysadm.ps_prcs_sess_parm (prcstype, prcsname, oprid, runcntlid, keyword, param_name, parmvalue)
with x as (
          select 'inmemory_query' param_name, 'SET' keyword, 'DISABLE' parmvalue from dual --Disable inmemory 
union all select 'cursor_sharing'           , 'SET' keyword, 'FORCE'             from dual --to mitigate excessive parse
), y as (
  select  prcstype, prcsname, ' ' oprid, ' ' runcntlid
  from	  ps_prcsdefn
  where   prcsname IN('GLPOCONS')
)
select  y.prcstype, y.prcsname, y.oprid, y.runcntlid, x.keyword, x.param_name, x.parmvalue
from    x,y
/

UPDATE PS_JRNL_LN SET JRNL_LINE_STATUS='0', PROCESS_INSTANCE=:1 
WHERE BUSINESS_UNIT=:2 AND JOURNAL_ID=:3 AND JOURNAL_DATE=TO_DATE(:4,'YYYY-MM-DD') AND UNPOST_SEQ=0

CREATE OR REPLACE TRIGGER gfc_jrnl_ln_gl_jedit2
FOR UPDATE OF process_instance ON ps_jrnl_ln
WHEN (new.process_instance != 0 and old.process_instance = 0)
COMPOUND TRIGGER
  l_process_instance INTEGER;
  l_runcntlid VARCHAR2(30);
  l_module VARCHAR2(64);
  l_action VARCHAR2(64);
  l_prcsname VARCHAR2(12);
  l_cursor_sharing CONSTANT VARCHAR2(64) := 'ALTER SESSION SET cursor_sharing=FORCE';

  AFTER EACH ROW IS 
  BEGIN
    l_process_instance := :new.process_instance;
    --dbms_output.put_line('process_instance='||l_process_instance);
  END AFTER EACH ROW;
  
  AFTER STATEMENT IS 
  BEGIN
    IF l_process_instance != 0 THEN
      dbms_application_info.read_module(l_module,l_action);
      --dbms_output.put_line('module='||l_module||',action='||l_action);
      IF l_module like 'PSAE.GL_JEDIT2.%' THEN --check this session is instrumented as being GL_JEDIT
        --check process instance being set is a running FSPCCURR process
        SELECT prcsname, runcntlid
        INTO l_prcsname, l_runcntlid
        FROM   psprcsrqst
        WHERE  prcsinstance = l_process_instance AND runstatus = '7';
        
        l_module := regexp_substr(l_module,'PSAE\.GL_JEDIT2\.[0-9]+',1,1)||':'||l_prcsname||':PI='||l_process_instance||':'||l_runcntlid;
        dbms_application_info.set_module(l_module,l_action);
        --dbms_output.put_line('set module='||l_module||',action='||l_action);
        EXECUTE IMMEDIATE l_cursor_sharing;
        --dbms_output.put_line('set cursor_sharing');
      END IF;
    END IF;
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN 
      --dbms_output.put_line('Cannot find running '||l_prcsname||' process instance '||l_process_instance);
      NULL; --cannot find running process instance number
    WHEN OTHERS THEN
      --dbms_output.put_line('Other Error:'||sqlerrm);
      NULL;
  END AFTER STATEMENT;

END gfc_jrnl_ln_gl_jedit2;
/

I have been working on a PeopleSoft Financials application that we have converted from a stand-alone database to be the only pluggable database (PDB) in an Oracle 19c container database (CDB).  We have been getting shared pool errors in the PDB that lead to ORA-4031 errors in the PeopleSoft application.  

I have written a longer version of this article on my Oracle blog, but here are the main points.

PeopleSoft systems dynamically generate lots of non-shareable SQL code.  This leads to lots of parse and consumes more shared pool.  ASMM can respond by shrinking the buffer cache and growing the shared pool.  However, this can lead to more physical I/O and degrade performance and it is not beneficial for the database to cache dynamic SQL statements that are not going to be executed again.  Other parse-intensive systems can also exhibit this behaviour.

In PeopleSoft, I normally set DB_CACHE_SIZE and SHARED_POOL_SIZE to minimum values to stop ASMM shuffling too far in either direction.  With a large SGA, moving memory between these pools can become a performance problem in its own right.  

We removed SHARED_POOL_SIZE, DB_CACHE_SIZE and SGA_MIN_SIZE settings from the PDB.  The only SGA parameters set at PDB level are SGA_TARGET and INMEMORY_SIZE.  

SHARED_POOL_SIZE and DB_CACHE_SIZE are set as I usually would for PeopleSoft, but at CDB level to guarantee a minimum buffer cache size.  

This is straightforward when there is only one PDB in the CDB.   I have yet to see what happens when I have another active PDB with a non-PeopleSoft system and a different kind of workload that puts less stress on the shared pool and more on the buffer cache.

• SGA_TARGET "specifies the total size of all SGA components".  Use this parameter to control the memory usage of each PDB.  The setting at CDB must be at least the sum of the settings for each PDB.
• Recommendations:
• Use only this parameter at PDB level to manage the memory consumption of the PDB.
• In a CDB with only a single PDB, set SGA_TARGET to the same value at CDB and PDB levels.  
• Therefore, where there are multiple PDBs, SGA_TARGET at CDB level should be set to the sum of the setting for each PDB.  However, I haven't tested this yet.
• There is no recommendation to reserve SGA for use by the CDB only, nor in my experience is there any need so to do.
• SHARED_POOL_SIZE sets the minimum amount of shared memory reserved to the shared pool.  It can optionally be set in a PDB.  
• Recommendation: However, do not set SHARED_POOL_SIZE at PDB level.  It can be set at CDB level.
• DB_CACHE_SIZE sets the minimum amount of shared memory reserved to the buffer cache. It can optionally be set in a PDB.  
• Recommendation: However, do not set DB_CACHE_SIZE at PDB level.  It can be set at CDB level.
• SGA_MIN_SIZE has no effect at CDB level.  It can be set at PDB level at up to half of the manageable SGA
• Recommendation: However, do not set SGA_MIN_SIZE.
• INMEMORY_SIZE: If you are using in-memory query, this must be set at CDB level in order to reserve memory for the in-memory store.  The parameter defaults to 0, in which case in-memory query is not available.  The in-memory pool is not managed by Automatic Shared Memory Management (ASMM), but it does count toward the total SGA used in SGA_TARGET.
• Recommendation: Therefore it must also be set in the PDB where in-memory is being used, otherwise we found(contrary to the documetntation) that the parameter defaults to 0, and in-memory query will be disabled in that PDB.

• A-04031 on Multitenant Database with Excessive Amounts of KGLH0 and / or SQLA Memory and Parameter SHARED_POOL_SIZE or SGA_MIN_SIZE Set at the PDB Level (Doc ID 2590172.1) – December 2022, Updated April 2023
• This one says “Remove the PDB-level SHARED_POOL_SIZE and/or SGA_MIN_SIZE initialization parameters. The only SGA memory sizing parameter that Oracle recommends setting at the PDB level is SGA_TARGET.”

• About memory configuration parameter on each PDBs (Doc ID 2655314.1) – Nov 2023
• “As a best practice, please do not to set SHARED_POOL_SIZE and DB_CACHE_SIZE on each PDBs and please manage automatically by setting SGA_TARGET.”
• "This best practice is confirmed by development in Bug 30692720"
• Bug 30692720 discusses how the parameters are validated.  Eg. "Sum(PDB sga size) > CDB sga size"
• Bug 34079542: "Unset sga_min_size parameter in PDB."

• on-line PS/Queries,
• nVision reports run through the PIA,

• PS/Queries run on the process scheduler using the PSQUERY application engine. A 4-hour maximum runtime limit is defined.

• nVision through the 3-tier nVision client

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'PSFT_PLAN', 'NVISION_GROUP', 'nVision Reports.'
    ,mgmt_p5 => 100
    ,parallel_degree_limit_p1=>2
  );

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'PSFT_PLAN', 'PSQUERY_ONLINE_GROUP'
    ,mgmt_p6 => 90
    ,parallel_queue_timeout=>900
    ,pq_timeout_action=>'RUN'
  );

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'PSFT_PLAN', 'PSQUERY_BATCH_GROUP'
    ,mgmt_p6 => 1
    ,switch_group => 'CANCEL_SQL'
    ,switch_time => 14400
    ,switch_estimate => TRUE 
    ,switch_for_call => TRUE
    );

Sometimes, performance analysis will turn up a problem SQL query that is probably a PS/Query. However, I need to know which PS/Query it is should I wish to alter it or talk to the user who wrote it. 

It is quite easy to spot SQL queries that are generated from queries defined in the PS/Query tool. These are typical characteristics:

• Single character row source aliases (eg. A, B, D) 
• The same row source with a suffix 1 (eg. D1) for query security records.
• Effective date/sequence subqueries are always correlated back to the same table.
• Order by column position number rather than column names or aliases.
  

SELECT A.EMPLID, A.ATTENDANCE, A.COURSE, B.DESCR, D.NAME, A.SESSION_NBR,
TO_CHAR(A.STATUS_DT,'YYYY-MM-DD'),B.COURSE
FROM PS_TRAINING A, PS_COURSE_TBL B, PS_PERSONAL_DTA_VW D, PS_PERS_SRCH_QRY D1
WHERE D.EMPLID = D1.EMPLID
AND D1.ROWSECCLASS = 'HCDPALL'
AND ( A.COURSE = :1
AND A.ATTENDANCE IN ('S','W')
AND A.COURSE = B.COURSE
AND A.EMPLID = D.EMPLID )

The text of a PS/Query is not stored in the database.  Instead, as with other objects in PeopleSoft, it is held as various rows in PeopleTools tables.  The PSQRY% tables are used to generate the SQL on demand.  We can query these tables to identify the query.  

REM findqry.sql
REM (c)Go-Faster Consultancy 2012

SELECT a.oprid, a.qryname
FROM   psqryrecord a
,      psqryrecord b
,      psqryrecord d
WHERE  a.oprid = b.oprid
AND    a.qryname = b.qryname
AND    a.oprid = d.oprid
AND    a.qryname = d.qryname
AND    a.corrname = 'A'
AND    a.recname = 'TRAINING'
AND    b.corrname = 'B'
AND    b.recname = 'COURSE_TBL'
AND    d.corrname = 'D'
AND    d.recname = 'PERSONAL_DTA_VW'
/

OPRID                          QRYNAME
------------------------------ ------------------------------
                               TRN002__SESSION_ROSTER
                               TRN003__COURSE_WAITING_LIST

Writing the query on PSQRYRECORD to find queries, which always is slightly different each time, is quite boring.  So I have written a script that will dynamically generate the SQL to identify a PS/Query.

SQL_ID c3h6vf2w5fxgp
--------------------
SELECT …
FROM PSTREELEAF B, PSTREENODE C, PS_OPER_UNIT_TBL A, PS_PRODUCT_TBL G 
…
UNION SELECT …
FROM PSTREENODE D,PS_TREE_NODE_TBL E, PSTREELEAF F 
…

--------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                                   | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------------------
|  *  7 |        INDEX STORAGE FAST FULL SCAN         | PSBPSTREELEAF    |   426K|    19M|       |  1178   (1)| 00:00:01 |
|    10 |          TABLE ACCESS BY INDEX ROWID BATCHED| PS_PRODUCT_TBL   |     1 |    41 |       |     3   (0)| 00:00:01 |
|  * 11 |           INDEX RANGE SCAN                  | PS_PRODUCT_TBL   |     1 |       |       |     2   (0)| 00:00:01 |
|  * 14 |              INDEX RANGE SCAN (MIN/MAX)     | PS_PRODUCT_TBL   |     1 |    21 |       |     2   (0)| 00:00:01 |
|  * 15 |       TABLE ACCESS STORAGE FULL             | PSTREENODE       |   135K|  5709K|       |   663   (1)| 00:00:01 |
|  * 17 |       INDEX STORAGE FAST FULL SCAN          | PS_OPER_UNIT_TBL |  1791 | 35820 |       |     4   (0)| 00:00:01 |
|  * 20 |       INDEX RANGE SCAN (MIN/MAX)            | PS_PSTREENODE    |     1 |    33 |       |     3   (0)| 00:00:01 |
|  * 23 |       INDEX RANGE SCAN (MIN/MAX)            | PSAPSTREELEAF    |     1 |    32 |       |     3   (0)| 00:00:01 |
|  * 26 |       INDEX RANGE SCAN (MIN/MAX)            | PS_OPER_UNIT_TBL |     1 |    20 |       |     2   (0)| 00:00:01 |
|    33 |          TABLE ACCESS INMEMORY FULL         | PS_TREE_NODE_TBL | 35897 |  1647K|       |     6   (0)| 00:00:01 |
|  * 35 |          TABLE ACCESS STORAGE FULL          | PSTREENODE       |   167K|  9670K|       |   663   (1)| 00:00:01 |
|- * 36 |       INDEX RANGE SCAN                      | PS_PSTREELEAF    |     1 |    39 |       |  1267   (1)| 00:00:01 |
|    37 |      INDEX STORAGE FAST FULL SCAN           | PS_PSTREELEAF    |   480K|    17M|       |  1267   (1)| 00:00:01 |
|  * 40 |       INDEX RANGE SCAN (MIN/MAX)            | PS_PSTREENODE    |     1 |    33 |       |     3   (0)| 00:00:01 |
|  * 43 |       INDEX RANGE SCAN (MIN/MAX)            | PS_TREE_NODE_TBL |     1 |    28 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

…
   7 - SEL$1 / B@SEL$1
  10 - SEL$1 / G@SEL$1
  11 - SEL$1 / G@SEL$1
…
  15 - SEL$1 / C@SEL$1
  17 - SEL$1 / A@SEL$1
…
  33 - SEL$6 / E@SEL$6
  35 - SEL$6 / D@SEL$6
  36 - SEL$6 / F@SEL$6
  37 - SEL$6 / F@SEL$6
…

I use this query on DBA_HIST_SQL_PLAN to extract the tables that have single-character row source aliases that correspond to PeopleSoft records, and put them into PLAN_TABLE. I use this table because it is delivered by Oracle as a global temporary table, so it is always there and I can make use of it even if I only have read-only access.

INSERT INTO plan_table (object_name, object_alias) 
with p as ( --plan lines with single letter aliases
SELECT DISTINCT object_owner, object_type, object_name, regexp_substr(object_alias,'[[:alpha:]]',2,1) object_alias
from dba_hist_sql_plan p
, ps.psdbowner d
where p.sql_id = '&&sql_id' --put SQL ID here--
and p.object_name IS NOT NULL
and p.object_owner = d.ownerid
and regexp_like(object_alias,'"[[:alpha:]]"') --single character aliases
), r as ( --PeopleSoft table records and the table name
select r.recname, DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) sqltablename
from psrecdefn r
where r.rectype = 0 --PeopleSoft table records
)
select r.recname, object_alias --referenced table
from p, r
where p.object_type like 'TABLE%'
and p.object_name = r.sqltablename
union --a query plan may reference an index and not the table
select r.recname, object_alias --table for referenced index
from p, r
, all_indexes i
where p.object_type like 'INDEX%'
and i.index_name = p.object_name
and i.owner = p.object_owner
and i.table_name = r.sqltablename
order by 2,1
/

RECNAME         O
--------------- -
OPER_UNIT_TBL   A
PSTREELEAF      B
PSTREENODE      C
PSTREENODE      D
TREE_NODE_TBL   E
PSTREELEAF      F
PRODUCT_TBL     G

Next, I can run this anonymous PL/SQL block to dynamically build the SQL query on PSQRYRECORD (one reference for every table) and execute it to find the matching PS/Queries

DECLARE 
  l_sep1 VARCHAR2(20);
  l_sep2 VARCHAR2(20);
  l_counter INTEGER := 0;
  l_sql CLOB := 'SELECT r1.oprid, r1.qryname';
  l_where CLOB;
  
  TYPE t_query IS RECORD (oprid VARCHAR2(30), qryname VARCHAR2(30));
  TYPE a_query IS TABLE OF t_query INDEX BY PLS_INTEGER;
  l_query a_query;
BEGIN
  FOR i IN(
    SELECT *
    FROM plan_table
    ORDER BY object_alias
  ) LOOP
    l_counter := l_counter + 1;
    dbms_output.put_line(i.object_alias||':'||i.object_name);
    IF l_counter = 1 THEN
      l_sep1 := ' FROM ';
      l_sep2 := ' WHERE ';
    ELSE
      l_sep1 := ' ,';
      l_sep2 := ' AND ';
      l_where := l_where||' AND r1.oprid = r'||l_counter||'.oprid AND r1.qryname = r'||l_counter||'.qryname';
    END IF;
    l_sql := l_sql||l_sep1||'psqryrecord r'||l_counter;
    l_where := l_where||l_sep2||'r'||l_counter||'.corrname = '''||i.object_alias||''' AND r'||l_counter||'.recname = '''||i.object_name||'''';
  END LOOP;
  l_sql := l_sql||l_where||' ORDER BY 1,2';
  dbms_output.put_line(l_sql);

  EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_query;

  FOR indx IN 1 .. l_query.COUNT
  LOOP
    DBMS_OUTPUT.put_line (indx||':'||l_query(indx).oprid||'.'||l_query(indx).qryname);
  END LOOP;
END;
/

The seven records found in my execution plan become a query of PSQRYRECORD 7 times, one for each record, joined on operator ID and query name.

SELECT r1.oprid, r1.qryname 
FROM psqryrecord r1 ,psqryrecord r2 ,psqryrecord r3 ,psqryrecord r4 ,psqryrecord r5 ,psqryrecord r6 ,psqryrecord r7 
WHERE r1.corrname = 'A' AND r1.recname = 'OPER_UNIT_TBL'
AND r1.oprid = r2.oprid AND r1.qryname = r2.qryname AND r2.corrname = 'B' AND r2.recname = 'PSTREELEAF' 
AND r1.oprid = r3.oprid AND r1.qryname = r3.qryname AND r3.corrname = 'C' AND r3.recname = 'PSTREENODE' 
AND r1.oprid = r4.oprid AND r1.qryname = r4.qryname AND r4.corrname = 'D' AND r4.recname = 'PSTREENODE' 
AND r1.oprid = r5.oprid AND r1.qryname = r5.qryname AND r5.corrname = 'E' AND r5.recname = 'TREE_NODE_TBL' 
AND r1.oprid = r6.oprid AND r1.qryname = r6.qryname AND r6.corrname = 'F' AND r6.recname = 'PSTREELEAF' 
AND r1.oprid = r7.oprid AND r1.qryname = r7.qryname AND r7.corrname = 'G' AND r7.recname = 'PRODUCT_TBL' 
ORDER BY 1,2

…
3: .PS_TREE_PRODUCT
4: .QUERY_PRODUCT_TREE
5: .RM_TREE_PRODUCT
6:XXXXXX.PS_TREE_PRODUCT_XX
…

I wrote about controlling the operating system priority of processes in PeopleSoft Tuxedo domains in Chapters 13 of 14 of PeopleSoft for the Oracle DBA, but I think it is worth a note here.

PSPRCSRV        SRVGRP=BASE
                SRVID=101
                MIN=1
                MAX=1
                RQADDR="SCHEDQ"
                REPLYQ=Y
                CLOPT="-n 4 -sInitiateRequest -- -C psprcs.cfg -CD HR88 -PS PSUNX -A start -S PSPRCSRV"

[Process Scheduler]
;=========================================================================
; General settings for the Process Scheduler
;=========================================================================
PrcsServerName=PSUNX
;-------------------------------------------------------------------------
;Reduce priority of Process Scheduler server process, set to 0 if not needed
Niceness=4
...

[PSAESRV]
;=========================================================================
; Settings for Application Engine Tuxedo Server
;=========================================================================
;-------------------------------------------------------------------------
;Reduce priority of application engine server process, set to 0 if not needed
Niceness=5
...

{APPENG}
#
# PeopleSoft Application Engine Server
#
PSAESRV         SRVGRP=AESRV
                SRVID=1
                MIN={$PSAESRV\Max Instances}
                MAX={$PSAESRV\Max Instances}
                REPLYQ=Y
                CLOPT="-n {$PSAESRV\Niceness} -- -C {CFGFILE} -CD {$Startup\DBName} -S PSAESRV"
{APPENG}
...
PSPRCSRV        SRVGRP=BASE
                SRVID=101
                MIN=1
                MAX=1
                RQADDR="SCHEDQ"
                REPLYQ=Y
                CLOPT="-n {$Process Scheduler\Niceness} -sInitiateRequest -- -C {CFGFILE} -CD {$Startup\DBName} -PS {$Process Scheduler\PrcsServerName} -A start -S PSPRCSRV"

#
# PeopleSoft Application Engine Server
#
PSAESRV         SRVGRP=AESRV
                SRVID=1
                MIN=1
                MAX=1
                REPLYQ=Y
                CLOPT="-n 5 -- -C psprcs.cfg -CD HR88 -S PSAESRV"
...
PSPRCSRV        SRVGRP=BASE
                SRVID=101
                MIN=1
                MAX=1
                RQADDR="SCHEDQ"
                REPLYQ=Y
                CLOPT="-n 4 -sInitiateRequest -- -C psprcs.cfg -CD HR88 -PS PSUNX -A start -S PSPRCSRV"

Batch processing is like opera (and baseball) - "It ain't over till the fat lady sings".  Users care about when it starts and when it finishes.  If the last process finishes earlier, then that is an improvement in performance. 

This note describes a method of additionally prioritising processes queued to run on the process scheduler in PeopleSoft by their requesting operator ID and run control.  Where processing consists of more instances of the same process than can run concurrently, it can be used to make the process scheduler run longer-running processes before shorter-running processes that were scheduled earlier, thus completing batch processing earlier.

In PeopleSoft, without customisation, it is only possible to prioritise processes queued to run on the process scheduler by assigning a priority to the process definition or their process category.  Higher priority processes are selected to be run in preference to lower priorities.  Otherwise, processes are run in the order of the time at which they are requested to run.

This chart shows the database activity when the batch runs.  We often see what has come to be called the 'long tail' while we wait for just a few long-running processes to complete.

If we could put our own priority into that column we could control the priority of the request.  Solution

Introduce a database trigger that fires on insert into PSPRCSQUE and sets a priority specified on a new metadata table.  PRCSPRTY is not validated by PeopleSoft, and therefore any value can be specified.  

The files are available in a Github repository davidkurtz/psprcspty. The exact metadata will vary with the use case and requirements, but I provided some examples of how it might be generated.

• process_prioritisation_by_cumulative_runtime.sql - master script that creates metadata table and trigger and then:
• nvision_prioritisation_by_cumulative_runtime.sql - example script to create a procedure to populate metadata for nVision batch.
• gppdprun_prioritisation_by_cumulative_runtime.sql - example script to create a procedure to populate metadata for Payroll/Absence calculation batch.  
• process_prioritisation_by_cumulative_runtime_test.sql - test trigger by inserting dummy data into process queue table.
• process_prioritisation_by_cumulative_runtime_report.sql - example of a report to compare median execution time with last execution time.

Metadata Table: PS_XX_GFCPRCSPRTY

We need a table that will hold the priority for each combination of process type, process name, operation ID, and run control ID.  A corresponding record should be created using the Application Designer project in the GitHub repository.

create table sysadm.ps_xx_gfcprcsprty
(prcstype  VARCHAR2(30 CHAR) NOT NULL
,prcsname  VARCHAR2(12 CHAR) NOT NULL
,oprid     VARCHAR2(30 CHAR) NOT NULL
,runcntlid VARCHAR2(30 CHAR) NOT NULL
,prcsprty  NUMBER NOT NULL
--------------------optional columns
,avg_duration NUMBER NOT NULL
,med_duration NUMBER NOT NULL
,max_duration NUMBER NOT NULL
,cum_duration NUMBER NOT NULL
,tot_duration NUMBER NOT NULL
,num_samples  NUMBER NOT NULL
) tablespace ptapp;

create unique index sysadm.ps_xx_gfcprcsprty
on sysadm.ps_xx_gfcprcsprty(prcstype, prcsname, oprid, runcntlid) 
tablespace psindex compress 3;

Trigger Before Insert into PSPRCSQUE

As processes are scheduled in PeopleSoft, a row is inserted into the process scheduler queue table PSPRCSQUE.  A trigger will be created on this table that fires after the insert.  It will look for a matching row on the metadata table, PS_XX_GFCPRCSPRTY for the combination of process type, process name, operator ID, and run control ID.  If found, the trigger will assign the specified priority to the process request.  Otherwise, it will take no action.

CREATE OR REPLACE TRIGGER sysadm.psprcsque_set_prcsprty
BEFORE INSERT ON sysadm.psprcsque
FOR EACH ROW
WHEN (new.prcsname = 'RPTBOOK')
DECLARE
  l_prcsprty NUMBER;
BEGIN
  SELECT prcsprty
  INTO   l_prcsprty
  FROM   ps_xx_gfcprcsprty
  WHERE  prcstype = :new.prcstype
  AND    prcsname = :new.prcsname
  AND    oprid = :new.oprid
  AND    runcntlid = :new.runcntlid;
 
  :new.prcsprty := l_prcsprty;
EXCEPTION
  WHEN no_data_found THEN NULL;
  WHEN others THEN NULL;
END;
/
show errors

In this case, I am only assigning priorities to RPTBOOK processes, so I have added a when clause to the trigger so that it only fires for RPTBOOK process requests.  This can either be changed for other processes or removed entirely.

Priority Metadata

How the priorities should be defined will depend on the specific use case.  In some cases, you may choose to create a set of metadata that remains unchanged.

In this case, the objective is that the processes to take the longest to run should be executed first.  Therefore, I decided that the priority of each nVision report book process (by operator ID and run control ID) will be determined by the median elapsed execution time in the last two months.  The priorities are allocated such that the sum of the median execution times for each priority will be as even as possible.  

I have created a PL/SQL procedure GFCPRCSPRIORITY to truncate the metadata table and then repopulate it using a query on the process scheduler table (although, an Application Engine program could have been written to do this instead).  The procedure is executed daily, thus providing a feedback loop so if the run time varies over time, or new processes are added to the batch, it will be reflected in the priorities.

REM nvision_prioritisation_by_cumulative_runtime.sql

set serveroutput on
create or replace procedure sysadm.gfcprcspriority as
  PRAGMA AUTONOMOUS_TRANSACTION; --to prevent truncate in this procedure affecting calling session
  l_hist INTEGER := 61 ; --consider nVision processes going back this many days
begin
  EXECUTE IMMEDIATE 'truncate table ps_xx_gfcprcsprty';

--populate priorty table with known nVision processes 
insert into ps_xx_gfcprcsprty
with r as (
select r.prcstype, r.prcsname, r.prcsinstance, r.oprid, r.runcntlid, r.runstatus, r.servernamerun
, CAST(r.rqstdttm AS DATE) rqstdttm
, CAST(r.begindttm AS DATE) begindttm
, CAST(r.enddttm AS DATE) enddttm
from t, psprcsrqst r
  inner join ps.psdbowner p on r.dbname = p.dbname -- in test exclude any history copied from another database
where r.prcstype like 'nVision%' --limit to nVision processes
and r.prcsname like 'RPTBOOK' -- limit to report books
and r.enddttm>r.begindttm  --it must have run to completion
and r.oprid IN('NVISION','NVISION2','NVISION3','NVISION4')  --limit to overnight batch operator IDs
and r.begindttm >= TRUNC(SYSDATE)+.5-l_hist --consider process going back l_hist days from midday today
and r.runstatus = '9' --limit to successful processes
and r.begindttm BETWEEN ROUND(r.begindttm)-5/24 AND ROUND(r.begindttm)+5/24 --started between 7pm and 5am
), x as (
select r.*, CEIL((enddttm-begindttm)*1440) duration -–rounded up to the next minute
from r
), y as (
select prcstype, prcsname, oprid, runcntlid
, AVG(duration) avg_duration
, MEDIAN(CEIL(duration)) med_duration 
, MAX(duration) max_duration
, SUM(CEIL(duration)) sum_duration
, COUNT(*) num_samples
from x
group by prcstype, prcsname, oprid, runcntlid
), z as (
select y.* 
, sum(med_duration) over (order by med_duration rows between unbounded preceding and current row) cum_duration 
, sum(med_duration) over () tot_duration
from y
)
select prcstype, prcsname, oprid, runcntlid 
, avg_duration, med_duration, max_duration, cum_duration, tot_duration, num_samples
--, CEIL(LEAST(tot_duration,cum_duration)/tot_duration*3)*4-3 prcsprty  --3 priorities
, CEIL(LEAST(tot_duration,cum_duration)/tot_duration*9) prcsprty  --9 priorities
--, DENSE_RANK() OVER (order by med_duration) prcsprty --unlimited priorities
from z
order by prcsprty, cum_duration;

  dbms_output.put_line(sql%rowcount||' rows inserted');
  commit;

end gfcprcspriority;
/
show errors

In testing, I found that using just the 3 delivered levels of priority was not sufficiently granular to prioritise the jobs adequately, so I chose to use 9 levels (1 to 9).  The process priority on PRCSQUE is not validated, so I can use any value.  I also found I could just rank the processes from 1 to n by duration, and that would also work.

It is possible to create additional priority levels for process categories (see also More Process Priority Levels for the Process Scheduler), but that still only works for prioritising different processes over each other.

Metadata

This is the metadata produced on a test system by the above query.  It will vary depending on what has been run recently, and how it performed.  There are more, shorter processes in the lower priority groups, and fewer, longer processes in the higher priority groups.  

PRCSTYPE                       PRCSNAME     OPRID        RUNCNTLID                        PRCSPRTY
------------------------------ ------------ ------------ ------------------------------ ----------
…
nVision-ReportBook             RPTBOOK      NVISION2     NVS_RPTBK_XXX1                          1
nVision-ReportBook             RPTBOOK      NVISION2     NVS_RPTBK_XXX3                          1
nVision-ReportBook             RPTBOOK      NVISION2     NVS_RPTBK_LLLL8                         1
…
nVision-ReportBook             RPTBOOK      NVISION2     NVS_RPTBK_LLLL9                         2
nVision-ReportBook             RPTBOOK      NVISION      NVS_RPTBOOK_STAT8                       2
nVision-ReportBook             RPTBOOK      NVISION      NVS_RPTBOOK_STAT1                       2
…
nVision-ReportBook             RPTBOOK      NVISION2     NVS_RPTBK_TEMPXX                        6
nVision-ReportBook             RPTBOOK      NVISION      NVS_RPTBOOK_3                           6
nVision-ReportBook             RPTBOOK      NVISION      NVS_RPTBOOK_17                          6
nVision-ReportBook             RPTBOOK      NVISION      NVS_RPTBOOK_STAT4                       7
nVision-ReportBook             RPTBOOK      NVISION      NVS_RPTBOOK_28                          7
nVision-ReportBook             RPTBOOK      NVISION2     NVS_RPTBK_INCXXX                        8
nVision-ReportBook             RPTBOOK      NVISION2     NVS_RPTBK_MORYYY1                       8
nVision-ReportBook             RPTBOOK      NVISION      NVS_RPTBOOK_24                          9
nVision-ReportBook             RPTBOOK      NVISION      NVS_RPTBOOK_16                          9

A Test Script

This test script inserts some dummy rows into PSPRCSQUE to check whether a priority is assigned by the trigger. The insert is then rolled back.

INSERT INTO psprcsque (prcsinstance, prcstype, prcsname, oprid, runcntlid)
VALUES (-42, 'nVision-ReportBook', 'RPTBOOK', 'NVISION','NVS_RPTBOOK_17');
INSERT INTO psprcsque (prcsinstance, prcstype, prcsname, oprid, runcntlid)
VALUES (-43, 'nVision-ReportBook', 'RPTBOOK', 'NVISION','NVS_RPTBOOK_STAT1');
select prcsinstance, prcstype, prcsname, oprid, runcntlid, prcsprty from psprcsque where prcsinstance IN(-42,-43);
rollback;

You can see that it was successful because priorities 2 and 7 were assigned.

PRCSINSTANCE PRCSTYPE                       PRCSNAME     OPRID        RUNCNTLID                        PRCSPRTY
------------ ------------------------------ ------------ ------------ ------------------------------ ----------
         -43 nVision-ReportBook             RPTBOOK      NVISION      NVS_RPTBOOK_STAT1                       2
         -42 nVision-ReportBook             RPTBOOK      NVISION      NVS_RPTBOOK_17                          7

Monitoring Script

This query in script process_prioritisation_by_cumulative_runtime_report.sql reports on the average, median, and cumulative median execution time for each nVision process that ran to success during the overnight processing window as calculated by the package GFCPRCSPRIORITY and stored in PS_XX_GFCPRCSPRTY.  It also compares that to the priority and last actual run time for that process.

Example Output

                                                                                                      Cum.                                            
                                                                        Average   Median            Median    Total         Last Run   Actual                   
                                                                  Prcs Duration Duration Duration Duration Duration     Num  Process Duration Duration Duration Priorty
PRCSTYPE             PRCSNAME   OPRID        RUNCNTLID            Prty   (mins)   (mins)   (mins)   (mins)   (mins) Samples Priority   (mins)    Diff    % Diff    Diff
-------------------- ---------- ------------ -------------------- ---- -------- -------- -------- -------- -------- ------- -------- -------- -------- -------- -------
nVision-ReportBook   RPTBOOK    NVISION      NVS_RPTBOOK_4           9    90.65      131      209     1834     1997      23        6      189       58       44       3 
nVision-ReportBook   RPTBOOK    NVISION      NVS_RPTBOOK_16          9   159.17      163      209     1997     1997      23        9      177       14        9       0

nVision-ReportBook   RPTBOOK    NVISION      NVS_RPTBOOK_14          8    89.26      127      215     1703     1997      23        6      167       40       31       2
nVision-ReportBook   RPTBOOK    NVISION      NVS_RPTBOOK_24          8   115.87      117      165     1576     1997      23        9      144       27       23      -1

nVision-ReportBook   RPTBOOK    NVISION2     NVS_RPTBK_MORYYY1       7    93.13       85      165     1459     1997      23        8      158       73       86      -1
nVision-ReportBook   RPTBOOK    NVISION      NVS_RPTBOOK_28          7    88.30       80      172     1374     1997      23        8      108       28       35      -1

nVision-ReportBook   RPTBOOK    NVISION2     NVS_RPTBK_INCXXX        6    83.61       79      149     1294     1997      18        7      118       39       49      -1
nVision-ReportBook   RPTBOOK    NVISION      NVS_RPTBOOK_17          6    70.96       69      105     1143     1997      23        7       81       12       17      -1
nVision-ReportBook   RPTBOOK    NVISION      NVS_RPTBOOK_STAT4       6    68.00       72       81     1215     1997       8        7       81        9       13      -1

nVision-ReportBook   RPTBOOK    NVISION2     NVS_RPTBK_MMMMMM        5    52.45       46      119      914     1997      22        5       91       46      100       0
nVision-ReportBook   RPTBOOK    NVISION2     NVS_RPTBK_TEMPXX        5    50.48       49      104      963     1997      23        5       94       45       92       0
nVision-ReportBook   RPTBOOK    NVISION      NVS_RPTBOOK_3           5    55.52       55       99     1018     1997      23        6       79       24       44      -1
nVision-ReportBook   RPTBOOK    NVISION      NVS_RPTBOOK_1           5    47.70       56      137     1074     1997      23        4       56        0        0       1
…

Monitoring Query

The query in prcsmap.sql is used to produce the data for a map of the processes, showing request time, time spent queuing, and time spent executing.  It is the basis of the second chart above. I normally run this in SQL Developer and export the data as an Excel workbook.  There is an example spreadsheet in the Github repository.

9 levels of Prioritisation

With prioritisation, we can see that the long-running jobs with higher priority ran earlier.  

We can also see that some of the higher-priority jobs that are scheduled later are running earlier than those scheduled earlier, and are thus finishing earlier.

There is no longer any tail of processing.  Instead, load drops quickly at the end of the batch, and the batch as a whole finishes earlier.

It is easy to access the PeopleSoft message log in Process Monitor component, but it can be a little difficult to work with in SQL because it is stored in multiple tables.

This started when I wanted to generate a PeopleSoft log message as a single string of text, so I could investigate shared pool memory errors by searching for ORA-04031 errors.  Ultimately, the string 'ORA-04031' is stored in PS_MESSAGE_LOGPARM, but I wanted to see the whole error message.

• Each process, has a request record on PSPRCSRQST, it can have many messages.
• Each message is stored in the message log table PS_MESSAGE_LOG
• The text of each message is stored in the message catalogue table PSMSGCATDEFN.  It can have up to 9 substitution strings (%1, %2, etc).
• A message can have up to 9 parameters stored on PS_MESSAGE_LOGPARM that are substituted into the message string.

I assemble the message text in a PL/SQL function exactly as PeopleTools programs do, substituting the variables in the message string from the message catalogue with the parameter values.  The PL/SQL function is put into the SQL query as a common table expression so that I don't have to create a function or package in the database.  The function returns the full message text in a CLOB, thus I can then easily manipulate the message string in SQL.

In this case, I wrote a SQL query to search for ORA-04031 (see psmsglogora4031.sql on Github), but the same PL/SQL function can be used in various queries.  

It can be slow to search the generated message string.  It can be faster to search PS_MESSAGE_LOGPARM directly. 

WITH FUNCTION psmsgtext(p_process_instance INTEGER, p_message_seq INTEGER) RETURN CLOB IS
  l_message_log ps_message_log%ROWTYPE;
  l_message_text CLOB;
BEGIN
  SELECT *
  INTO   l_message_log
  FROM   ps_message_log 
  WHERE  process_instance = p_process_instance
  AND    message_seq = p_message_seq;

  SELECT message_text
  INTO   l_message_text
  FROM   psmsgcatdefn
  WHERE  message_set_nbr = l_message_log.message_set_nbr
  AND    message_nbr     = l_message_log.message_nbr;

  --dbms_output.put_line(l_message_text);
  FOR i IN (
    SELECT *
    FROM   ps_message_logparm
    WHERE  process_instance = p_process_instance
    AND    message_seq = p_message_seq
    ORDER BY parm_seq
  ) LOOP
    --dbms_output.put_line(i.message_parm);
    l_message_text := REPLACE(l_message_text,'%'||i.parm_seq,i.message_parm);
  END LOOP;

  --and tidy up the unused replacements at the end
  RETURN REGEXP_REPLACE(l_message_text,'%[1-9]','');
END;
x as (
select r.prcstype, r.prcsname, r.oprid, r.runcntlid
, l.*, psmsgtext(l.process_instance, l.message_seq) message_text
from ps_message_log l
LEFT OUTER JOIN psprcsrqst r ON r.prcsinstance = l.process_instance
WHERE …
)
select *
from x
ORDER BY dttm_stamp_sec
/

Process              Process         Operator                             Process  Msg                                   Msg         Msg
Type                 Name            ID         Run Control              Instance  Seq JOBID           PROGRAM_NAME     Set#  Msg#   Sev DTTM_STAMP_SEC
-------------------- --------------- ---------- ---------------------- ---------- ---- --------------- --------------- ----- ----- ----- ----------------------------
MESSAGE_TEXT
------------------------------------------------------------------------------------------------------------------------------------------------------
nVision-ReportBook   RPTBOOK         VP1        NVS_XXXXXXX_99            1234567    1 PRCS SCHDL      psprschd           65    70     0 01/04/2023 20.37.21
Process Request shows status of 'INITIATED' or 'PROCESSING' but no longer running

nVision-ReportBook   RPTBOOK         VP1        NVS_XXXXXXX_99            1234567    2 PRCS SCHDL      psprschd           65    73     0 01/04/2023 20.37.23
PSNT1 failed to post files to the report repository.  Server scheduled to try again on 2023-04-01-20.37.41.239539.  See log
...

I've written previously about how to clear the physical cache files on a PeopleTools process, but I have found myself explaining it a few times recently, so I am going to post about it again.

When you refresh the database of a PeopleSoft system, you need to clear the physical cache files of the PeopleTools processes.  The files are outside the database and they no longer reflect what is inside the database.  This includes all application servers and process schedulers and anywhere where Application Designer or a client process is used.

It is common to refresh a database when testing a PeopleSoft system.  For example, to copy production to a performance test environment.  It is also increasingly common to use Oracle database flashback during testing.  A guaranteed restore point is taken, a test is performed, and then the database is flashed back to that restore point.  Flashback returns the whole database not just to the same logical state, but also the same physical state.  Block for block, the entire database is physically the same as when the restore point was taken.  Thus a test is completely repeatable with the same initial conditions.  Although the database instance will have been restarted during the flashback so the content of the database memory will have been cleared.

It is also common, after the flashback to then make small changes or corrections, take a new restore point and repeat the test.  Some of those changes might include Application Designer projects that will then be loaded into the physical cache.  Flashing the database back won't change the physical cache files stored outside the database, so they need to be cleared too.  Otherwise, they may have higher version numbers than the objects in the database, and caching won't work correctly.  When you retest, your changes may not be loaded and executed by PeopleTools processes.

The officially approved method is to go around each server and use either the purge option in the psadmin utility or manually delete the files.  See:

• Oracle Support Note: E-WS: How to Delete / Purge Cache for the PeopleSoft Application (Doc ID 753215.1)
• PeopleBooks: System and Server Admin ► Using the Application Server Administration Menu 

However, since at least PeopleTools 5, it has been possible to invalidate all physical cache files on all servers by updating the LASTREFRESHDTTM on the single row in table PSSTATUS.  Any cached object older than the value of LASTREFRESHDTTM will be purged from the cache when the process that reference that cache is started. Therefore, if immediately after a restore or flashback that value is updated to the current system time, all caches will be purged as the processes are restarted.

UPDATE PSSTATUS
SET    LASTREFRESHDTTM = SYSDATE
/
COMMIT
/

Sometimes, ASH and AWR are not enough.  SQL may not be sampled by ASH if it is short-lived, and even if it is sampled, the SQL may not be captured by AWR.  Sometimes, in order to investigate a problem effectively, it is necessary to use database session SQL trace.  

It is easy to trace a process initiated by the process scheduler with a trigger (see Enabling Oracle Database Trace on PeopleSoft processes with a Trigger).

Another tactic is to use an AFTER LOGON trigger with logic to look at the program name.  The program name can be read using SYS_CONTEXT().  If it matches what I am looking for, I can enable session trace.

Here is an example I used for the OpenXML nVision server PSNVSSRV

• I want to trace SQL and not any wait events or bind variables.  Therefore, I will set event 10046 at level 1.
• I also set a tracefile_identifier that will be included in the trace file name, so I can more easily identify the trace file.

REM additional SQL trace triggers
CREATE OR REPLACE TRIGGER sysadm.gfc_nvision_trace_on_logon
AFTER LOGON
ON sysadm.schema
DECLARE
  l_process_instance INTEGER;
  l_program          VARCHAR2(64 CHAR);
  l_sql              VARCHAR2(100);
BEGIN

  SELECT sys_context('USERENV', 'CLIENT_PROGRAM_NAME')
  INTO   l_program
  FROM   dual;

  IF l_program like 'PSNVSSRV%' THEN --then this is a NVISION session
    EXECUTE IMMEDIATE 'ALTER SESSION SET tracefile_identifier = ''PSNVSSRV''';
    EXECUTE IMMEDIATE 'ALTER SESSION SET events ''10046 TRACE NAME CONTEXT FOREVER, LEVEL 1''';
  END IF;

EXCEPTION 
  WHEN OTHERS THEN NULL;
END;
/
show errors
ALTER TRIGGER sysadm.gfc_nvision_trace_on_logon ENABLE;

A trace level is set in a process definition in PS_PRCSDEFN precedence over a trace level set in the process scheduler configuration file (psprcs.cfg).

I often set the process scheduler trace level for Application Engine to 1152 to enable batch timings to both the database batch timings tables and the AE trace file, but then I often find that a trace is left enabled on a few processes to aid performance analysis of a troublesome process.

This script updates the trace level set in the parameter list in the process definition to include the bit flags set by 1152 (to enable batch timings).  

• The current trace level is extracted with regular expression substring functions.
• A bitwise OR is performed between the current trace level and the desired settings.  There is no single function to do this in Oracle SQL, but it can be calculated simply (see Oracle blog: There is no BITOR() in Oracle SQL).  
• The old trace value is replaced with the new one in the parameter list with a regular expression replace function.
• The version number on the process definition is also updated as it would be if updated by the process definition component in the PIA.  Thus it is correctly re-cached by the process scheduler, the scheduler does not need to be recycled, nor does the cache need to be cleared

The script is available on Github.

REM fixprcstracelevel.sql
set pages 99 lines 200 serveroutput on
spool fixprcstracelevel append
ROLLBACK;
DECLARE
  l_counter INTEGER := 0;
  l_trace_expr VARCHAR2(20); /*expression containing TRACE keyword and value*/
  l_req_trace_level INTEGER := 1152; /*trace value set in the scheduler config*/
  l_cur_trace_level INTEGER; /*current trace level*/
  l_new_trace_level INTEGER; /*new calculated trace level*/
  l_parmlist ps_prcsdefn.parmlist%TYPE;
BEGIN
  for i in (
    SELECT t.*
    FROM   ps_prcsdefn t
    WHERE  UPPER(t.parmlist) LIKE '%-%TRACE%'
    AND   prcstype LIKE 'Application Engine'
--  AND parmlisttype IN('1','2','3')
  ) LOOP
    l_trace_expr := REGEXP_SUBSTR(i.parmlist,'\-trace[ ]*[0-9]+',1,1,'i');
    l_cur_trace_level := TO_NUMBER(REGEXP_SUBSTR(l_trace_expr,'[0-9]+',1,1,'i'));
    l_new_trace_level := l_req_trace_level+l_cur_trace_level-bitand(l_cur_trace_level,l_req_trace_level);
    l_parmlist := REGEXP_REPLACE(i.parmlist,l_trace_expr,'-TRACE '||l_new_trace_level,1,1,'i');

    IF l_new_trace_level = l_cur_trace_level THEN
      dbms_output.put_line(i.prcstype||':'||i.prcsname||':'||i.parmlist||'=>No Change');
    ELSE
      l_counter := l_counter + 1;
      IF l_counter = 1 THEN
        UPDATE psversion
        SET    version = version+1
        WHERE  objecttypename IN('SYS','PPC');

        UPDATE pslock
        SET    version = version+1
        WHERE  objecttypename IN('SYS','PPC');
      END IF;
      dbms_output.put_line(l_counter||':'||i.prcstype||' '||i.prcsname||':'||i.parmlist||'=>'||l_parmlist);
      UPDATE ps_prcsdefn
      SET    version = (SELECT version FROM psversion WHERE objecttypename = 'PPC')
      ,      parmlist = l_parmlist
      WHERE  prcstype = i.prcstype
      AND    prcsname = i.prcsname;
    END IF;
  END LOOP;
  COMMIT;
END;
/
spool off

Application Engine:GL_JEDIT:-TRACE 1159=>No Change
1:Application Engine PTDEFSECINRL:-toolstacepc 2048 -toolstracesql 15 -TRACE 15=>-toolstacepc 2048 -toolstracesql 15 -TRACE 1167

• TRACE for GL_JEDIT is already 1159, so no change is required.
• TRACE for PTDEFSECINRL was changed from 15 to 1167.

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

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

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

Wed Aug 11                                                                                                                                                             page    1
                                                                              Dependency Hierarchy

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


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


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


...4  VIEW    SYSADM       PS_POSN_HISTORY    TABLE   SYSADM PS_JOB                        HARD

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

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

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

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

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

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

SQL>select * from ps.psdbowner;

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

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

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

New Process Schedulers for ADG

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

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

Existing Process Schedulers

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

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

Configuration by SQL

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

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

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

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

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

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

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

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

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

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

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

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

Disable Query Statistics

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

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

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

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

They can be disabled in the PIA

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

Enable Query Execution Logging

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

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