The Goal

One of my favourite books on Oracle performance, "Optimizing Oracle Performance" by Cary Millsap & Jeff Holt, introduced me to "The Goal" by Eli Goldratt and Jeff Cox.  

The Goal is all about performance, without being anything to do with computers.  It is a story of a man who has to save his manufacturing plant from closure by making it profitable.  The language is about manufacturing, but it applies to any system of processes, including any software application and your Oracle (or any other) database!  

Recently, I was checking a quote from it, and I ended up reading it again.  It is 20 years since I first read these two books.  They completely changed how I thought about performance.  Both remain as valid today as they were then.  

It is good to be reminded of these fundamental principles every now and then.

"So this is the goal: To make money by increasing net profit, while simultaneously increasing return on investment, and simultaneously increasing cash flow." 

"There are three measurements which express the goal of making money ... throughput, inventory and operational expense"

"Throughput is the rate at which the system generates money through sales.

Inventory is all the money that the system has invested in purchasing things which it intends to sell.

Operational expense is all the money the system spends in order to turn inventory into throughput."

"A plant in which everyone is working all the time is very inefficient."

"A bottleneck is any resource whose capacity is equal to or less than the demand placed upon it.  And a non-bottleneck is any resource whose capacity is greater than the demand placed on it."

"What does lost time on a bottleneck mean?  It means you have lost throughput."

"The capacity of a plant is equal to the capacity of its bottlenecks."

"A system of local optimums is not an optimum system at all; it is a very inefficient system."

"An hour lost at a bottleneck is an hour lost for the entire system.
An hour saved at a non-bottleneck is worthless."

"1. IDENTIFY the system's constraint(s).

2. Decide how to EXPLOIT the system's constraint(s).

3. SUBORDINATE everything else to the above decision.

4. ELEVATE the system's constraint(s).

5. WARNING!!!! If in the previous steps, a constraint has been broken, go back to step 1, but do not allow INERTIA to cause a system's constraint."

"I started to have a very good guideline; if it comes from cost accounting it must be wrong."

Performance optimisation is sometimes viewed as a black art.  It is not.  Instead, like detection, it "is, or ought to be, an exact science, and should be treated in the same cold and unemotional manner".  

• "The Goal" explains the general principles.
• "Optimizing Oracle Performance" applies them to the Oracle database.

More Bang for your Buck in the Cloud with Resource Manager

Much of the cost in database IT is tied to the number of CPUs.  Oracle database licencing is priced per CPU.  The dominant factor in determining your cloud subscription cost is also CPU, although, disk, memory, and network can also be a cost factor. 

That incentivises you to minimise your CPU.  I believe it is inevitable that cloud systems will be configured with fewer CPUs and it will become more common to see them running either close to or beyond the point of having 0% idle CPU.  In fact, I'll go further:  

In the cloud, if your system is not constrained by CPU, at least some of the time, you are probably spending too much money on renting too many CPUs.

What happens to an Oracle database when it runs out of CPU?

The resource manager has been part of the Oracle database since 8i, but in my experience, it is rarely used.

Every process has to demand CPU and if necessary wait on the CPU run queue.  If you don't have a resource manager plan, then all the Oracle processes will have equal priority on that queue.  The resource manager will not intervene.  

However, not all processes are created equal.  Instead, the users of an application will consider some things more important or urgent than others.  Some processes are on a critical path to delivering something by a deadline, while others can wait.  That implies a hierarchy of priority.  A resource manager plan allocates CPU to higher priority processes over low priority within the constraint of a minimum guaranteed CPU allocation and can restrict the degree of parallelism.  

Note that "By default, all predefined maintenance windows use the resource plan DEFAULT_MAINTENANCE_PLAN".  When you introduce your own resource manager plan you don't need to alter the predefined windows.

A resource manager plan that reflects the business priorities can enable a system to meet its objectives with fewer resources, particularly CPU resources.  In a cloud system, using fewer resources, particularly CPU resources, will tend to save money on cloud subscription costs.

A User Story

Let me tell you about a PeopleSoft Financials system at an insurance company.  Like all insurance companies, they like to slice and dice their General Ledger in lots of different ways and produce lots of reports every night.

Data flows through the system from GL transaction processing via summary ledgers on which materialized views are built and then reports are run

Transactions -> Post to Ledger -> Summary Ledgers -> Materialised Views -> Reports

A fundamentally important thing this company did was to provide a quantitative definition of acceptable performance.  

• "GL reports must be finished by the time continental Europe starts work at 8am CET / 2am EST"
• "Without making the system unavailable to Asia/Pac users" 
• "At night (in the US), some other things can wait, but need to be available at the start of the US working day."

They were running on a two-node RAC database on an engineered system, on-premises.  When the overnight GL batch was designed and configured on the old hardware, parallelism was increased until it consumed the entire box.

The system has now moved to an Exadata cloud-at-customer machine.  It is still a two-node RAC cluster.  We have a choice of up to 10 OCPUs (20 virtual CPUs) per node.  During testing, we progressively reduced the CPU count until we could only just meet that target.  Every time we reduce the CPU by 1 OCPU on each of the two nodes, we reduced the cost of the cloud subscription by approximately US$2000/month.

Implicit in that statement of adequate performance is also a statement of what is important to the business.  We started to create a hierarchy of processes.  

• If the business is waiting on the output of a process then that is a high-priority process that is guaranteed a high proportion of available CPU. 
• If a process is finished before the business needs it then it has a lower priority.  For example, a set of processes was building reporting tables that were not needed until the start of the US working day, so their start time was pushed back, and they were put in a lower prior consumer group that also restricted their degree of parallelism. 

Sometimes, it can be hard to determine whether the users are waiting and whether the performance is adequate, but usually, they will tell you!  However, with an overnight batch process, it is straightforward.  If it is outside office hours, then the users aren't waiting for it, but it needs to be there when they come into the office in the morning.

Like so many physical things in life, nearly everything that happens in computing involves putting a task on a queue and waiting for it to come back.  Most computer systems are chains of inbound and outbound queues.  On the way other requests for resources may be invoked that also have to be queued.  Ultimately, every system is bound by its resources.  On a computer that is CPU, memory, disk, and network.  A critical process whose performance is degraded, because it is not getting enough of the right kind of resource, becomes a bottleneck.

"Time lost at a bottleneck is lost across the system." 

One of my favourite books on Oracle performance is Optimizing Oracle Performance by Cary Millsap & Jeff Holt.  It introduced me to another book, The Goal by Eli Goldratt and Jeff Cox.  Its central theme is the nature of bottlenecks, otherwise called constraints.   "A bottleneck is any resource whose capacity is equal to or less than the demand placed upon it."

It is all about performance, without being anything to do with computers.  It is a Socratic case study of how to implement the 5-step strategy dubbed "The Theory of Constraints" to improve the performance of a system.  The five steps are set out plainly and then again in another book by Goldratt "What is This Thing Called Theory of Constraints and How Should It Be Implemented?"

IDENTIFY the system's constraint(s).

Decide how to EXPLOIT the system's constraint(s).

SUBORDINATE everything else to the above decision.

ELEVATE the system's constraint(s).

WARNING!!!! If in the previous steps, a constraint has been broken, go back to step 1, but do not allow INERTIA to cause a system's constraint.

In the factory in The Goal, the goal is to increase throughput while simultaneously reducing inventory and operating expense.

In the cloud, the goal is to increase system throughput while simultaneously reducing response time and the cost of resources.

The Resource Manager Plan

The hierarchy of processes then determines who should get access to the CPU in preference to whom.  It translates into a database resource manager plan.  This is the 4^th of Goldratt's 5 steps.  The higher priority processes are on the critical processing path get precedence for CPU so that they can make process.  The lower-priority processes may have to wait for CPU so they don't impede higher-priority processes (this is the 3^rd step).

The resource plan also manages the degree of parallelism that can be used within each consumer group, so that we don't run out of parallel query servers.  Higher-priority processes may not have a high PQ limit because there are more processes that run concurrently.  Processes are mostly allocated to consumer groups through mappings of module, action, and program name, some are mapped explicitly using triggers.

Over the years, the resource manager plan for this particular system has gone through three main design iterations.  The 4 lowest-priority consumer groups were added to restrict the consumption of these groups when the higher groups were active.

Priority	1st Iteration	2nd Iteration	3rd Iteration	Description of Consumer Group
1	PSFT_GROUP			General group for PeopleSoft application and batch processes. PQ limit = ½ of CPU_COUNT (3rd iteration)
2			HIGH_GROUP	For weekly stats collection process of 2 multi-billion row tables (LEDGER and JRNL_LN). PQ limit = 2x CPU_COUNT
3	SUML_GROUP			Process that refresh summary ledger tables, and MVs on summary ledgers. PQ limit = ¾ of CPU_COUNT
4	NVISION _GROUP			nVision General Ledger reporting processes. PQ limit ≈ 3/8 of CPU_COUNT
5			GLXX_GROUP	Processes that build GLXX reporting tables, and do some reporting. Parallelism disabled. Run concurrently with nVision, but more important to complete GL reporting. PQ limit = 1. No parallelism
6		PSQUERY _GROUP	NVSRUN _GROUP	Other queries submitted via PeopleSoft ad-hoc query tool and ad-hoc nVision PQ limit = 3 - 4
7		ESSBASE _GROUP		Essbase processes. PQ limit = 2 - 4
8			LOW_GROUP, LOW_LIMITED _GROUP	Other Processes. Also deals with an Oracle bug that causes AQ$_PLSQL_NTFN% jobs to run continuously consuming CPU. Actual/Estimated Time Limit

This approach has certainly prevented the processes in GLXX_GROUP, ad-hoc queries in the PSQUERY_GROUP, and other processes in the LOW_GROUP from taking CPU away from critical processes in PSFT_GROUP, NVISION_GROUP and SUML_GROUP.  We also adjusted the configuration of the application to reduce the number of processes that can run concurrently.

What if we decide to change the number of CPUs 

When this system ran on an on-premises machine we had a single resource plan because the number of CPUs was fixed.  

Now it has moved to the cloud, we can choose how many CPUs to pay for.  Performance was tested with various configurations.  Consequently, we have created several different resource plans for different numbers of CPUs with different PQ limits.  When we change the number of CPUs we just specify the corresponding resource manager plan.  

Some other database parameters have been set to lower non-default values to restrict overall SQL parallelism and the number of concurrent processes on the database job scheduler. These are also changed in line with the number of CPUs.

alter system set RESOURCE_MANAGER_PLAN=PSFT_PLAN_CPU8 scope=both sid='*';
alter system set JOB_QUEUE_PROCESSES=8 scope=both sid='*';
alter system set PARALLEL_MAX_SERVERS=40 scope=both sid='*';
alter system set PARALLEL_SERVERS_TARGET=40 scope=both sid='*';

It is possible that in the future we might automate changing the number of CPUs by schedule.  It is then easy to switch resource manager plans by simply setting an initialisation parameter.  

At the moment, we have one plan in force at all times.  It is also possible to change plans on a schedule using scheduler windows, and you can still intervene manually by opening a window.

TL;DR In the Cloud, Performance is Instrumented as Cost

You can have as much CPU and performance as you are willing to pay for.

By configuring the resource manager to prioritise CPU allocation to high-priority processes, ones for which users are waiting, over lower-priority ones, a system can achieve its performance objectives while consuming fewer resources.

Querying the PeopleSoft Message Log with SQL

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).
• These correspond to 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
/

Now, I can easily produce a report of messages, like this:

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
…

Investigating Unfamiliar PL/SQL with the Hierarchical Profiler

The PL/SQL profiler can tell you how much time you spend where in your PL/SQL code (see my presentation Performance Tuning with the PL/SQL Performance Profilers).  Therefore it also tells you which code blocks were executed, and in which test run.  If you are debugging code with which you are unfamiliar, this can provide insight into where to focus attention and determine what is going on.

Example Problem

I was looking at a third-party application that uses the database job scheduler to run multiple concurrent batch jobs.  I needed to work out why they were not balancing properly between the database instances.  This application has its own scheduler package.  It is driven by metadata to define the jobs to be submitted.  This package then calls the delivered DBMS_SCHEDULER package.  However, this third-party package is quite complicated, there are lots of similar calls, and it is difficult to work out what was executed by just reading the code.

I ran the application having enabled the hierarchical profiler, DBMS_HPROF.  I was able to query the profiler tables to find the calls to DBMS_SCHEDULER that were executed.

Querying the Profiler Tables

Each time DBMSHP is run, the data is tagged with a separate run ID, so if I do different tests I can easily separate them.  However, in this example, I have run only one test.

SELECT * 
FROM   dbmshp_runs
ORDER BY runid;

 Run
  ID RUN_TIMESTAMP                  TOTAL_ELAPSED_TIME RUN_COMMENT                                          TRACE_ID
---- ------------------------------ ------------------ -------------------------------------------------- ----------
   3 28-MAR-23 19.23.20.891595000             78254498                                                             2

Usually, I am interested in improving performance, so I look for the code that took the most time and profile the code blocks by elapsed time.  However, this time, I have sorted them by module and line number so I can see which code blocks were executed.

BREAK ON OWNER ON TYPE ON module skip 1
SELECT fi.symbolid, fi.owner, fi.type, fi.module, fi.function, fi.line#, fi.namespace, fi.calls, fi.function_elapsed_time, fi.sql_id
FROM   dbmshp_function_info fi
WHERE  fi.runid = 3
ORDER BY fi.owner, fi.module, fi.line#;

The profile includes the application code and also the Oracle packages owned by SYS Function

 Symbol                                                                                                        Line Name             Elapsed
     ID OWNER              TYPE            MODULE                    FUNCTION                                     # Space   CALLS       Time SQL_ID
------- ------------------ --------------- ------------------------- ---------------------------------------- ----- ----- ------- ---------- -------------
      8 XXXXX_CUST         PACKAGE BODY    CUST_PARALLEL_JOBS        ISJOBSRUNNING                                6 PLSQL      38        708
      9                                                              ISJOBSRUNNING.C_RUNNING_JOBS_CNT            12 PLSQL      38        266
    137                                                              __static_sql_exec_line13                    13 SQL        38       9681 2y7y7t8bf4ykw
    133                                                              __sql_fetch_line23                          23 SQL        38    2026632 2y7y7t8bf4ykw
      6                                                              GETJOBSSTATUS                               42 PLSQL       7        150
      7                                                              GETJOBSSTATUS.C_JOB_STATUS                  48 PLSQL       7         59
    138                                                              __static_sql_exec_line49                    49 SQL         7        565 d5g73bnmxjuqd
    134                                                              __sql_fetch_line59                          59 SQL         7        238 d5g73bnmxjuqd
      3                                                              CUST_SIMULATE_SURRENDER                    105 PLSQL       1       1232
      5                                                              CUST_SIMULATE_SURRENDER.C_JOB_GROUPS       110 PLSQL       1         16
    135                                                              __static_sql_exec_line111                  111 SQL         1        159 1xrrajz8mgbhs
      4                                                              CUST_SIMULATE_SURRENDER.C_CFG              119 PLSQL       1         12
    136                                                              __static_sql_exec_line120                  120 SQL         1         90 9ytv0rhjjp3mr
    131                                                              __sql_fetch_line160                        160 SQL         1        118 1xrrajz8mgbhs
    132                                                              __sql_fetch_line165                        165 SQL         1        135 9ytv0rhjjp3mr
…
     54 XXXXX_SCHEDULER    PACKAGE BODY    SCHEDULER_ENGINE          __pkg_init                                   0 PLSQL       1          5
     55 XXXXX_SCHEDULER    PACKAGE SPEC    SCHEDULER_ENGINE          __pkg_init                                   0 PLSQL       1          5
     52 XXXXX_SCHEDULER    PACKAGE BODY    SCHEDULER_ENGINE          RUN_JOB                                    770 PLSQL       7        176
     53                                                              SET_JOB_ARGUMENT                          1317 PLSQL      21        202
    178                                                              __static_sql_exec_line1355                1355 SQL        21       4733 3h8uatusjv84c
…
    118 SYS                PACKAGE BODY    DBMS_SCHEDULER            CREATE_PROGRAM                              15 PLSQL       1         24
    121                                                              DROP_PROGRAM                                43 PLSQL       2         98
    119                                                              DEFINE_PROGRAM_ARGUMENT                    112 PLSQL       3        186
    122                                                              DROP_PROGRAM_ARGUMENT                      211 PLSQL       6        363
    117                                                              CREATE_JOB                                 432 PLSQL       7        428
    124                                                              RUN_JOB                                    546 PLSQL       7        239
    120                                                              DROP_JOB                                   696 PLSQL      14       7484
    123                                                              ENABLE                                    2992 PLSQL       1         87
    125                                                              SET_ATTRIBUTE                             3063 PLSQL      14        957
    126                                                              SET_ATTRIBUTE                             3157 PLSQL      14       2923
    127                                                              SET_ATTRIBUTE_NULL                        3274 PLSQL       7         42
    116                                                              CHECK_SYS_PRIVS                           3641 PLSQL      69     153470
…

The hierarchical profiler tracks which code blocks call which code blocks, so I can perform a hierarchical query starting where the parent is null.

SELECT symbolid, parentsymid,
       RPAD(' ', (level-1)*2, ' ') || a.name AS name, 
       a.line#, a.calls,
       a.subtree_elapsed_time, 
       a.function_elapsed_time       
FROM   (SELECT fi.symbolid,
               pci.parentsymid,
               RTRIM(fi.owner || '.' || fi.module || '.' || NULLIF(fi.function, fi.module), '.') AS name,
               fi.line#,
               NVL(pci.subtree_elapsed_time, fi.subtree_elapsed_time) AS subtree_elapsed_time, 
               NVL(pci.function_elapsed_time, fi.function_elapsed_time) AS function_elapsed_time, 
               NVL(pci.calls, fi.calls) AS calls
        FROM   dbmshp_function_info fi
               LEFT JOIN dbmshp_parent_child_info pci ON fi.runid = pci.runid AND fi.symbolid = pci.childsymid
        WHERE  fi.runid = 3
        AND    NOT fi.module LIKE 'DBMS_HPROF%'
        ) a
CONNECT BY a.parentsymid = PRIOR a.symbolid
START WITH a.parentsymid IS NULL;

I can see that CUST_PARALLEL_JOBS.CUST_SIMULATE_SURRENDER calls XXXXX_SCHEDULER.SCHEDULER_ENGINE.RUN_JOB and that calls DBMS_SCHEDULER.RUN_JOB.

Symbol  Parent                                                                                                       Line            Elapsed    Elapsed
     ID  Sym ID NAME                                                                                                     #   CALLS       Time       Time
------- ------- ---------------------------------------------------------------------------------------------------- ----- ------- ---------- ----------
     18         XXXXX_CUST_ADDON.CUST_SCHED_SIMSURRENDERS                                                                1       1   78254334        570
      3      18   XXXXX_CUST.CUST_PARALLEL_JOBS.CUST_SIMULATE_SURRENDER                                                105       1   77139478       1232
      4       3     XXXXX_CUST.CUST_PARALLEL_JOBS.CUST_SIMULATE_SURRENDER.C_CFG                                        119       1        102         12
    136       4       XXXXX_CUST.CUST_PARALLEL_JOBS.__static_sql_exec_line120                                          120       1         90         90
…
     52       3     XXXXX_SCHEDULER.SCHEDULER_ENGINE.RUN_JOB                                                           770       7      58708        176
     56      52       XXXXX_SCHEDULER.SCHEDULER_UTILS.LOG_AUDIT_EVENT                                                  173       7         45         40
    115      56         SYS.DBMS_OUTPUT.PUT_LINE                                                                       109      41         43         43
     57      52       XXXXX_SCHEDULER.SCHEDULER_UTILS.SCHEMA_OWNER                                                     238       7         24         24
    124      52       SYS.DBMS_SCHEDULER.RUN_JOB                                                                       546       7      58463        239
    104     124         SYS.DBMS_ISCHED.CHECK_COMPAT                                                                  3509       7         11         11
    112     124         SYS.DBMS_ISCHED.RUN_JOB                                                                        242       7      44391      44391
…

Now I know which code to examine. This query outer joins the profiler data to the source code. NB. Any wrapped code will not be available in the ALL_SOURCE view. You might want to unwrap it, at least in a test environment (see Philipp Salisberg's PL/SQL Unwrapper for SQL Developer).

break on owner on name skip 1 on type
SELECT s.owner, s.type, s.name, h.function, s.line, 
       h.function_elapsed_time/1e6 function_elapsed_time, h.calls, s.text
FROM   all_source s
  LEFT OUTER JOIN dbmshp_function_info h
    ON s.owner = h.owner and s.name = h.module and s.type = h.type and s.line = h.line# and h.runid = 3
WHERE ((         s.owner = 'XXXXX_CUST'
             AND s.name = 'CUST_PARALLEL_JOBS'
             AND s.type = 'PACKAGE BODY'
             AND s.line between 100 and 300
       ) OR (    s.owner = 'XXXXX_SCHEDULER'
             AND s.name = 'SCHEDULER_ENGINE'
             AND s.type = 'PACKAGE BODY'
             AND s.line between 770 and 858
      ))
ORDER BY s.owner, s.name, s.type, s.line
/

Now, I can scan through the code and see how the code blocks were called.

                                                                                     Function
                                                                                      Elapsed
OWNER           TYPE         NAME                 FUNCTION                   LINE        Time   CALLS TEXT
--------------- ------------ -------------------- ------------------------- ----- ----------- ------- -------------------------------------------------------------------------------------------------------------
XXXXX_CUST      PACKAGE BODY CUST_PARALLEL_JOBS   CUST_SIMULATE_SURRENDER     105     .001232       1 PROCEDURE Cust_Simulate_Surrender (pi_bus_in IN SrvContext, pio_err  IN OUT SrvErr)
                                                                              106                     IS
…
                                                                              213                                     -- run current job when it is not started yet
                                                                              214                                     IF l_cfg_tbl(indx_job).allowed = 'Y' -- flag Y - to be started
                                                                              215                                     THEN
                                                                              216                                         -- run current job
                                                                              217                                         XXXXX_scheduler.scheduler_engine.Run_Job (l_cfg_tbl(indx_job).XXXXX_job_name);
                                                                              218                                         --XXXXX_scheduler.scheduler_engine.enable_Job (l_cfg_tbl(indx_job).XXXXX_job_name);
…
XXXXX_SCHEDULER PACKAGE BODY SCHEDULER_ENGINE     RUN_JOB                     770     .000176       7 PROCEDURE RUN_JOB( PI_JOB_NAME SCHEDULER_JOBS.JOB_NAME%TYPE )
…
                                                                              778                     IS
                                                                              779                     BEGIN
                                                                              780                         DBMS_SCHEDULER.RUN_JOB(
                                                                              781                             SCHEDULER_UTILS.SCHEMA_OWNER || '."' || PI_JOB_NAME || '"', USE_CURRENT_SESSION=>FALSE );
                                                                              782
                                                                              783
                                                                              784
                                                                              785                         SCHEDULER_UTILS.LOG_AUDIT_EVENT( 'RunJob', TRUE, PI_OBJECT_NAME => PI_JOB_NAME );
                                                                              786                     EXCEPTION
                                                                              787                       WHEN OTHERS THEN
…
                                                                              798                     END;
                                                                              799
                                                                              800
                                                                              801                     --dmk 29.3.2023 added
                                                                              802                     PROCEDURE ENABLE_JOB( PI_JOB_NAME SCHEDULER_JOBS.JOB_NAME%TYPE )
…
                                                                              810                     IS
                                                                              811                     BEGIN
                                                                              812                         DBMS_SCHEDULER.Enable(
                                                                              813                             SCHEDULER_UTILS.SCHEMA_OWNER || '."' || PI_JOB_NAME || '"');
                                                                              814
                                                                              815
                                                                              816
                                                                              817                         SCHEDULER_UTILS.LOG_AUDIT_EVENT( 'Enable_Job', TRUE, PI_OBJECT_NAME => PI_JOB_NAME );
                                                                              818                     EXCEPTION
                                                                              819                       WHEN OTHERS THEN
…
                                                                              830                     END;

By following the profiler data, I have found that DBMS_SCHEDULER.RUN_JOB was used.  I was then able to add an alternative procedure that calls DBMS_SCHEDULER.ENABLE and call that from the custom application code.

Purging PeopleTools Physical Cache Files after Database Restore or Database Flashback

I have 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
/