The PeopleSoft DBA Blog

Managing Ad Hoc PS/Queries in PeopleSoft

noreply@blogger.com (David Kurtz) — Tue, 06 Jan 2026 15:30:00 +0000

This post draws together all the recommendations for managing ad hoc user queries in PeopleSoft PS/Query. Some points relate to the database, some to the configuration of the middleware (Application servers and process schedulers), and some are human issues rather than technical ones.

Different timeouts are set in different places, but some of these timeouts need to align.

Long-running queries mostly consume CPU on the database, and in the cloud, most of what you pay for is CPU. Nobody would want to allow an unconstrained query load to lead to an increase in the cloud subscription requirements!

Recommendations

Do not increase the PIA inactivity timeout or PS/Query timeouts. They are both delivered set at 20 minutes. They are primarily there to protect users and the system as a whole.

If you have already increased them, you may need to bring them down in stages, allowing users to get used to the new limits and handle exceptional queries.
If you permit users to use PS/Query, then the PIA inactivity timeout should be the same ICQuery Tuxedo service timeout set on the PSQRYSRV server (default 20 minutes)
If you do not use online PS/Query, then the PIA timeout can be reduced as low as the PSAPPSRV timeout (default 5 minutes).

It is not possible to regulate when users run queries in the PIA. Users, quite legitimately, also need to run queries that may take longer than the timeouts. Encourage users to schedule such queries on the process scheduler.

You can restrict the number of online queries that execute concurrently on the database by restricting the maximum number of PSQRYSRV processes. There is still nothing to prevent users from starting queries which then just queue up on the application server. Users cannot distinguish between a query that is queuing in the application server or executing on the database. The service timeout starts from when the service request is queued in the application server.

However, you still don’t want even scheduled queries to run for hours on end. Set a maximum processing time in the process definition for the PSQUERY application engine. Say 4 hours. The process scheduler will terminate the PSQUERY process after this amount of time.

A common cause of long-running PS/Queries is a mistake in the query definition, resulting in missing or incorrect joins in the SQL.
A SQL query can continue to run on the database server after the client-side process, the PSQRYSRV server process or the PSQUERY application engine process, has terminated, at least until the current fetch returns. A common cause is that a large sort or hash operation must complete before the first fetch returns.

Enable dead connect detection by setting SQLNET.EXPIRE_TIME in SQLNET.ORA. The idle Oracle shadow process will periodically poll the client process to check that it is still responsive. If a PSQUERY process has been terminated by the process scheduler, this mechanism should also terminate the session if it is still running on the database.

See Oracle Terminated Connection Timeout

Use the database resource manager to allocate and manage CPU allocation and limit run time.

The resource manager only intervenes when the system runs out of free CPU.
Define a hierarchy of resource manager consumer groups. Map database sessions to consumer groups using session instrumentation. In general, online processing is higher priority than scheduled processes, which are higher priority than ad-hoc queries.

See PSFT_PLAN: A Sample Oracle Database Resource Manager Plan for PeopleSoft

Set a maximum CPU/runtime limit for scheduled queries. Using different consumer groups for online and scheduled batch queries.

For scheduled queries, match the consumer group timeout to the maximum runtime for the PSQUERY application engine.
For online queries, match the consumer group timeout to the PIA inactivity timeout (which should also be the same as the ICQuery Tuxedo service timeout).
See A Resource Manager CPU Time Limit for PS/Queries Executed Online in the PIA

NB: Resource Manager is an Oracle Enterprise Edition Feature.

On Exadata, use SQL Quarantine to identify queries with a track record of exceeding the consumer group timeout.

Enable SQL Quarantine for scheduled queries only using a trigger on PSPRQSRQST – see Github: set_prcs_sess_parm_trg.sql
Quarantined queries are immediately terminated with error ORA-00040: active time limit exceeded - call aborted. For scheduled queries, this error can be found in the process scheduler message log.
Enable SQL Quarantine for SQL Developer, etc, using an on-logon trigger – see Github: set_sess_parm_trg.sql

Add run control logging for scheduled PS/Queries

Whenever a scheduled PS/Query, run via PSQUERY, terminates other than successfully, the SQL query can be found in the message log. However, bind variable values are not recorded.
Bind variable values may be changed between executions of the same query using the same run control by the same user. Enable run control logging so that it is possible to reconstruct the query and bind variables run by the user.

See Logging Run Controls and Bind Variables for Scheduled PS/Queries

There is nothing to stop the user from altering the PS/Query between executions, in which case you may need to get the SQL of a long-running query that completes successfully from AWR.

See: Management of Long Running PS/Queries Cancelled by Resource Manager CPU Limit

It is not possible to allocate PS/Queries to a different temporary tablespace because that is determined by the database user ID. Everything in PeopleSoft runs as the Owner ID (usually SYSADM). Unless you are faking a reporting database with a second schema pointed to by a second entry in PS.PSDBOWNER.

Determining Optimal Index Key Compression Length

noreply@blogger.com (David Kurtz) — Fri, 25 Jul 2025 10:25:00 +0000

In 2008, Richard Foote wrote this still excellent 4-part guide to index key compression.

He started with this comment, which I think is just as valid as it was then:

“Index compression is perhaps one of the most under used and neglected index options available. It has the potential to substantially reduce the overall size of non-Unique indexes and multi-column unique indexes, in some scenarios dramatically so... Not only will it potentially save storage, but if the resultant index contains fewer leaf blocks, that’s potentially fewer LIOs and from the Cost Based Optimizer’s point of view, potentially a cheaper execution plan option.”

Index key compression is a highly effective option for reducing index size and improving index performance.

“Oracle stores each distinct combination of compressed column values found within a specific index leaf block in a ‘Prefix’ table within the leaf block and assigns each combination a unique prefix number.” If the prefix length (the number of leading columns to be compressed) is too great, then the prefix table will contain more entries, ultimately one for every row in the index. The compressed index could end up being larger than the uncompressed index! If the prefix length is too small, then you might not get as much compression as you might with a longer prefix length.

In other words, there is a sweet spot where you will achieve optimal compression. That sweet spot can vary from no compression to compressing all the columns. It will vary from index to index, from partition to partition, and potentially over time as the data in an index changes.

Test Every Option to Determine Optimal Compression

One way to determine optimal compression is through exhaustive testing. Each index could be rebuilt at each possible compression prefix length, and the size of the index could be compared, and the performance of application processes could be tested.

The following PL/SQL script (available on GitHub) rebuilds each index on a named table at each possible compression length, collects statistics and stores them in a table.

REM index_compression_test.sql
create table gfc_index_compression_stats
(table_name varchar2(128)
,index_name varchar2(128)
,num_rows number
,last_analyzed date
,prefix_length number 
,blevel number 
,leaf_blocks number 
,avg_leaf_blocks_per_key number 
,avg_data_blocks_per_key number 
,clustering_factor number 
,constraint gfc_index_compression_stats_pk primary key (table_name, index_name, prefix_length)
);

DECLARE
  l_table_name VARCHAR2(128) := 'PSTREENODE';
  l_num_cols INTEGER;
  l_sql CLOB;
  e_invalid_compress_length EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_invalid_compress_length,-25194); 
BEGIN
  FOR i IN (
    SELECT table_name, index_name, column_position prefix_length FROM user_ind_columns
    WHERE table_name = l_table_name
    UNION
    SELECT table_name, index_name, 0 FROM user_indexes
    WHERE table_name = l_table_name
    ORDER BY table_name, index_name, prefix_length DESC
  ) LOOP
   IF i.prefix_length > 0 THEN 
     l_sql := 'ALTER INDEX '||i.index_name||' REBUILD COMPRESS '||i.prefix_length;
   ELSE
     l_sql := 'ALTER INDEX '||i.index_name||' REBUILD NOCOMPRESS';
   END IF;

   BEGIN
     dbms_output.put_line(l_sql);
     EXECUTE IMMEDIATE l_sql;
     dbms_stats.gather_index_stats(user,i.index_name);
   
     MERGE INTO gfc_index_compression_stats u
     USING (SELECT * FROM user_indexes WHERE table_name = i.table_name And index_name = i.index_name) s
     ON (u.table_name = s.table_name AND u.index_name = s.index_name AND u.prefix_length = NVL(s.prefix_length,0))
     WHEN MATCHED THEN UPDATE SET u.num_rows = s.num_rows, u.last_analyzed = s.last_analyzed, u.blevel = s.blevel, u.leaf_blocks = s.leaf_blocks, u.avg_leaf_blocks_per_key = s.avg_leaf_blocks_per_key, u.avg_data_blocks_per_key = s.avg_data_blocks_per_key, u.clustering_factor = s.clustering_factor
     WHEN NOT MATCHED THEN INSERT (table_name, index_name, num_rows, last_analyzed, prefix_length, blevel, leaf_blocks, avg_leaf_blocks_per_key, avg_data_blocks_per_key, clustering_factor)
     VALUES (s.table_name, s.index_name, s.num_rows, s.last_analyzed, NVL(s.prefix_length,0), s.blevel, s.leaf_blocks, s.avg_leaf_blocks_per_key, s.avg_data_blocks_per_key, s.clustering_factor);
   EXCEPTION 
     WHEN e_invalid_compress_length THEN NULL;
   END;  
  END LOOP; 
END;
/

The following chart presents the data collected by the script above for the PSTREENODE table in PeopleSoft. The number of leaf blocks is graphed against the compression prefix length. The left-hand end of each line shows the uncompressed size of the index.

For most indexes, the size decreases as the compression prefix length increases until it reaches a minimum. That is the optimal compression. Beyond that point, where the prefix columns are (or are almost) unique, the compressed index is larger than the uncompressed index. In my example, only two indexes benefit from the entire key index being compressed. For all the other indexes, the optimal compression is obtained when some, but not all, of the key columns are compressed.

There are 8 indexes on PSTREENODE, and in all, the script performed 53 index rebuilds. On a small table such as this, it only takes a few minutes to work through this, but on a larger table, this could easily become prohibitively time-consuming.

Let Oracle Calculate the Optimal Compression Prefix Length

The alternative is to let Oracle calculate the optimal compression prefix length.

Before Oracle introduced the DBMS_STATS package, we used the ANALYZE command to collect optimizer statistics. The last remaining use of this command is to validate object structures.

ANALYZE INDEX … VALIDATE STRUCTURE CASCADE;

You can validate an object and all dependent objects (for example, indexes) by including the CASCADE option. The following statement validates the emp table and all associated indexes:

ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;

[Validating Tables, Indexes, Clusters, and Materialized Views]

For an index, Oracle Database verifies the integrity of each data block in the index and checks for block corruption. This clause does not confirm that each row in the table has an index entry or that each index entry points to a row in the table. You can perform these operations by validating the structure of the table with the CASCADE clause.

Oracle Database also computes compression statistics (optimal prefix compression count) for all normal indexes.

Oracle Database stores statistics about the index in the data dictionary views INDEX_STATS and INDEX_HISTOGRAM.

[SQL Language Reference > Analyze]

The following script analyses each index.

INDEX_STATS displays only the results for the last ANALYZE command in the current session, so the script transfers them to a permanent table. It does not reanalyse indexes for which a result is already stored.

REM calc_opt_comp.sql
REM (c)Go-Faster Consultancy Ltd. 2014
REM see https://blog.psftdba.com/2016/02/implementing-index-compression-and.html
set serveroutput on autotrace off
clear columns
SPOOL calc_opt_comp

REM DROP TABLE sysadm.gfc_index_stats PURGE;

--create working storage table with same structure as INDEX_STATS
CREATE TABLE sysadm.gfc_index_stats 
AS SELECT * FROM index_stats
WHERE 1=2
/

ALTER TABLE sysadm.gfc_index_stats
MODIFY name NOT NULL
/

CREATE UNIQUE INDEX sysadm.gfc_index_stats
ON sysadm.gfc_index_stats (name, partition_name)
/

undefine table_name
DECLARE
 l_sql        VARCHAR2(100);
 l_owner      VARCHAR2(8) := 'SYSADM';
 l_table_name VARCHAR2(30) := '&&table_name';
BEGIN
 FOR i IN (
  SELECT i.index_name, ip.partition_name
  FROM   all_indexes i
  ,      all_ind_partitions ip
  WHERE  i.index_type like '%NORMAL'
  AND    i.table_owner = l_owner
  AND    i.partitioned = 'YES'
  AND    i.table_name = l_table_name
  AND    ip.index_owner = i.owner
  AND    ip.index_name  = i.index_name
  AND    ip.subpartition_count = 0
  AND    ip.segment_created = 'YES'
  UNION
  SELECT i.index_name, isp.subpartition_name
  FROM   all_indexes i
  ,      all_ind_subpartitions isp
  WHERE  i.index_type like '%NORMAL'
  AND    i.table_owner = l_owner
  AND    i.partitioned = 'YES'
  AND    i.table_name = l_table_name
  AND    isp.index_owner = i.owner
  AND    isp.index_name  = i.index_name
  AND    isp.segment_created = 'YES'
  UNION
  SELECT i.index_name, NULL
  FROM   all_indexes i
  WHERE  i.index_type like '%NORMAL'
  AND    i.table_owner = l_owner
  AND    i.table_name = l_table_name
  AND    i.partitioned = 'NO'
  AND    i.segment_created = 'YES'
  MINUS
  SELECT name, partition_name
  FROM   sysadm.gfc_index_stats
 ) LOOP
  IF i.partition_name IS NULL THEN
    l_sql := 'ANALYZE INDEX '||l_owner||'.'||i.index_name||' VALIDATE STRUCTURE';
  ELSE
    l_sql := 'ANALYZE INDEX '||l_owner||'.'||i.index_name||' PARTITION ('||i.partition_name||') VALIDATE STRUCTURE';
  END IF;

  dbms_output.put_line(l_sql);
  EXECUTE IMMEDIATE l_sql;

  DELETE FROM sysadm.gfc_index_stats g
  WHERE EXISTS(
	SELECT  'x'
	FROM	index_stats i
	WHERE 	i.name = g.name
	AND	(i.partition_name = g.partition_name OR (i.partition_name IS NULL AND g.partition_name IS NULL)));

  INSERT INTO sysadm.gfc_index_stats 
  SELECT i.* FROM index_stats i;
  COMMIT;
 END LOOP;
END;
/
…

The script produces reports of its analysis. The summary report shows the optimal compression length for each index and lists the columns that are and are not compressed. We can see that the result of the ANALYZE command agrees with the result of the previous test that rebuilt each index at each compression length and measured the size of the index.

                                                     Summary Report

                                      Opt Comp                         Weighted         Est.                            
                                        Prefix        Num               Average         Comp                            
Table Name         Index Name           Length FREQ Parts       Blocks Saving %       Blocks                            
------------------ ------------------ -------- ---- ----- ------------ -------- ------------                            
Compress Columns                                            Do Not Compress Columns                                     
----------------------------------------------------------- ----------------------------------------------------------- 
PSTREENODE         PSAPSTREENODE             4    1     0        2,048     41.0        1,208                            
SETID, TREE_NAME, EFFDT, TREE_BRANCH                        TREE_NODE, TREE_NODE_NUM, TREE_NODE_NUM_END, TREE_NODE_TYPE 
                                                                                                                        
                   PSBPSTREENODE             8    1     0        1,920     34.0        1,267                            
SETID, TREE_NAME, TREE_BRANCH, TREE_NODE_NUM, TREE_NODE, TR                                                             
EE_NODE_NUM_END, TREE_LEVEL_NUM, TREE_NODE_TYPE                                                                         
                                                                                                                        
                   PSDPSTREENODE             3    1     0        1,280     61.0          499                            
SETID, TREE_NAME, EFFDT                                     PARENT_NODE_NUM                                             
                                                                                                                        
                   PSFPSTREENODE             2    1     0        1,024     67.0          338                            
TREE_NAME, EFFDT                                                                                                        
                                                                                                                        
                   PSGPSTREENODE             2    1     0        2,304     35.0        1,498                            
PARENT_NODE_NAME, TREE_NAME                                 EFFDT, TREE_NODE, SETID                                     
                                                                                                                        
                   PSHPSTREENODE             2    1     0        2,048     24.0        1,556                            
TREE_NODE, TREE_NAME                                        EFFDT, SETID, SETCNTRLVALUE, TREE_NODE_NUM                  
                                                                                                                        
                   PSIPSTREENODE             3    1     0        1,152       .0        1,152                            
SETID, TREE_NAME, EFFDT                                     TREE_NODE, TREE_NODE_NUM, TREE_NODE_NUM_END                 
                                                                                                                        
                   PS_PSTREENODE             4    1     0        1,792     46.0          968                            
SETID, SETCNTRLVALUE, TREE_NAME, EFFDT                      TREE_NODE_NUM, TREE_NODE, TREE_BRANCH                       
                                                                                                                        
******************                                  ----- ------------          ------------                            
                                                                                                                        
                                                                                                                        
sum                                                     0       13,568                 8,486

Compression of Partitioned Indexes

If you partition an index, then the script validates the structure of each physical partition. The detailed report shows the optimal compression for each partition. You may find that Oracle determines that the optimal compression is different for different partitions.

Only a single compression length can be specified for each index. It is then applied to all the partitions, although compression can be disabled on specific partitions. A judgement has to be made as to what is the best balance.

                                                     Detail Report

                                                                     Opt Comp                             Est.          
                                                                       Prefix              Saving         Comp          
Table Name         Index Name         Partition Name                   Length       Blocks      %       Blocks          
------------------ ------------------ ------------------------------ -------- ------------ ------ ------------          
…
                   PSHJRNL_LN         JRNL_LNH201612                        1      143,264  142.0      -60,171          
                                      JRNL_LNH201712                        0       88,192   74.0       22,930          
                                      JRNL_LNH201812                        6       12,240     .0       12,240          
                                      JRNL_LNH201912                        6       11,104     .0       11,104          
…
                                      JRNL_LNH202201                        6       13,752     .0       13,752          
                                      JRNL_LNH202202                        6        5,496     .0        5,496          
                                      JRNL_LNH202203                        6        6,504     .0        6,504          
                                      JRNL_LNH202204                        6        5,920     .0        5,920          
                                      JRNL_LNH202205                        6        6,864     .0        6,864          
                                      JRNL_LNH202206                        6       13,584     .0       13,584          
                                      JRNL_LNH202207                        6       12,408     .0       12,408          
                                      JRNL_LNH202208                        3      212,904  121.0      -44,710          
                                      JRNL_LNH202209                        0      262,472  111.0      -28,872          
                                      JRNL_LNH202210                        3      228,552  102.0       -4,571          
                   ******************                                         ------------        ------------          
                   sum                                                           1,625,328             574,550

NB: ANALYZE INDEX on some partitions predicted a saving greater than 100%, leading to a negative predicted size estimate. This is obviously impossible. In the past, there was a bug (now resolved) that caused this behaviour. This occurs when the predicted optimal compression length is less than the current compression length in an index that is already compressed. However, this problem does not occur consistently.

In the above example, 6 columns of PSHJRLN_LN have already been compressed for all partitions. The script has validated that, for the majority of partitions, this is optimal and calculates that there is no further space saving available. However, some partitions require less compression.

The choice is between:

Choosing to compress the entire index at a shorter compression. In which case, most of the partitions will be larger, the exception partitions will be small, but the net effect is that the index will be larger.
Disabling compression on these partitions. Over-compressed indexes are generally only slightly larger than uncompressed indexes, so the benefit is probably only small
Leave compression at the length that is optimal for most of the partitions, accepting that a few partitions will be over-compressed. This usually results in the smallest index overall.

I tend to favour the last option on the basis that an over-compressed index is only slightly larger than an uncompressed index, but your mileage will vary.

Optimising Journal Line Queries: 5. Conclusion

noreply@blogger.com (David Kurtz) — Mon, 30 Jun 2025 13:05:00 +0000

This is the last of five articles that examine the challenges posed by typical queries on the journal line table (PS_JRNL_LN).

After introducing Exadata system statistics, partitioning, and compression and also archiving some historical data, we arrived at the point where the execution plan of the statement changes without needing to use hints.

Original Execution Plan (With Hints)

I have used hints to force the original execution plan.

ALTER SESSION SET STATISTICS_LEVEL=ALL;
SELECT /*+LEADING(A) USE_NL(B) 
          INDEX(B (PS_JRNL_LN.BUSINESS_UNIT PS_JRNL_LN.JOURNAL_ID PS_JRNL_LN.JOURNAL_DATE PS_JRNL_LN.UNPOST_SEQ PS_JRNL_LN.JOURNAL_LINE PS_JRNL_LN.LEDGER))*/
     A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, B.BUSINESS_UNIT, B.JOURNAL_ID, TO_CHAR(B.JOURNAL_DATE,'YYYY-MM-DD'), B.LEDGER, B.ACCOUNT
, B.PRODUCT, B.PROJECT_ID, B.CHARTFIELD1, B.CHARTFIELD2, B.CURRENCY_CD, B.AFFILIATE, SUM( B.MONETARY_AMOUNT), B.FOREIGN_CURRENCY
, SUM( B.FOREIGN_AMOUNT), A.REVERSAL_CD, A.REVERSAL_ADJ_PER, A.JRNL_HDR_STATUS, TO_CHAR(A.POSTED_DATE,'YYYY-MM-DD'), A.OPRID, A.DESCR254
, B.DEPTID, A.SOURCE, B.ALTACCT, TO_CHAR(CAST((A.DTTM_STAMP_SEC) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), B.LINE_DESCR 
FROM PS_JRNL_HEADER A, PS_JRNL_LN B 
WHERE (A.BUSINESS_UNIT = B.BUSINESS_UNIT 
AND A.JOURNAL_ID = B.JOURNAL_ID 
AND A.JOURNAL_DATE = B.JOURNAL_DATE 
AND A.UNPOST_SEQ = B.UNPOST_SEQ 
AND A.JRNL_HDR_STATUS IN('P','V','U') 
AND A.FISCAL_YEAR IN (2024) 
AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 12
AND B.CHARTFIELD1 IN ('1234567','1234568','1234569')
AND B.LEDGER IN ('LEDGER')) 
GROUP BY A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, B.BUSINESS_UNIT, B.JOURNAL_ID, B.JOURNAL_DATE, B.LEDGER, B.ACCOUNT, B.PRODUCT, B.PROJECT_ID, B.CHARTFIELD1, 
B.CHARTFIELD2, B.CURRENCY_CD, B.AFFILIATE, B.FOREIGN_CURRENCY, A.REVERSAL_CD, A.REVERSAL_ADJ_PER, A.JRNL_HDR_STATUS, A.POSTED_DATE, A.OPRID, A.DESCR254, 
B.DEPTID, A.SOURCE, B.ALTACCT, A.DTTM_STAMP_SEC, B.LINE_DESCR
/
select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED +ADAPTIVE'));

Rather than get the execution plan from EXPLAIN PLAN, I have executed the query with STATISTICS_LEVEL set to ALL, and then displayed the cursor.

The execution plan starts with the PS_JRNL_HEADER table and uses a nested loop join with a lookup of the unique index on PS_JRNL_LN. Although note that the optimizer costs were produced with Exadata system statistics.

Plan hash value: 4030641493

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |      1 |        |       |       |  1740K(100)|          |       |       |    209K|00:06:41.85 |     238M|     15M|
|   1 |  HASH GROUP BY                      |                |      1 |    498K|   108M|   129M|  1740K  (1)| 00:01:08 |       |       |    209K|00:06:41.85 |     238M|     15M|
|   2 |   NESTED LOOPS                      |                |      1 |    498K|   108M|       |  1722K  (1)| 00:01:08 |       |       |    495K|01:03:03.80 |     238M|     15M|
|   3 |    NESTED LOOPS                     |                |      1 |    498K|   108M|       |  1722K  (1)| 00:01:08 |       |       |    459M|00:11:20.66 |    5549K|   4259K|
|*  4 |     TABLE ACCESS STORAGE FULL       | PS_JRNL_HEADER |      1 |    430K|    41M|       |  1135   (8)| 00:00:01 |       |       |    430K|00:00:00.34 |   88642 |  88637 |
|   5 |     PARTITION RANGE ITERATOR        |                |    430K|      1 |       |       |     3   (0)| 00:00:01 |   KEY |   KEY |    459M|00:10:38.60 |    5460K|   4170K|
|*  6 |      INDEX RANGE SCAN               | PS_JRNL_LN     |    430K|      1 |       |       |     3   (0)| 00:00:01 |   KEY |   KEY |    459M|00:09:55.80 |    5460K|   4170K|
|*  7 |    TABLE ACCESS BY LOCAL INDEX ROWID| PS_JRNL_LN     |    459M|      1 |   127 |       |     4   (0)| 00:00:01 |     1 |     1 |    495K|00:50:25.33 |     233M|     11M|
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------

   4 - storage(("A"."FISCAL_YEAR"=2024 AND INTERNAL_FUNCTION("A"."JRNL_HDR_STATUS") AND "A"."ACCOUNTING_PERIOD"<=12 AND "A"."ACCOUNTING_PERIOD">=1))
       filter(("A"."FISCAL_YEAR"=2024 AND INTERNAL_FUNCTION("A"."JRNL_HDR_STATUS") AND "A"."ACCOUNTING_PERIOD"<=12 AND "A"."ACCOUNTING_PERIOD">=1))
   6 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT" AND "A"."JOURNAL_ID"="B"."JOURNAL_ID" AND "A"."JOURNAL_DATE"="B"."JOURNAL_DATE" AND
              "A"."UNPOST_SEQ"="B"."UNPOST_SEQ" AND "B"."LEDGER"='LEDGER')
       filter("B"."LEDGER"='LEDGER')
   7 - filter(("B"."CHARTFIELD1"='1234567' OR "B"."CHARTFIELD1"='1234568' OR "B"."CHARTFIELD1"='1234569'))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3
---------------------------------------------------------------------------

1 -  SEL$1
	           -  LEADING(A)

   6 -  SEL$1 / B@SEL$1
           -  INDEX(B (PS_JRNL_LN.BUSINESS_UNIT PS_JRNL_LN.JOURNAL_ID PS_JRNL_LN.JOURNAL_DATE PS_JRNL_LN.UNPOST_SEQ PS_JRNL_LN.JOURNAL_LINE PS_JRNL_LN.LEDGER))
           -  USE_NL(B)

The cost of this execution plan depends mainly upon how many journal header rows are selected. There is a cost of 3 per index lookup, plus another 1 for the table access, making a total of 4 per journal header row. Here we selected 430K rows from PS_JRNL_HEADER, so 430K rows * 4/row = 1720K. We got an actual cost of 1722K. The discrepancy is because the 430K was rounded off by the representation of numbers in the execution plan. Then the cost of the GROUP BY operation is 18K. Hence, the overall cost is 1740K.

The actual execution time of the query was 63 minutes (78 minutes with time taken to fetch 211K rows across the network to the client).

New Execution Plan (Without Hints)

Without the hints, the execution plan changes. It still starts with a full scan of PS_JRNL_HEADER, but then full scans PS_JRNL_LN, filtering the rows by LEDGER and CHARTFIELD1 (operation at line 12) and Bloom filtering by the columns joined to PS_JRNL_HEADER. PS_JRNL_LN is then hash joined against the rows from PS_JRNL_HEADER to remove false positives returned by the Bloom filter

Plan hash value: 1053505630

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                             | Name           | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                      |                |      1 |        |       |       |   535K(100)|          |       |       |    209K|00:00:10.06 |      27M|     27M|
|     1 |  HASH GROUP BY                        |                |      1 |    498K|   108M|   129M|   535K (24)| 00:00:21 |       |       |    209K|00:00:10.06 |      27M|     27M|
|  *  2 |   HASH JOIN                           |                |      1 |    498K|   108M|    46M|   517K (24)| 00:00:21 |       |       |    495K|00:00:09.23 |      27M|     27M|
|     3 |    PART JOIN FILTER CREATE            | :BF0000        |      1 |    498K|   108M|       |   517K (24)| 00:00:21 |       |       |    430K|00:00:00.66 |   88642 |  88638 |
|-    4 |     NESTED LOOPS                      |                |      1 |    498K|   108M|    46M|   517K (24)| 00:00:21 |       |       |    430K|00:00:00.30 |   88642 |  88638 |
|-    5 |      NESTED LOOPS                     |                |      1 |        |       |       |            |          |       |       |    430K|00:00:00.24 |   88642 |  88638 |
|-    6 |       STATISTICS COLLECTOR            |                |      1 |        |       |       |            |          |       |       |    430K|00:00:00.19 |   88642 |  88638 |
|  *  7 |        TABLE ACCESS STORAGE FULL      | PS_JRNL_HEADER |      1 |    430K|    41M|       |  1135   (8)| 00:00:01 |       |       |    430K|00:00:00.13 |   88642 |  88638 |
|-    8 |       PARTITION RANGE ITERATOR        |                |      0 |        |       |       |            |          |   KEY |   KEY |      0 |00:00:00.01 |       0 |      0 |
|- *  9 |        INDEX RANGE SCAN               | PS_JRNL_LN     |      0 |        |       |       |            |          |   KEY |   KEY |      0 |00:00:00.01 |       0 |      0 |
|- * 10 |      TABLE ACCESS BY LOCAL INDEX ROWID| PS_JRNL_LN     |      0 |      1 |   127 |       |   515K (24)| 00:00:21 |     1 |     1 |      0 |00:00:00.01 |       0 |      0 |
|    11 |    PARTITION RANGE JOIN-FILTER        |                |      1 |    498K|    60M|       |   515K (24)| 00:00:21 |:BF0000|:BF0000|    815K|00:00:07.65 |      27M|     27M|
|  * 12 |     TABLE ACCESS STORAGE FULL         | PS_JRNL_LN     |     18 |    498K|    60M|       |   515K (24)| 00:00:21 |:BF0000|:BF0000|    815K|00:00:07.55 |      27M|     27M|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "A"@"SEL$1")
      FULL(@"SEL$1" "B"@"SEL$1")
      LEADING(@"SEL$1" "A"@"SEL$1" "B"@"SEL$1")
      USE_HASH(@"SEL$1" "B"@"SEL$1")
      USE_HASH_AGGREGATION(@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."JOURNAL_DATE"="B"."JOURNAL_DATE" AND "A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT" AND "A"."JOURNAL_ID"="B"."JOURNAL_ID" AND "A"."UNPOST_SEQ"="B"."UNPOST_SEQ")
   7 - storage(("A"."FISCAL_YEAR"=2024 AND INTERNAL_FUNCTION("A"."JRNL_HDR_STATUS") AND "A"."ACCOUNTING_PERIOD"<=12 AND "A"."ACCOUNTING_PERIOD">=1))
       filter(("A"."FISCAL_YEAR"=2024 AND INTERNAL_FUNCTION("A"."JRNL_HDR_STATUS") AND "A"."ACCOUNTING_PERIOD"<=12 AND "A"."ACCOUNTING_PERIOD">=1))
   9 - access("A"."BUSINESS_UNIT"="B"."BUSINESS_UNIT" AND "A"."JOURNAL_ID"="B"."JOURNAL_ID" AND "A"."JOURNAL_DATE"="B"."JOURNAL_DATE" AND "A"."UNPOST_SEQ"="B"."UNPOST_SEQ"
              AND "B"."LEDGER"='LEDGER')
       filter("B"."LEDGER"='LEDGER')
  10 - filter(("B"."CHARTFIELD1"='1234567' OR "B"."CHARTFIELD1"='1234568' OR "B"."CHARTFIELD1"='1234569'))
  12 - storage(("B"."LEDGER"='LEDGER' AND INTERNAL_FUNCTION("B"."CHARTFIELD1")))
       filter(("B"."LEDGER"='LEDGER' AND INTERNAL_FUNCTION("B"."CHARTFIELD1")))

The actual execution time is just 21 seconds (or 13 minutes, including fetches). So, this is much faster.

The optimizer cost drops to just 535K. This is the cheapest plan, and therefore Oracle uses it without further intervention. 515K of the cost comes from the full scan of PS_JRNL_LN, the Bloom filter takes it up by just 2K, and the GROUP BY operation by 18K. It is an adaptive plan, so Oracle can still switch between the nested loop and the Bloom/hash join at run time on the basis of the statistics collected at run time (at line 6).

The full scan Bloom-Hash filter of PS_JRNL_LN is cheaper than the nested loop with 430K index probes. In fact, the tipping point will be whenever the optimizer estimates that it gets at least 129K rows from PS_JRNL_HEADER (535K / 4/row =128.75K).

TL;DR

Through a combination of Exadata system stats, archiving, partitioning and compression, the cost of smart-full scanning the PS_JRNL_LN table, that contains several billion rows, Oracle has switched to a more efficient execution plan.

Optimising Journal Line Queries: 4. Compression

noreply@blogger.com (David Kurtz) — Fri, 27 Jun 2025 14:38:00 +0000

This is the fourth of five articles that examine the challenges posed by typical queries on the journal line table (PS_JRNL_LN).

If it had been possible to referentially partition PS_JRNL_LN by FISCAL_YEAR, then the sample query shown in earlier posts would have been able to prune partitions by fiscal year. This would have significantly reduced the cost of the full scan.

Instead, if the sample query has to full scan PS_JRNL_LN, it has to scan all the partitions in PS_JRNL_LN and then filter them against the keys retrieved from PS_JRNL_HEADER. The only way to reduce the cost of the full scan is to reduce the number of blocks being scanned by purge and compression. On Exadata, Hybrid Columnar Compression (HCC) will achieve a much higher compression ratio, and unwanted compression units will be eliminated efficiently.

To meet reporting requirements, Financials systems inevitably need to keep journal data going back several years. Nonetheless, purging unneeded historical journals as aggressively as permissible is the first step in improving or at least preserving query performance.

Compression

Compressing the historical journal line partitions will further reduce the number of blocks in the segments and reduce the cost of the full scan in the query, thus making the optimiser more likely to switch away from the nested loop join to the full scan/Bloom filter/hash join.

In Oracle, basic compression and Hybrid Columnar Compression (HCC) are well suited to data warehouse applications, but they also have application in OLTP systems. Compression occurs during direct-path insert and segment reorganisation operations and does not apply to anything modified by PeopleSoft processes that use conventional DML.

Basic compression is available on any Oracle database; essentially, it avoids storing the same data values many times in the same data block. Hybrid Columnar Compression (HCC) is available on Exadata. Different HCC compression levels use different compression algorithms. DML on compressed data will decompress it.

I do not recommend using Advanced Compression to compress current periods due to the impact on day-to-day processing.

Which Partitions are Static?

DBA_TAB_MODIFICATIONS shows the number of DML operations in each partition since statistics were last collected.

SELECT p.partition_position, m.*, p.high_value
FROM dba_tab_modifications m
  INNER JOIN dba_tab_partitions p 
    ON p.table_owner = m.table_owner AND p.table_name = m.table_name AND p.partition_name = m.partition_name
WHERE m.table_owner = 'SYSADM' AND m.table_name = 'PS_JRNL_LN'
ORDER BY 1
/

This report was generated in December 2024. Most of the updates are in the current and previous monthly periods. There are almost no updates that are older than a year.

Part Table                                                                                              Drop                                                                                 
Pos# Owner    TABLE_NAME   PARTITION_NAME       S   INSERTS   UPDATES   DELETES TIMESTAMP           TRU Segs HIGH_VALUE                                                                      
---- -------- ------------ -------------------- - --------- --------- --------- ------------------- --- ---- --------------------------------------------------------------------------------
…
  40 SYSADM   PS_JRNL_LN   JRNL_LN_202212                 0         0         0 13/10/2024 10:08:56 NO     0 TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  41 SYSADM   PS_JRNL_LN   JRNL_LN_202301                 0         0         0 29/09/2024 10:01:16 NO     0 TO_DATE(' 2023-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  42 SYSADM   PS_JRNL_LN   JRNL_LN_202302                 0         0         0 29/09/2024 10:01:16 NO     0 TO_DATE(' 2023-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  43 SYSADM   PS_JRNL_LN   JRNL_LN_202303                 0         0         0 29/09/2024 10:01:16 NO     0 TO_DATE(' 2023-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  44 SYSADM   PS_JRNL_LN   JRNL_LN_202304                 0         0         0 29/09/2024 10:01:16 NO     0 TO_DATE(' 2023-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  45 SYSADM   PS_JRNL_LN   JRNL_LN_202305                 0         0         0 29/09/2024 10:01:16 NO     0 TO_DATE(' 2023-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  46 SYSADM   PS_JRNL_LN   JRNL_LN_202306                 0         0         0 29/09/2024 10:01:16 NO     0 TO_DATE(' 2023-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  47 SYSADM   PS_JRNL_LN   JRNL_LN_202307                 0         0         0 29/09/2024 10:01:16 NO     0 TO_DATE(' 2023-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  48 SYSADM   PS_JRNL_LN   JRNL_LN_202308                 0         0         0 29/09/2024 10:01:16 NO     0 TO_DATE(' 2023-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  49 SYSADM   PS_JRNL_LN   JRNL_LN_202309                 0         0         0 13/10/2024 10:08:56 NO     0 TO_DATE(' 2023-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  50 SYSADM   PS_JRNL_LN   JRNL_LN_202310                 0         0         0 27/10/2024 10:59:45 NO     0 TO_DATE(' 2023-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  51 SYSADM   PS_JRNL_LN   JRNL_LN_202311                 0         0         0 29/09/2024 10:01:16 NO     0 TO_DATE(' 2023-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  52 SYSADM   PS_JRNL_LN   JRNL_LN_202312                34       193        34 10/12/2024 14:21:38 NO     0 TO_DATE(' 2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

  53 SYSADM   PS_JRNL_LN   JRNL_LN_202401             42374    127736       210 12/12/2024 05:27:31 NO     0 TO_DATE(' 2024-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  54 SYSADM   PS_JRNL_LN   JRNL_LN_202402             34803     92215         0 12/12/2024 05:26:30 NO     0 TO_DATE(' 2024-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  55 SYSADM   PS_JRNL_LN   JRNL_LN_202403             54940    166263         0 12/12/2024 05:12:29 NO     0 TO_DATE(' 2024-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  56 SYSADM   PS_JRNL_LN   JRNL_LN_202404              5900     13730         0 13/12/2024 05:29:32 NO     0 TO_DATE(' 2024-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  57 SYSADM   PS_JRNL_LN   JRNL_LN_202405              6151     13869         0 13/12/2024 05:31:06 NO     0 TO_DATE(' 2024-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  58 SYSADM   PS_JRNL_LN   JRNL_LN_202406             18317     58263         0 13/12/2024 16:15:49 NO     0 TO_DATE(' 2024-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  59 SYSADM   PS_JRNL_LN   JRNL_LN_202407           5067792  14937405         0 13/12/2024 16:02:36 NO     0 TO_DATE(' 2024-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  60 SYSADM   PS_JRNL_LN   JRNL_LN_202408           5217744  15378822         0 13/12/2024 18:02:57 NO     0 TO_DATE(' 2024-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  61 SYSADM   PS_JRNL_LN   JRNL_LN_202409             65389    243360       160 13/12/2024 12:45:25 NO     0 TO_DATE(' 2024-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  62 SYSADM   PS_JRNL_LN   JRNL_LN_202410             44839    152210         0 13/12/2024 00:28:54 NO     0 TO_DATE(' 2024-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  63 SYSADM   PS_JRNL_LN   JRNL_LN_202411          28279594  53637873  27478940 13/12/2024 18:18:00 NO     0 TO_DATE(' 2024-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  64 SYSADM   PS_JRNL_LN   JRNL_LN_202412          34761590  53485631  27484239 13/12/2024 19:16:11 NO     0 TO_DATE(' 2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

  65 SYSADM   PS_JRNL_LN   JRNL_LN_202501            137138    473452         0 13/12/2024 19:18:09 NO     0 TO_DATE(' 2025-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORI1
  66 SYSADM   PS_JRNL_LN   JRNL_LN_202502                 0         0         0 10/11/2024 10:08:21 NO     0 TO_DATE(' 2025-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  67 SYSADM   PS_JRNL_LN   JRNL_LN_202503               466         0         0 13/12/2024 03:59:20 NO     0 TO_DATE(' 2025-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  68 SYSADM   PS_JRNL_LN   JRNL_LN_202504                 0         0         0 17/11/2024 10:03:01 NO     0 TO_DATE(' 2025-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
  69 SYSADM   PS_JRNL_LN   JRNL_LN_202505                 0         0         0 17/11/2024 10:03:01 NO     0 TO_DATE(' 2025-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
…

Therefore, compressing journal lines in the current or previous fiscal years is not viable, as subsequent updates would destroy the compression. However, I can look at explicitly compressing older partitions.

Attribute Clustering

Attribute Clustering sorts or clusters the rows by certain columns. Like compression, this is declarative and will only take effect during direct-path load or segment reorganisation (such as a compression operation). It will not take effect during normal DML. It can be defined at table level and will be implemented during compression.

I have defined attribute clustering on PS_JRNL_LN to sort the rows by the unique key columns of its parent record, PS_JRNL_HEADER.

ALTER TABLE ps_jrnl_ln ADD CLUSTERING BY LINEAR ORDER (business_unit, journal_id, journal_date, unpost_Seq);
ALTER TABLE ps_jrnl_ln MODIFY CLUSTERING YES ON LOAD YES ON DATA MOVEMENT;

I have found it produces a small (approximately 2-5% of the original size) improvement in the resulting compression, further reducing the cost of the full table scan. So, it is a marginal gain for no additional cost.

Hybrid Columnar Compression (HCC)

I have chosen to compress partitions older than one complete fiscal year with QUERY LOW, and those than 3 complete fiscal years with QUERY HIGH.

Each historical partition can be compressed using an online compress operation. This can be done in parallel. It is important to specify that it maintains the indexes so that they remain valid and usable after the operation. This approach will not lock PS_JRNL_LN, and so the application can continue to run during this process.

…
ALTER TABLE ps_jrnl_ln MOVE PARTITION jrnl_ln_202012 COMPRESS FOR QUERY HIGH UPDATE INDEXES ONLINE PARALLEL;
ALTER TABLE ps_jrnl_ln MOVE PARTITION jrnl_ln_202101 COMPRESS FOR QUERY LOW UPDATE INDEXES ONLINE PARALLEL;
…

The statistics on the newly compressed partitions should be updated, as well as the global statistics.

Statistics

Optimizer statistics have to be regathered on the freshly compressed partitions; otherwise, the reduction in the cost of the full scan will not occur.

On some systems, statistics collection on the very largest tables, including PS_JRNL_LN, may not be completed within the regular maintenance window. Then, Oracle may repeatedly attempt to collect statistics on these segments, and other statistics can become stale too. Instead, I usually lock the statistics on such tables (to remove them from the maintenance window job) and create a specific recurring job to collect statistics at a convenient time (e.g. at the weekend).

An interim option is to simply manually update the number of blocks in the partition statistics to the number of blocks in the compressed segment (if it is lower), and recalculate the total number of blocks in the whole table for the global statistics.

set serveroutput on timi on
DECLARE 
  l_table_name VARCHAR2(18) := 'PS_JRNL_LN';
  l_part_update BOOLEAN := FALSE;
BEGIN
  FOR i IN(
    select t.table_name, t.partition_name, t.num_rows, t.blocks stat_blocks, s.blocks seg_blocks
    ,      s.tablespace_name, p.compress_for, t.num_rows/NULLIF(LEAST(t.blocks,s.blocks),0) rpb
    from user_segments s
      inner join user_tab_partitions p ON p.table_name = s.segment_name AND p.partition_name = s.partition_name
      inner join user_tab_statistics t ON s.segment_name = t.table_name AND s.partition_name = t.partition_name and t.blocks>s.blocks
    where s.segment_type = 'TABLE PARTITION' and p.compress_for IS NOT NULL and s.segment_name = l_table_name 
  ) LOOP
    l_part_update := TRUE;
    dbms_output.put_line(i.table_name||' ('||i.partition_name||') '||i.stat_blocks||' => '||i.seg_blocks||' blocks');
    dbms_stats.set_table_stats(ownname=>'SYSADM',tabname=>i.table_name,partname=>i.partition_name,numblks=>i.seg_blocks,force=>TRUE);
  END LOOP;
  IF l_part_update THEN
    FOR i IN (
      SELECT table_name, sum(blocks) blocks
      FROM user_tab_statistics
      WHERE table_name = l_table_name AND partition_name IS NOT NULL 
      GROUP BY table_name
    ) LOOP
      dbms_output.put_line(i.table_name||' = '||i.blocks||' blocks');
      dbms_stats.set_table_stats(ownname=>'SYSADM',tabname=>i.table_name,numblks=>i.blocks,force=>TRUE);
    END LOOP;
  ELSE
    dbms_output.put_line(l_table_name||' - no action required');
  END IF;
END;
/

Orphaned Index Entries & Space Recovery

One side effect of the table segment compression operation (or any other segment reorganisation operation) is that we get orphaned entries in any global indexes. I could rebuild these indexes.

ALTER INDEX psdjrnl_ln REBUILD ONLINE TABLESPACE psindex PARALLEL;

Or, I could wait for the PMO_DEFERRED_GIDX_MAINT_JOB job to run (scheduled by default during the maintenance window) to clean out orphaned index entries from all currently impacted global indexes. Another alternative is to manually run the dbms_part.cleanup_gidx procedure that is in turn called by this job (see also Richard Foote: 12c Asynchronous Global Index Maintenance Part II)

However, another side effect is that global indexes can grow as they are maintained by the table partition compression operations. These indexes can be coalesced, cleaned up, and then shrunk. The shrink implies a coalesce. ALTER INDEX … SHRINK SPACE COMPACT is equivalent to ALTER INDEX … COALESCE - see Jonathan Lewis's Oracle Scratchpad: Shrinking indexes). However, the coalesce can be done in parallel, but the shrink cannot. Therefore, I have chosen to do a parallel coalesce that includes a clean-up of orphaned entries, and then I shrink the segment.

ALTER INDEX psdjrnl_ln COALESCE CLEANUP PARALLEL;
ALTER INDEX psdjrnl_ln SHRINK SPACE;

I also rebuild corresponding partitions in locally partitioned indexes.

…
ALTER INDEX ps_jrnl_ln REBUILD PARTITION jrnl_ln_202012 ONLINE;
ALTER INDEX ps_jrnl_ln REBUILD PARTITION jrnl_ln_202101 ONLINE;
…

At the end of this process, space has been released back to the tablespace, but the free space will be distributed throughout the tablespace, so it probably won't be possible to release space back to the file system.

If you are compressing many partitions, perhaps because you are doing this for the first time, and they are in a dedicated tablespace, then I would suggest completely rebuilding the objects into new tablespaces. Tablespaces can be renamed as required.

Optimising Journal Line Queries: 3. Partitioning

noreply@blogger.com (David Kurtz) — Thu, 26 Jun 2025 12:58:00 +0000

This is the third of a series of five articles that examine the challenges posed by typical queries on the journal line table (PS_JRNL_LN).

PeopleSoft does not partition tables by default. Application Designer does not support partitioning, mainly because different databases implement partitioning differently. Thus, it is always left to the customer to implement as a customisation. In this article, I am only going to discuss partitioning on Oracle.

Interval Partitioning

This is the sample query that I started with.

SELECT A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, B.BUSINESS_UNIT, B.JOURNAL_ID, TO_CHAR(B.JOURNAL_DATE,'YYYY-MM-DD'), B.LEDGER, B.ACCOUNT
, B.PRODUCT, B.PROJECT_ID, B.CHARTFIELD1, B.CHARTFIELD2, B.CURRENCY_CD, B.AFFILIATE, SUM( B.MONETARY_AMOUNT), B.FOREIGN_CURRENCY
, SUM( B.FOREIGN_AMOUNT), A.REVERSAL_CD, A.REVERSAL_ADJ_PER, A.JRNL_HDR_STATUS, TO_CHAR(A.POSTED_DATE,'YYYY-MM-DD'), A.OPRID, A.DESCR254
, B.DEPTID, A.SOURCE, B.ALTACCT, TO_CHAR(CAST((A.DTTM_STAMP_SEC) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), B.LINE_DESCR 
FROM PS_JRNL_HEADER A, PS_JRNL_LN B 
WHERE (A.BUSINESS_UNIT = B.BUSINESS_UNIT 
AND A.JOURNAL_ID = B.JOURNAL_ID 
AND A.JOURNAL_DATE = B.JOURNAL_DATE 
AND A.UNPOST_SEQ = B.UNPOST_SEQ 
AND A.JRNL_HDR_STATUS IN('P','V','U') 
AND A.FISCAL_YEAR IN (2024) 
AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 12 
AND B.CHARTFIELD1 IN ('1234567','1234568','1234569')
AND B.LEDGER IN ('LEDGER')) 
GROUP BY A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, B.BUSINESS_UNIT, B.JOURNAL_ID, B.JOURNAL_DATE, B.LEDGER, B.ACCOUNT, B.PRODUCT
, B.PROJECT_ID, B.CHARTFIELD1, B.CHARTFIELD2, B.CURRENCY_CD, B.AFFILIATE, B.FOREIGN_CURRENCY, A.REVERSAL_CD, A.REVERSAL_ADJ_PER
, A.JRNL_HDR_STATUS, A.POSTED_DATE, A.OPRID, A.DESCR254, B.DEPTID, A.SOURCE, B.ALTACCT, A.DTTM_STAMP_SEC, B.LINE_DESCR

It would have been desirable to have been able to partition PS_JRNL_LN by FISCAL_YEAR. However, that column is only present on the parent table, PS_JRNL_HEADER. Oracle can do referential partitioning, where the child table is partitioned by an attribute of a column in the parent table. The parent table must also be partitioned similarly, thus producing a 1:1 mapping of partitions between the parent and child tables. However, this feature also requires the presence of an enforced foreign key constraint between parent and child tables.

PeopleSoft has never implemented or supported database-enforced referential integrity constraints (again, mainly because it was done differently on different databases). Although it is tempting to add a foreign key constraint between these tables, that would be a customisation to PeopleSoft that Oracle would not support. The application would then have to insert parent rows before child rows and delete child rows before deleting parent rows. It has never been tested against these constraints.

Therefore, it is only possible to consider partitioning by a column on PS_JRNL_LN. A column in the unique key is an obvious choice.

Depending on how BUSINESS_UNIT is set up and used, you might be able to list sub-partition by this column, and split journal lines down into several subpartitions. However, it is almost inevitable that the volumes will be heavily skewed.
It is tempting to range partition on JOURNAL_ID. Although this column usually contains an entirely numeric value, it is in fact defined as a character (VARCHAR2) data type. Therefore, it is not possible to interval partition upon it. Periodically, it would be necessary to add partitions manually.
The alternative is to interval range partition on JOURNAL_DATE. I chose to define a monthly interval. I specified the first few partitions for whole years because at this customer, these partitions contained less data after archiving. Thereafter, Oracle automatically creates monthly partitions as data is inserted.

CREATE TABLE PS_JRNL_LN 
(BUSINESS_UNIT VARCHAR2(5 CHAR) NOT NULL
,JOURNAL_ID VARCHAR2(10 CHAR) NOT NULL 
,JOURNAL_DATE DATE NOT NULL 
,UNPOST_SEQ NUMBER(*,0) NOT NULL 
,JOURNAL_LINE NUMBER(9,0) NOT NULL 
,LEDGER VARCHAR2(10 CHAR) NOT NULL 
…
) PARTITION BY RANGE (JOURNAL_DATE) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) 
(PARTITION JRNL_LN_2016 VALUES LESS THAN (TO_DATE('2017-01-01', 'YYYY-MM-DD'))
,PARTITION JRNL_LN_2017 VALUES LESS THAN (TO_DATE('2018-01-01', 'YYYY-MM-DD'))
,PARTITION JRNL_LN_2018 VALUES LESS THAN (TO_DATE('2019-01-01', 'YYYY-MM-DD'))
,PARTITION JRNL_LN_2019 VALUES LESS THAN (TO_DATE('2020-01-01', 'YYYY-MM-DD')) 
) 
/

Partitioning on JOURNAL_DATE still arranges the journal line data into current and historical partitions. We find that the various financial processes only create journal lines in the current and previous fiscal years. Therefore, earlier fiscal years are effectively static. This presents an opportunity to compress these partitions because nothing will subsequently update them that would decompress compressed rows. Thus, partitioning and compression go together.

Renaming Partitions

By default, interval partitions are given system-generated names. I find it convenient to rename them to something more meaningful. This has no bearing on performance. In this case, I used something based on the date to which the partition relates. There are 2 implicit cursors in the following PL/SQL block. The first renames table partitions, and the second renames index partitions. This script should be run periodically as new partitions are added. It is available on GitHub.

rem rename_jrnl_ln_partitions.sql    
rem requires https://github.com/davidkurtz/psscripts/blob/master/psftapi.sql
spool rename_jrnl_ln_partitions.lst
set serveroutput on
DECLARE
  l_high_value DATE;
  l_sql CLOB;
  l_new_partition_name VARCHAR2(30);
BEGIN
  psft_ddl_lock.set_ddl_permitted(TRUE);
  FOR i IN (
    select /*+LEADING(r upt upkc utc)*/ r.recname, upt.table_name, utp.partition_name, utp.high_value, upt.interval interval_size
    from sysadm.psrecdefn r 
      INNER JOIN user_part_tables upt ON upt.table_name = DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) 
        AND upt.partitioning_type = 'RANGE' and upt.interval IS NOT NULL
      INNER JOIN user_part_key_columns upkc ON upkc.name = upt.table_name AND upkc.object_Type = 'TABLE' and upkc.column_position = 1
      INNER JOIN user_tab_columns utc ON utc.table_name = upkc.name AND utc.column_name = upkc.column_name
      INNER JOIN user_tab_partitions utp ON utp.table_name = upt.table_name AND utp.partition_name like 'SYS_P%'
    WHERE r.recname = 'JRNL_LN' AND r.rectype = 0
    AND (utc.data_type = 'DATE' OR utc.data_type like 'TIMESTAMP%')
  ) LOOP
    l_sql := 'SELECT '||i.high_value||'-'||i.interval_size||' FROM DUAL';
    EXECUTE IMMEDIATE l_sql INTO l_high_value;
    l_new_partition_name := i.recname||'_'||TO_CHAR(l_high_value,'YYYYMM');
    l_sql := 'ALTER TABLE '||i.table_name||' RENAME PARTITION '||i.partition_name||' TO '||l_new_partition_name;
    IF i.partition_name != l_new_partition_name THEN
      dbms_output.put_line(l_sql);
      EXECUTE IMMEDIATE l_sql;
    END IF;
  END LOOP;

  FOR i IN (
    select /*+LEADING(r upi upkc utc)*/ r.recname, upi.index_name, uip.partition_name, uip.high_value, upi.interval interval_size
    from sysadm.psrecdefn r 
      INNER JOIN user_part_indexes upi ON upi.table_name = DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) 
        AND upi.partitioning_type = 'RANGE' and upi.interval IS NOT NULL
      INNER JOIN user_part_key_columns upkc ON upkc.name = upi.index_name AND upkc.object_Type = 'INDEX' and upkc.column_position = 1
      INNER JOIN user_tab_columns utc ON utc.table_name = upi.table_name AND utc.column_name = upkc.column_name
      INNER JOIN user_ind_partitions uip ON uip.index_name = upi.index_name 
        AND (uip.partition_name like 'SYS_P%' OR SUBSTR(uip.partition_name,1+LENGTH(r.recname),1) != SUBSTR(upi.index_name,3,1))
    WHERE r.recname = 'JRNL_LN' AND r.rectype = 0
    AND (utc.data_type = 'DATE' OR utc.data_type like 'TIMESTAMP%')
  ) LOOP
    l_sql := 'SELECT '||i.high_value||'-'||i.interval_size||' FROM DUAL';
    EXECUTE IMMEDIATE l_sql INTO l_high_value;
    l_new_partition_name := i.recname||SUBSTR(i.index_name,3,1)||TO_CHAR(l_high_value,'YYYYMM');
    l_sql := 'ALTER INDEX '||i.index_name||' RENAME PARTITION '||i.partition_name||' TO '||l_new_partition_name;
    IF i.partition_name != l_new_partition_name THEN
      dbms_output.put_line(l_sql);
      EXECUTE IMMEDIATE l_sql;
    END IF;
  END LOOP;
  psft_ddl_lock.set_ddl_permitted(FALSE);
END;
/
spool off

Optimising Journal Line Queries: 2. Exadata System Statistics

noreply@blogger.com (David Kurtz) — Tue, 24 Jun 2025 15:19:00 +0000

This is the second of a series of five articles that examine the challenges posed by typical queries on the journal line table (PS_JRNL_LN).

Exadata System Statistics

Many other people have written notes about how Oracle's optimizer costs a full table scan. This is a selection:

Neil Chandler: Oracle Optimizer System Statistics
John Brady: Oracle Optimizer Plan Costing - Full Table Scans
Randolf Geist: Understanding the different modes of System Statistics aka. CPU Costing and the effects of multiple block sizes - part 4
Martin Widlake: Cost of Full Table Scans

Roughly speaking, the cost calculated by the optimizer that we see in an execution plan is an estimate of the time taken to perform an operation, where the unit of time is the duration of a single block read. Although that statement is an oversimplification. There are various guesses and assumptions built into the optimizer's calculation. The cost-based optimizer looks for the cheapest plan, that ought to be the fastest to execute. However, in many cases, cost does not correspond to execution time.

See also Jonathan Lewis: Cost is Time

Full Scan Cost

The cost of a full table scan is made up of an I/O cost (the time taken to read the blocks from disk) and a CPU cost (the time taken to process the rows). The I/O cost is the number of multi-block read operations, multiplied by the ratio of the duration of a multi-block read to a single-block read.

IO Cost = (HWM / MBRC) . (MREADTIM / SREADTIM)

Where

HWM = the high water mark of the segment expressed as a number of blocks
MBRC = average multi-block read count for sequential read, in blocks (see parameter DB_FILE_MULTIBLOCK_READ_COUNT).
MREADTIME = average time to perform a multi-block read at once (sequential read), in milliseconds
SREADTIME = average time to read a single block (random read), in milliseconds

See PL/SQL Packages and Types Reference: DBMS_STATS

The single and multi-block read times are derived from two system statistics, the block size and the multi-block read count.

SREADTIM = IOSEEKTIM + DB_BLOCK_SIZE / IOTFRSPEED
MREADTIM = IOSEEKTIM + (DB_BLOCK_SIZE * MBRC) / IOTFRSPEED

Where

IOSEEKTIM = Seek time + latency time + operating system overhead time, in milliseconds (default 10ms).
IOTFRSPEED = I/O transfer speed in bytes per millisecond (or if you prefer KBytes/second)
DB_BLOCK_SIZE = block size of the segment (usually 8Kb)

System statistics can be gathered based on actual system behaviour using DBMS_STATS, or set to pre-defined values using DBMS_STATS.GATHER_SYSTEM_STATS. Over the years many blogs, forums and presentations have discussed the merits or otherwise of collecting or setting system statistics.

Oracle's position is set out in the Oracle Optimizer Blog: Should You Gather System Statistics? It can be summarised as:

Do not gather your own system statistics.
Use the Oracle-provided defaults.
Except on Exadata, where you can consider using the Exadata defaults, and perhaps not even then on a mixed workload. You will have to test this for yourself.

On any Oracle system, the default system statistics can be reset with

exec DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD');

This sets the system statistics as follows:

MBRC=8
IOSEEKTIM=10
IOTFRSPEED=10

Thus:

SREADTIM = IOSEEKTIM + DB_BLOCK_SIZE / IOTFRSPEED

= 10 + 8192 / 4096

= 12 (ms)

MREADTIM = IOSEEKTIM + (DB_BLOCK_SIZE * MBRC) / IOTFRSPEED

= 10 + (8192 * 8 ) / 4096

= 10 + 16

= 26 (ms)

However, on Exadata, you can set 'system statistics take into account the unique capabilities of Oracle Exadata, such as large I/O size and high I/O throughput'

exec DBMS_STATS.GATHER_SYSTEM_STATS('EXADATA');

Some system statistics are then set differently:

MBRC=128
IOSEEKTIM=10
IOTFRSPEED=204800

Thus

SREADTIM = IOSEEKTIM + DB_BLOCK_SIZE / IOTFRSPEED

= 10 + 8192 / 204800

= 10.04 (ms)

MREADTIM = IOSEEKTIM + (DB_BLOCK_SIZE * MBRC) / IOTFRSPEED

= 10 + (8192 . 128 ) / 204800

= 10 + 5.1200

= 15.12000 (ms)

Now, I can put these numbers back into the formula Oracle uses to calculate the I/O cost of a full scan.

IO Cost = (HWM / MBRC) . (MREADTIM / SREADTIM)

Let us suppose that we are going to read 100M blocks. The I/O cost of that scan will be very different with Exadata system statistics rather than the normal default system statistics.

Normal IO Cost = (100000000/8) . (26/12)

= 27,083,333.3

Exadata IO Cost = (100000000/128) . (15.12/10.04)

= 1,176,543.8

Thus, introducing Exadata system statistics significantly reduces the cost of the full scan, making the database more likely to use a full scan than index lookups. That may or may not be desirable.

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |   428K|    93M|       |  2834K  (8)| 00:01:51 |       |       |
|   1 |  HASH GROUP BY                 |                |   428K|    93M|   111M|  2834K  (8)| 00:01:51 |       |       |
|*  2 |   HASH JOIN                    |                |   428K|    93M|    46M|  2819K  (8)| 00:01:51 |       |       |
|   3 |    JOIN FILTER CREATE          | :BF0001        |   428K|    41M|       |  1476   (7)| 00:00:01 |       |       |
|   4 |     PART JOIN FILTER CREATE    | :BF0000        |   428K|    41M|       |  1476   (7)| 00:00:01 |       |       |
|*  5 |      TABLE ACCESS STORAGE FULL | PS_JRNL_HEADER |   428K|    41M|       |  1476   (7)| 00:00:01 |       |       |
|   6 |    JOIN FILTER USE             | :BF0001        |  1120K|   136M|       |  2817K  (8)| 00:01:51 |       |       |
|   7 |     PARTITION RANGE JOIN-FILTER|                |  1120K|   136M|       |  2817K  (8)| 00:01:51 |:BF0000|:BF0000|
|*  8 |      TABLE ACCESS STORAGE FULL | PS_JRNL_LN     |  1120K|   136M|       |  2817K  (8)| 00:01:51 |:BF0000|:BF0000|
-------------------------------------------------------------------------------------------------------------------------

If I look at the same example query that I used earlier, then with the Exadata default system statistics, the cost has come down significantly (from 66M to 2817K). It is a significant improvement, but it is still greater than the cost of the nested loop (1730K). Therefore, for this query, I still only get this execution plan if I hint the statement to force it. I still need to make the full scan cheaper.

Different queries will have different costs and will flip between the nested loop and Full scan/Bloom filter/hash join at different points.

Non-Exadata System Statistics

If you are not running on Exadata, then the advice from Oracle is clear and unambiguous: use the default system statistics that can be reset with

exec DBMS_STATS.GATHER_SYSTEM_STATS('NOWORKLOAD');

You will have to consider other techniques to reduce the cost of the full table scan.

Optimising Journal Line Queries: 1. Problem Statement

noreply@blogger.com (David Kurtz) — Mon, 23 Jun 2025 15:30:00 +0000

In each PeopleSoft product, certain tables usually grow to become the largest in the implementations at most customers. The challenges they present and the options for dealing with them are also common to most systems. Most PeopleSoft Financials systems use General Ledger. In General Ledger, the ledger, summary ledger and journal line tables are usually the largest tables, and present the biggest challenges.

This is the first of five articles that examine the challenges typically presented by queries on the journal line table (PS_JRNL_LN).

Problem Statement

In General Ledger, we typically see many queries on the ledger (or summary ledger) tables and also queries in the application, drill-down queries in nVision reporting, and ad-hoc PS/Queries that query details of journals posted to the ledger. Below is part of a typical query. The statement and execution plans below were taken from a PeopleSoft Financials system. It is running on Oracle 19c on Exadata. Making use of Exadata features will also be a topic.

SELECT A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, B.BUSINESS_UNIT, B.JOURNAL_ID, TO_CHAR(B.JOURNAL_DATE,'YYYY-MM-DD'), B.LEDGER, B.ACCOUNT
, B.PRODUCT, B.PROJECT_ID, B.CHARTFIELD1, B.CHARTFIELD2, B.CURRENCY_CD, B.AFFILIATE, SUM( B.MONETARY_AMOUNT), B.FOREIGN_CURRENCY
, SUM( B.FOREIGN_AMOUNT), A.REVERSAL_CD, A.REVERSAL_ADJ_PER, A.JRNL_HDR_STATUS, TO_CHAR(A.POSTED_DATE,'YYYY-MM-DD'), A.OPRID, A.DESCR254
, B.DEPTID, A.SOURCE, B.ALTACCT, TO_CHAR(CAST((A.DTTM_STAMP_SEC) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), B.LINE_DESCR 
FROM PS_JRNL_HEADER A, PS_JRNL_LN B 
WHERE (A.BUSINESS_UNIT = B.BUSINESS_UNIT 
AND A.JOURNAL_ID = B.JOURNAL_ID 
AND A.JOURNAL_DATE = B.JOURNAL_DATE 
AND A.UNPOST_SEQ = B.UNPOST_SEQ 
AND A.JRNL_HDR_STATUS IN('P','V','U') 
AND A.FISCAL_YEAR IN (2024) 
AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 12 
AND B.CHARTFIELD1 IN ('1234567','1234568','1234569')
AND B.LEDGER IN ('LEDGER')) 
GROUP BY A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, B.BUSINESS_UNIT, B.JOURNAL_ID, B.JOURNAL_DATE, B.LEDGER, B.ACCOUNT, B.PRODUCT
, B.PROJECT_ID, B.CHARTFIELD1, B.CHARTFIELD2, B.CURRENCY_CD, B.AFFILIATE, B.FOREIGN_CURRENCY, A.REVERSAL_CD, A.REVERSAL_ADJ_PER
, A.JRNL_HDR_STATUS, A.POSTED_DATE, A.OPRID, A.DESCR254, B.DEPTID, A.SOURCE, B.ALTACCT, A.DTTM_STAMP_SEC, B.LINE_DESCR

The journal line table (PS_JRNL_LN) is joined to its parent, the journal header table (PS_JRNL_HEADER), by the 4 key columns on the journal header (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ).
There are criteria on both the journal header and line tables.

The number of journal line rows per header is usually highly variable, and it also varies from customer to customer depending on the shape of their data. It is not unusual to see thousands of journal line rows per header row. Filtering it by FISCAL_YEAR and perhaps also ACCOUNTING_PERIOD could be very effective. However, these columns are on PS_JRNL_HEADER, and not on PS_JRNL_LN.
Queries often include criteria on other attribute columns on PS_JRNL_LN. However, these columns are not indexed by default, though many customers add such indexes.

Below is the execution plan that Oracle produced for this query. The optimizer chose to full scan the PS_JRNL_HEADER table, expecting to find 428,000 rows, and then do an index look-up on PS_JRNL_LN for each header row. The optimizer predicts that the query will run for about 68s. In practice, this query runs for over an hour, spending most of its time on the index lookup of PS_JRNL_LN by its unique index of the same name.

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |   428K|    93M|       |  1730K  (1)| 00:01:08 |       |       |
|   1 |  HASH GROUP BY                      |                |   428K|    93M|   111M|  1730K  (1)| 00:01:08 |       |       |
|   2 |   NESTED LOOPS                      |                |   428K|    93M|       |  1715K  (1)| 00:01:07 |       |       |
|   3 |    NESTED LOOPS                     |                |   428K|    93M|       |  1715K  (1)| 00:01:07 |       |       |
|*  4 |     TABLE ACCESS STORAGE FULL       | PS_JRNL_HEADER |   428K|    41M|       |  1476   (7)| 00:00:01 |       |       |
|   5 |     PARTITION RANGE ITERATOR        |                |     1 |       |       |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  6 |      INDEX RANGE SCAN               | PS_JRNL_LN     |     1 |       |       |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  7 |    TABLE ACCESS BY LOCAL INDEX ROWID| PS_JRNL_LN     |     1 |   128 |       |     4   (0)| 00:00:01 |     1 |     1 |
------------------------------------------------------------------------------------------------------------------------------

If I add hints to the SQL statement forcing it to full scan PS_JRNL_HEADER and generate a Bloom filter that it will apply during a full scan of PS_JRNL_LN, then the cost of the execution plan goes up (from 1730K to 66M), and the estimated run time goes up to 43 minutes, due to the cost of the full scan. However, the actual execution time comes down to under 3 minutes.

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |   428K|    93M|       |    66M  (1)| 00:43:30 |       |       |
|   1 |  HASH GROUP BY                 |                |   428K|    93M|   111M|    66M  (1)| 00:43:30 |       |       |
|*  2 |   HASH JOIN                    |                |   428K|    93M|    46M|    66M  (1)| 00:43:30 |       |       |
|   3 |    JOIN FILTER CREATE          | :BF0001        |   428K|    41M|       | 32501   (1)| 00:00:02 |       |       |
|   4 |     PART JOIN FILTER CREATE    | :BF0000        |   428K|    41M|       | 32501   (1)| 00:00:02 |       |       |
|*  5 |      TABLE ACCESS STORAGE FULL | PS_JRNL_HEADER |   428K|    41M|       | 32501   (1)| 00:00:02 |       |       |
|   6 |    JOIN FILTER USE             | :BF0001        |  1132K|   137M|       |    66M  (1)| 00:43:28 |       |       |
|   7 |     PARTITION RANGE JOIN-FILTER|                |  1132K|   137M|       |    66M  (1)| 00:43:28 |:BF0000|:BF0000|
|*  8 |      TABLE ACCESS STORAGE FULL | PS_JRNL_LN     |  1132K|   137M|       |    66M  (1)| 00:43:28 |:BF0000|:BF0000|
-------------------------------------------------------------------------------------------------------------------------

I could solve the problem by adding hints either directly to the statement or with a SQL profile or SQL patch. However, this is one of many statements, and the users will continuously produce more with the ad-hoc PS/Query tool. I need a generic solution.

The Cost-Based Optimizer chose the nested loop plan because it calculated that it was cheaper. However, that does not correspond to the change in the actual execution time. The plan that was executed more quickly cost more, indicating a problem with the cost model.

Why is the full scan so expensive?
How can I make it cheaper?

Now read on...

Logging Run Controls and Bind Variables for Scheduled PS/Queries

noreply@blogger.com (David Kurtz) — Thu, 08 May 2025 08:06:00 +0000

This blog proposes additional logging for scheduled PS/Queries so that long-running queries can be reconstructed and analysed.

Previous blog posts have discussed limiting PS/Query runtime with the resource manager (see Management of Long Running PS/Queries Cancelled by Resource Manager CPU Limit). From 19c, on Engineered Systems only, the 'Oracle Database automatically quarantines the plans for SQL statements terminated by … the Resource Manager for exceeding resource limits'. SQL Quarantine is enabled by default in Oracle 19c on Exadata (unless patch 30104721 is applied that backports the new 23c parameters, see Oracle Doc ID 2635030.1: 19c New Feature SQL Quarantine - How To Stop Automatic SQL Quarantine).

What is the Problem?

SQL Quarantine prevents a query from executing. Therefore, AWR will not capture the execution plan. AWR will also purge execution plans where an execution has not been captured within the AWR retention period. The original long-running query execution that was quarantined, if captured by AWR, will be aged out because it will not execute again.

If we want to investigate PS/Queries that produced execution plans that exceeded the runtime limit and were then quarantined, we need to reproduce the execution plan, either with the EXPLAIN PLAN FOR command or by executing the query in a session where the limited resource manager consumer group does not apply.

However, PS/Queries with bind variables present a challenge. A PS/Query run with different bind variables can produce different execution plans. One execution plan might be quarantined and so never complete, while another may complete within an acceptable time.

In AWR, a plan is only captured once for each statement. Therefore, it is possible to find one set of bind variables for each plan, although there may be many sets of bind variables that all produce the same execution plan. However, we cannot obtain Oracle bind variables for quarantined execution plans that did not execute. To regenerate their execution plans, we need another way to obtain their bind variables.

This problem occurs more generally where the Diagnostics Pack is not available, then it is not possible to reconstruct long-running queries without additional logging or tracing.

Solution

Scheduled PS/Queries are executed by the PSQUERY application engine. The name of the query and the bind variables are passed via two run control records. Users typically reuse an existing run control but provide different bind variable values. I propose to introduce two tables to hold a copy of the data in these tables for each process instance.

PS_QUERY_RUN_CNTRL: Scheduled Query Run Control. This record identifies the query executed. Rows in this table will be copied to PS_QRYRUN_CTL_HST.
PS_QUERY_RUN_PARM: Scheduled Query Run Parameters. This record holds the bind variables and the values passed to the query. The table contains a row for each bind variable for each execution. Rows in this table will be copied to PS_QRYRUN_PARM_HST

Two database triggers manage the history tables:

A database trigger that fires when the run status of the request is updated to '7' (processing). It copies rows for the current run control into two corresponding history tables. Thus, we will have a log of every bind variable for every scheduled query.
A second database trigger will fire when a PSQUERY request record is deleted. It deletes the corresponding rows from these history tables.

When a PS/Query produces a quarantined execution plan, the PSQUERY process terminates with error ORA-56955: quarantined plan used (see Quarantined SQL Plans for PS/Queries). Now we can obtain the bind variables that resulted in attempts to execute a quarantined query execution plan.

Implementation

The following script (ps_query_run_cntrl_hist_trigger.sql) creates the tables and triggers.

Application Designer record definitions should be created for the two history tables by importing the project QRYRUN_HST (download QRYRUN_HST.zip from GitHub).

REM ps_query_run_cntrl_hist_trigger.sql
REM 21.4.2025 - trigger and history tables to capture 
set echo on serveroutput on timi on
clear screen
spool ps_query_run_cntrl_hist_trigger
rollback;

CREATE TABLE SYSADM.PS_QRYRUN_CTL_HST 
  (PRCSINSTANCE INTEGER  DEFAULT 0 NOT NULL,
   OPRID VARCHAR2(30)  DEFAULT ' ' NOT NULL,
   RUN_CNTL_ID VARCHAR2(30)  DEFAULT ' ' NOT NULL,
   DESCR VARCHAR2(30)  DEFAULT ' ' NOT NULL,
   QRYTYPE SMALLINT  DEFAULT 1 NOT NULL,
   PRIVATE_QUERY_FLAG VARCHAR2(1)  DEFAULT 'N' NOT NULL,
   QRYNAME VARCHAR2(30)  DEFAULT ' ' NOT NULL,
   URL VARCHAR2(254)  DEFAULT ' ' NOT NULL,
   ASIAN_FONT_SETTING VARCHAR2(3)  DEFAULT ' ' NOT NULL,
   PTFP_FEED_ID VARCHAR2(30)  DEFAULT ' ' NOT NULL) TABLESPACE PTTBL
/
CREATE UNIQUE  iNDEX SYSADM.PS_QRYRUN_CTL_HST 
ON SYSADM.PS_QRYRUN_CTL_HST (PRCSINSTANCE) TABLESPACE PSINDEX PARALLEL NOLOGGING
/
ALTER INDEX SYSADM.PS_QRYRUN_CTL_HST NOPARALLEL LOGGING
/
CREATE TABLE SYSADM.PS_QRYRUN_PARM_HST 
  (PRCSINSTANCE INTEGER  DEFAULT 0 NOT NULL,
   OPRID VARCHAR2(30)  DEFAULT ' ' NOT NULL,
   RUN_CNTL_ID VARCHAR2(30)  DEFAULT ' ' NOT NULL,
   BNDNUM SMALLINT  DEFAULT 0 NOT NULL,
   FIELDNAME VARCHAR2(18)  DEFAULT ' ' NOT NULL,
   BNDNAME VARCHAR2(30)  DEFAULT ' ' NOT NULL,
   BNDVALUE CLOB) TABLESPACE PSIMAGE2 
/
CREATE UNIQUE  iNDEX SYSADM.PS_QRYRUN_PARM_HST 
ON SYSADM.PS_QRYRUN_PARM_HST (PRCSINSTANCE, BNDNUM) TABLESPACE PSINDEX PARALLEL NOLOGGING
/
ALTER INDEX SYSADM.PS_QRYRUN_PARM_HST NOPARALLEL LOGGING
/

PSQUERY is not a restartable Application Engine program. Therefore, there is no risk of duplicate inserts into the history tables.
The exception handlers in the triggers deliberately suppress any error, in case that causes the process scheduler to crash.

CREATE OR REPLACE TRIGGER sysadm.query_run_cntrl_hist_ins
BEFORE UPDATE OF runstatus ON sysadm.psprcsrqst
FOR EACH ROW
WHEN (new.runstatus ='7' AND old.runstatus != '7' AND new.prcsname = 'PSQUERY' AND new.prcstype = 'Application Engine')
BEGIN
  INSERT INTO PS_QRYRUN_CTL_HST 
  (PRCSINSTANCE, OPRID, RUN_CNTL_ID, DESCR ,QRYTYPE, PRIVATE_QUERY_FLAG, QRYNAME, URL, ASIAN_FONT_SETTING, PTFP_FEED_ID)
  SELECT :new.prcsinstance, OPRID, RUN_CNTL_ID, DESCR ,QRYTYPE, PRIVATE_QUERY_FLAG, QRYNAME, URL, ASIAN_FONT_SETTING, PTFP_FEED_ID 
  FROM ps_query_run_cntrl WHERE oprid = :new.oprid AND run_cntl_id = :new.runcntlid;
  
  INSERT INTO PS_QRYRUN_PARM_HST
  (PRCSINSTANCE, OPRID, RUN_CNTL_ID, BNDNUM, FIELDNAME, BNDNAME, BNDVALUE) 
  SELECT :new.prcsinstance prcsinstance, OPRID, RUN_CNTL_ID, BNDNUM, FIELDNAME, BNDNAME, BNDVALUE
  FROM ps_query_run_parm WHERE oprid = :new.oprid AND run_cntl_id = :new.runcntlid;

  EXCEPTION WHEN OTHERS THEN NULL; --exception deliberately coded to suppress all exceptions 
END;
/

CREATE OR REPLACE TRIGGER sysadm.query_run_cntrl_hist_del
BEFORE DELETE ON sysadm.psprcsrqst
FOR EACH ROW
WHEN (old.prcsname = 'PSQUERY' AND old.prcstype = 'Application Engine')
BEGIN
  DELETE FROM PS_QRYRUN_CTL_HST WHERE prcsinstance = :old.prcsinstance;
  DELETE FROM PS_QRYRUN_PARM_HST WHERE prcsinstance = :old.prcsinstance;

  EXCEPTION WHEN OTHERS THEN NULL; --exception deliberately coded to suppress all exceptions
END;
/ 
show errors

spool off

Example

When a query is scheduled to run on the process scheduler, the bind variables are specified through this generic dialogue.

Once the PSQUERY process has started (it immediately commits its update to RUNSTATUS), these values are written to the new history tables.

select * from ps_qryrun_ctl_hst;

PRCSINSTANCE OPRID    RUN_CNTL_ID  DESCR                             QRYTYPE P QRYNAME                        URL                                                ASI PTFP_FEED_ID                  
------------ -------- ------------ ------------------------------ ---------- - ------------------------------ -------------------------------------------------- --- ------------------------------
    12345678 ABCDEF   042225       Journal Line Detail - Account           1 N XXX_JRNL_LINE_DTL_ACCT         https://xxxxxxx.yyyyy.com/psp/XXXXXXX/EMPLOYEE/ERP                                   

select * from ps_qryrun_ctl_hst;

PRCSINSTANCE OPRID    RUN_CNTL_ID  BNDNUM FIELDNAME          BNDNAME              BNDVALUE                      
------------ -------- ------------ ------ ------------------ -------------------- ------------------------------
    12345678 ABCDEF   042225            1 bind1              BUSINESS_UNIT        354XX 
    12345678 ABCDEF   042225            2 bind2              BUSINESS_UNIT        354XX 
    12345678 ABCDEF   042225            3 FISCAL_YEAR        FISCAL_YEAR          2025 
    12345678 ABCDEF   042225            4 ACCOUNTING_PD_FROM ACCOUNTING_PD_FROM   2 
    12345678 ABCDEF   042225            5 ACCOUNTING_PD_TO   ACCOUNTING_PD_TO     2 
    12345678 ABCDEF   042225            6 bind6              ACCOUNT              23882XXXXX 
    12345678 ABCDEF   042225            7 bind7              ACCOUNT              23882XXXXX 
    12345678 ABCDEF   042225            8 bind8              ALTACCOUNT           23882XXXXX 
    12345678 ABCDEF   042225            9 bind9              ALTACCOUNT           23882XXXXX

Conclusion

If the query is quarantined, PSQUERY will terminate with error ORA-56955: quarantined plan used. The SQL statement can be extracted from the message log, and the execution plan can be generated with the EXPLAIN PLAN FOR command, using the bind variable values captured in the history tables.

Note: The signature of the SQL Quarantine directive is the exact matching signature of the SQL text (it can be generated from the SQL text with dbms_sqltune.sqltext_to_signature). There can be multiple PLAN_HASH_VALUEs for the same signature (because there can be multiple execution plans for the same SQL). Verify that the FULL_PLAN_HASH_VALUE of the execution plan generated with the captured bind variables corresponds to the PLAN_HASH_VALUE of a SQL Quarantine directive.

A Resource Manager CPU Time Limit for PS/Queries Executed On-line in the PIA

noreply@blogger.com (David Kurtz) — Thu, 06 Mar 2025 07:26:00 +0000

In previous posts, I have proposed:

A Sample Oracle Database Resource Manager Plan for PeopleSoft. It can be downloaded from Github: PSFT_PLAN.
Setting a maximum CPU time for PS/Queries run on the process scheduler (see Management of Long Running PS/Queries Cancelled by Resource Manager CPU Limit).

This blog looks at whether a limit could also be set for PS/Queries run via the PeopleSoft Internet Architecture (PIA).

The main objection to having the database terminate a PS/Query running in the PIA is that the resulting Oracle error message will be displayed in the browser without any further explanation. Therefore, I think it is better to allow the PIA to handle termination of the query. However, I also think that it would be prudent to prevent queries continuing to run in background after the client session has been terminated.

Timeout Settings for PS/Query

The inactivity timeout in the PIA, is delivered at 20 minutes (1200 seconds), with a warning 2 minutes earlier. Then the user's session in the PIA is terminated.

There are timeouts on every Tuxedo service. In the PeopleSoft Application Server configuration, the service timeout is specified for each type of application server/queue.

…
[PSQRYSRV]
;=========================================================================
; Settings for PSQRYSRV
;=========================================================================

;-------------------------------------------------------------------------
; UBBGEN settings
Min Instances=3
Max Instances=3
Service Timeout=1200
…

All the services advertised on the queue are given that timeout.

…
ICQuery         SRVGRP=APPSRV
                LOAD=50 PRIO=50
{QUERYSRV}
                SVCTIMEOUT={$PSQRYSRV\Service Timeout}
{QUERYSRV}
{!QUERYSRV}
                SVCTIMEOUT={$PSAPPSRV\Service Timeout}
{!QUERYSRV}
                BUFTYPE="ALL"
…

PS/Queries are run by the ICQuery service that is advertised on PSQRYSRV server if configured, and whose service timeout is also delivered set to 1200s. Otherwise, it is advertised on PSAPPSRV whose timeout is 300s.

Recommendations

PSQRYSRV should always be configured if PS/Queries are to be run online in an application server domain. Partly, so that it has the longer timeout, and partly so that long running PS/Queries do not block short on-line requests.
Please avoid the temptation to increase either of these timeouts. If a query needs to run for more than 20 minutes, then it should be run on the process scheduler.

When the service timeout is reached, the Tuxedo server process will terminate. This may not terminate the query on the database until the current fetch operation completes. If a query involves a large group or sort operation, it can be a long time before the first fetch returns.

Oracle Terminated Connection Timeout (sometimes known as Dead Connect Detection) should be configured by setting SQLNET.EXPIRE_TIME in the SQLNET.ORA file. Then database shadow process periodically sends a probe to the otherwise idle client, and if the client doesn't respond the session terminates. However, this process is not perfect.

Therefore, it would be prudent to set a timeout in the consumer group for online PS/Query sessions. In PSFT_PLAN, that is the PSQUERY_ONLINE_GROUP consumer group. We don't want the timeout to terminate the session before either ICQuery service times out, nor do we want the query to run on afterwards. Therefore, the consumer group timeout should be set to the same value as the PSQRYSRV timeout, so also 1200 seconds.

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'PSFT_PLAN', 'PSQUERY_ONLINE_GROUP'
    ,mgmt_p6 => 90
    ,switch_group => 'CANCEL_SQL'
    ,switch_time => 1200
    ,switch_for_call => TRUE
  );

If you change the default inactivity timeout, these three settings should all be set to the same value.

If you are not running on Exadata, then it is safe to set this timeout without any further configuration.

Disabling SQL Quarantine on Exadata for PS/Query While Using it Elsewhere

However, on Exadata on 19c, the database will automatically create SQL quarantine directives for statements that exceed the CPU time limit specified in the consumer group and are terminated with error ORA-00040: active time limit exceeded - call aborted. It may take a few minutes for the database to create the quarantine directive - see 2634990.1: 19c New Feature SQL Quarantine Not Work. Then, the next time the same query generates the same execution plan, it will immediately be terminated with error ORA-56955: quarantined plan used. Again, we don't want such SQL errors produced in the PIA.

In Oracle 21c, two new parameters have been introduced to control SQL Quarantine.

OPTIMIZER_CAPTURE_SQL_QUARANTINE enables or disables the automatic creation of SQL Quarantine configurations. The default value is FALSE.
OPTIMIZER_USE_SQL_QUARANTINE determines whether the optimizer considers SQL Quarantine configurations when choosing an execution plan for a SQL statement. The default value is TRUE.

The parameters can be backported to Oracle 19.3 or later by applying patch 30104721 (see Oracle Doc ID 2635030.1: 19c New Feature SQL Quarantine - How To Stop Automatic SQL Quarantine, and Go-Faster Oracle Blog: New Parameters In 21c To Control Automatic SQL Quarantine Can Be Backported To 19c). Both these parameters can be set at system and session level.

If you want to take advantage of SQL Quarantine, you have to enable it. However, I suggest leaving the new parameters at their default values at system level, even though this means a change of behaviour in 19c when the patch is applied.

OPTIMIZER_CAPTURE_SQL_QUARANTINE = FALSE
OPTIMIZER_USE_SQL_QUARANTINE = TRUE

Then set both parameters to TRUE at session level in the session for

PSQUERY processes run on the process scheduler.
SQL*Plus and SQL Developer processes.

Ensure they are not set for PIA application server processes

PSAPPSRV
PSQRYSRV

CPU Timeouts in the Sample PSFT_PLAN Resource Plan

In my sample resource plan for PeopleSoft, three consumer groups now have timeouts. SQL Quarantine works in conjunction with consumer groups that have CPU timeouts. I want SQL Quarantine disabled in PSQUERY_ONLINE_GROUP, but enabled in PSQUERY_BATCH_GROUP and LOW_LIMITED_GROUP.

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'PSFT_PLAN', 'PSQUERY_ONLINE_GROUP'
    ,mgmt_p6 => 90
    ,switch_group => 'CANCEL_SQL'
    ,switch_time => 1200 /*same as ICQuery service timeout*/
    ,switch_elapsed_time => 1200
    ,switch_estimate => FALSE /*do not timeout on basis of estimated time*/
    ,switch_for_call => TRUE
  );
  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
  );
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'PSFT_PLAN', 'LOW_LIMITED_GROUP'
    ,mgmt_p8 => 1
    ,switch_group => 'CANCEL_SQL'
    ,switch_time => 7200
    ,switch_elapsed_time => 7200
    ,switch_estimate => TRUE 
    ,switch_for_call => TRUE
  );

The consumer groups are mapped to sessions by program name, module and action.

DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (attribute => DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM    , value => 'PSQRYSRV%'                 , consumer_group => 'PSQUERY_ONLINE_GROUP');
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (attribute => DBMS_RESOURCE_MANAGER.MODULE_NAME_ACTION, value => 'QUERY_MANAGER.QUERY_VIEWER', consumer_group => 'PSQUERY_ONLINE_GROUP');

DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (attribute => DBMS_RESOURCE_MANAGER.MODULE_NAME       , value => 'PSQUERY'                   , consumer_group => 'PSQUERY_BATCH_GROUP');
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (attribute => DBMS_RESOURCE_MANAGER.MODULE_NAME       , value => 'PSAE.PSQUERY.%'            , consumer_group => 'PSQUERY_BATCH_GROUP');

DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (attribute => DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM    , value => 'SQL Developer%'            , consumer_group => 'LOW_LIMITED_GROUP');
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (attribute => DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM    , value => 'sqlplus%'                  , consumer_group => 'LOW_LIMITED_GROUP');
DBMS_RESOURCE_MANAGER.set_consumer_group_mapping (attribute => DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM    , value => 'Toad%'                     , consumer_group => 'LOW_LIMITED_GROUP');

Enabling SQL Quarantine for Scheduled PSQUERY Processes

Initialisation parameters can be set for processes run on the process scheduler using a trigger on the PSPRCSRQST table. This technique was described in a previous blog post: Setting Oracle Session Parameters for Specific Process Scheduler Processes.

When a PeopleSoft process is initiated on the process scheduler, the first thing it does is to update its RUNSTATUS on PSPRCSRQST to '7' indicating that it is processing. The SET_PRCS_SESS_PARM_TRIG trigger fires on that transition. It dynamically generates ALTER SESSION commands for the metadata that matches the current process.

Script to create trigger set_prcs_sess_parm_trg.sql
Example script to create metadata: set_prcs_sess_parm.sql. It includes the following statement that will create metadata for PSQUERY to set the new SQL Quarantine parameters if they are available and if running on Exadata.

----------------------------------------------------------------------------------------------------
--Settings for SQL Quarantine in PSQuery on Exadata -- note SQL checks that parameter is valid
----------------------------------------------------------------------------------------------------
DELETE FROM sysadm.PS_PRCS_SESS_PARM where prcsname = 'PSQUERY' 
AND param_name IN('optimizer_capture_sql_quarantine','optimizer_use_sql_quarantine')
/
----------------------------------------------------------------------------------------------------
INSERT INTO sysadm.PS_PRCS_SESS_PARM (prcstype, prcsname, oprid, runcntlid, keyword, param_name, parmvalue)
with n as ( --returns a row on Exadata only
SELECT  COUNT(DISTINCT cell_name) num_exadata_cells
FROM    v$cell
HAVING  COUNT(DISTINCT cell_name)>0
), x (param_name, keyword, parmvalue) as ( --returns rows if parameters available
select  name, 'SET', 'TRUE' 
from    v$parameter, n
where   name IN('optimizer_capture_sql_quarantine','optimizer_use_sql_quarantine')
), y (prcstype, prcsname, oprid, runcntlid) as (
select  prcstype, prcsname, ' ', ' ' 
from    ps_prcsdefn
where   prcsname = 'PSQUERY'
)
select  y.prcstype, y.prcsname, y.oprid, y.runcntlid, x.keyword, x.param_name, x.parmvalue
from    x,y
/

Enabling/Disabling SQL Quarantine for Other Processes at Logon

This is done with a AFTER LOGON trigger and another metadata data table that is similar to the scheduled process trigger. The mappings in the metadata in this script must match the mappings for the consumer groups where automatic SQL quarantine capture is required.

Script to create trigger and metadata: set_sess_parm_trg.sql

TL;DR

Set the timeout for the consumer group for PS/Query to be the same value as the ICQuery Tuxedo service timeout (usually also the same value as the PIA inactivity timeout).
SQL Quarantine is only available on Exadata. On other platforms, the query just runs to the CPU timeout, by which time the ICQuery service has already timed out.
If on Exadata, then

Apply patch 30104721 on 19c to backport new 21c parameters.
Leave the quarantine parameters at their default values at system level
Set both parameters to TRUE at session level in the session for

PSQUERY processes run on the process scheduler.
SQL*Plus and SQL Developer processes.

Quarantined SQL Plans for PS/Queries

noreply@blogger.com (David Kurtz) — Mon, 24 Feb 2025 17:46:00 +0000

This follows on from my previous post, Management of Long Running PS/Queries Cancelled by Resource Manager.

From 19c, on Engineered Systems only (such as Exadata and Exadata Cloud Service) the 'Oracle Database automatically quarantines the plans for SQL statements terminated by … the Resource Manager for exceeding resource limits.

The Resource Manager can set a maximum estimated execution time for a SQL statement, for example, 20 minutes. If a statement execution exceeds this limit, then the Resource Manager terminates the statement. However, the statement may run repeatedly before being terminated, wasting 20 minutes of resources each time it is executed.

Starting in Oracle Database 19c, if a statement exceeds the specified resource limit, then the Resource Manager terminates the execution and “quarantines” the plan. To quarantine the plan means to put it on a blacklist of plans that the database will not execute. Note that the plan is quarantined, not the statement itself.'

[Oracle SQL Tuning Guide: 4.7 About Quarantined SQL Plans]

See also Tim Hall's article: SQL Quarantine in Oracle Database 19c

When an attempt is made to execute a quarantined execution plan an error is produced: ORA-56955: quarantined plan used.

Oracle does not log timed-out or quarantined queries. On V$SQL and V$SQLSTAT, AVOIDED_EXECUTIONS records the number of times a SQL query has been prevented from running. However, this will not stay in the library cache long on a PeopleSoft system, due to the continual parse of dynamically generated SQL statements. As of Oracle 19.20, it is not recorded in AWR on DBA_HIST_SQLSTAT.

If an error condition occurs during a PSQUERY process run on the process scheduler, the process terminates with an error. The SQL statement and the error message are recorded in the Application Engine message log. As demonstrated in the previous blog, we can detect such failures by inspecting the message log of the PSQUERY process that did not finish successfully (ie. it has an end time, but the run status does not indicate success).

Matching Quarantine Directives to Cancelled Queries

Quarantine directives are visible via DBA_SQL_QUARANTINE, including SQL text and execution plan hash value.

It would be useful to know which quarantine directive relates to which query. However, it is not easy to match the SQL in the PeopleSoft message log entries with that in the quarantine entries. The SQL text in the message log can have multiple spaces. These are stripped out in the DBA_SQL_QUARANTINE view where the normalised SQL statement is visible.

The timestamp of creation and last execution of the quarantine directive is stored on it, but matching these to when the query was running can result in false positives.

Also, you cannot tell which quarantine directive was created by which consumer group. The maximum CPU timeout is recorded on DBA_SQL_QUARANTINE. In my example, it is only possible to distinguish the originating consumer group because the two consumer groups happen to have different timeouts.

A method that matches exactly, but only returns partial rows is to:

Obtain ASH data for queries terminated by the resource manager. It can be matched by timestamp, MODULE, and ACTION (provided that EnableAEMonitoring is enabled).

Profile the ASH to find the statement that took the longest during each PSQUERY process, and that is almost certain to be the SQL query. Thus obtaining the SQL_ID, SQL Plan Hash Value and consumer group ID. It is also possible to determine the total database time for the query, and the database time spent on CPU.
The consumer group name can then be obtained from DBA_HIST_RSRC_CONSUMER_GROUP

Obtain the SQL text for the long-running query. It would also have to be captured by an AWR snapshot. This does often occur because it was a long-running SQL, but it is not certain.
The signature for the SQL statement (not the force-matching signature) can be derived using the SQLTEXT_TO_SIGNATURE function in DBMS_SQLTUNE. This can be matched to the signature recorded in DBA_SQL_QUARANTINE.
You can have multiple quarantine directives for the same signature (i.e. the same SQL statement), each with a different plan hash value.

NB: The plan hash value on DBA_SQL_QUARANTINE is the adaptive plan hash value (with all of its possible plan alternatives), and therefore it matches SQL_FULL_PLAN_HASH_VALUE in the ASH data, and not SQL_PLAN_HASH_VALUE (the plan that actually executed).

Note that

When a query executes until timed-out, producing ORA-00040, you usually can find the SQL statement in the AWR repository and so generate the signature to exactly match the quarantine record.
When an attempt is made to run a quarantined statement and execution plan, you usually cannot find the SQL statement because it hasn't run for long enough to produce an ASH sample. Even when it has, you also have to rely on the statement having been captured previously by AWR. Those conditions only come together occasionally.

This matching process is done by this query: message_log_checker-psquery2.sql. Below is a sample output.

We can see the quarantine directives that were created when the resource manager cancelled a query, raising error ORA-00040: active time limit exceeded - call aborted.
However, where quarantine directives have prevented SQL from executing, raising error ORA-56955: quarantined plan used, the ASH data from the event that originally created the directive has since been purged, so we cannot use it to match directives.

Mon Feb 24                                                                                                                                                           page    1
                                                     PS/Queries terminated by Resource Manager/quarantined Execution Plan

                                                          Public/                                                              ASH
                                                          Private                                Ru Oracle      Exec    ASH    CPU Message Log
     P.I. DBNAME  OPRID    RUNCNTLID                      Query   QRYNAME                        St Err. #      Secs   Secs   Secs Date/Time Stamp              SQL_ID
--------- ------- -------- ------------------------------ ------- ------------------------------ -- --------- ------ ------ ------ ---------------------------- -------------
   SQL Plan   Full Plan                                                                                      CPU
 Hash Value  Hash Value Consumer Group Name                   SIGNATURE Quarantine Name                      Time  Quarantine Created           Quarantine Last Executed
----------- ----------- ------------------------- --------------------- ------------------------------------ ----- ---------------------------- ----------------------------
…
 31452465 FSPROD  USR0001  GBR_JRNL_LN_DETAIL_ACCT        Public  GBR_JRNL_LN_DETAIL_ACCT        10 ORA-56955     36     10     10 20-FEB-25 06.28.03.578218 PM 0wm9g6xkys12h
 4009529842   653370716 PSQUERY_BATCH_GROUP         5584654620166156419

 31451318 FSPROD  USR0002  GBR_JRNL_LN_DETAIL_ACCT        Public  GBR_JRNL_LN_DETAIL_ACCT        10 ORA-56955     36               20-FEB-25 02.36.38.590841 PM


 31451292 FSPROD  USR0002  GBR_JRNL_LN_DETAIL_ACCT        Public  GBR_JRNL_LN_DETAIL_ACCT        10 ORA-56955     36               20-FEB-25 02.30.51.777351 PM


 31438602 FSPROD  USR0003  1                              Private DK_GBR_GL_DETAIL_NEW           10 ORA-00040  28316  28275  14203 18-FEB-25 11.39.19.502412 PM 5qrbrf775whky
 3446094907  3491308607 PSQUERY_BATCH_GROUP        16266909742923016361 SQL_QUARANTINE_f3gxc76u48u59d019243f 14400 18-FEB-25 11.49.33.091081 PM

 31437925 FSPROD  USR0004  16                             Private TB_TEST2                       10 ORA-00040  17684  17654  17541 18-FEB-25 06.09.14.060615 PM 06xqrgj18wp05
 4256462904  2062199471 PSQUERY_BATCH_GROUP         6341132966559464532 SQL_QUARANTINE_5h01uuscnrg2n7aeaaaaf 14400 18-FEB-25 06.17.20.679769 PM

 31437907 FSPROD  USR0004  16                             Private TB_TEST2                       10 ORA-00040  17694  17695  17592 18-FEB-25 06.04.05.942470 PM 4yurn75y2p0t2
 3232504707   121066138 PSQUERY_BATCH_GROUP         4966087806133732884 SQL_QUARANTINE_49usqjjc001hn0737529a 14400 18-FEB-25 06.17.24.869185 PM
 …

Management of Long Running PS/Queries Cancelled by Resource Manager CPU Limit

noreply@blogger.com (David Kurtz) — Thu, 20 Feb 2025 11:46:00 +0000

I have written previously about using the Oracle database resource manager to prioritise the allocation of CPU to different processes in a PeopleSoft system. I proposed a sample resource plan that can be used as a starting point to build a resource plan that meets a system's specific objectives and requirements.

see PSFT_PLAN: A Sample Oracle Database Resource Manager Plan for PeopleSoft
The script to implement the sample PSFT_PLAN resource plan is available from GitHub.

This post looks at

How to configure the resource manager to cancel long-running queries,
What happens when it does and what PeopleSoft users experience,
How the system administrators can monitor such queries,
What action could they take?

Configuring SQL Cancellation in a Resource Manager Consumer Group

The sample resource plan, PSFT_PLAN, contains various server consumer groups. It relies upon MODULE and ACTION being set by enabling PeopleSoft instrumentation (EnableAEMonitoring=1) and/or the psftapi_store_prcsinstance trigger on PSPRCSRQST (see Effective PeopleSoft Performance Monitoring),

PSQUERY_BATCH_GROUP

Applies to scheduled PSQUERY Application Engine Programs
Limited to 4 hours on CPU (or estimated at >= 4 hours)

PSQUERY_ONLINE

Applies to queries run online via the PeopleSoft Internet Architecture (PIA).
There is no resource manager limit for this consumer group.

The PIA has a session timeout (default 20 minutes).
The ICQuery Tuxedo service that runs the queries also has a timeout (default 20 minutes)

When the resource manager cancels a SQL call, it simply raises an Oracle error that appears in a PIA message box without further explanation. It is better to let the PIA timeouts handle online queries in a more controlled fashion.
To prevent queries continuing to run on the database after the client has been terminated

Enable Terminated Connection Timeout by setting SQLNET.EXPIRE_TIME to 5 to 10 minutes in SQLNET.ORA.
Set timeout CPU timeout to match PIA/ICQuery timeout. However, if on Exadata only enable SQL Quarantine for scheduled PS/Queries (see A Resource Manager CPU Time Limit for PS/Queries Executed On-line in the PIA)

LOW_LIMITED_GROUP

Applies to SQL*Plus, SQL Developer and Toad.

Limited to 2 hours on CPU (or estimated at >= 2 hours)

Recommendations:

Users should generally be encouraged to schedule queries that will take more than a few minutes to run on the process scheduler.
Resist the temptation to increase either the PIA inactivity timeout or ICQuery service timeout from the delivered setting of 20 minutes.

Plan Directives

Plan directives are created with DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE.

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

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'PSFT_PLAN', 'PSQUERY_ONLINE_GROUP'
    ,mgmt_p6 => 90
  );

  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'PSFT_PLAN', 'LOW_LIMITED_GROUP'
    ,mgmt_p8 => 1
    ,switch_group => 'CANCEL_SQL'
    ,switch_time => 7200
    ,switch_elapsed_time => 7200
    ,switch_estimate => TRUE 
    ,switch_for_call => TRUE
  );

Four parameters control cancellation behaviour.

SWITCH_GROUP specifies the consumer group to which the session is switched when a switch condition is met. If the group switches to CANCEL_SQL the current call is cancelled, raising error ORA-00400.
SWITCH_TIME specified the number of seconds on CPU (not elapsed time).
If SWITCH_ESTIMATE is true, the resource manager also switches group if the estimated run time is greater than the switch time
SWITCH_FOR_CALL is set to true so that if the consumer group is switched, it is then restored to the original consumer group at the end of the top call. Thus a persistent session is not permanently switched. This is important if switching an application engine server (PSAESRV) session.

Cancellation Behaviour

The resource manager can cancel long-running queries in these consumer groups raising ORA-00040: active time limit exceeded - call aborted

The query may be cancelled immediately because the estimated execution time is greater than the limit.
Otherwise, it is quite likely to run for an elapsed time that is greater than the CPU time limit. The database session is only on CPU if the event reported on the session is NULL. Otherwise, it is doing something else.
Some time will be consumed in the client process, during which the database session will be idle waiting for the next fetch request from the client. This is usually reported as event SQL*Net message from client.
Some of the database time may not be on CPU because it may be doing something else, such as physical IO.
The database session may be held back by the resource manager allocating CPU to higher priority processes, in which case again the session will not be on CPU, and will report being on event resmgr: cpu quantum.

Querying the PeopleSoft Message Log

The full message text is stored in multiple pieces in PS_MESSAGE_LOGPARM and must be reconstructed so that it can be searched for the error code. I demonstrated this technique in another blog post: Querying the PeopleSoft Message Log with SQL.

For this analysis, I have made some alterations to the message log query (see message_log_checker-psquery.sql).

This query is restricted to messages generated by PSQUERY processes that did not run to success (not run status 9).
PeopleSoft messages are typically defined with up to 9 substitution variables, but long SQL statements can have many more entries in PS_MESSAGE_LOGPARM. So the PL/SQL function in this query simply appends any additional log parameter rows beyond the 9 substitution variables to the end of the generated string.
Once the message has been generated we can look for one of the error messages associated with the resource manager terminating a query:

ORA-00040: active time limit exceeded - call aborted
ORA-56955: quarantined plan used

It is necessary to filter by message number because even in PS/Query users can write invalid SQL that produces other error messages. However, all this text processing for each row retrieved makes the query quite slow.

Here is an example output. User USR001 ran a private query MY_TEST2 with run control 42. It ran for 20772s (5h 46m) until terminated by the resource manager. As explained above, the 4-hour limit is on CPU time that will be less than the elapsed time.

                                                         Public/
                                                          Private                                Ru   Exec                              Msg Msg  Msg
     P.I. DBNAME  OPRID    RUNCNTLID                      Query   QRYNAME                        St   Secs DTTM_STAMP_SEC               Seq Set  Nbr
--------- ------- -------- ------------------------------ ------- ------------------------------ -- ------ ---------------------------- --- --- ----
MSG
---------------------------------------------------------------------------------------------------------------------------------------------------- 
 12395311 FSPROD  USR001   42                             Private MY_TEST2                       10  20772 10-FEB-25 04.41.47.384694 PM   1  65   30
File: C:\PT860P13B_2403250500-retail\peopletools\src\pssys\qpm.cppSQL error. Stmt #: 8526  Error Position: 189  Return: 40 - ORA-00040: active time
 limit exceeded - call abortedFailed SQL stmt: SELECT A.LEDGER, A.FISCAL_YEAR, A.BUSINESS_UNIT, …
                                                                                                           10-FEB-25 04.41.47.421800 PM   2  50  380
Error in running query because of SQL Error, Code=40, Message=ORA-00040: active time limit exceeded - call aborted
…

From Oracle 19c on Exadata, timed-out statements are automatically quarantined. If a quarantined statement is run and a quarantined execution plan is generated, then error ORA-56955 is generated immediately. Therefore, it can also be detected in the logs. The query searches for both messages.

                                                          Public/
                                                          Private                                Ru   Exec                              Msg Msg  Msg
     P.I. DBNAME  OPRID    RUNCNTLID                      Query   QRYNAME                        St   Secs DTTM_STAMP_SEC               Seq Set  Nbr
--------- ------- -------- ------------------------------ ------- ------------------------------ -- ------ ---------------------------- --- --- ----
MSG
----------------------------------------------------------------------------------------------------------------------------------------------------
 12319513 FSPROD  USR002   Transactions                   Public  GBR_JRNL_LINE_DTL_ACCT         10     25 13-FEB-25 11.13.35.746644 PM   1  65   30
File: C:\PT860P13B_2403250500-retail\peopletools\src\pssys\qpm.cppSQL error. Stmt #: 8526  Error Position: 2783  Return: -8581 - ORA-56955: quarant
ined plan usedFailed SQL stmt: SELECT A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, A.JRNL_HDR_STATUS, B.LED
…
                                                                                                           13-FEB-25 11.13.35.814112 PM   2  50  380
Error in running query because of SQL Error, Code=-8581, Message=ORA-56955: quarantined plan used

I discuss automatic SQL quarantine for PS/Query in a subsequent blog.

Opinion

So far, I have explained how to set a maximum CPU time limit for PS/Queries in a resource manager consumer group and how to detect cancelled PS/Queries by examining the message log.

The final stage is to close the feedback loop and go back to the users producing the queries, find out what they are trying to do, and why the queries are running for such a long time.

SQL Profiles for Application Engine Steps that Reference Temporary Records via the %Table() Meta-SQL

noreply@blogger.com (David Kurtz) — Tue, 28 Jan 2025 17:36:00 +0000

Oracle generally advises using SQL Profiles/Patches/Baselines rather than introducing hints into application code. Using one of these forms of plan stability saves you from having to alter the code, and then having to test and verify that the change is functionally neutral and release it to production. It also saves repeating that whole process if you ever choose to remove or change the hint.

I generally use SQL profiles with PeopleSoft, because they are the only plan stability mechanism that can force match a statement. That is to say, a force-matching SQL profile will match other SQL statements that differ only in embedded literal values. PeopleSoft code makes extensive use of literal values. SQL patches and baselines only exactly match statements (so they match the SQL ID and not the force matching signature). Note that SQL Profiles require that the Tuning Pack is licenced, and that is only available on Enterprise Edition or Oracle.

I have written previously about using SQL Profiles to inject hints into dynamically generated code. If any part of the SQL (other than literal values) changes, then a different SQL profile is needed for each variation.

When generating SQL profiles for dynamically generated code, you have to be able to predict every possible variation in the code and generate a SQL profile for every combination of every variation. You end up writing code that mimics the dynamic code generation in the application

The same is true of Application Engine steps that reference temporary records via the %Table() meta-SQL but are otherwise static. This variation is a special case because it is a part of PeopleTools' behaviour. It is easy to determine which tables could be referenced by querying some of the PeopleTools tables. Although, the table name itself can be set dynamically!

Let's take an example statement. I identified it as performing poorly during an analysis of ASH data. I have a SQL_ID, a plan hash value, and ACTION indicates the name of the Application Engine step that generated it (because Application Engine instrumentation is enabled by setting EnableAEMonitoring - see https://blog.psftdba.com/2015/03/undocumented-application-engine.html).

SQL ID = bk98x60cspttj
SQL Plan Hash Value = 113493817
Action = XX_TREESEL.iDetVal.iDetVal.S

This is the statement and execution plan report generated by dbms_xplan.display_workload_repository (formatted and edited slightly). I have the statement, the execution plan and the outline of hints that describes the plan.

SELECT * FROM table(dbms_xplan.display_workload_repository('bk98x60cspttj',113493817,'ADVANCED +ADAPTIVE'));

SQL_ID
-------------
INSERT INTO PS_TSEL_P_TAO12 (CHARTFIELD, CHARTFIELD_VALUE,
PROCESS_INSTANCE) SELECT DISTINCT 'ACCOUNT', DV.ACCOUNT, 12345678 
FROM PS_TSEL_R30_TAO12 A, PS_GL_ACCOUNT_TBL DV 
WHERE A.CHARTFIELD = 'ACCOUNT' AND A.PROCESS_INSTANCE = 12345678 
AND DV.SETID = '12345' 
AND DV.EFFDT = (SELECT MAX(EFFDT) 
 	FROM PS_GL_ACCOUNT_TBL B WHERE SETID = DV.SETID AND ACCOUNT = DV.ACCOUNT 
 	AND EFFDT <= TO_DATE('2025-01-31','YYYY-MM-DD')) 
AND DV.ACCOUNT >= A.RANGE_FROM_30
AND DV.ACCOUNT <= A.RANGE_TO_30

Plan hash value: 113493817

--------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                   |                   |       |       | 38754 (100)|          |
|   1 |  LOAD TABLE CONVENTIONAL           | PS_TSEL_P_TAO17   |       |       |            |          |
|   2 |   SORT UNIQUE                      |                   |     1 |   161 | 38754   (1)| 00:00:02 |
|*  3 |    FILTER                          |                   |       |       |            |          |
|   4 |     MERGE JOIN                     |                   | 24219 |  3807K|     8  (50)| 00:00:01 |
|   5 |      SORT JOIN                     |                   |  1138 |   151K|     3  (34)| 00:00:01 |
|*  6 |       INDEX STORAGE FAST FULL SCAN | PSATSEL_R30_TAO17 |  1138 |   151K|     2   (0)| 00:00:01 |
|*  7 |      FILTER                        |                   |       |       |            |          |
|*  8 |       SORT JOIN                    |                   |  8513 |   207K|     3  (34)| 00:00:01 |
|*  9 |        INDEX STORAGE FAST FULL SCAN| PS_GL_ACCOUNT_TBL |  8513 |   207K|     2   (0)| 00:00:01 |
|  10 |     SORT AGGREGATE                 |                   |     1 |    25 |            |          |
|  11 |      FIRST ROW                     |                   |     1 |    25 |     2   (0)| 00:00:01 |
|* 12 |       INDEX RANGE SCAN (MIN/MAX)   | PS_GL_ACCOUNT_TBL |     1 |    25 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   6 - SEL$1 / A@SEL$1
   9 - SEL$1 / DV@SEL$1
  10 - SEL$2
  12 - SEL$2 / B@SEL$2

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"INS$1")
      FULL(@"INS$1" "PS_TSEL_P_TAO17"@"INS$1")
      INDEX_FFS(@"SEL$1" "A"@"SEL$1" ("PS_TSEL_R30_TAO17"."PROCESS_INSTANCE"
              "PS_TSEL_R30_TAO17"."CHARTFIELD" "PS_TSEL_R30_TAO17"."RANGE_FROM_30"
              "PS_TSEL_R30_TAO17"."RANGE_TO_30"))
      INDEX_FFS(@"SEL$1" "DV"@"SEL$1" ("PS_GL_ACCOUNT_TBL"."SETID" "PS_GL_ACCOUNT_TBL"."ACCOUNT"
              "PS_GL_ACCOUNT_TBL"."EFFDT"))
      LEADING(@"SEL$1" "A"@"SEL$1" "DV"@"SEL$1")
      USE_MERGE(@"SEL$1" "DV"@"SEL$1")
      PQ_FILTER(@"SEL$1" SERIAL)
      INDEX(@"SEL$2" "B"@"SEL$2" ("PS_GL_ACCOUNT_TBL"."SETID" "PS_GL_ACCOUNT_TBL"."ACCOUNT"
              "PS_GL_ACCOUNT_TBL"."EFFDT"))
      END_OUTLINE_DATA
  */
…

Below is the underlying Application Engine step that generated the SQL. There is other dynamic code in this step that is driven by configuration data as well as the %Table() meta-SQLs. Other reasons not to introduce hints into the statement include:

different dynamic variations of the code might require different hints, and then I would have to write more code to generate the hint dynamically. However, in this case, I am only going to deal with a single variation, and I am only going to produce one set of SQL Profiles,
this step and the code generation are delivered by PeopleSoft, any change in these areas would be considered as a customisation.

%InsertSelect(DISTINCT
, %Bind(FT_TSEL_AET.RECNAME_SEL_TBL, NoQuotes)
, %Bind(FT_TSEL_STR_AET.DTL_RECNAME, NoQuotes
) DV
, %Bind(FT_TSEL_AET.FIELDNAME_CF, NoQuotes) = %Bind(FT_TSEL_STR_AET.DTL_FIELDNAME)
, %Bind(FT_TSEL_AET.FIELDNAME_VALUE, NoQuotes) = DV.%Bind(FT_TSEL_STR_AET.DTL_FIELDNAME, NoQuotes)
, PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)
, TREE_NAME = %Bind(FT_TSEL_AET.TREE_NAME)
, TREE_NODE = A.TREE_NODE
, TREE_NODE_NUM = A.TREE_NODE_NUM
, TREE_LEVEL_NUM = A.TREE_LEVEL_NUM, SETCNTRLVALUE =%Bind(FT_TSEL_AET.SETCNTRLVALUE)
, %Bind(FT_TSEL_GEN_AET.FIELDNAME_FROM, NoQuotes) = A.%Bind(FT_TSEL_GEN_AET.FIELDNAME_FROM, NoQuotes)
, %Bind(FT_TSEL_GEN_AET.FIELDNAME_TO, NoQuotes) = A.%Bind(FT_TSEL_GEN_AET.FIELDNAME_TO, NoQuotes)
, SETID_TREE = %Bind(FT_TSEL_GEN_AET.SETID_TREE)
, EFFDT = %Bind(FT_TSEL_STR_AET.EFFDT)
, CFV_SET = %Bind(FT_TSEL_AET.CFV_SET)) 
FROM %Table(%Bind(FT_TSEL_GEN_AET.RECNAME_SEL_TBL, NoQuotes)) A
, %Table(%Bind(FT_TSEL_STR_AET.DTL_RECNAME, NoQuotes)) DV 
WHERE A.CHARTFIELD = %Bind(FT_TSEL_AET.FIELDNAME) 
AND A.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE) 
%Bind(FT_TSEL_WRK_AET.WHERE_TXT_LONG, NoQuotes)

This step references two temporary records. It generates data in one temporary table TSEL_P_TAO based on data in another, TSEL_R30_TAO. It happens to use instance 12 of both records. This is really just a coincidence. Temporary table instances are allocated and deallocated in a group when an application engine starts and ends, so it is common for one step to use the same instance of different tables, but there is nothing to stop different table instances from being used. That can occur when concurrently executing multiple instances of a program or different programs that allocate just some of the same tables.

I now need to produce SQL profiles for each permutation. I will start by generating a script to create a single profile for a single SQL statement using Oracle's coe_xfr_sql_profile.sql script that is delivered as a part of the SQLT utility. It is available from the Oracle support website (All About the SQLT Diagnostic Tool (Doc ID 215187.1)).

It produces a SQL script such as the one below (I have removed the comments). The SQL text is in a CLOB variable, and all the hints required to reproduce the same execution plan are in an array. The script generates a signature for the statement and then creates a SQL profile from that information.

I have already customised my version of coe_xfr_sql_profile.sql so that FORCE_MATCH => TRUE in the generated scripts.

WHENEVER SQLERROR EXIT SQL.SQLCODE;
VAR signature NUMBER;
DECLARE
sql_txt CLOB;
h       SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
INSERT INTO PS_TSEL_P_TAO17 (CHARTFIELD, CHARTFIELD_VALUE, PROCESS_INSTANCE) SELECT DISTINCT 'ACCOUNT', DV.ACCOUNT, 12345678
FROM PS_TSEL_R30_TAO17 A, PS_GL_ACCOUNT_TBL DV WHERE A.CHARTFIELD = 'ACCOUNT' AND A.PROCESS_INSTANCE = 12345678 AND DV.SETID 
= 'GLOBE' AND DV.EFFDT = (SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL B WHERE SETID = DV.SETID AND ACCOUNT = DV.ACCOUNT AND 
EFFDT <= TO_DATE('2025-01-31','YYYY-MM-DD')) AND DV.ACCOUNT >= A.RANGE_FROM_30 AND DV.ACCOUNT <= A.RANGE_TO_30
]';
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('19.1.0')]',
q'[DB_VERSION('19.1.0')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$683B0107")]',
q'[OUTLINE_LEAF(@"SEL$C772B8D1")]',
q'[UNNEST(@"SEL$2")]',
q'[OUTLINE_LEAF(@"INS$1")]',
q'[OUTLINE(@"SEL$2")]',
q'[OUTLINE(@"SEL$7511BFD2")]',
q'[OUTLINE(@"SEL$1")]',
q'[FULL(@"INS$1" "PS_TSEL_P_TAO17"@"INS$1")]',
q'[NO_ACCESS(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2")]',
q'[INDEX(@"SEL$C772B8D1" "DV"@"SEL$1" ("PS_GL_ACCOUNT_TBL"."SETID" "PS_GL_ACCOUNT_TBL"."ACCOUNT" "PS_GL_ACCOUNT_TBL"."EFFDT"))]',
q'[FULL(@"SEL$C772B8D1" "A"@"SEL$1")]',
q'[LEADING(@"SEL$C772B8D1" "VW_SQ_1"@"SEL$7511BFD2" "DV"@"SEL$1" "A"@"SEL$1")]',
q'[USE_NL(@"SEL$C772B8D1" "DV"@"SEL$1")]',
q'[USE_MERGE(@"SEL$C772B8D1" "A"@"SEL$1")]',
q'[PARTIAL_JOIN(@"SEL$C772B8D1" "A"@"SEL$1")]',
q'[INDEX_FFS(@"SEL$683B0107" "B"@"SEL$2" ("PS_GL_ACCOUNT_TBL"."SETID" "PS_GL_ACCOUNT_TBL"."ACCOUNT" "PS_GL_ACCOUNT_TBL"."EFFDT"))]',
q'[END_OUTLINE_DATA]');

:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);

DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text    => sql_txt,
profile     => h,
name        => 'coe_g7wz6ctquwjcy_2476903986',
description => 'coe g7wz6ctquwjcy 2476903986 '||:signature||'',
category    => 'DEFAULT',
validate    => TRUE,
replace     => TRUE,
force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
WHENEVER SQLERROR CONTINUE
SET ECHO OFF;
PRINT signature
PRO
PRO ... manual custom SQL Profile has been created
PRO
SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF "";
SPO OFF;
PRO
PRO COE_XFR_SQL_PROFILE_g7wz6ctquwjcy_2476903986 completed

In Application Engine, different tables will be used if multiple programs referencing the same table run concurrently, or if a process crashes and holds its allocation to a particular table instance. Therefore, I need to create a set of similar SQL profiles, one for each statement that could be generated by this Application Designer step. I need to iterate through all possibilities, so I have added some additional pieces to the script. They are explained in the footnotes below.

REM coe_xfr_sql_profile_XX_TREESEL.iDetVal.iDetVal.sql
SPO coe_xfr_sql_profile_XX_TREESEL.iDetVal.iDetVal.log;
WHENEVER SQLERROR CONTINUE
SET serveroutput on ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999;
clear screen
ALTER SESSION SET CURRENT_SCHEMA=SYSADM;¹
…
WHENEVER SQLERROR EXIT SQL.SQLCODE;
VAR signature NUMBER;

DECLARE
  l_recname1 VARCHAR2(15) :=    'TSEL_R30_TAO';²
  l_table1   VARCHAR2(18) := 'PS_TSEL_R30_TAO17';
  l_recname2 VARCHAR2(15) :=    'TSEL_P_TAO';
  l_table2   VARCHAR2(18) := 'PS_TSEL_P_TAO17';
  l_name     VARCHAR2(30);
  sql_txt CLOB;
  h       SYS.SQLPROF_ATTR;

  e_no_sql_profile EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_no_sql_profile, -13833);
BEGIN
FOR i IN (³
  WITH v⁴  AS (SELECT rownum-1 row_number FROM dual CONNECT BY LEVEL <= 100) 
  SELECT DISTINCT 
         v1.row_number id1, r1.recname recname1, t1.table_name table_name1
  ,      v2.row_number id2, r2.recname recname2, t2.table_name table_name2
  ,      o.TEMPTBLINSTANCES
  FROM   psrecdefn r1
  ,      psrecdefn r2
  ,      pstemptblcntvw⁵ i1
  ,      pstemptblcntvw i2
  ,      all_tables t1
  ,      all_tables t2
  ,      psoptions o 
  ,      ps.psdbowner p 
  ,      v v1
  ,      v v2
  WHERE  r1.rectype = 7 AND r1.recname = i1.recname AND r1.recname = l_recname1
  AND    r2.rectype = 7 AND r2.recname = i2.recname AND r2.recname = l_recname2
  AND    v1.row_number <= i1.temptblinstances + o.temptblinstances⁶ --up to total number of instances
  AND    v2.row_number <= i2.temptblinstances + o.temptblinstances  --up to total number of instances
  AND    (v1.row_number = 0 OR v1.row_number > o.temptblinstances)⁷ --omit online temp tables
  AND    (v2.row_number = 0 OR v2.row_number > o.temptblinstances)  --omit online temp tables
  and    t1.owner = p.ownerid AND t1.table_name 
         = DECODE(r1.sqltablename,' ','PS_'||r1.recname,r1.sqltablename) 
         ||DECODE(v1.row_number,0,'',LTRIM(TO_NUMBER(v1.row_number)))⁸ --derive table table
  and    t2.owner = p.ownerid AND t2.table_name 
         = DECODE(r2.sqltablename,' ','PS_'||r2.recname,r2.sqltablename) 
         ||DECODE(v2.row_number,0,'',LTRIM(TO_NUMBER(v2.row_number)))  --derive table table
  AND    ABS(v1.row_number-v2.row_number)<=1⁹  --max variation in instance numbers
  ORDER BY id1, id2
) LOOP
  -----------123456789012345678901234567890
  l_name := 'XX_TREESEL.iDetVal.iDetVal'||i.id1||i.id2;¹⁰
  
sql_txt := q'[
INSERT INTO PS_TSEL_P_TAO17 (CHARTFIELD, CHARTFIELD_VALUE, PROCESS_INSTANCE) SELECT DISTINCT 'ACCOUNT', DV.ACCOUNT, 12345678
FROM PS_TSEL_R30_TAO17 A, PS_GL_ACCOUNT_TBL DV WHERE A.CHARTFIELD = 'ACCOUNT' AND A.PROCESS_INSTANCE = 12345678 AND DV.SETID 
= 'GLOBE' AND DV.EFFDT = (SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL B WHERE SETID = DV.SETID AND ACCOUNT = DV.ACCOUNT AND 
EFFDT <= TO_DATE('2025-01-31','YYYY-MM-DD')) AND DV.ACCOUNT >= A.RANGE_FROM_30 AND DV.ACCOUNT <= A.RANGE_TO_30
]';

h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[ALL_ROWS]',
q'[LEADING(@"SEL$1" "A"@"SEL$1")]',¹¹
q'[END_OUTLINE_DATA]');

sql_txt := REPLACE(sql_txt, l_table1,i.table_name1);¹²
sql_txt := REPLACE(sql_txt, l_table2,i.table_name2);
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
dbms_output.put_line(l_name||':'||:signature||':'||sql_txt);

for n in 1 .. h.count LOOP¹³
  if h(n) LIKE '%'||l_table1||'%' THEN
    h(n) := REPLACE(h(n), l_table1,i.table_name1);
    dbms_output.put_line(n||':'||h(n));
  end if;
  if h(n) LIKE '%'||l_table2||'%' THEN
    h(n) := REPLACE(h(n), l_table2,i.table_name2);
    dbms_output.put_line(n||':'||h(n));
  end if;
end loop;

BEGIN --drop profile if already exists
  DBMS_SQLTUNE.drop_SQL_PROFILE(name => l_name);
  EXCEPTION WHEN e_no_sql_profile THEN NULL;
END;

DBMS_SQLTUNE.IMPORT_SQL_PROFILE ( --create new profile
sql_text    => sql_txt,
profile     => h,
name        => l_name,
description => 'coe XX_TREESEL.iDetVal.iDetVal '||l_name||' '||:signature||'',
category    => 'DEFAULT',
validate    => TRUE,
replace     => TRUE,
force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
  END LOOP;
END;
/
WHENEVER SQLERROR CONTINUE
…
SPO OFF;
PRO
PRO coe_xfr_sql_profile_XX_TREESEL.iDetVal.iDetVal completed

Set CURRENT_SCHEMA to specify PeopleSoft owning schema, SYSADM.

For each temporary record in the original SQL statement, add pairs of variables to specify the name of PeopleSoft record and Oracle temporary table instance referenced in the statement. I could derive the record name from the table name, but it is easier just to hard-code it.

The SQL statement produces all combinations of temporary records that could appear in the SQL statement. I will put it in an implicit cursor loop, and then for each row returned, the script will create a SQL profile.

Common table expression V returns 100 rows, numbered 0 to 99. Irrespective of the number of temporary table instances specified in each Application Engine program, there can only be non-shared 99 table instances for each PeopleTools record, plus the shared instance (that doesn't have a suffix number).

PSTEMPTBLCNTVW returns the number of non-shared batch (i.e. not online) instances of each temporary record that needs to be built. This is in addition to the number of online temporary table instances.

The query will return a row for each instance of each temporary table up to the number of instances required by the application engines plus the number of online table instances, but not exceeding the 99 rows returned by CTE V.

Most Application Engines do not run online in the component processor, therefore there is no need to build SQL profiles on these instances. There are exceptions, such as some as the journal and voucher edit and post processes in Financials, in which case these criteria should be removed.

This expression joins the record and instance number to the table in the database. Instance 0 will be used to refer to the shared instance.

The instance numbers of the temporary records is permitted to vary by up to one in either direction. Thus the script generates profiles with instance 12 of one table, and instances 11 to 13 of the other. It is rare, but possible, for there to be any difference in instance numbers between tables. It is possible, but rarer for the difference to be greater than one.

SQL Profile names are limited to 30 characters. I have specified a meaningful name based on the Application Engine step, up to 26 characters, and then the two IDs (which can be up to 2 digits each).

In this example, I am not going to use the full set of hints in the captured profile. I just want to introduce a single leading hint.

I will substitute each table name in the SQL text with the specific table instance name.

Tables are generally referenced in hints via the row source alias. However, sometimes the table name appears in the hints, and must also be replaced with the specific table name. So I also work through all the hints in array h and substitute any table names that may be there. Indexes are not referenced by their names but by a list of indexed columns.

The result is a set of profiles for each possible SQL statement.

XX_TREESEL.iDetVal.iDetVal00:11943215885789639839:
INSERT INTO PS_TSEL_P_TAO (CHARTFIELD, CHARTFIELD_VALUE, PROCESS_INSTANCE) SELECT DISTINCT 'ACCOUNT', DV.ACCOUNT, 12345678 
FROM PS_TSEL_R30_TAO A, PS_GL_ACCOUNT_TBL DV WHERE A.CHARTFIELD = 'ACCOUNT' AND A.PROCESS_INSTANCE = 12345678AND DV.SETID 
= 'GLOBE' AND DV.EFFDT = (SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL B WHERE SETID = DV.SETID AND ACCOUNT = DV.ACCOUNT AND 
EFFDT <= TO_DATE('2025-01-31','YYYY-MM-DD')) AND DV.ACCOUNT >= A.RANGE_FROM_30 AND DV.ACCOUNT <= A.RANGE_TO_30

XX_TREESEL.iDetVal.iDetVal1717:15747497907378648788:
INSERT INTO PS_TSEL_P_TAO17 (CHARTFIELD, CHARTFIELD_VALUE, PROCESS_INSTANCE) SELECT DISTINCT 'ACCOUNT', DV.ACCOUNT, 12345678 
FROM PS_TSEL_R30_TAO17 A, PS_GL_ACCOUNT_TBL DV WHERE A.CHARTFIELD = 'ACCOUNT' AND A.PROCESS_INSTANCE = 12345678 AND DV.SETID 
= 'GLOBE' AND DV.EFFDT = (SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL B WHERE SETID = DV.SETID AND ACCOUNT = DV.ACCOUNT AND 
EFFDT <= TO_DATE('2025-01-31','YYYY-MM-DD')) AND DV.ACCOUNT >= A.RANGE_FROM_30 AND DV.ACCOUNT <= A.RANGE_TO_30

XX_TREESEL.iDetVal.iDetVal1718:12015611546030583918:
INSERT INTO PS_TSEL_P_TAO18 (CHARTFIELD, CHARTFIELD_VALUE, PROCESS_INSTANCE) SELECT DISTINCT 'ACCOUNT', DV.ACCOUNT, 12345678 
FROM PS_TSEL_R30_TAO17 A, PS_GL_ACCOUNT_TBL DV WHERE A.CHARTFIELD = 'ACCOUNT' AND A.PROCESS_INSTANCE = 12345678 AND DV.SETID 
= 'GLOBE' AND DV.EFFDT = (SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL B WHERE SETID = DV.SETID AND ACCOUNT = DV.ACCOUNT AND 
EFFDT <= TO_DATE('2025-01-31','YYYY-MM-DD')) AND DV.ACCOUNT >= A.RANGE_FROM_30 AND DV.ACCOUNT <= A.RANGE_TO_30

XX_TREESEL.iDetVal.iDetVal1817:14883898515022367531:
INSERT INTO PS_TSEL_P_TAO17 (CHARTFIELD, CHARTFIELD_VALUE, PROCESS_INSTANCE) SELECT DISTINCT 'ACCOUNT', DV.ACCOUNT, 12345678 
FROM PS_TSEL_R30_TAO18 A, PS_GL_ACCOUNT_TBL DV WHERE A.CHARTFIELD = 'ACCOUNT' AND A.PROCESS_INSTANCE = 12345678 AND DV.SETID 
= 'GLOBE' AND DV.EFFDT = (SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL B WHERE SETID = DV.SETID AND ACCOUNT = DV.ACCOUNT AND 
EFFDT <= TO_DATE('2025-01-31','YYYY-MM-DD')) AND DV.ACCOUNT >= A.RANGE_FROM_30 AND DV.ACCOUNT <= A.RANGE_TO_30
…

Note

If the number of non-shared instances of a table in an Application Engine is increased, you need to build add table instances with Application Designer.
If the number of online temporary instances is increased, you need to build additional instances for every temporary record in the database. If the number is reduced some tables will cease to be used, and they ought to be dropped.
If either the number of application engine table instances or online temporary table instances are changed, then you will need to rerun the script to create additional SQL profiles.

In the Cloud Performance is Instrumented as Cost - A Resource Plan for PeopleSoft

noreply@blogger.com (David Kurtz) — Mon, 02 Dec 2024 22:40:00 +0000

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

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

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

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

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

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

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

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

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

PeopleSoft PS/Query: Finding Users' Cartesian Joins

noreply@blogger.com (David Kurtz) — Tue, 26 Nov 2024 09:25:00 +0000

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

What is the Problem?

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

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

Finding Execution Plans with Cartesian Joins

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

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

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

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

There are two SQL statements for the same private query. XXX_GL_BJU run by user BXXXXXX that exhibited a Cartesian join.

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

The SQL statement and execution plan can be extracted from AWR using DBMS_XPLAN.DISPLAY_WORKLOAD_REPSITORY.

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

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

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

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

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

Line 2 of the execution plan reports a MERGE JOIN CARTESIAN operation that feeds into the SORT GROUP operation at line 1.

Plan hash value: 603930234

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

A profile of database time for that execution plan by event and plan line ID shows that most of the time is spent on line 1, sorting the output of the Cartesian product.

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

This query never finished because the Cartesian product was so large. The time recorded was spent in two executions that were eventually cancelled by system operators.

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

Finding PS/Queries Without Joins on Related Records

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

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

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

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

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

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

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

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

/

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

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

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

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

PeopleSoft PS/Query: Identify Long Running Queries (on Process Schedulers)

noreply@blogger.com (David Kurtz) — Mon, 25 Nov 2024 13:53:00 +0000

This is the first of a series in which I will share some of my PeopleSoft scripts, and explain how they work.

What is the Problem?

This tool is disliked and even feared by database administrators (DBAs) and system administrators, because it is easy for users to create poor queries, that either don't work as intended or can run for long periods, sometimes indefinitely, without even producing results. This can consume significant amounts of CPU.

Managing Queries Scheduled on the Process Scheduler

The PSQUERY application engine program runs queries on the process scheduler. Users should be encouraged to use this rather than running them online.

Queries run online via the PeopleSoft Internet Architecture (PIA) cannot be managed.

There is no limit to the number of queries that users can initiate concurrently.
The number that can actually execute concurrently is limited by the number of PSQRYSRV processes in each application server domain. Any additional requests will simply queue up in Tuxedo.
It is possible to set maximum execution times in the PeopleSoft configuration, on the ICQuery service on the PSQRYSRV server in the application server.

It is easier to manage and monitor the queries run in PSQUERY processes on the process scheduler. They don't put any load on the PIA, but they put load on the database.

A system-wide maximum number of concurrently executing instances of the application engine program can be set on the process definition.
A maximum number of concurrently executing instances of the application engine program per process scheduler can be set (by using a process class).
The application engine, or its process class, can be given a lower priority so that other queued processes are run in preference.

PS/Queries run either in the PIA or on the process scheduler can be mapped to low-priority consumer groups in an Oracle database resource manager plan so that they do not starve the rest of the system of CPU (see PeopleSoft DBA Blog: PSFT_PLAN: A Sample Oracle Database Resource Manager Plan for PeopleSoft).
A maximum run time, or maximum estimated run time, can be defined for a consumer group. If the limit is breached an Oracle error is raised: ORA-00040: active time limit exceeded - call aborted. In the PIA, the error message is simply presented to the user. The scheduled PSQUERY application engine process will terminate and the error will be logged. In both cases, the user has to recognise the error message and understand what it means. Otherwise, they will raise the issue with support.
The various methods of setting maximum execution time limits are quite blunt instruments. They are essentially one-size-fits-all approaches. Typically, some queries are expected to run for a long time, and then the limits must be set to accommodate them.

Queries Scheduled on the Process Scheduler

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

In this case, I am interested in

• the top 50 PS/Queries by cumulative execution

• with a cumulative execution time of over 5 minutes (300s)

• that were scheduled yesterday between 8am and 7pm

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

I now have a profile of top queries that I can use to direct further investigation.

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

Notes:

Some details have been redacted from this real-world example.
The result is not guaranteed to be completely accurate. A user might have reused a run control record and can only get the current value.
This and other scripts can be downloaded from GitHub davidkurtz/psscripts.

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

noreply@blogger.com (David Kurtz) — Fri, 04 Oct 2024 13:44:00 +0000

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

Introduction
What happens during SQL Parse? What is a 'hard' parse? What is a 'soft' parse? The additional overhead of a hard parse.
How to set CURSOR_SHARING for specific scheduled processes.
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

noreply@blogger.com (David Kurtz) — Wed, 02 Oct 2024 15:30:00 +0000

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

Introduction
What happens during SQL Parse? What is a 'hard' parse? What is a 'soft' parse? The additional overhead of a hard parse.
How to set CURSOR_SHARING for specific scheduled processes
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

Setting a session for a specific process run on the PeopleSoft process scheduler is straightforward. The first thing a process does is 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.

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.

REM gfc_jrnl_ln_gl_jedit2_trigger.sql
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?

noreply@blogger.com (David Kurtz) — Tue, 01 Oct 2024 14:15:00 +0000

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

Introduction
What happens during SQL Parse? What is a 'hard' parse? What is a 'soft' parse? The additional overhead of a hard parse.
How to set CURSOR_SHARING for specific scheduled processes.
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

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.

Cursor Sharing in Scheduled Processes: 1. Introduction

noreply@blogger.com (David Kurtz) — Mon, 30 Sep 2024 17:01:00 +0000

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

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:

Statements in the shared pool differ only in the values of literals
Response time is suboptimal because of a very high number of library cache misses.
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.
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

noreply@blogger.com (David Kurtz) — Mon, 29 Apr 2024 12:26:00 +0000

This article has been rewritten and is now part of a 4-part series about cursor sharing.

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

noreply@blogger.com (David Kurtz) — Thu, 11 Apr 2024 15:06:00 +0000

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

noreply@blogger.com (David Kurtz) — Mon, 11 Mar 2024 11:55:00 +0000

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.

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.

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:

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

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 at different times that cannot be satisfied by a single resource plan. In this 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?

noreply@blogger.com (David Kurtz) — Tue, 20 Feb 2024 12:05:00 +0000

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

noreply@blogger.com (David Kurtz) — Thu, 25 Jan 2024 14:38:00 +0000

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"

Prioritising Scheduled Processes by Operator ID/Run Control

noreply@blogger.com (David Kurtz) — Fri, 10 Nov 2023 11:13:00 +0000

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

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

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

Problem Statement

During an overnight batch, many nVision report books are scheduled to run on the Windows process schedulers by one of several specific batch operator IDs. Many more reports are scheduled than can run concurrently, so some execute while others queue. Inevitably, the reports have widely varying execution times. The maximum concurrency of the nVision report book (RPTBOOK) process definition has been set, and the Oracle database resource manager has also been configured, to prevent too many of these processes from overloading the database.

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

The next chart shows the processing time of each nVision report process. The blue bars run from when the started to when it ended, ordered by start time. The clear boxes below run from the time when it was requested to when it started, thus showing the period for which the processes were queued on a process scheduler, but were blocked because the maximum number of processes were already processing.

All these processes run with the same priority because they are the same process definition. Some long-running jobs execute earlier in the batch simply because they were scheduled earlier, but others that started later, run on beyond the end of the batch.

It would be better if the longest-running processes were executed earlier, irrespective of the order in which they were requested. Thus the shorter processes can run later as slots on the scheduler become free, and thus all processes should finished both closer together and earlier.

There is nothing delivered in the PeopleSoft process scheduler configuration that will let you assign different priorities to different executions of the same process. In PeopleSoft, only three priorities are defined PRCSPRIORITY (1=Low, 5=Medium, 9=High) on the process definition and the server category. These priorities are transferred to the process request queue (PSPRCSQUE.PRCSPRTY).

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

Solution

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

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

process_prioritisation_by_cumulative_runtime.sql - master script that creates metadata table and trigger and then:

nvision_prioritisation_by_cumulative_runtime.sql - example script to create a procedure to populate metadata for nVision batch.
gppdprun_prioritisation_by_cumulative_runtime.sql - example script to create a procedure to populate metadata for Payroll/Absence calculation batch.
process_prioritisation_by_cumulative_runtime_test.sql - test trigger by inserting dummy data into process queue table.

process_prioritisation_by_cumulative_runtime_report.sql - example of a report to compare median execution time with last execution time.

Metadata Table: PS_XX_GFCPRCSPRTY

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

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

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

Trigger Before Insert into PSPRCSQUE

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

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

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

Priority Metadata

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

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

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

REM nvision_prioritisation_by_cumulative_runtime.sql

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

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

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

end gfcprcspriority;
/
show errors

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

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

Metadata

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

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

A Test Script

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

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

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

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

Monitoring Script

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

Example Output

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

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

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

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

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

Monitoring Query

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

9 levels of Prioritisation

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

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

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