Cursor Sharing in Scheduled Processes: 4. How to Identify Candidate Processes for Cursor Sharing

This is the last 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.

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.

Candidates for Cursor Sharing

Use ASH for a given process to identify candidate processes by calculating the following measures.

• Elapsed time of the process from the first to the last ASH sample.  This is not the elapsed duration of the client process, but it will be a reasonable approximation. Otherwise, you can get the exact duration from the process request record (PSPRCSRQST).
• Total database time for a process (all ASH samples).
• Total time that a process is restrained by the resource manager (where EVENT is 'resmgr: CPU quantum')
• Total database time spent on CPU (where EVENT is null).
• Total database time spent on SQL parse (where IN_PARSE flag is set to Y)
• Number of distinct SQL IDs.
• Number of distinct force matching signatures.

Look for processes with high elapsed time, of which a significant proportion is spent on both CPU and SQL parse.  This should correlate with processes where there are many more SQL IDs than force matching signatures.

Is Cursor Sharing Enabled Already?

It is possible to determine whether cursor sharing is already set for a process, although this is not explicitly recorded.  

• If cursor sharing is not enabled then the number of distinct SQL_IDs should be greater than the number of distinct force-matching signatures. This may not be the case if you don't have enough ASH samples, but then the program probably doesn't consume enough time for it to be worth considering cursor sharing.
• If the number of SQL_IDs is equal to the number of force matching signatures then cursor sharing is probably enabled, but again this could be unreliable if the number of ASH samples is low (and close to the number of SQL IDs).
• It should be impossible for the number of distinct SQL IDs to be less than the number of distinct force matching signatures, but it can happen due to quirks in ASH sampling.
  

I have coded this into my queries.  It will be reasonably accurate if you have several ASH samples per SQL ID.  Otherwise, you may detect false positives.

Sample Queries and Output

I have written a couple of queries that I have published on GitHub.  They happen to be specific to nVision, but can easily be extended to other processes.

• The first query calculates average values for each process/run control ID combination within the AWR retention period (high_parse_nvision_avg.sql)

Having implemented cursor sharing for a particular process it is necessary to watch it over time and decide whether the change has been effective. The metrics shown below come from a real system (although actual run control IDs have been changed).  

• All the timings for NVS_RPTBOOK_1 have come down significantly. The number of SQL_IDs has dropped from 238 to 11.  The number of force matching signatures has also dropped, but that is because we have fewer ASH samples and some statements are no longer sampled at all.  Cursor sharing is beneficial and can be retained.
• However, this is not the case for the second process. Although NVS_RPTBOOK_2 looked like a good candidate for cursor sharing, and the parse time has indeed come down, all the other durations have gone up.  The cursor sharing setting will have to be removed for this report.

                                            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 31^st 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
…

Note that on 5^th August the report erroneously claims that cursor sharing went back to EXACT.  This is because there are more SQL_IDs than force matching signatures.  Again, this is a quirk of ASH sampling. 

Cursor Sharing in Scheduled Processes: 3. How to Set Initialisation Parameters for Specific Scheduled Processes

This is the third 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.

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.

Session Settings for Processes Executed on the Process Scheduler 

It is straightforward 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 database trigger can be created on this transition that will then be executed in the session of the process.  

I initially used this technique to set other session settings for nVision reports.  I used a table to hold a list of the settings, and the trigger matches this metadata to the processes being run by up to 4 attributes: process type, process name, operation and run control.

It is usual to set up different run controls to run different instances of the same code on different sets of data.  I sometimes see certain run controls set up to regularly run certain nVision or other reports.

• 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;
/

The first delivered program that was a candidate for cursor sharing was GLPOCONS (GL Consolidations process).  It is only necessary is to insert the corresponding 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 in this example the insert statement is written in this way, and you can also see that in-memory query has been disabled for the same process. 

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
/

Stand-alone Application Engine (PSAE) -v- Application Engine Server PSAESRV

In PeopleTools 8.4, the Process Scheduler became a fully-fledged Tuxedo domain and the Application Engine server process PSAESRV was also introduced.  Stand-alone Application Engine executable psae is still available, but the Tuxedo server process is configured by default.  The Tuxedo server process is a persistent process that creates a persistent database connection that may service many different Application Engine programs during its lifetime.  If you make session settings during the execution of one Application Engine program, they will still be set when the same server process executes the next program. Generally, you don't want session settings bleeding from one process request to another.  Instead, any setting made at the start of the process would have to be reverted to the previous value at the end.  That is not necessarily the same as resetting it back to the default.

This is, therefore, another reason why it is preferable to revert to using the legacy stand-alone Application Engine process (psae) that creates a new database session for each request.

The set_prcs_sess_parm  trigger does not save or reset the previous value for settings it makes. Therefore, it should NOT be used in conjunction with PSAESRV.

• See also Application Engine in Process Scheduler: PSAESRV Server Process -v- Standalone PSAE executable
• Oracle Support Document 747389.1: PeopleTools Performance Guidelines -> Using PSAE and PSESRV

Cursor Sharing Application Engine Programs Spawned Directly by COBOL Programs

In PeopleSoft, some COBOL programs directly spawn stand-alone Application Engine processes.  These processes do not update the status on the process request record, so the set_prcs_sess_parm trigger described above does not fire.  

A different data change must be found upon which to place a trigger.  It may be different for different processes.  In Financials, GL_JEDIT2 is such a process, and it is a good candidate for cursor sharing.  

I chose to create a hard-coded compound trigger on the insert into the journal line table (PS_JRNL_LN).  

• See gfc_jrnl_ln_gl_jedit2_trigger.sql
• This update is specific to this process, so the trigger is simply hard-coded. It does not use any metadata.
• The after row part of the trigger copies the process instance number from the JRNL_LN rows being inserted into a local variable. This is deliberately minimal so that overhead on the insert is minimal
• The after statement part of the trigger cannot be directly read from the table that was updated.  Instead, it checks that the process instance number, that was captured during the after row section and stored in the local variable, is for an instance of FSPCCURR or GLPOCONS that is currently processing (PSPRCSRQST.RUNSTATUS = '7').  If so it sets CURSOR_SHARING to FORCE at session level.  
• The ALTER SESSION command is Data Dictionary Language (DDL).  In PL/SQL this must be executed as dynamic code.
• The FSPCCURR and GLPOCONS COBOL processes may each spawn GL_JEDIT2 many times. Each runs as a separate stand-alone PSAE process that makes a new connection to the database, runs and then disconnects.  Cursor sharing is enabled separately for each.

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;
/

Cursor Sharing in Scheduled Processes: 2. What happens during SQL Parse? What is a 'hard' parse? What is a 'soft' parse?

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.

Cursor Sharing in Scheduled Processes: 1. Introduction

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.

PeopleSoft applications make extensive use of configuration data that is then interpreted by dynamic code. This leads to lots of similar statements with different literal values; statements that differ only by literal values are still different. In an Oracle database, each statement that cannot be exactly matched with a statement already in the Shared SQL Area (also known as the Cursor Cache) must be parsed.  Thus, each distinct statement must be parsed at least once.  

Hence, PeopleSoft systems have many SQL statements that are only ever executed once,each of which has to be fully parsed.  This is often called a 'hard' parse.  With PeopleSoft, this parse overhead can have a significant impact on performance.

In an Oracle database, CURSOR_SHARING can be set to FORCE (the default is EXACT). Then, it will substitute the literals with system-generated bind variables. The statement is still exactly matched against the contents of the Shared SQL Area, but now statements that previously only differed by their literal values will match the same bind variables.  Oracle can omit some of the parse processing for matched statements, leaving what is often called a 'soft' parse. This can significantly reduce the CPU overhead of SQL parse, and thus improve the performance of some processes.

Do not set CURSOR_SHARING to FORCE at database level.  Over the years, I have tried this several times with different PeopleSoft systems, and on various different versions of Oracle.  The net result was always negative. Some things improved, but many more degraded and often to a greater extent.

However, I have found that it can be effective to set it at session-level for specific processes, and sometimes just specific run controls for specific processes.  

What is Oracle's advice?

Oracle has always been clear that it is always better to write sharable SQL (i.e. that uses bind variables), and that setting CURSOR_SHARING = FORCE should not be used as a permanent fix.

As a general guideline, the Oracle Real-World Performance group recommends against setting CURSOR_SHARING to FORCE except … when all of the following conditions are met:

1. Statements in the shared pool differ only in the values of literals 
2. Response time is suboptimal because of a very high number of library cache misses.
3. Your existing code has a serious security and scalability bug—the absence of bind variables—and you need a temporary band-aid until the source code can be fixed.
4. You set this initialization parameter at the session level and not at the instance level.

See SQL Tuning Guide: Improving Real-World Performance Through Cursor Sharing -> Real-World Performance Guidelines for Cursor Sharing -> Do Not Use CURSOR_SHARING = FORCE as a Permanent Fix

I submit that my approach in PeopleSoft meets at least 3 these of conditions.  As for the third criterion, we are talking about SQL statements that are either delivered as part of PeopleSoft code or that are dynamically generated from configuration data, also by delivered code.  The reality is that these are as 'fixed' as they are going to be within PeopleSoft.  Most of this code is at least very difficult, if not impossible, for the customer to alter, and the cost and risk of owning and maintaining any such customisation is almost certainly prohibitive.  Cursor sharing may be a band-aid, but it will be there for the long term.

The next article looks at how the Oracle database parses SQL statements.

Enabling Cursor Sharing in PeopleSoft Processes

This article has been rewritten and is now part of a 4-part series on the subject.

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

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

PSFT_PLAN: A Sample Oracle Database Resource Manager Plan for PeopleSoft

In the cloud (or any virtualised environment), performance is instrumented as cost.  This is also true in any other on-premises environment, but it takes a lot longer to feedback!

• If you never run out of CPU, then you have probably bought/rented/allocated/licensed too many CPUs.
• If you do run out of CPU, then you should use the database resource manager to prioritise the processes that are most important to the business.
• If you don't enable the resource manager, you will have less visibility of when you do run out of CPU.

At the very least, you can use one of the sample resource manager plans available in the Oracle database by default.  

This article proposes a resource plan for PeopleSoft systems.  It can be used as a starting point before enhancing it with your own specific requirements.  

• See also Go-Faster Blog: More Bang for your Buck in the Cloud with Resource Manager

Resource Plan Design Goals

The purpose of a database resource plan is to prioritise important/urgent processes over less important/less urgent processes by allocating CPU, to the higher priority processes, and by restricting CPU, other resources, and the degree of parallelism for lower priority processes.

The design of a resource plan should reflect what the business defines as important.  

Consumer Groups

A resource plan consists of several resource groups with different priorities, and resource allocations. Each priority level defined by the business becomes a consumer group in the resource plan.  A consumer group can be allocated to one of 8 priority levels in a resource plan.  Multiple consumer groups can exist at the same priority level with different CPU guarantees (adding up to not more than 100%) and can include other limits.

I have made some assumptions about process priorities in a typical PeopleSoft system, and have grouped and ranked them in the table below starting with the highest priority.  Not all customers run all these processes.  Consumer groups and mappings that are not needed can be omitted. There are gaps in the priority levels to allow for other definitions to be introduced.

Priority Level	Consumer Group	%CPU Guarantee	Comment
1	SYS _GROUP	100%	Oracle system processes. Defined automatically.
2	PSFT _GROUP	100%	Any process that connects to the database as either SYSADM (the default PeopleSoft owner ID) or PS has higher priority than other processes unless other rules apply. The online application (other than ad hoc query) falls into this category so that the online user experience is safeguarded before other PeopleSoft processes. This includes remote call Cobol processes, but not remote call Application Engine that should be run in the component processor.
4	BATCH _GROUP	100%	Process scheduler processes, and processes run by the process schedulers
5	NVISION _GROUP	100%	nVision (NVSRUN) and nVision report book (RPTBOOK) processes
6	PSQUERY _ONLINE _GROUP	90%	Ad hoc queries are allocated to one of three consumer groups with the same priority, but different CPU guarantees, comprising: on-line PS/Queries, nVision reports run through the PIA,
	PSQUERY _BATCH _GROUP	9%	PS/Queries run on the process scheduler using the PSQUERY application engine. A 4-hour maximum runtime limit is defined.
	NVSRUN _GROUP	1%	nVision through the 3-tier nVision client
8	LOW _GROUP	1%	Other low-priority processes
	LOW _LIMITED _GROUP	1%	Other low-priority processes, but whose maximum query time is limited.
	OTHER _GROUPS	1%	All other processes.  Defined automatically.

See also DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE

Consumer Group Mapping Priority

Sessions are allocated to the consumer groups.  They can be allocated explicitly, or via mapping rules that use various session attributes. As the attributes are set or changed, the consumer group will be set according to the matching rules.  

I have set the following attributes to be mapped in the following order of precedence.  The more specific mappings take precedence over the more generic ones.

Priority	Mapping Attribute	Comment
2	Module, Action	The PIA instrumentation sets attributes MODULE to the component name and ACTION to the page name. Specific component pages are allocated to specific consumer groups
3	Module	Specific scheduled processes are allocated by name to specific consumer groups.  PeopleSoft instrumentation puts this name in the MODULE attribute.
4	Client Program	Batch and query processes are identified by program name and allocated to certain consumer groups.
5	Oracle User	Anything that connects to the database as either SYSADM or PS is allocated to the PSFT_GROUP. So other mapping rules must take precedence over this mapping.

See also DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI

Required PeopleSoft Configuration

The PSFT_PLAN sample resource manager plan relies on MODULE and ACTION being set by the PeopleSoft Application.  Therefore, the following additional configuration is required.

• Enable PeopleSoft instrumentation: Set EnableAEMonitoring=1 in ALL PeopleSoft application server and process scheduler domains so that PeopleSoft processes set MODULE and ACTION information in the session attributes (using DBMS_APPLICATION_INFO).  

See also:

• PeopleTools PeopleBooks: Administration Tools -> Data Management -> Administering PeopleSoft Databases on Oracle -> Monitoring PeopleSoft MODULE and ACTION Information, Press Enter to collapse
• PeopleSoft DBA Blog: Undocumented (until PeopleTools 8.55) Application Engine Parameter: EnableAEMonitoring 
• PeopleSoft DBA Blog: PeopleTools 8.50 uses DBMS_APPLICATION_INFO to Identify Database Sessions
• Go-Faster Blog: One of my Favourite Database Things: DBMS_APPLICATION_INFO

• Install instrumentation trigger for PeopleSoft (psftapi.sql).  Not all PeopleSoft processes are instrumented.  COBOL, SQR, and nVision do not set MODULE or ACTION.  When a PeopleSoft process is started by the process scheduler, the first thing it does is set its own status to 7, meaning that it is processing.  This script creates a database trigger that fires on that DML and sets the session attributes MODULE to the name of the process and ACTION to the process instance number.  Application Engine processes may then subsequently update these values again.

Consumer Group Mappings

Consumer groups are matched to session attributes.  The highest priority matching mapping is applied.  Mappings can be matched to literal values, or with LIKE or REGEXP_LIKE operations.

Mapping Priority	Attribute	Value	Consumer Group Priority	Consumer Group
2	MODULE_ACTION	QUERY_MANAGER.QUERY_VIEWER	6	PSQUERY_ONLINE_GROUP
3	MODULE	RPTBOOK NVSRUN	5	NVISION_GROUP
		PSQRYSRV%	6	PSQUERY_ONLINE_GROUP
		PSAE.PSQUERY.%	6	PSQUERY_BATCH_GROUP
4	CLIENT_PROGRAM	PSRUNRMT	2	PSFT_GROUP
		psae% PSAESRV% PSDSTSRV% PSMSTPRC% PSRUN@% PSSQR% pssqr% sqr%	4	BATCH_GROUP
		PSQRYSRV%	6	PSQUERY_ONLINE_GROUP
		PSNVSSRV%	6	NVSRUN_GROUP
		SQL Developer sqlplus% Toad%	8	LOW_GROUP / LOW_LIMITED_GROUP
5	ORACLE_USER	PS SYSADM	2	PSFT_GROUP

See also DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING

Resource Plan Script

Two SQL scripts are available on GitHub

• psft_resource_plan_simple.sql creates the resource plan.  This is intended to be a starting point to which either unwanted parts can be removed, or additional requirements can be added
• resource_plan_report.sql reports on all the resource plan metadata.

Other Options

There are other resource manager options that are either not illustrated in the sample plan, or that are commented out.  They may be worth considering in some situations.

• PeopleSoft does not use parallel query by default, but if you do use it, you may well want to limit which processes use how much parallelism.  Consumer groups can specify a limit to the parallel query degree.
• If you use the resource plan to restrict the degree of parallelism, and you also plan to vary the number of CPUs in a cloud environment, then I suggest creating a resource plan for each number of CPUs and switch between the plans by changing the setting of  the RESOURCE_MANAGER_PLAN parameter.

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

• A parallel query may queue waiting to obtain sufficient parallel query server processes.  A timeout can be specified to limit that wait and to determine the behaviour when the timeout is reached.  The query can either be cancelled raising error ORA-07454, or run at a reduced parallelism).

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

• A consumer group can restrict queries that run for a long time, or that are expected to run for a long time based on their optimizer cost.  They can be switched to the CANCEL_SQL group after a number of seconds and they will terminate with ORA-00040: active time limit exceeded - call aborted:.  This has only specified for the LOW_LIMITED_GROUP, and the PSQUERY_BATCH_GROUP for scheduled queries because the message is captured by the process scheduler and logged.  It has not been specified for PSQUERY_ONLINE_GROUP because this error is not handled well by the online application.  Just the Oracle error message will be displayed to the user without further explanation, which is neither friendly nor helpful.  Instead, there are PeopleSoft configuration options to limit query runtime.

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 customers may have different priorities and different priorities at different times that cannot be satisfied by a single resource plan.  In which case, different resource plans can be activated at different times by different scheduler windows. 

Other Online Resources

• Oracle White Paper: Using Oracle Database ResourceManager
• Documentation: Oracle Resource Manager Enhancements in Oracle Database 10g 
• Documentation: Oracle Resource Manager Enhancements in Oracle Database 12c Release 1 (12.1)

What PS/Query is that?

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. 

Is it a PS/Query?

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.
  

Sometimes, you may find SQL that looks like a PS/Query coming from other parts of PeopleSoft because a developer has copied the text of a PS/Query, usually into an Application Engine step.

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.  

PSQRYRECORD holds a row for every record referenced in the query (not including effective date/sequence subqueries).  My usual tactic is to write a SQL query on PSQRYRECORD, like the one below, that looks for PS/Queries that reference these tables with these table aliases (see PeopleSoft for the Oracle DBA, Chapter 11).  

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'
/

The example PS/Query above is TRN003__COURSE_WAITING_LIST from the HCM demo database.  However, my query on PSQRYRECORD found another PS/Queries with the same 3 records using the same row source aliases.  It is worth looking at queries on the same tables as they often suffer from the same problems, and you might want to make the same fix.  

Another source of results for this query (though not this time) can be when users copy a public PS/Query to a private one so they can alter it in isolation.

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.

Start with a SQL_ID

A SQL tuning activity will usually identify the SQL_ID and plan hash value of a statement.  If you are lucky, AWR will have captured the text and execution plan.  If not, you may have to try looking for a different SQL_ID that produces the same execution plan.  From the statement text, it is easy to see whether it might be a PS/Query.  

In this example, I have cut the SQL statement and execution plan back to show just the tables and indexes referenced.

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
/

I now have a list of records and row source aliases aliases

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

The query finds several queries. I can look at the public PS/Queries in the Query Manager tool.  I can also see which users' private queries exist.
NB. You can only open public queries (where OPRID is a single space) or your own private queries.  In the Query Manager, you cannot see a private query owned by another user.

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

The new findqry.sql script is available on Github.

Reducing the Operating System Priority of PeopleSoft Processes

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.

On Linux and Unix systems, the nice command can be used to lower the operating system scheduling priority of a process (or a privileged can increase the priority). When a server has no free CPU, processes with a lower priority get less time on the CPU. However, when there is free CPU available, the scheduling priority does not affect the amount of CPU that the process can utilise. 

On Unix, the priority of a Tuxedo server process can be adjusted using the -n server command line option in the configuration. The parameters to this option are simply passed through to the nice(2) function. Hence, this option does not work on Windows.

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"

The operating system priority of a process is inherited from its parent. Therefore, lowering the priority of the Process Scheduler running under Tuxedo will also lower the priority of the batch processes that it spawns. 

• Therefore Stand-alone Application Engine processes (psae) and Cobol processes inherit the priority of the process scheduler server process (PSPRCSRV).
• However, if the Application Engine server process (PSAESRV) is used, its priority can be set directly. 

There are some potential uses for this approach.

• If the process scheduler is co-resident with the application server, then it could be run at a lower priority to ensure the online users get preferential allocation of CPU, and that online performance does not suffer excessively at the hands of the batch.
• A system might have two websites: one for self-service and the other for the 'back-office' users. You could configure separate application servers for each site, and run the self-service application server is run at a lower priority. 

In PeopleSoft, I prefer to create additional variables in the configuration file (psprcs.cfg).

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

From PeopleTools 8.4, the Application Engine server process is configured by default. The priority of the AE server processes can then be controlled independently of the process scheduler by creating a separate variable in the PSAESRV section of the configuration file.  However, it is generally better to use standalone PSAE, unless you have many short-lived application engine processes, as in CRM (see Application Engine in Process Scheduler: PSAESRV Server Process -v- Standalone PSAE executable).   

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

In this example, I have reduced the priorities of both the process scheduler and AE servers, but the process scheduler is left with a higher priority than the AE servers. The new variables can then be referenced Tuxedo template file (psprcsrv.ubx).

{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"

When the domain is configured in psadmin, the variables are resolved in the Tuxedo configuration file (psprcsrv.ubb).  The -n option can be seen in the server command-line options (CLOPT).

#
# 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"