Purging Standard and Unified Audit Data

Introduction

It is not uncommon for businesses to have policies to retain audit data for several years, but then it ought to follow that that data should be purged at the end of the retention period.  A regular purge process should be put in place, otherwise, audit data will accumulate.  However, it is all too common for the purge to be deferred, often indefinitely, until it becomes a problem.

I have been working on a system that has built up 12 years of audit data.  It was starting to consume significant space in the SYSAUX tablespace, and queries on the standard audit data had become slow.  We noticed that Oracle Enterprise Manager was regularly querying this data looking for recent failed logins.  This was performing so poorly that it had become a never-ending process.

This company has a stated requirement that audit data be retained for 7 years, but some can be purged after 2 years.  However, within the last 7 years, this production database has had different database IDs due to its upgrade history.  

Oracle delivers a PL/SQL package to manage audit purges.  However, this utility will only purge the current database ID, and it will only purge all of that data by a date.  It cannot selectively purge some data by other criteria.  Therefore, a custom purge utility that extends that package was required.  This post discusses that extension. 

We introduced

• Regular weekly purge jobs for all forms of audit, for all DBIDs.  They run every weekend when the system is quieter.
• This includes a custom purge job to delete some data after 2 years

General Recommendations

• Standard audit is deprecated from Oracle 19c and will be desupported in a future release.  Therefore, Oracle recommends that customers migrate from standard audit to Unified Audit.  Oracle provides a Traditional to Unified Audit Syntax Converter - Generate Unified Audit Policies from Current Traditional Audit Configuration (Doc ID 2909718.1). Note, that this will only migrate the audit configuration.  The standard audit data that has built up will still need to be purged according to the retention policy until it is all gone.
• Should any future database maintenance/upgrade activity cause the database ID to change, a new purge timestamp will be required for the new database ID, and an additional purge job will be required for the new legacy database ID.

Overview

Standard audit and Unified Audit (introduced in 12c) can be configured to monitor certain operations by database users.  Audit data is generally written to tables in the SYSAUX tablespace, but unified audit can be written to XML files outside the database.

• Standard audit data is written to SYS.AUD$.  It is exposed via DBA_AUDIT_TRAIL.  Oracle does not support partitioning this table, and users who have tried have reported errors.
• Unified audit data is written to AUDSYS.UNIFIED$AUD. It is exposed via UNIFIED_AUDIT_TRAIL. Unified Audit is enabled by default, so if you think you are only using standard audit, you are probably also writing some Unified Audit.  This table is interval range partitioned.  The default interval is calendar monthly, but can be altered.

Purging Audit

Audit Timestamps

Oracle's delivered audit management package DBMS_AUDIT_MGMT can purge various types of audit.  Each audit purge works on an individual database ID, the current database ID by default.  An audit purge timestamp is created for each type of audit, and for each DBID to be purged.  This is defined as a fixed date and not a retention period.  Audit data that is before the date is purged.  The audit timestamps must be updated before each purge to maintain the desired retention period.

The first step is to identify the DBIDs.  In my case, most of the data is standard audit.  I will simply query the number of rows and the range of dates for each DBID in the audit data.

select dbid, count(*), min(timestamp), max(timestamp)
from dba_audit_trail
group by dbid
order by 3
/

      DBID   COUNT(*) MIN(TIMESTAMP)      MAX(TIMESTAMP)
---------- ---------- ------------------- -------------------
1000000002   33606824 11/09/2012 00:06:42 20/08/2016 00:46:22
1000000003  241327475 20/08/2016 03:36:36 13/05/2023 04:47:20
1000000005   23320615 12/05/2023 23:54:18 06/01/2024 00:56:49
1000000006     134188 06/01/2024 01:05:29 04/03/2024 22:24:53
2000000008     282780 05/03/2024 03:39:39 02/05/2024 15:05:41

However, some unified audit is enabled by default, so both types of audit have to be purged.

select dbid, count(*), min(event_timestamp), max(event_timestamp)
from unified_audit_trail
group by dbid
order by 3
/

      DBID   COUNT(*) MIN(EVENT_TIMESTAMP MAX(EVENT_TIMESTAMP
---------- ---------- ------------------- -------------------
1000000004      28323 21/04/2023 10:09:39 21/04/2023 12:00:51
1000000005    1434181 12/05/2023 22:44:47 06/01/2024 00:56:49
1000000006    1700032 06/01/2024 01:05:29 04/03/2024 22:02:29
2000000008     263038 05/03/2024 03:12:27 07/05/2024 16:39:41

The following PL/SQL creates an audit timestamp for each database ID, for each type of audit.  The list of database IDs obtained above was then hard-coded in the following PL/SQL block.  Doing this dynamically can involve a long query on the audit data, so it is easier to hard code them.

If a database ID is not specified to DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP, a control record for the current database ID is created. The audit timestamp is set to 7 years ago because our policy is to purge any audit data older than 7 years.  Later, a database scheduler job will be created for each timestamp defined.

At the time of writing, it will be several years until the Unified Audit data is old enough to be purged, but if the purge jobs are not created now, nobody will remember to add them later!

declare
  k_standard_retention_years CONSTANT INTEGER NOT NULL := 7;
begin
  FOR i IN(
    with d (dbid) as (
          select TO_NUMBER(NULL) from dual
    union select 1000000001 from dual 
    union select 1000000002 from dual 
    union select 1000000003 from dual 
    union select 1000000004 from dual 
    union select 1000000005 from dual 
    union select 1000000006 from dual 
    )
    select dbid from d
  ) LOOP
    DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP
    (audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
    ,last_archive_time => ADD_MONTHS(sysdate,-12*k_standard_retention_years)
    ,database_id => i.dbid
    );
  END LOOP;

  FOR i IN(
    with d (dbid) as (
          select TO_NUMBER(NULL) from dual
    union select 1000000004 from dual --2023 unified only
    union select 1000000005 from dual --2023-24 --current prod +unified
    union select 1000000006 from dual --2024 +unified
    --union select 2000000008 from dual --2024 +unified --current PRT
    )
    select dbid from d
  ) LOOP
    DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP
    (audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED
    ,last_archive_time => ADD_MONTHS(sysdate,-12*k_standard_retention_years)
    ,database_id => i.dbid
    );
  end loop;
end;
/

As old database IDs are completely purged their last archive timestamps can be removed from the audit purge configuration in the database with DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP and the corresponding database jobs can be dropped.

Should any future database maintenance/migration/upgrade activity cause the database ID to change again, new timestamps must be set up for the new current database ID, and corresponding scheduler purge jobs must be created.

Custom Audit Purge Management Package

A custom package procedure XX_CUSTOM_AUDIT_PURGE has been created.  It is available on GitHub.  The package is deliberately created to be owned by the AUDSYS user that is used to run the default audit purge jobs.  That account is delivered by Oracle and is locked. It should not be unlocked.  Therefore, a SYSDBA privileged account must deploy the package.  

This package contains various procedures

• INIT_AUDIT_PURGE
• initialises the standard and fine-grained audit purges,
• creates a job resource constraint so the scheduler only runs one purge process at a time, and 
• creates a job class to assign the job to a resource plan consumer group so that the purge job runs at low priority.
• UPDATE_AUDIT_PURGE_TS updates the audit purge timestamps to 7 years ago, thus maintaining a rolling 7-year purge.
• CUSTOM_PURGE performs a limited purge of some audit data after 2 years.  Exactly which audit actions are to be purged is hard-coded in this procedure.
• CREATE_AUDIT_PURGE_JOBS uses DBMS_AUDIT_MGMT.CREATE_PURGE_JOB to create the various scheduler jobs to run the Oracle-delivered purge procedure DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL for the various audits to be purged. 
• However, this procedure alters the jobs to control their scheduler, concurrency and resource manager consumer group.
• CREATE_CUSTOM_PURGE_JOB creates a scheduler job to execute the CUSTOM_PURGE procedure.
• CREATE_UPDATE_AUDIT_PURGE_TS_JOB creates a job on the database scheduler to run UPDATE_AUDIT_PURGE_TS.

Some constants and defaults have been hard-coded in the package including

• The 2-year and 7-year purge rules.  
• Resource consumer group of the purge jobs
• Job Class for purge jobs to which resource consumer group is assigned.
• Number of rows to be deleted between commits during custom delete (2-year purge).

The audit purge requirements are unlikely to change, and if they do a code change is required to implement the change that must be installed by the core DBA.  I felt that this approach was easier and more secure than creating and controlling access to metadata.

After an initial large purge to clear the backlog, we find that these jobs only run for a few minutes each.  By scheduling them to run regularly, we maintain a relatively steady data volume.

Reclaiming Space after Initial Large Purge

The audit purge is no more than a simple delete.  Once deleted (and committed) the space freed up can be used by other rows of audit data, but space is not released from the audit tables back to the SYSAUX tablespace.

The only supported way to recover space from the purged audit tables is to rebuild them by moving them to a different tablespace, and if necessary, moving them back using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOC procedure again.

Create a tablespace SYSAUX_AUD.  It should be created as a BIGFILE tablespace as SYSAUX is also a BIGFILE tablespace.  Otherwise, in our case, it would have needed at least 3 data files (so it could expand to 96G).

The following PL/SQL block creates a database scheduler job for each call to this procedure so that it can run in the background.

• This time the jobs are one-time execution jobs that will be auto-dropped on completion.
• The same resource constraint XX_PURGE_RESOURCE, as used by the other purge jobs, ensures that it will not run concurrently with any of those jobs (nor vice versa).
• If the job start date is not set, the job will run as soon as it is enabled and no other job using the same resource is running.

DECLARE
  TYPE t_aud_types IS TABLE OF VARCHAR2(8);
  a_aud_types t_aud_types;
  l_job_name VARCHAR2(30);
  l_tablespace_name VARCHAR2(30) := 'SYSAUX_AUD';
  e_job_does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_job_does_not_exist, -27475);
BEGIN
  a_aud_types := t_aud_types('AUD_STD','UNIFIED') /*FGA_STD deliberately excluded*/;
  FOR i IN a_aud_types.FIRST..a_aud_types.LAST
  LOOP
    l_job_name := 'XX_AUDIT_TRAIL_LOC_'||a_aud_types(i);
    BEGIN
      dbms_scheduler.drop_job(job_name => l_job_name);
    EXCEPTION WHEN e_job_does_not_exist THEN NULL;
    END;
    dbms_scheduler.create_job
    (job_name => l_job_name
    ,job_type => 'PLSQL_BLOCK'
    ,job_action => 'BEGIN dbms_audit_mgmt.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_'||a_aud_types(i)||',audit_trail_location_value => '''||l_tablespace_name||''');  END;'
    ,start_date => SYSTIMESTAMP at time zone ('US/Eastern') 
    ,job_class => 'XX_PURGE_CLASS'
    ,auto_drop => TRUE
    ,enabled => FALSE);
    DBMS_SCHEDULER.set_resource_constraint --Only one purge job can run at any one time
    (object_name   => l_job_name
    ,resource_name => 'AUDSYS.XX_PURGE_RESOURCE'
    ,units         => 1);     
    dbms_scheduler.enable(l_job_name);
  END LOOP;     
END;
/

• NB: I specified the time zone for the database job because I am working in a different time zone to the database!

We have found that due to a bug, the unified audit table is not relocated to the new tablespace.  It is expected that this will be fixed in version 23ai. 

• Bug 34080691 - can't change aud$unified default table space using set_audit_trail_location on version 12.1.0.2 (Doc ID 34080691.8)
• See also Fundamental Security Part Eight – Unified Audit (https://chandlerdba.com/2024/01/15/fundamental-security-part-eight-unified-audit/)

Validate/Reinstate Privileges

Using SET_AUDIT_TRAIL_LOCATION will strip off any explicitly granted privileges on the audit tables. Ensure privileges on the standard audit tables are intact.  Otherwise, the custom purge will fail.

column grantee format a8
column owner format a8
column table_name format a20
column privilege format a20
column grantor format a8
select * from dba_tab_privs
where (owner,table_name) IN(('SYS','FGA_LOG$'),('SYS','AUD$'),('AUDSYS','AUD$UNIFIED'))
/

Minimum expected output

GRANTEE  OWNER    TABLE_NAME           GRANTOR  PRIVILEGE            GRA HIE COM TYPE                     INH
-------- -------- -------------------- -------- -------------------- --- --- --- ------------------------ ---
AUDSYS   SYS      AUD$                 SYS      DELETE               NO  NO  NO  TABLE                    NO 
AUDSYS   SYS      AUD$                 SYS      SELECT               NO  NO  NO  TABLE                    NO

TL;DR

If you collect audit data, then you should create a retention policy and schedule purge jobs to delete it after the retention period expires.  It is not unreasonable to keep audit data for several years.  The database ID may change in that time due to upgrade or migration.  Purge processes are also required for each legacy DBID.

After an initial large purge reclaims space by relocating the Standard Audit table, but remember to reinstate any explicitly granted privileges.  

Unified Audit cannot be relocated until a bug is resolved in 23ai.  

The performance of purge and query of standard audit will improve after doing this.

Enabling Cursor Sharing in PeopleSoft Processes

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

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

I have been working on a PeopleSoft Financials application that we have converted from a stand-alone database to be the only pluggable database (PDB) in an Oracle 19c container database (CDB).  We have been getting shared pool errors in the PDB that lead to ORA-4031 errors in the PeopleSoft application.  

I have written a longer version of this article on my Oracle blog, but here are the main points.

SGA Management with a Parse Intensive System (PeopleSoft).

PeopleSoft systems dynamically generate lots of non-shareable SQL code.  This leads to lots of parse and consumes more shared pool.  ASMM can respond by shrinking the buffer cache and growing the shared pool.  However, this can lead to more physical I/O and degrade performance and it is not beneficial for the database to cache dynamic SQL statements that are not going to be executed again.  Other parse-intensive systems can also exhibit this behaviour.

In PeopleSoft, I normally set DB_CACHE_SIZE and SHARED_POOL_SIZE to minimum values to stop ASMM shuffling too far in either direction.  With a large SGA, moving memory between these pools can become a performance problem in its own right.  

We removed SHARED_POOL_SIZE, DB_CACHE_SIZE and SGA_MIN_SIZE settings from the PDB.  The only SGA parameters set at PDB level are SGA_TARGET and INMEMORY_SIZE.  

SHARED_POOL_SIZE and DB_CACHE_SIZE are set as I usually would for PeopleSoft, but at CDB level to guarantee a minimum buffer cache size.  

This is straightforward when there is only one PDB in the CDB.   I have yet to see what happens when I have another active PDB with a non-PeopleSoft system and a different kind of workload that puts less stress on the shared pool and more on the buffer cache.

Initialisation Parameters

• SGA_TARGET "specifies the total size of all SGA components".  Use this parameter to control the memory usage of each PDB.  The setting at CDB must be at least the sum of the settings for each PDB.
• Recommendations:
• Use only this parameter at PDB level to manage the memory consumption of the PDB.
• In a CDB with only a single PDB, set SGA_TARGET to the same value at CDB and PDB levels.  
• Therefore, where there are multiple PDBs, SGA_TARGET at CDB level should be set to the sum of the setting for each PDB.  However, I haven't tested this yet.
• There is no recommendation to reserve SGA for use by the CDB only, nor in my experience is there any need so to do.
• SHARED_POOL_SIZE sets the minimum amount of shared memory reserved to the shared pool.  It can optionally be set in a PDB.  
• Recommendation: However, do not set SHARED_POOL_SIZE at PDB level.  It can be set at CDB level.
• DB_CACHE_SIZE sets the minimum amount of shared memory reserved to the buffer cache. It can optionally be set in a PDB.  
• Recommendation: However, do not set DB_CACHE_SIZE at PDB level.  It can be set at CDB level.
• SGA_MIN_SIZE has no effect at CDB level.  It can be set at PDB level at up to half of the manageable SGA
• Recommendation: However, do not set SGA_MIN_SIZE.
• INMEMORY_SIZE: If you are using in-memory query, this must be set at CDB level in order to reserve memory for the in-memory store.  The parameter defaults to 0, in which case in-memory query is not available.  The in-memory pool is not managed by Automatic Shared Memory Management (ASMM), but it does count toward the total SGA used in SGA_TARGET.
• Recommendation: Therefore it must also be set in the PDB where in-memory is being used, otherwise we found(contrary to the documetntation) that the parameter defaults to 0, and in-memory query will be disabled in that PDB.

Oracle Notes

• A-04031 on Multitenant Database with Excessive Amounts of KGLH0 and / or SQLA Memory and Parameter SHARED_POOL_SIZE or SGA_MIN_SIZE Set at the PDB Level (Doc ID 2590172.1) – December 2022, Updated April 2023
• This one says “Remove the PDB-level SHARED_POOL_SIZE and/or SGA_MIN_SIZE initialization parameters. The only SGA memory sizing parameter that Oracle recommends setting at the PDB level is SGA_TARGET.”

• About memory configuration parameter on each PDBs (Doc ID 2655314.1) – Nov 2023
• “As a best practice, please do not to set SHARED_POOL_SIZE and DB_CACHE_SIZE on each PDBs and please manage automatically by setting SGA_TARGET.”
• "This best practice is confirmed by development in Bug 30692720"
• Bug 30692720 discusses how the parameters are validated.  Eg. "Sum(PDB sga size) > CDB sga size"
• Bug 34079542: "Unset sga_min_size parameter in PDB."

Configuring Shared Global Area (SGA) in a Multitenant Database

I have been working on a PeopleSoft Financials application that we have converted from a stand-alone database to be the only pluggable database (PDB) in an Oracle 19c container database (CDB).  We have been getting ORA-4031 (unable to allocate shared memory) errors in the PeopleSoft application.  

It has taken a while to solve and test, and I have to acknowledge quite a lot of advice from my friends.  

If you are wondering why you should be involved with your local Oracle user group, and regularly attend their meetings, this is an example: So you can ask people who have experience of different systems in different situations that you haven't encountered yet!

Documentation

I am going to look at 6 initialisation parameters that control the use of SGA.  The Oracle documentation, even in 21c, suggests they can mostly be set at CDB and PDB levels.  However, more recent Oracle guidance confirmed by my own experience suggests that is not a good idea.

• SGA_MAX_SIZE can only be set at CDB level.  It sets the size of the shared memory segment that is the SGA.  It cannot be changed during the life of the database instance.  
• Recommendation:
• It can be useful to set it higher than SGA_TARGET if you plan either to increase SGA_TARGET, or add PDBs to the CDB, without restarting the instances.
• SGA_TARGET "specifies the total size of all SGA components".  Use this parameter to control the memory usage of each PDB.  The setting at CDB must be at least the sum of the settings for each PDB.
• Recommendations:
• Use only this parameter at PDB level to manage the memory consumption of the PDB.
• In a CDB with only a single PDB, set SGA_TARGET to the same value at CDB and PDB levels.  
• Therefore, where there are multiple PDBs, SGA_TARGET at CDB level should be set to the sum of the settings for each PDB.  However, I haven't tested this yet.
• There is no recommendation to reserve SGA for use by the CDB only, nor in my experience is there any need so to do.
• SHARED_POOL_SIZE sets the minimum amount of shared memory reserved to the shared pool.  It can optionally be set in a PDB.  
• Recommendation: However, do not set SHARED_POOL_SIZE at PDB level.  It can be set at CDB level.
• DB_CACHE_SIZE sets the minimum amount of shared memory reserved to the buffer cache. It can optionally be set in a PDB.  
• Recommendation: However, do not set DB_CACHE_SIZE at PDB level.  It can be set at CDB level.
• SGA_MIN_SIZE has no effect at CDB level.  It can be set at PDB level at up to half of the manageable SGA
• Recommendation: However, do not set SGA_MIN_SIZE.
• INMEMORY_SIZE: If you are using in-memory query, this must be set at CDB level in order to reserve memory for the in-memory store.  The parameter defaults to 0, in which case in-memory query is not available.  The in-memory pool is not managed by Automatic Shared Memory Management (ASMM), but it does count toward the total SGA used in SGA_TARGET.
• Recommendation: Therefore it must also be set in the PDB where in-memory is being used, otherwise we found (contrary to the documentation) that the parameter defaults to 0, and in-memory query will be disabled in that PDB.

Oracle Notes

There are a lot of Oracle support notes on the subject SGA management in a multi-tenant database.  The older nodes talk about setting memory parameters in the PDB, and a later note and a bug advises only setting these parameters at CDB level, and not at all in the PDB.

• How to Control and Monitor the Memory Usage (Both SGA and PGA) Among the PDBs in Multitenant Database- 12.2 New Feature (Doc ID 2170772.1) – December 2018, updated July 2023
• This document discusses setting SGA_MIN_SIZE at PDB level.
• The diagrams tend to suggest that there is a requirement to reserve some memory for the CDB SGA, but it does not suggest how it might be done.  Presumably by making sure the sum of SGA_TARGET for each PDB are less than SGA_TARGET at CDB level. The documentation states that SGA_MIN_SIZE has no effect at CDB level,

• How To Modify memory parameters On a Pluggable Database (PDB) (Doc ID 2706020.1) – September 2020 - Updated March 2024
• This note discusses setting SHARED_POOL_SIZE, DB_CACHE_SIZE, and MIN_SGA_SIZE 

• How to Control the SGA Memory Usage Among the CDB/PDBs in Multitenant Database. (Doc ID 2712535.1) – September 2020, Updated April 2023
• This note discusses setting SHARED_POOL_SIZE, DB_CACHE_SIZE and SGA_MIN_SIZE at PDB level.

• ORA-04031 on Multitenant Database with Excessive Amounts of KGLH0 and / or SQLA Memory and Parameter SHARED_POOL_SIZE or SGA_MIN_SIZE Set at the PDB Level (Doc ID 2590172.1) – December 2022, Updated April 2023
• This one says “Remove the PDB-level SHARED_POOL_SIZE and/or SGA_MIN_SIZE initialization parameters. The only SGA memory sizing parameter that Oracle recommends setting at the PDB level is SGA_TARGET.”

• About memory configuration parameter on each PDBs (Doc ID 2655314.1) – November 2023
• “As a best practice, please do not to set SHARED_POOL_SIZE and DB_CACHE_SIZE on each PDBs and please manage automatically by setting SGA_TARGET.”
• "This best practice is confirmed by development in Bug 30692720"
• Bug 30692720 discusses how the parameters are validated.  Eg. "Sum(PDB sga size) > CDB sga size"
• Bug 34079542: "Unset sga_min_size parameter in PDB."

SGA Management with a Parse Intensive System (PeopleSoft).

PeopleSoft systems dynamically generate lots of non-shareable SQL code.  This leads to lots of parse and consumes more shared pool.  ASMM can respond by shrinking the buffer cache and growing the shared pool.  However, this can lead to more physical I/O and degrade performance and it is not beneficial for the database to cache dynamic SQL statements that are not going to be executed again.  Other parse-intensive systems can also exhibit this behaviour.

In PeopleSoft, I normally set DB_CACHE_SIZE and SHARED_POOL_SIZE to minimum values to stop ASMM shuffling too far in either direction.  With a large SGA, moving memory between these pools can become a performance problem in its own right.  

We removed SHARED_POOL_SIZE, DB_CACHE_SIZE and SGA_MIN_SIZE settings from the PDB.  The only SGA parameters set at PDB level are SGA_TARGET and INMEMORY_SIZE.  We have found it is safe to reduce the setting of SGA_TARGET at PDB level, but reducing at CDB level without also restarting the instance has caused problems.

SHARED_POOL_SIZE and DB_CACHE_SIZE are set as I usually would for PeopleSoft, but only at CDB level to guarantee a minimum buffer cache size.  

This is straightforward when there is only one PDB in the CDB.   I have yet to see what happens when I have another active PDB with a non-PeopleSoft system and a different kind of workload that puts less stress on the shared pool and more on the buffer cache.

TL;DR

• Do not set any SGA parameter in a PDB other than SGA_TARGET and (if necessary) INMEMORY_SIZE.
• Do not set DB_CACHE_SIZE, SHARED_POOL_SIZE at PDB level.  They can be set at CDB level. 
• Do not set SGA_MIN_SIZE at either PDB or CDB level.

PSFT_PLAN: A Sample Oracle Database Resource Manager Plan for PeopleSoft

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

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

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

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

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

Resource Plan Design Goals

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

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

Consumer Groups

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

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

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

See also DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE

Consumer Group Mapping Priority

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

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

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

See also DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI

Required PeopleSoft Configuration

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

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

See also:

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

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

Consumer Group Mappings

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

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

See also DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING

Resource Plan Script

Two SQL scripts are available on GitHub

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

Other Options

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

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

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

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

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

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

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    'PSFT_PLAN', 'PSQUERY_BATCH_GROUP'
    ,mgmt_p6 => 1
    ,switch_group => 'CANCEL_SQL'
    ,switch_time => 14400
    ,switch_estimate => TRUE 
    ,switch_for_call => TRUE
    );

• Sometimes customers may have different priorities at different times that cannot be satisfied by a single resource plan.  In this case, different resource plans can be activated at different times by different scheduler windows. 

Other Online Resources

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