Since its introductions Oracle Resource Manager has been a key tool to guarantee performance stability and predictability, regulating the access to the database resources.
In the era of extreme consolidation, the role of Oracle Resource Manager is more important then ever, and Oracle Multitenant has extended its functionalities.
In Oracle 12.1 the resource manager was capable to handle:
- Parallel server processes
The version 12.2 the following options have been introduced:
- I/O rate limits for PDBs (Not supported on Exadata enviroment where I/O Resource Manager exists)
- Memory Management
- Performance Profiles
- Resource Monitoring
How to limit a PDB I/O consumption
--Limiting the number of I/O per second. Alter System set MAX_IOPS = 9500 scope = BOTH; --Limiting the amount of MB per second. Alter System set MAX_MBPS = 70 scope = BOTH;
Optionally starting from Oracle 12.2 it is possible to limit the PDB memory utilization using the following parameters:
- SGA_Target – Max SGA size for PDB
- SGA_Min_Size – Amount of guaranteed SGA size for PDB.
- DB_Cache_Size – Amount of guaranteed Buffer Cache size for PDB.
- DB_Shared_Pool_Size – Amount of guaranteed Shared Pool size for PDB.
- PGA_Aggregate_Limit – Max PGA size for PDB.
- PGA_Aggregate_Target – Target PGA size for PDB.
The Profiles allows to create standard and common Resouce Profiles, based for example on the different SLA levels (platinum, gold, silver), to grant directly to the PDBs.
DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE( plan => 'PDB_Silver', profile => 'silver', shares => 2, utilization_limit => 30, parallel_server_limit => 20, memory_limit=> 30 );
The new system view V$RSRCPDBMETRIC can be used to monitor chargeback the I/O and memory consumpion.
SELECT r.CON_ID, p.PDB_NAME, r.IOPS,r.SGA_BYTES, r.SHARED_POOL_BYTES FROM V$RSRCPDBMETRIC r, CDB_PDBS p WHERE r.CON_ID = p.CON_ID;