Monday, April 29, 2024

Enabling Cursor Sharing in PeopleSoft Processes

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.  

Reusing AE Statements

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.

Cursor Sharing

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.

Session Settings for Processes Executed on the Process Scheduler 

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.

CREATE OR REPLACE TRIGGER sysadm.set_prcs_sess_parm
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOLLOWS sysadm.psftapi_store_prcsinstance 
WHEN (new.runstatus = 7 AND old.runstatus != 7 AND new.prcstype != 'PSJob')
  l_cmd VARCHAR2(100 CHAR);
  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;
    END IF;

The first delivered program that was a candidate for cursor sharing was GLPOCONS (GL Consolidations process).  All that is necessary is to insert the relevant metadata, and it will apply the next time the process starts.  Anything you can set with an ALTER SESSION command can be put in the metadata.  At times, other settings have been defined, hence the insert statement is written in this way.

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

Cursor Sharing in Stand-Alone Application Engine Programs

In PeopleSoft, some Application Engine programs are executed by other programs.  For example, the General Ledger Revaluation process (FSPCCURR) and (GLPOCONS), will directly invoke the Journal Edit and Budget Check process (GL_JEDIT2) for each journal that needs to be edited.  GL_JEDIT2 inherits the process instance of the FSPCCURR process that invoked it, but there is no process scheduler request record for it to update, so the trigger technique described above does not work.

A different approach, specific to GL_JEDIT2 is required.  The first thing GL_JEDIT2 does is write the current process instance number onto the JRNL_LN records it is working on.


The update statement may update many rows, but I only want to enable cursor sharing once.  Therefore I have created a compound trigger. 

  • The trigger only fires when a statement updates PS_JRN_LN.PROCESS_INSTANCE from a zero to a non-zero value.
  • The after statement section executes once after the update statement completes.  This will contain the logic that checks the setting of module to verify that this is a GL_JEDIT2 process and that the current process instance is a process that is currently executing.  It also enhances the value of the MODULE setting with the process name and instance; thus making it possible to determine which GL_JEDIT2 process was invoked by which parent process.  Finally, it enables cursor sharing for the current session.  However, the after statement section cannot read the data values being updated.
  • Therefore an after row section is needed to collect the process instance.  It fires for each row being updated.  It is as minimal as possible to avoid adding overhead to the update statement.  It copies the updated value of PROCESS_INSTANCE to a global PL/SQL variable, and nothing else.  The variable value can then be read in the after statement section.
  • The dbms_output commands are left over from testing and have been commented out in the final trigger.
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)
  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';

    l_process_instance := :new.process_instance;
    IF l_process_instance != 0 THEN
      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_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;
      --dbms_output.put_line('Cannot find running '||l_prcsname||' process instance '||l_process_instance);
      NULL; --cannot find running process instance number
      --dbms_output.put_line('Other Error:'||sqlerrm);

END gfc_jrnl_ln_gl_jedit2;

Thursday, April 11, 2024

Configuring Shared Global Area (SGA) in a Multitenant Database with a PeopleSoft Pluggable Database (PDB)

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.

SGA Management with a Parse Intensive System (PeopleSoft).

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.

Initialisation Parameters

  • 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.

Oracle Notes

  • 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."