Introduction
Oracle 12c Release 2 New Features:
Controlling Memory allocation and Disk I/O on individual PDB.
In the previous release (12.1) oracle introduced Multitenant Database, allowing better management of resources and cost reduction.
On oracle 12c release 1 there was no easy way to control the amount of disk I/O and memory usage on individual PDB. As a result a “Noise Neighbor” could use lots of Memory and I/O impacting the performance of other PDB in the same instance.
In oracle 12c release 2 we can restrict the amount of memory and disk I/O on PDB Level, as well as creating resource plans based on performance profiles, which defines the resource management for groups of PDBs.
Requirements
Oracle 12c release 2 onward.
Container Database (Multitenant).
PDB Memory Parameters
The following parameters can be set at the PDB level.
DB_CACHE_SIZE: The minimum buffer cache size for the PDB. SHARED_POOL_SIZE: The minimum shared pool size for the PDB. PGA_AGGREGATE_LIMIT: The maximum PGA size for the PDB. PGA_AGGREGATE_TARGET: The target PGA size for the PDB. SGA_MIN_SIZE: The minimum SGA size for the PDB. SGA_TARGET: The maximum SGA size for the PDB.
These initialization parameters control the memory usage of PDBs only if the following conditions are met:
- The NONCDB_COMPATIBLE initialization parameter is set to false in the CDB root.
- The MEMORY_TARGET initialization parameter is not set or is set to 0 (zero) in the CDB root.
SQL> show parameter target PARAMETER_NAME TYPE VALUE ------------------------------------------------------------ ----------- ------------------------------------------ memory_max_target big integer 0 memory_target big integer 0 pga_aggregate_target big integer 200M sga_target big integer 800M SQL> show parameter NONCDB_COMPATIBLE PARAMETER_NAME TYPE VALUE ------------------------------------------------------------ ----------- ------------------------------------------ noncdb_compatible boolean FALSE
Set PDB as current Container
SQL> alter session set container = WINTPDB;
Set SGA_TARGET for the current PDB
SQL> alter system set sga_target=300M scope=both; Sistema alterado. SQL> show parameter sga_target PARAMETER_NAME TYPE VALUE ------------------------------------------------------------ ----------- ----------------------------- sga_target big integer 300M
I/O Parameters
The following parameters can be set at the PDB level.
MAX_IOPS : The maximum I/O operations per second for the PDB. MAX_MBPS : The maximum megabytes of I/O per second for the PDB.
View parameter on CDB level. (If set at the CDB level they become default values used on all PDBs)
SQL> show parameter max_iops PARAMETER_NAME TYPE VALUE ------------------------------------------------------------ ----------- ------------------------------- max_iops integer 0
Set PDB as current Container
SQL> alter session set container = WINTPDB;
Set MAX_IOPS for the current PDB
SQL> alter system set max_iops=100 scope=both; Sistema alterado. SQL> show parameter max_iops PARAMETER_NAME TYPE VALUE ------------------------------------------------------------ ----------- ----------------------------- max_iops integer 100
Monitoring Resources
The following views can be used to monitor PDB Resources (CPU, I/O, Parallel Execution, Memory).
V$RSRCPDBMETRIC : A single row per PDB, holding the last of the 1 minute samples. V$RSRCPDBMETRIC_HISTORY : 61 rows per PDB, holding the last 60 minutes worth of samples from the V$RSRCPDBMETRIC view. V$RSRC_PDB : Cumulative statistics since the CDB resource plan ws set. DBA_HIST_RSRC_PDB_METRIC : AWR snaphots, retained based on the AWR retention period.
Monitor I/O and Memory Usage for PDBs (Last 1 minute samples)
COLUMN pdb_name FORMAT A10
COLUMN begin_time FORMAT A21
COLUMN end_time FORMAT A21
SELECT r.con_id,
p.pdb_name,
r.begin_time,
r.end_time,
round(r.iops ,3) iops,
round(r.iombps ,3) iombps,
round(r.iops_throttle_exempt ,3) iops_throttle_exempt,
round(r.iombps_throttle_exempt ,3) iombps_throttle_exempt,
r.avg_io_throttle,
r.sga_bytes,
r.pga_bytes,
r.buffer_cache_bytes,
r.shared_pool_bytes
FROM v$rsrcpdbmetric r,
cdb_pdbs p
WHERE r.con_id = p.con_id
ORDER BY p.pdb_name;
DB Performance Profiles
In Oracle 12c Release 2 is possible to create a resource plan based on performance profiles which defines the resource management for groups of PDBs.
Each profile directive is made up of the following elements:
profile : The profile the directive relates to. shares : The proportion of the CDB resources available to the PDB. utilization_limit : The percentage of the CDBs available CPU that is available to the PDB. parallel_server_limit : The percentage of the CDBs available parallel servers (PARALLEL_SERVERS_TARGET initialization parameter) that are available to the PDB.
Creating CDB Resource Plan:
DECLARE l_plan VARCHAR2(30) := 'CDB_PLAN_TESTE'; BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; DBMS_RESOURCE_MANAGER.create_cdb_plan( plan => l_plan, comment => 'Profile Plan para Testes'); DBMS_RESOURCE_MANAGER.create_cdb_profile_directive( plan => l_plan, profile => 'gold', shares => 3, memory_min => 50); DBMS_RESOURCE_MANAGER.create_cdb_profile_directive( plan => l_plan, profile => 'silver', shares => 2, utilization_limit => 40, memory_limit => 25); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; /
Information about the available CDB resource plans can be queried using the DBA_CDB_RSRC_PLANS view.
col comments format a30 col plan format a30 col profile format a30 SELECT PLAN, PROFILE, SHARES, UTILIZATION_LIMIT, PARALLEL_SERVER_LIMIT, MEMORY_MIN, MEMORY_LIMIT, COMMENTS FROM DBA_CDB_RSRC_PLAN_DIRECTIVES WHERE PLAN = 'CDB_PLAN_TESTE';
View parameter on CDB level. (If set at the CDB level they become default values used on all PDBs)
SHOW PARAMETER RESOURCE_MANAGER_PLAN ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'CDB_PLAN_TESTE';
Setting performance profile on PDB
ALTER SESSION SET CONTAINER=WINTPDB; ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'CDB_PLAN_TESTE'; ALTER SYSTEM SET DB_PERFORMANCE_PROFILE=gold SCOPE=SPFILE; ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE; ALTER PLUGGABLE DATABASE OPEN; SHOW PARAMETER DB_PERFORMANCE_PROFILE;
References:
https://oracle-base.com/articles/12c/multitenant-disk-iops-mdps-resource-management-for-pdbs-12cr2
https://oracle-base.com/articles/12c/multitenant-memory-resource-management-for-pdbs-12cr2
https://oracle-base.com/articles/12c/multitenant-resource-manager-pdb-performance-profiles-12cr2
https://www.linkedin.com/pulse/oracle-12c-release-2-new-feature-controlling-memory-gavin-soorma/
