Using Attribute Clustering to Improve Compression, Response Time and CPU Consumption: 2. An Example

Attribute Clustering reorders data in a table so that similar data values are clustered together.  This can improve both basic and columnar compression, resulting in better response time and lower CPU consumption.

This is the second of a two-part blog post.

1. Introduction
2. Example and Test Results

An Example of Attribute Clustering

This test illustrates the potential benefits of attribute clustering (the scripts are available on GitHub). It simulates the fact table in a data warehouse, or in my use case the General Ledger table in a Financials system. The table will have 20 million rows. Each dimension column will randomly have one of 256 distinct values, padded to 8 characters.  In this case, the distribution of data values is skewed by the square root function.  The alternative commented section produces uniform data. 

create table t0(a varchar2(8 char), b varchar2(8 char), c varchar2(8 char), x number);
truncate table t0;
BEGIN
  FOR i IN 1..2 LOOP
    insert  /*+APPEND PARALLEL*/ into t0
    select  /*+PARALLEL*/
/*--------------------------------------------------------------------------------------------------------------
            rPAD(LPAD(LTRIM(TO_CHAR(FLOOR(dbms_random.value(0,255)),'XX')),2,'0'),8,'X') a
    ,       rPAD(LPAD(LTRIM(TO_CHAR(FLOOR(dbms_random.value(0,255)),'XX')),2,'0'),8,'X') b
    ,       rPAD(LPAD(LTRIM(TO_CHAR(FLOOR(dbms_random.value(0,255)),'XX')),2,'0'),8,'X') c
--------------------------------------------------------------------------------------------------------------*/
            rPAD(LPAD(LTRIM(TO_CHAR(FLOOR(SQRT(dbms_random.value(0,65535))),'XX')),2,'0'),8,'X') a
    ,       rPAD(LPAD(LTRIM(TO_CHAR(FLOOR(SQRT(dbms_random.value(0,65535))),'XX')),2,'0'),8,'X') b
    ,       rPAD(LPAD(LTRIM(TO_CHAR(FLOOR(SQRT(dbms_random.value(0,65535))),'XX')),2,'0'),8,'X') c
--------------------------------------------------------------------------------------------------------------*/
    ,       dbms_random.value(1,1e6)
    from dual connect by level <= 1e7;
    COMMIT;
  end loop;
end;
/
exec dbms_stats.gather_table_stats(user,'T0');

I will create an identical materialized view on that table

create table mv(a varchar2(8 char), b varchar2(8 char), c varchar2(8 char), x number);
create materialized view mv on prebuilt table enable query rewrite as select * from t0;

For each test, I can set different attributes and then fully refresh the materialized view in non-atomic mode.  The various attributes take effect as the materialized view is truncated and repopulated in direct-path mode.

truncate table MV drop storage;
--------------------------------------------------
rem set compression
--------------------------------------------------
--alter materialized view MV nocompress;
--alter materialized view MV compress;
alter materialized view MV compress for query low;
--------------------------------------------------
rem set in memory
--------------------------------------------------
alter table mv inmemory;
--------------------------------------------------
rem set clustering and number of clustering columns
--------------------------------------------------
alter table mv drop clustering;
--alter table mv add clustering by interleaved order (b);
alter table mv add clustering by interleaved order (b, c);
--alter table mv add clustering by interleaved order (b, c, a);
--------------------------------------------------
exec dbms_mview.refresh('MV',atomic_refresh=>FALSE);
exec dbms_inmemory.repopulate(user,'MV');

Then I can see how large the physical and In-Memory segments are.

select * from user_mviews where mview_name = 'MV';
select table_name, tablespace_name, num_rows, blocks, compression, compress_for, inmemory, inmemory_compression 
from user_tables where table_name IN('MV','T0');
select segment_name, segment_type, tablespace_name, bytes/1024/1024 table_MB, blocks, extents, inmemory, inmemory_compression
from user_Segments where segment_name IN('MV','T0');

with x as (
select segment_type, owner, segment_name, inmemory_compression, inmemory_priority
, count(distinct inst_id) instances
, count(distinct segment_type||':'||owner||'.'||segment_name||'.'||partition_name) segments
, sum(inmemory_size)/1024/1024 inmemory_mb, sum(bytes)/1024/1024 tablespace_Mb
from   gv$im_segments i
where segment_name = 'MV'
group by segment_type, owner, segment_name, inmemory_compression, inmemory_priority)
select x.*, inmemory_mb/tablespace_mb*100-100 pct from x
order by owner, segment_type, segment_name
/

I will use a simple test query to see how the performance changes

select b,c, count(a), sum(x) from t0 where b='2AXXXXXX' group by b,c fetch first 10 rows only;

I tested 

• Uniformly distributed data -v- skewed data
• Without table compression -v- basic compression -v- Hybrid Columnar Compression (HCC)
• No attribute clustering -v- interleaved clustering on 1, 2, and 3 columns

Table      Tablespace                                                                                         
Name       Name         NUM_ROWS     BLOCKS COMPRESS COMPRESS_FOR                   INMEMORY INMEMORY_COMPRESS
---------- ---------- ---------- ---------- -------- ------------------------------ -------- -----------------
MV         PSDEFAULT    20000000      60280 ENABLED  QUERY LOW                      ENABLED  FOR QUERY LOW    
T0         PSDEFAULT    20000000     150183 DISABLED                                DISABLED                  

                      Tablespace      Table                                                 
Segment Na Segment Ty Name               MB     BLOCKS    EXTENTS INMEMORY INMEMORY_COMPRESS
---------- ---------- ---------- ---------- ---------- ---------- -------- -----------------
MV         TABLE      PSDEFAULT       472.0      60416        130 ENABLED  FOR QUERY LOW    
T0         TABLE      PSDEFAULT     1,220.0     156160        203 DISABLED                  

                                                                                 In Memory Tablespace           
Segment Ty OWNER    Segment Na INMEMORY_COMPRESS INMEMORY  INSTANCES   SEGMENTS         MB         MB        PCT
---------- -------- ---------- ----------------- -------- ---------- ---------- ---------- ---------- ----------
TABLE      SYSADM   MV         FOR QUERY LOW     NONE              2          1      829.2      936.6 -11.4662752

With query rewrite on the materialized view and the materialized view in the In-Memory store, we see Oracle rewrite the query from the underlying table to the materialized view and then to an in-memory query.

select b,c, sum(x) from t0 where b='2AXXXXXX' group by b,c;

Plan hash value: 389206685
-----------------------------------------------------------------------------------------------
| Id  | Operation                              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |      |   182 |  7280 |   876  (31)| 00:00:01 |
|   1 |  HASH GROUP BY                         |      |   182 |  7280 |   876  (31)| 00:00:01 |
|*  2 |   MAT_VIEW REWRITE ACCESS INMEMORY FULL| MV   | 78125 |  3051K|   875  (31)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Test Results

Conclusions

• Without any table compression, attribute clustering does not affect the size of the table on the tablespace, but the size of the table in the In-Memory store is reduced, and query performance is improved.
• With either basic or Hybrid Columnar compression, attribute clustering reduces the size of the table both in the tablespace and in the in-memory store.
• All forms of compression and attribute clustering increase the duration of the materialized view refresh. Degradation of the refresh due to clustering was less severe with HCC than with either no compression or simple compression.
• I found that query performance degraded when using interleaved clustering in combination with simple compression although this resulted in a smaller in-memory segment than HCC, but performance improved with HCC.
• Uniform data compressed marginally better than skewed.  Otherwise, they produced very similar results.  
• You do not have to take advantage of compression on the physical segment to take advantage of the compression in In-Memory, but you may get better performance if you do.
• With this test data set, optimal performance was achieved when clustering on 2 dimension columns. When clustering on all three columns I obtained worse compression and query performance. This varies with the data. With real-world data, I have had examples with better compression and performance with the maximum of 4 clustering column groups. Generally, the best performance corresponds to the attribute clustering that gives the best columnar compression. This is not always the case for simple compression.

Using Attribute Clustering to Improve Compression, Response Time and CPU Consumption: 1. Introduction

Attribute Clustering reorders data in a table so that similar data values are clustered together.  This can improve both basic and columnar compression, resulting in better response time and lower CPU consumption.

This is the first of a two-part blog post.

1. Introduction
2. Example and Test Results

Use Case

I am working on a Financials system running on an engineered system.  It runs a daily batch of GL reports on summary ledgers that have unindexed materialized views.  The materialized views are also hybrid column compressed (HCC) to reduce their size and improve reporting performance.  

We also put the materialized views into the In-Memory store.  Initially, we used 'free' base-level In-Memory and worked within the 16Gb/instance limit.  Having moved to Exadata Cloud@Customer, we can use the fully licensed version of In-Memory.

Now I have introduced Attribute Clustering for the materialized views.

Attribute Clustering

Attribute Clustering has been available on Enterprise Edition since Oracle 12.1.0.2.  Data is clustered in close physical proximity according to certain columns.  Linear Ordering stores the data according to the order of the specified clustering columns.  Interleaved Ordering uses a Z-order curve to cluster data in multiple dimensions (this graphic is from Oracle's documentation).

The GL reports have multiple combinations of different predicates. Therefore, as recommended by Oracle, we used interleaved ordering.  Linear ordering is not suitable in this case because there is no single suitable order for each table.  Linear ordering also caused the runtime of the materialized view refresh to extend much more than interleaved ordering as it has to sort the data.

We have not introduced Zone Maps.  That is to say that after testing, we removed them.  Zone maps can be thought of as a coarse index of the zones in the attribute clustering, and would normally be expected to improve the access of the data.  You can see them being used in the execution plans to access the table both in the tablespace and in the In-Memory store.  However, our application dynamically generates a lot of SQL and therefore performs a lot of SQL parse.  We found that the additional work to process the zone map significantly degraded performance.

Attribute Clustering is not enforced for every DML operation. It only affects direct-path insert operations, data movement, or table creation. It is easy to implement it for segments that are already HCC, which also relies on direct-path operations.  The materialized views were created to introduce HCC, hence they are refreshed in non-atomic mode which truncates and repopulates them in direct-path mode.  Thus attribute clustering specified on the materialized views will be implemented as they refresh.

Historical, and therefore static, partitions in the ledger tables are marked for HCC, and we schedule an online rebuild to compress them.  Now, that will also apply attribute clustering.  This process could be automated with Automatic Storage Compression.

Compression

Simply by storing similar data values together, we obtained better compression from HCC.  The tables underlying the materialized views were smaller.  

In-Memory also uses columnar compression.  Attribute clustering produced a reduction in the size of segments in the In-Memory store.  If we were still working within the constraints of Base-Level In-Memory, we would have been able to store more segments in In-Memory.

We are using attribute clustering not to directly improve data access, but to harness a secondary effect, that of improved compression.  We are seeing a reduction in the runtime of the reports.  Most of the database time is already spent on the CPU (as most of the fact tables are in In-Memory), so this translates to a reduction in CPU consumption.  We can consider running more reports concurrently to complete the batch earlier.  We can also consider reducing the number of CPUs and therefore reduce cloud subscription costs.

The second part of this blog will show an example test script and results.

Database Constraints Enforced but not Validated for New Data Now, but Not Existing Data

Recently, while discussing a problem, somebody said to me 'I would like to make this column NOT NULL to stop [this problem] from occurring, but first I would need to go back and fix all the historical data'. 

In Oracle, a constraint that is enabled will apply during DML, so as you insert or update the data, the constraint is applied to the rows that are being updated. Only when a constraint is validated, does the database check that all the data in the table conforms to the constraint.  

See also Oracle 19c SQL Language Reference: Common SQL DDL Clauses: constraint enable clause.

If you create a constraint that is enforced, but not validated, you may be able to prevent a problem from getting worse while you fix the data you already have.

A Demonstration 

I will create a table with a unique constraint and two other columns that are NOT NULL.  I put some data in the tables.  Column B is null for some of the rows, but not for others.

create table t (a number, b number, c number, constraint t_pk primary key(A));

insert into t (a,b)
select level, CASE WHEN MOD(level,2)=1 then 1 end --B is null on alternate rows
from dual connect by level<=10;

select * from t;

         A          B          C
---------- ---------- ----------
         1          1           
         2                      
         3          1           
         4                      
         5          1           
         6                      
         7          1           
         8                      
         9          1           
        10                      

10 rows selected.

I would like to make B a NOT NULL column (to stop the application from writing an invalid value to the database), but cannot because I already have some invalid values in the database.

SQL> alter table t modify b not null ;

ORA-02296: cannot enable (SCOTT.) - null values found
02296. 00000 - "cannot enable (%s.%s) - null values found"
*Cause:    an alter table enable constraint failed because the table
           contains values that do not satisfy the constraint.
*Action:   Obvious

However, I can create the constraint with the NOVALIDATE option.

SQL> alter table t modify b not null novalidate;

Table T altered.

SQL> select constraint_name, search_condition_vc from user_constraints where table_name = 'T';

CONSTRAINT_NAME SEARCH_CONDITION_VC                                         
--------------- ------------------------------------------------------------
T_PK                                                                        
SYS_C00221684   "B" IS NOT NULL

Note that at the moment column B is not described as NOT NULL because although the constraint is enforced, it has not been validated.

SQL> desc t
Name Null?    Type   
---- -------- ------ 
A    NOT NULL NUMBER 
B             NUMBER 
C             NUMBER

If I try to add more rows where some of the data in column B is null, the constraint prevents it.

SQL> insert into t (a,b)
  2  select level+10, CASE WHEN MOD(level,2)=1 then 1 end 
  3  from dual connect by level<=10;

ORA-01400: cannot insert NULL into ("SCOTT"."T"."B")

I can set B column to a NOT NULL value, but I cannot set it back to null.

SQL> update t set b = 2 where a=2;

1 row updated.

SQL> update t set b = NULL where a=2;

ORA-01407: cannot update ("SCOTT"."T"."B") to NULL

I can successfully update a different column on a row where B is null and therefore does not meet the constraint.  I do not get an error because I have not updated it.  

SQL> SQL> update t set c = a;

10 rows updated.

Eventually, I will want to validate the constraint so that I know that B has a not null value in all the rows. But I can't do it while there are still some null values

SQL> select * from t;

         A          B          C
---------- ---------- ----------
         1          1          1
         2          2          2
         3          1          3
         4                     4
         5          1          5
         6                     6
         7          1          7
         8                     8
         9          1          9
        10                    10

SQL> DECLARE
  2    l_sql CLOB;
  3  BEGIN
  4    FOR I IN (select * from user_constraints where table_name = 'T' AND constraint_type ='C' 
  5              and validated != 'VALIDATED' and search_condition_vc = '"B" IS NOT NULL') LOOP
  6      l_sql := 'alter table '||i.table_name||' modify constraint '||i.constraint_name||' VALIDATE';
  7      dbms_output.put_line(l_sql);
  8      EXECUTE IMMEDIATE l_sql;
  9    END LOOP;
 10  END;
 11  /
alter table T modify constraint SYS_C00221684 VALIDATE

ORA-02293: cannot validate (SCOTT.SYS_C00221684) - check constraint violated
ORA-06512: at line 7
ORA-06512: at line 7
02293. 00000 - "cannot validate (%s.%s) - check constraint violated"
*Cause:    an alter table operation tried to validate a check constraint to
           populated table that had nocomplying values.
*Action:   Obvious

First I have to fix the data, and then I can validate the constraint

SQL> UPDATE t set b=a where b is null;

4 rows updated.

SQL> REM now validate the constraint
SQL> BEGIN
  2    FOR I IN (select * from user_constraints where table_name = 'T' AND constraint_type ='C' 
  3              and validated != 'VALIDATED' and search_condition_vc = '"B" IS NOT NULL') LOOP
  4      EXECUTE IMMEDIATE 'alter table '||i.table_name||' modify constraint '||i.constraint_name||' VALIDATE';
  5    END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

Only now that the new constraint has been validated is the column described as NOT NULL.

SQL> desc t
Name Null?    Type   
---- -------- ------ 
A    NOT NULL NUMBER 
B    NOT NULL NUMBER 
C             NUMBER

Prioritising Scheduled Processes by Operator ID/Run Control

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.

The Goal

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3. SUBORDINATE everything else to the above decision.

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

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

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

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

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

More Bang for your Buck in the Cloud with Resource Manager

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

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

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

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

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

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

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

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

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

A User Story

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

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

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

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

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

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

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

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

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

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

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

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

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

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

IDENTIFY the system's constraint(s).

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

SUBORDINATE everything else to the above decision.

ELEVATE the system's constraint(s).

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

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

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

The Resource Manager Plan

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

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

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

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

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

What if we decide to change the number of CPUs 

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

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

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

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

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

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

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

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

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

Querying the PeopleSoft Message Log with SQL

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

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

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

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

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

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

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

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

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

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

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

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

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

Investigating Unfamiliar PL/SQL with the Hierarchical Profiler

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

Example Problem

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

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

Querying the Profiler Tables

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

SELECT * 
FROM   dbmshp_runs
ORDER BY runid;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Purging PeopleTools Physical Cache Files after Database Restore or Database Flashback

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

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

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

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

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

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

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

UPDATE PSSTATUS
SET    LASTREFRESHDTTM = SYSDATE
/
COMMIT
/

Using SQL Profiles to Tackle High Parse Time and CPU Consumption

The Challenge of Dynamic SQL with Literals   

The following example is taken from a PeopleSoft General Ledger system. The SQL was generated by the nVision reporting tool (some literal values have been obfuscated).

SELECT L4.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT) 
FROM PS_XX_SUM_XXXXX_VW A, PSTREESELECT10 L4, PSTREESELECT10 L2 
WHERE A.LEDGER='X_UKMGT' 
AND A.FISCAL_YEAR=2022 AND A.ACCOUNTING_PERIOD=1 
AND L4.SELECTOR_NUM=415 AND A.CHARTFIELD3=L4.RANGE_FROM_10 
AND L2.SELECTOR_NUM=416 AND A.ACCOUNT=L2.RANGE_FROM_10 
AND (A.DEPTID BETWEEN '10000' AND '18999' OR
A.DEPTID BETWEEN '20000' AND '29149' OR A.DEPTID='29156' OR
A.DEPTID='29158' OR A.DEPTID BETWEEN '29165' AND '29999' OR A.DEPTID
BETWEEN '30000' AND '39022' OR A.DEPTID BETWEEN '39023' AND '39999' OR
A.DEPTID BETWEEN '40000' AND '49999' OR A.DEPTID BETWEEN '50000' AND
'59999' OR A.DEPTID BETWEEN '60000' AND '69999' OR A.DEPTID BETWEEN
'70000' AND '79999' OR A.DEPTID BETWEEN '80000' AND '89999' OR
A.DEPTID='29150' OR A.DEPTID=' ') 
AND A.CHARTFIELD1='0120413' 
AND A.CURRENCY_CD='GBP' 
GROUP BY L4.TREE_NODE_NUM

Plan hash value: 1653134809

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                   | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                            |                    |       |       |    27 (100)|          |       |       |        |      |            |
|   1 |  PX COORDINATOR                                             |                    |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)                                       | :TQ10006           |     1 |    29 |    27  (63)| 00:00:01 |       |       |  Q1,06 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                                            |                    |     1 |    29 |    27  (63)| 00:00:01 |       |       |  Q1,06 | PCWP |            |
|   4 |     PX RECEIVE                                              |                    |     1 |    29 |    27  (63)| 00:00:01 |       |       |  Q1,06 | PCWP |            |
|   5 |      PX SEND HASH                                           | :TQ10005           |     1 |    29 |    27  (63)| 00:00:01 |       |       |  Q1,05 | P->P | HASH       |
|   6 |       HASH GROUP BY                                         |                    |     1 |    29 |    27  (63)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
|   7 |        HASH JOIN                                            |                    |     1 |    29 |    27  (63)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
|   8 |         JOIN FILTER CREATE                                  | :BF0000            |     1 |    16 |    25  (68)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
|   9 |          PX RECEIVE                                         |                    |     1 |    16 |    25  (68)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
|  10 |           PX SEND HYBRID HASH                               | :TQ10003           |     1 |    16 |    25  (68)| 00:00:01 |       |       |  Q1,03 | P->P | HYBRID HASH|
|  11 |            STATISTICS COLLECTOR                             |                    |       |       |            |          |       |       |  Q1,03 | PCWC |            |
|  12 |             VIEW                                            | VW_GBC_10          |     1 |    16 |    25  (68)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  13 |              HASH GROUP BY                                  |                    |     1 |    67 |    25  (68)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  14 |               PX RECEIVE                                    |                    |     1 |    67 |    25  (68)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|  15 |                PX SEND HASH                                 | :TQ10002           |     1 |    67 |    25  (68)| 00:00:01 |       |       |  Q1,02 | P->P | HASH       |
|  16 |                 HASH GROUP BY                               |                    |     1 |    67 |    25  (68)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  17 |                  HASH JOIN                                  |                    |    60 |  4020 |    24  (67)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  18 |                   JOIN FILTER CREATE                        | :BF0001            |    60 |  3120 |    22  (73)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  19 |                    PX RECEIVE                               |                    |    60 |  3120 |    22  (73)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  20 |                     PX SEND HYBRID HASH                     | :TQ10000           |    60 |  3120 |    22  (73)| 00:00:01 |       |       |  Q1,00 | P->P | HYBRID HASH|
|  21 |                      STATISTICS COLLECTOR                   |                    |       |       |            |          |       |       |  Q1,00 | PCWC |            |
|  22 |                       PX BLOCK ITERATOR                     |                    |    60 |  3120 |    22  (73)| 00:00:01 |    29 |    29 |  Q1,00 | PCWC |            |
|  23 |                        MAT_VIEW REWRITE ACCESS INMEMORY FULL| PS_XX_SUM_XXXXX_MV |    60 |  3120 |    22  (73)| 00:00:01 |    29 |    29 |  Q1,00 | PCWP |            |
|  24 |                   PX RECEIVE                                |                    |   306 |  4590 |     2   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  25 |                    PX SEND HYBRID HASH                      | :TQ10001           |   306 |  4590 |     2   (0)| 00:00:01 |       |       |  Q1,01 | P->P | HYBRID HASH|
|  26 |                     JOIN FILTER USE                         | :BF0001            |   306 |  4590 |     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|  27 |                      PX BLOCK ITERATOR                      |                    |   306 |  4590 |     2   (0)| 00:00:01 |   416 |   416 |  Q1,01 | PCWC |            |
|  28 |                       TABLE ACCESS STORAGE FULL             | PSTREESELECT10     |   306 |  4590 |     2   (0)| 00:00:01 |   416 |   416 |  Q1,01 | PCWP |            |
|  29 |         PX RECEIVE                                          |                    |   202 |  2626 |     2   (0)| 00:00:01 |       |       |  Q1,05 | PCWP |            |
|  30 |          PX SEND HYBRID HASH                                | :TQ10004           |   202 |  2626 |     2   (0)| 00:00:01 |       |       |  Q1,04 | P->P | HYBRID HASH|
|  31 |           JOIN FILTER USE                                   | :BF0000            |   202 |  2626 |     2   (0)| 00:00:01 |       |       |  Q1,04 | PCWP |            |
|  32 |            PX BLOCK ITERATOR                                |                    |   202 |  2626 |     2   (0)| 00:00:01 |   415 |   415 |  Q1,04 | PCWC |            |
|  33 |             TABLE ACCESS STORAGE FULL                       | PSTREESELECT10     |   202 |  2626 |     2   (0)| 00:00:01 |   415 |   415 |  Q1,04 | PCWP |            |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$6240F0FF
  12 - SEL$B80655F7 / VW_GBC_10@SEL$9C8D6CC0
  13 - SEL$B80655F7
  23 - SEL$B80655F7 / PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6
  28 - SEL$B80655F7 / L2@SEL$1
  33 - SEL$6240F0FF / L4@SEL$1
… 

In my example, ASH sampled 276 different SQL IDs.  Each one was only executed once.  There may have been more statements, but ASH only persists one sample every 10s. Cumulatively, they consumed 2843 seconds of DB time in SQL hard parse.

                                                                                                                                          Plan
                                                                                          SQL Plan        Force Matching    SQL   Plan   Parse
 # OPRID        RUNCNTLID              ACTION                           SQL_ID          Hash Value             Signature    IDs  Execs    Secs Table Name
-- ------------ ---------------------- -------------------------------- ------------- ------------ --------------------- ------ ------ ------- ------------------ 
 1 NVISION      NVS_RPTBOOK_99         PI=9984520:UKGL999I:12345        01g5hvs91k4hn   1653134809   1995330195085985689    276    276    2843 PS_XX_SUM_XXXXX_MV 
… 

This is one of at least 276 different SQL statements that all have the same force-matching signature. The statements are essentially the same but differ in some of their literal values. That means that the database has to treat each one as a different SQL statement that must be fully parsed separately. 

SQL Parse involves checking the statement is syntactically correct, and that the user has permission to access the objects, then during the SQL optimization stage the optimizer decides how to execute the statement before it moves to row source generation. 

If the statement has been parsed previously and is still in the shared pool, Oracle can skip the optimization and row source generation stages. This is often called soft parse. 

SQL Optimization 

During the optimization stage, the optimizer calculates the 'cost' of different possible execution plans. Depending upon the SQL, the optimizer considers different table join orders, different table join methods, and different SQL transformations. The optimizer cost is an estimation of the time that it will take to execute a particular plan. The unit of cost is roughly equivalent to the duration of a single block read. More expensive plans are abandoned as they become more expensive than the cheapest known plan so far. Thus the 'cost-based' optimizer produces the cheapest plan. However, the process of optimization consumes time and CPU. 

If I write SQL that is executed many times with bind variables rather than literals, then I should avoid some hard parses and the associated CPU consumption. Oracle has always recommended using bind variables rather than literals to improve performance as well as protect against SQL injection. However, there are many applications that still use literals, particularly in dynamically generated SQL. Every statement has to be hard parsed, and the cumulative CPU consumption can start to become significant. PeopleSoft is one such application that does this in some areas of the product, but it is by no means an isolated example. 

Oracle produced a feature called Cursor Sharing. Literals in statements are automatically converted to bind variables. It can be very effective. It does reduce SQL parse, but can sometimes also produce undesirable side effects where the execution plan may not change as the bind variable values change. 

Hints 

Hints are directives to the optimizer. They tell it to do something or more generally not to do something else. If I were to add some optimizer hints to a statement that will produce the same, or a similar, execution plan, then the optimizer should do less work, consume less CPU, and less time coming to the same or similar conclusion. 

 For example, if I add a LEADING hint to force the optimizer to start with a particular object, that will reduce the number of join orders to be considered. 

• A two-table query has 2 possible join orders; a LEADING hint will reduce it to 1. 
• A three-table query has 6 possible join orders; a LEADING hint on a single table will reduce it to 2. 

Often, it is not possible to add hints directly to the code in the application because it is all dynamically generated inside a package, or it may not be desirable to alter third-party code. In my example, the SQL was generated by compiled code within the nVision reporting tool that I cannot alter. I can't use a SQL Patch because I would need a patch for every SQL_ID and I can't predicate the SQL_IDs. Instead, I can create a force-matching SQL profile that will match every statement with the same force-matching signature. 

N.B. SQL Profiles require the SQL Tuning pack licence. 

Example SQL Profile 

I don't have to use the full outline of hints from the execution plan, I have chosen to apply just a few. 

• LEADING(L2): I want the query to start with the dimension table PSTREESELECT10. This will result in a change to the execution plan 
• REWRITE: PS_XX_SUM_XXXXX_MV is a materialized view built on the view PS_XX_SUM_XXXXX_VW of an underlying summary ledger. Rewriting the SQL to use the materialized view is a cost-based decision. Oracle usually decides to rewrite it to use the materialized view, but I want to ensure that this always happens with this hint. 
• NO_PARALLEL: This query selects only a single accounting period, so it is only scanning a single partition, therefore I don't want to invoke a parallel query. 
• PX_JOIN_FILTER(PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6): The dimension table is equijoined to the fact table. Therefore, it is a good candidate for using a Bloom filter on the look-up fact table. This doesn't always happen naturally on this statement. I have had to use the query block name taken from the execution plan of the rewritten statement.  The query block name is stable, it is a hash value based on the object name and the operation.
  

The profile is then created with DBMS_SQLTUNE.IMPORT_SQL_PROFILE.

set serveroutput on
DECLARE
  l_sql_text CLOB;
  l_signature NUMBER;
  h       SYS.SQLPROF_ATTR;
…
BEGIN
…
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[NO_PARALLEL]',
q'[LEADING(L2)]',
q'[PX_JOIN_FILTER(PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6)]',
q'[REWRITE]',
q'[END_OUTLINE_DATA]');

l_signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(l_sql_text);

DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text    => l_sql_text,
profile     => h,
name        => 'NVS_UKGL999I_FUNC_ACEXP1',
category    => 'DEFAULT',
validate    => TRUE,
replace     => TRUE,
force_match => TRUE);
…
END;
/

This is the execution plan with the SQL Profile. The note confirms that a SQL profile was used. The hint report shows the hints from the SQL Profile.  

Note that the SELECTOR_NUM and CHARTFIELD1 predicates have changed.

SELECT L4.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT) 
FROM PS_XX_SUM_XXXXX_VW A, PSTREESELECT10 L4, PSTREESELECT10 L2 
WHERE A.LEDGER='X_UKMGT' 
AND A.FISCAL_YEAR=2023 AND A.ACCOUNTING_PERIOD=1 
AND L4.SELECTOR_NUM=433 AND A.CHARTFIELD3=L4.RANGE_FROM_10 
AND L2.SELECTOR_NUM=434 AND A.ACCOUNT=L2.RANGE_FROM_10 
AND (A.DEPTID BETWEEN '10000' AND '18999' OR
A.DEPTID BETWEEN '20000' AND '29149' OR A.DEPTID='29156' OR
A.DEPTID='29158' OR A.DEPTID BETWEEN '29165' AND '29999' OR A.DEPTID
BETWEEN '30000' AND '39022' OR A.DEPTID BETWEEN '39023' AND '39999' OR
A.DEPTID BETWEEN '40000' AND '49999' OR A.DEPTID BETWEEN '50000' AND
'59999' OR A.DEPTID BETWEEN '60000' AND '69999' OR A.DEPTID BETWEEN
'70000' AND '79999' OR A.DEPTID BETWEEN '80000' AND '89999' OR
A.DEPTID='29150' OR A.DEPTID=' ') 
AND A.CHARTFIELD1='0051001' 
AND A.CURRENCY_CD='GBP' 
GROUP BY L4.TREE_NODE_NUM

Plan hash value: 3033847137

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                    |       |       |   214 (100)|          |       |       |
|   1 |  SORT GROUP BY                             |                    |     5 |   400 |   214  (62)| 00:00:01 |       |       |
|   2 |   HASH JOIN                                |                    |  2347 |   183K|   213  (62)| 00:00:01 |       |       |
|   3 |    HASH JOIN                               |                    |  2347 |   153K|   210  (63)| 00:00:01 |       |       |
|   4 |     JOIN FILTER CREATE                     | :BF0000            |   306 |  4590 |     3   (0)| 00:00:01 |       |       |
|   5 |      PARTITION RANGE SINGLE                |                    |   306 |  4590 |     3   (0)| 00:00:01 |   434 |   434 |
|   6 |       TABLE ACCESS STORAGE FULL            | PSTREESELECT10     |   306 |  4590 |     3   (0)| 00:00:01 |   434 |   434 |
|   7 |     JOIN FILTER USE                        | :BF0000            | 26468 |  1344K|   206  (64)| 00:00:01 |       |       |
|   8 |      PARTITION RANGE SINGLE                |                    | 26468 |  1344K|   206  (64)| 00:00:01 |    42 |    42 |
|   9 |       MAT_VIEW REWRITE ACCESS INMEMORY FULL| PS_XX_SUM_XXXXX_MV | 26468 |  1344K|   206  (64)| 00:00:01 |    42 |    42 |
|  10 |    PARTITION RANGE SINGLE                  |                    |   202 |  2626 |     3   (0)| 00:00:01 |   433 |   433 |
|  11 |     TABLE ACCESS STORAGE FULL              | PSTREESELECT10     |   202 |  2626 |     3   (0)| 00:00:01 |   433 |   433 |
---------------------------------------------------------------------------------------------------------------------------------

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

   1 - SEL$38F8C49D
   6 - SEL$38F8C49D / L2@SEL$1
   9 - SEL$38F8C49D / PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6
  11 - SEL$38F8C49D / L4@SEL$1

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

   0 -  STATEMENT
         U -  NO_PARALLEL
…
   1 -  SEL$38F8C49D
           -  LEADING(L2)
           -  REWRITE

   9 -  SEL$38F8C49D / PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6
           -  PX_JOIN_FILTER(PS_XX_SUM_XXXXX_MV@SEL$CAD4EEF6)

Note
-----
…
   - SQL profile "NVS_UKGL999I_FUNC_ACEXP1" used for this statement

• The new execution plan does indeed start with the dimension table 
• The query was rewritten to use the materialized view 
• A Bloom filter was used on the materialized view that is now the fact table
• The NO_PARALLEL hint wasn't used because Oracle chose not to parallelise this statement anyway.

                                                                                 Plan
                                                                                          SQL Plan        Force Matching    SQL   Plan   Parse
 # OPRID        RUNCNTLID              ACTION                           SQL_ID          Hash Value             Signature    IDs  Execs    Secs Table Name
-- ------------ ---------------------- -------------------------------- ------------- ------------ --------------------- ------ ------ ------- ------------------ 
…
 1 NVISION      NVS_RPTBOOK_99         PI=9984933:UKGL278I:12345        03nwc4yy1r1r7   3033847137   1995330195085985689    138    138    1428 PS_XX_SUM_XXXXX_MV

Now just 1428s is spent on parse time. We only found 138 SQL IDs, but that is just because there are fewer ASH samples because it is taking less time. 

In this case, adding these hints with a SQL Profile has halved the time spent parsing this set of SQL statements.